How Do I Revoke the Permission of a User If grant select on table t1 to public Has Been Executed on a Table

Symptom

Assume that there are two common users user1 and user2, and there are two tables t1 and t2 in the database. Run the following statement:

1

GRANT SELECT ON table t1 TO public;

user1 and user2 have the permission to access table t1. After user3 is created, user3 also has the permission to access table t1. Running the REVOKE SELECT on table t1 FROM user3; statement to revoke user3's permission to query table t1 does not take effect.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

testdb=# REVOKE SELECT ON table t1 FROM user3;
REVOKE
testdb=# \c - user3
Password for user user3: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb" as user "user3".
testdb=> SELECT * FROM t1;
 a 
---
(0 rows)

test=> SELECT relname, relacl FROM pg_class WHERE relname = 't1';
 relname |                    relacl                     
---------+-----------------------------------------------
 t1      | {user3=arwdDxt/user3,=r/user3}
(1 row)

Possible Causes

In the preceding problem, the revocation of user3's permission to access table t1 does not take effect because the GRANT SELECT ON table t1 TO public; statement has been executed. The keyword public in the statement indicates that the permission is granted to all roles, including the roles created later. Therefore, user3 has the permission to access the table. public can be regarded as an implicitly defined group that contains all roles.

Therefore, after REVOKE SELECT ON table t1 FROM user3; is executed, although user3 does not have the permission to access table t1 (you can view the permissions of table t1 in the relacl column in the pg_class system catalog), user3 still has the public permission. Therefore, user3 can still access the table.

Handling Procedure

You need to revoke the public permission of user3 and then separately manage and control its permission. However, after the public permission is revoked, users (user1 and user2) who could access the table may fail to do so, affecting services on the live network. Therefore, you need to run the grant command to grant the corresponding permissions to these users before revoking the public permission.

1. View all users.

   1 2 3 4 5 6 7 8 9

   SELECT * FROM pg_user WHERE usesysid >= 16384; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valbegin | valuntil | respool | parent | spacelimit | useconfig | nodegroup | tempspacelimit | spillspacelimit ---------+----------+-------------+----------+-----------+---------+----------+----------+----------+--------------+--------+------------+-----------+-----------+----------------+----------------- jack | 16408 | f | f | f | f | ******** | | | default_pool | 0 | | | | | tom | 16412 | f | f | f | f | ******** | | | default_pool | 0 | | | | | user1 | 16437 | f | f | f | f | ******** | | | default_pool | 0 | | | | | user2 | 16441 | f | f | f | f | ******** | | | default_pool | 0 | | | | | user3 | 16448 | f | f | f | f | ******** | | | default_pool | 0 | | | | | (5 rows)

2. Run the GRANT statement to grant permissions to the original user.

   1 2	GRANT select on table t1 TO jack,tom,user1,user2; GRANT

3. Revoke the public permission on the sample table t1.

   1	REVOKE select on table t1 FROM public;

4. Switch to user3 and query sample table t1. The result shows that user3's permission to access table t1 has been revoked successfully.

   1 2 3 4 5 6

   testdb=# \c - user3 Password for user user3: Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "testdb" as user "user3". testdb=> SELECT * FROM t1; ERROR: permission denied for relation t1