root=# CREATE TABLE xxx ( id SERIAL PRIMARY KEY , name CHAR(15) NOT NULL ); CREATETABLE root=# CREATEUSERtestPASSWORD'test'; CREATEROLE root=# GRANT ALL ON xxx TOtest; GRANT root=# DROPUSERtest; ERROR: role "test" cannot be dropped because some objects depend on it DETAIL: privileges for table xxx
可以通过下面这条语句查询PG中“test”用户的权限,并根据查询到的结果撤销之。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
root=# SELECT * FROM information_schema.table_privileges WHERE grantee='test'; grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy ---------+---------+---------------+--------------+------------+----------------+--------------+---------------- root | test | root | public | xxx | INSERT | NO | NO root | test | root | public | xxx | SELECT | NO | YES root | test | root | public | xxx | UPDATE | NO | NO root | test | root | public | xxx | DELETE | NO | NO root | test | root | public | xxx | TRUNCATE | NO | NO root | test | root | public | xxx | REFERENCES | NO | NO root | test | root | public | xxx | TRIGGER | NO | NO (7rows)
root=# REVOKE ALL ON xxx FROMtest; REVOKE root=# DROPUSERtest; DROPROLE root=#
批量撤销用户在数据库中所有表的权限:
1 2
revoke all ondatabase XX fromtest; revoke all on all tablesinschemapublicfromtest;