Help Center> Data Warehouse Service> Troubleshooting> Account, Password, and Permission> How Do I Revoke the Permission of a User If grant select on table t1 to public Has Been Executed on a Table
View PDF

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 use1 and use2, and there are two tables t1 and t2 in the database. Run the following statement:

1
 
grant select on table t1 to public;

Users use1 and use2 have the permission for accessing the table. After the user use3 is created, use3 also has the permission for accessing the table, and the revoke select on table t1 from use3 statement cannot be executed.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
 
test=# revoke select on table t1 from use3;
REVOKE
test=# \c - use3
Password for user use3: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "test" as user "use3".
test=> select * from t1;
 a 
---
(0 rows)

test=> select relname, relacl from pg_class where relname = 't1';
 relname |                    relacl                     
---------+-----------------------------------------------
 t1      | {liukunpeng=arwdDxt/liukunpeng,=r/liukunpeng}
(1 row)

Possible Causes

The grant select on table t1 to public SQL statement has been executed. The keyword public in the SQL statement indicates that the permission needs to be granted to all roles, including the roles created later. Therefore, the new user use3 also has the permission for accessing the table. public can be regarded as an implicitly defined group that contains all roles.

After the revoke select on table t1 from use3 statement is executed, user use3 still has the public permission for accessing the table although it does not have the permission for accessing the table (you can view the permission in the relacl column of the table).

Handling Procedure

You need to revoke the public permission of use3 and then separately manage and control its permission. However, after the public permission is revoked, users (use1 and use2) 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
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
 
test=# --View all users
test=# 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 |            |           |           |                | 
 use1    |    16437 | f           | f        | f         | f       | ******** |          |          | default_pool |      0 |            |           |           |                | 
 use2    |    16441 | f           | f        | f         | f       | ******** |          |          | default_pool |      0 |            |           |           |                | 
 use3    |    16448 | f           | f        | f         | f       | ******** |          |          | default_pool |      0 |            |           |           |                | 
(5 rows)
test=# --Grant permissions to the original user
test=# grant select on table t1 to jack,tom,use1,use2;
GRANT
test=# -- Revoke the public permission
test=# revoke select on table t1 from public;
test=# \c - use3
Password for user use3: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "test" as user "use3".
test=> select * from t1;
ERROR:  permission denied for relation t1