Skip to content

Assigning Database Permissions to PostgreSQL Users

Prerequisites§

Process§

  1. In the pgAdmin, connect to the database server:

    1. In the Object Explorer, expand Servers. Then click the required server.

    2. If password request appears, in the text field of that dialog, enter the password for the user which name appears in the title.

    3. Select Save Password to avoid the dialog in the future.
    4. Click OK.

  2. Add a new schema:

    1. In the Object Explorer menu, expand Servers → <your PostgreSQL> → Databases → <your database>.
    2. Right click Schemas, point to Create, and select Schema…

  3. Set up general schema settings:

    1. In the window that appears, go to the General tab.

    2. In the Name field, enter the name of the schema.

    3. From the Owner list, select the database owner.

  4. Assign created user accounts to the schema:

    1. In the window, go to the Security tab.

    2. On the tab, in the Privileges section, click Add row 

    3. In a new row, in the Grantee column, from the list, select one of created user.
    4. In the same row, in the Privileges column, in the list, select the USAGE check box.

  5. Repeat the previous step for the second created user.

  6. Access the database permission settings:

    1. In the window, go to the Default privileges tab.

    2. On the tab, go to the Tables sub-tab.

  7. Set up high-privileged user permissions:

    1. On the Tables sub-tab, click Add row 
    2. In a new row, in the Grantee column, from the list, select the high-privileged user account.
    3. In the same row, in the Privileges column, perform the following actions:
      1. Select the ALL check box.
      2. Clear the SELECT check box.

  8. Set up low-privileged user permissions:

    1. On the Tables sub-tab, click Add row 
    2. In a new row, in the Grantee column, from the list, select the low-privileged user account.
    3. In the same row, in the Privileges column, select the SELECT check box.

  9. In the window, click  Save.