pg数据库中如何删除一个用户

一般情况下直接执行 drop role xxx; 就可以把这个用户删除。但是很多时候会因为用户有依赖而报错。

1.如果不保留owner 的数据库对象，执行：

test123=# \c

You are now connected to database “test123” as user “postgres”

test123=# REASSIGN OWNED BY test TO test2;

REASSIGN OWNED

test123=# DROP OWNED BY test;

DROP OWNED

test123=# drop role test;

ERROR: ROLE “test” cannot be dropped because some objects depend on it ……….

2.如果保留owner 的数据库对象，执行：

test123=# REASSIGN OWNED BY test TO test;

REASSIGN OWNED

test123=# drop role test;

ERROR: ROLE “test” cannot be dropped because some objects depend on it ……….

注意：REASSIGN OWNED 需要执行者所属的role （或者子集）必须包含test 和postgres 或者是superuser。另外必须所有涉及到的数据库上都执行该以上语句才能删除用户。

mytest=# \c

You are now connected to database “mytest” as user “postgres”

mytest =# REASSIGN OWNED BY test TO test2;

REASSIGN OWNED

mytest =# DROP OWNED BY test;

DROP OWNED

mytest =# drop role test;

DROP ROLE

这样，所有涉及的数据库上都执行该以上语句才能删除用户。