openGauss/MogDB 删除用户之default privileges

ERROR

maleah_db=# drop user a ;
ERROR:  role "a" cannot be dropped because some objects depend on it
DETAIL:  privileges for default privileges on new relations belonging to role utest in schema utest
owner of default privileges on new relations belonging to role a in schema sc_test
owner of schema sc_test

回收权限

1）owner of schema sc_test

1）查看 sc_test

maleah_db=# \dn sc_test 
 List of schemas
  Name   | Owner 
---------+-------
 sc_test | a
(1 row)

2）REASSIGN OWNED

maleah_db=# REASSIGN OWNED BY a to b ;
REASSIGN OWNED
maleah_db=# \dn sc_test 
 List of schemas
  Name   | Owner 
---------+-------
 sc_test | b
(1 row)

3）再次执行

maleah_db=# drop user a ;
ERROR:  role "a" cannot be dropped because some objects depend on it
DETAIL:  privileges for default privileges on new relations belonging to role utest in schema utest
owner of default privileges on new relations belonging to role a in schema sc_test

2）privileges for default privileges …

 Schema | Type  | grantor | grantee | Access privileges 
--------+-------+---------+---------+-------------------
 utest  | table | utest   | a       | SELECT, ALTER
(1 row)

回收权限

maleah_db=# alter default privileges for user utest in schema utest revoke SELECT, ALTER on tables from a ;
ALTER DEFAULT PRIVILEGES

再次执行

maleah_db=# drop user a ;
ERROR:  role "a" cannot be dropped because some objects depend on it
DETAIL:  owner of default privileges on new relations belonging to role a in schema sc_test

3、owner of default privileges …

 Schema  | Type  | grantor | grantee |                                         Access privileges                                      
    
---------+-------+---------+---------+------------------------------------------------------------------------------------------------
----
 sc_test | table | a       | utest   | INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, ALTER, DROP, COMMENT, INDEX, VAC
UUM

回收权限

maleah_db=# alter default privileges for user a in schema sc_test revoke all privileges on tables from utest ;
ERROR:  permission denied for schema sc_test
DETAIL:  N/A

试想下报这个错误的原因？

maleah_db=# \dn+ sc_test 
                          List of schemas
  Name   | Owner | Access privileges | Description | WithBlockChain 
---------+-------+-------------------+-------------+----------------
 sc_test | b     | b=UC/b            |             | f
(1 row)

没错，其实是因为我们用户 a 对 sc_test 已经没有权限了

赋权：

maleah_db=# grant USAGE,CREATE on schema sc_test to a ;
GRANT
maleah_db=# \dn+ sc_test 
                          List of schemas
  Name   | Owner | Access privileges | Description | WithBlockChain 
---------+-------+-------------------+-------------+----------------
 sc_test | b     | b=UC/b           +|             | f
         |       | a=UC/b            |             | 
(1 row)

再次回收权限

maleah_db=# alter default privileges for user a in schema sc_test revoke all privileges on tables from utest ;
ALTER DEFAULT PRIVILEGES
maleah_db=# drop user a ;
ERROR:  role "a" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema sc_test

把我们刚刚赋权的schema权限回收，再次drop user成功

maleah_db=# alter default privileges for user a in schema sc_test revoke all privileges on tables from utest ;
ALTER DEFAULT PRIVILEGES
maleah_db=# drop user a ;
ERROR:  role "a" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema sc_test
maleah_db=# 
maleah_db=# revoke USAGE,CREATE on schema sc_test from a ;
REVOKE
maleah_db=# drop user a ;
DROP ROLE