#1.首先谈谈为什么会出现这种问题？
对于收悉postgres数据库的同学来说，postgres数据库本身没有drop user username cascade;这种命令格式，不同于mysql和oracle数据库来说，只要你确认并核实用户对象删除正确，就可以使用使用drop user username cascade直接删除。
#2.postgres数据库删除user命令方法
方法一：
执行：
REASSIGN OWNED BY [username] to [other_name]; //这里的username指的是你要删除的用户名，other_name指数据库其他用户；该语句含义是将username用户下的对象的Owner修改成other_name。
drop owned by [username]; //删除当前数据库中被指定 角色之一拥有的所有对象
drop user [username]; //删除用户
案例：
yzxdb=# create user test1 password 'XXXXXX';
CREATE ROLE
yzxdb=# grant role_yygl_yywh to test1;
GRANT ROLE
yzxdb=# \dn
List of schemas
Name | Owner
-------------+-------
dbms_lock | antdb
dbms_output | antdb
dbms_random | antdb
oracle | antdb
public | antdb
(5 rows)yzxdb=# create schema ngad_base;
CREATE SCHEMA
yzxdb=# alter schema ngad_base owner to test1;
ALTER SCHEMA
yzxdb=# \dn
List of schemas
Name | Owner
-------------+-------
dbms_lock | antdb
dbms_output | antdb
dbms_random | antdb
ngad_base | test1
oracle | antdb
public | antdb
(6 rows)
yzxdb=# \c yzxdb test1
You are now connected to database "yzxdb" as user "test1".
yzxdb=> \conninfo
You are connected to database "yzxdb" as user "test1" via socket in "/tmp" at port "5432".
yzxdb=> create table a(id int,b int);
CREATE TABLE
yzxdb=> insert into a values (1,2);
INSERT 0 1
yzxdb=>
yzxdb=> \d+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+------+-------+-------+-------------+------------+-------------
public | a | table | test1 | permanent | 8192 bytes |
(1 row)yzxdb=# REASSIGN OWNED BY test1 to antdb;
REASSIGN OWNEDyzxdb=> \d+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+------+-------+-------+-------------+------------+-------------
public | a | table | antdb | permanent | 8192 bytes |
(1 row)注：1.执行REASSIGN OWNED BY命令后我们发现对象的Owner列的属性已经改变。
2.如果你将要删除的用户，并且该用户下对象也不需要留，REASSIGN OWNED BY命令不用执行。
这里我们使用以下SQL来查看当前用户对象
select r.rolname as owner,n.nspname as schema,relname, case
when c.relkind = 'i' then 'index'
when c.relkind = 'm' then 'materialized view'
when c.relkind = 'r' then 'table'
when c.relkind = 'S' then 'sequence'
when c.relkind = 't' then 'TOAST'
when c.relkind = 'v' then 'view'
when c.relkind = 'p' then 'partitioned table'
when c.relkind = 'I' then 'partitioned index'
when c.relkind = 'f' then 'foreign table'
else concat(c.relkind,'')
end relkind_dec
from pg_class c inner join pg_namespace n
on c.relnamespace = n.oid
inner join pg_roles r
on c.relowner = r.oid
where 1=1
and r.rolname ='udonman'
order by owner,schema,relname;<elowner = r.oid^Jwhere 1=1^Jand r.rolname ='test1' ^Jorder by owner,schema,relname;
owner | schema | relname | relkind_dec
-------+--------+---------+-------------
test1 | public | a | tableyzxdb=# drop owned by test1;
DROP OWNED<.relowner = r.oid^Jwhere 1=1^Jand r.rolname ='test1' ^Jorder by owner,schema,relname;
owner | schema | relname | relkind_dec
-------+--------+---------+-------------
(0 rows)注意：我们看到对应对象已被删除
yzxdb=> \duS+ test1
List of roles
Role name | Attributes | Member of | Description
-----------+------------+------------------+-------------
test1 | | {role_yygl_yywh} |yzxdb=# drop user test1;
DROP ROLEyzxdb=> \duS+ test1
List of roles
Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
方法二：
通过对应的报错信息逐一进行权限收回
数据库权限：
postgres=# drop role udonman;
ERROR: role "udonman" cannot be dropped because some objects depend on it
DETAIL: owner of database udondb
postgres=#
postgres=# select d.datname,u.usename as owner,pg_encoding_to_char(d.encoding),s.spcname
postgres-# ,pg_tablespace_location(d.dattablespace) as "Location"
postgres-# ,pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
postgres-# from pg_database d inner join pg_user u on d.datdba = u.usesysid
postgres-# left outer join pg_tablespace s on d.dattablespace = s.oid
postgres-# order by d.oid;
datname | owner | pg_encoding_to_char | spcname | Location | Access privileges
-----------+----------+---------------------+------------+----------+-----------------------
template1 | postgres | UTF8 | pg_default | | =c/postgres +
| | | | | postgres=CTc/postgres
template0 | postgres | UTF8 | pg_default | | =c/postgres +
| | | | | postgres=CTc/postgres
postgres | postgres | UTF8 | pg_default | |
db | postgres | UTF8 | pg_default | |
udondb | udonman | UTF8 | pg_default | |
(5 行)
postgres=# alter database udondb owner to postgres;
ALTER DATABASE
postgres=# drop user udonman;
DROP ROLE
schema权限postgres=# drop user udonman;
ERROR: role "udonman" cannot be dropped because some objects depend on it
DETAIL: owner of schema udonman
postgres=# select catalog_name,schema_name,schema_owner
postgres-# from information_schema.schemata order by schema_name;
catalog_name | schema_name | schema_owner
--------------+--------------------+-------------------
postgres | information_schema | postgres
postgres | pg_catalog | postgres
postgres | pg_toast | postgres
postgres | public | pg_database_owner
postgres | udonman | udonman
(5 行)
postgres=# alter schema udonman owner to postgres;
ALTER SCHEMA
postgres=# drop user udonman;
DROP ROLE无法删除，因为它是对象的所有者
udondb=# drop user udonman;
ERROR: role "udonman" cannot be dropped because some objects depend on it
DETAIL: privileges for schema public
owner of table ex01
udondb=# select
udondb-# r.rolname as owner
udondb-# ,n.nspname as schema
udondb-# ,relname
udondb-# , case
udondb-# when c.relkind = 'i' then 'index'
udondb-# when c.relkind = 'm' then 'materialized view'
udondb-# when c.relkind = 'r' then 'table'
udondb-# when c.relkind = 'S' then 'sequence'
udondb-# when c.relkind = 't' then 'TOAST'
udondb-# when c.relkind = 'v' then 'view'
udondb-# when c.relkind = 'p' then 'partitioned table'
udondb-# when c.relkind = 'I' then 'partitioned index'
udondb-# when c.relkind = 'f' then 'foreign table'
udondb-# else concat(c.relkind,'')
udondb-# end relkind_dec
udondb-# from pg_class c inner join pg_namespace n
udondb-# on c.relnamespace = n.oid
udondb-# inner join pg_roles r
udondb-# on c.relowner = r.oid
udondb-# where 1=1
udondb-# and r.rolname ='udonman' -- オブジェクトオーナー
udondb-# order by owner,schema,relname
udondb-# ;
owner | schema | relname | relkind_dec
---------+--------+---------+-------------
udonman | public | ex01 | table
(1 行)--因为是ex01表的所有者所以不能删除
--更改表的所有者
udondb=# alter table public.ex01 owner to postgres;
ALTER TABLE--执行还是报错
udondb=# drop user udonman;
ERROR: role "udonman" cannot be dropped because some objects depend on it
DETAIL: privileges for schema public--通过查询pg_catalog.pg_namespace视图进行进一步查询
udondb=# select n.nspname as "schema",pg_catalog.pg_get_userbyid(n.nspowner) as "owner"
udondb-# ,n.nspacl
udondb-# from pg_catalog.pg_namespace n
udondb-# where n.nspname !~ '^pg_' and n.nspname <> 'information_schema'
udondb-# order by 1;
schema | owner | nspacl
--------+-------------------+--------------------------------------------------------------------------------------------
public | pg_database_owner | {pg_database_owner=UC/pg_database_owner,=U/pg_database_owner,udonman=UC/pg_database_owner}(1 行)
udondb=# revoke all on schema public from udonman;
REVOKE
udondb=# drop user udonman;
DROP ROLE
#总结：
1)方法一： 优点：方便操作，执行比较简单；缺点：不能很好了解哪些对象被删除，哪些对象被赋权给other_name，到时候使用还得重新排查梳理。
方法二：优点：能够很好了解被删除的对象和被赋权给其他用的对象；缺点：只能根据报错提示去一步一步来检查用户权限。通过revoke和alter方式来解决，比较费时。
2）在postgres数据库中，我们建议使用角色授权用户的方式来给用户授权，这种授权方式能有效解决方法二带来的这种困扰；postgres数据库排查权限比较费力，如果单独授权角色权限给用户的话，我们排查权限也比较省时省力。
2065
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
