某张表执行过grant select on table t1 to public，如何针对某用户回收权限

问题现象

假设当前有两个普通用户user1和user2，当前数据库testdb下有两张表t1和t2， 使用GRANT语句进行赋权：

1

GRANT SELECT ON table t1 TO public;

用户user1和user2对该表t1有访问权限，随后新建用户user3后，新用户user3对该表也有访问权限，且执行REVOKE SELECT on table t1 FROM user3;语句撤销user3查询t1表的权限不生效。

 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)

原因分析

上述问题中撤销user3对表t1的访问权限未生效是因为：之前执行过GRANT SELECT ON table t1 TO public;这条SQL语句，该语句中关键字public表示该权限要赋予给所有角色，包括之后新创建的角色，所以新用户user3对该表也有访问权限。public可以看做是一个隐含定义好的组，它包含所有角色。

因此，执行完REVOKE SELECT ON table t1 FROM user3;之后，虽然user3用户没有了表t1的访问权限（通过系统表pg_class的relacl字段可查看t1表的权限），但是他仍然有public的权限，所以仍能访问该表。

处理方法

撤销public的权限后对user3用户的权限单独管控。但是由于REVOKE回public的权限后可能导致原来能访问该表的用户（user1和user2）无法访问该表，影响正在使用的业务，因此需要先对这些用户执行GRANT赋予相应权限，然后REVOKE回public的权限。

1. 查看所有用户。

   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. 对原用户执行GRANT语句进行赋权。

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

3. 撤销示例表t1的public权限。

   1	REVOKE select on table t1 FROM public;

4. 切换至用户user3，再次查询示例表t1，结果显示user3访t1表的权限已成功撤销。

   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