postgresql-【运维相关】删用户

删除用户，用户拥有对象或权限

db1=# drop role user1;
ERROR:  role "user1" cannot be dropped because some objects depend on it
DETAIL:  privileges for column oid of table pg_proc
privileges for column proname of table pg_proc
......

方法一：权限转移

db1=# REASSIGN OWNED BY user1 TO user4;
REASSIGN OWNED

方法二：删除所有依赖此用户的对象、权限

db1=# drop OWNED BY user1;
DROP OWNED

方法三：回收权限

revoke ALL ON ALL TABLES in schema public FROM user1;
revoke ALL ON ALL FUNCTIONS in schema public FROM user1;
revoke ALL ON ALL SEQUENCES in schema public FROM user1;

拓展一点点~~

1、删之前最好nologin一段时间

2、删之前确认下此用户下有哪些表：

pg_tables where  tableowner = ''

3、有哪些权限：

information_schema.table_privileges