How Do I Grant Table Permissions to a User?

Syntax

1
2
3
4
5
6

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | ANALYZE | ANALYSE } [, ...] 
      | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
       | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] 
    [ WITH GRANT OPTION ];

Scenario

Assume there are users u1, u2, u3, u4, and u5 and five schemas named after these users. Their permission requirements are as follows:

• User u2 is a read-only user and requires the SELECT permission for the u1.t1 table.
• User u3 requires the SELECT permission for the u1.t1 table.
• User u3 requires the UPDATE permission for the u1.t1 table.
• User u5 requires the full permissions for the u1.t1 table.

1

GRANT SELECT ON u1.t1 TO u2;

1

GRANT INSERT ON u1.t1 TO u3;

1

GRANT SELECT,UPDATE ON u1.t1 TO u4;  

1

GRANT ALL PRIVILEGES ON u1.t1 TO u5;

Procedure

Perform the following steps to grant and verify permissions:

1. Connect to your database as dbadmin. Run the following statements to create users u1 to u5. Five schemas will be created and named after the users by default.

   1 2 3 4 5

   CREATE USER u1 PASSWORD '{password}'; CREATE USER u2 PASSWORD '{password}'; CREATE USER u3 PASSWORD '{password}'; CREATE USER u4 PASSWORD '{password}'; CREATE USER u5 PASSWORD '{password}';

2. Create table u1.t1 in schema u1.

   1	CREATE TABLE u1.t1 (c1 int, c2 int);

3. Insert two records to the table.

   1 2	INSERT INTO u1.t1 VALUES (1,2); INSERT INTO u1.t1 VALUES (1,2);

4. Grant schema permissions to users.

   1	GRANT USAGE ON SCHEMA u1 TO u2,u3,u4,u5;

5. Grant user u2 the permission to query the u1.t1 table.

   1	GRANT SELECT ON u1.t1 TO u2;

6. Start a new session and connect to the database as user u2. Verify that user u2 can query the u1.t1 table but cannot write to or modify the table.

   1 2 3

   SELECT * FROM u1.t1; INSERT INTO u1.t1 VALUES (1,20); UPDATE u1.t1 SET c2 = 3 WHERE c1 =1;

   

7. In the session started by user dbadmin, grant permissions to users u3, u4, and u5.

   1 2 3

   GRANT INSERT ON u1.t1 TO u3; -- Allow u3 to insert data. GRANT SELECT,UPDATE ON u1.t1 TO u4; -- Allow u4 to modify the table. GRANT ALL PRIVILEGES ON u1.t1 TO u5; -- Allow u5 to query, insert, modify, and delete table data.

8. Start a new session and connect to the database as user u3. Verify that user u3 can query the u1.t1 table but cannot query or modify the table.

   1 2 3

   SELECT * FROM u1.t1; INSERT INTO u1.t1 VALUES (1,20); UPDATE u1.t1 SET c2 = 3 WHERE c1 =1;

   

9. Start a new session and connect to the database as user u4. Verify that user u4 can modify and query the u1.t1 table, but cannot insert data to the table.

   1 2 3

   SELECT * FROM u1.t1; INSERT INTO u1.t1 VALUES (1,20); UPDATE u1.t1 SET c2 = 3 WHERE c1 =1;

   

10. Start a new session and connect to the database as user u5. Verify that user u4 can query, insert, modify, and delete data in the u1.t1 table.

    1 2 3 4

    SELECT * FROM u1.t1; INSERT INTO u1.t1 VALUES (1,20); UPDATE u1.t1 SET c2 = 3 WHERE c1 =1; DELETE FROM u1.t1;

    

11. In the session started by user dbadmin, execute the has_table_privilege function to query user permissions.

    1	SELECT * FROM pg_class WHERE relname = 't1';

    Check the relacl column in the command output. rolename=xxxx/yyyy indicates that rolename has the xxxx permission on the table and the permission is obtained from yyyy.

    The following figure shows the command output.

    

    • u1=arwdDxtA/u1 indicates that u1 is the owner and has full permissions.
    • u2=r/u1 indicates that u2 has the read permission.
    • u3=a/u1 indicates that u3 has the insert permission.
    • u4=rw/u1 indicates that u4 has the read and update permissions.
    • u5=arwdDxtA/u1 indicates that u5 has full permissions.