postgresql用户管理之删除用户操作 -m6米乐安卓版下载

postgresql删除用户命令：

drop user [ if exists ] name [, ...]
或
drop role [ if exists ] name [, ...]

drop user/drop role命令，并没有提供casecade关键字，原因在于：数据库用户可能在多个数据库下拥有对象或权限，但是一个sql语句只能影响当前数据库下的对象。

当执行删除用户操作时可能会遇到如下报错：

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
......

这时，就需要先将用户下的对象和权限全部清理掉，才能正常删除用户。

下面介绍两种能够简化清理被删除用户下的对象和权限的方法:

方法一：通过角色继承方式赋予给用户的权限，在删除用户时，不需要额外的回收权限操作。

角色继承是将现在以及将来某个角色所具有的权限授予其它角色。它非常适用于给多个用户赋予相同的一组权限的场景。
一个角色可以继承其他角色的权限从而成为其成员角色 （member role）；拥有成员角色的角色称为组角色 （group role）。

grant role_name [, ...] to role_name [, ...] [ with admin option ]

测试：

##创建待删除用户user4
db1=# create user user4 password 'xxxxxx';
create role
##给user4用户赋予db1数据库schema的owner权限，并使用user4用户在postgres数据库下创建表test1.
db1=# create schema user4 authorization user4;
create schema
db1=# \c postgres user4
you are now connected to database "postgres" as user "user4".
postgres=> create table test1 (id int);
create table
##创建组角色role1，并赋予其omm2模式的所有权限，和表omm2.ttt的所有权限
postgres=> \c - postgres
you are now connected to database "postgres" as user "postgres".
postgres=# create role role1;
create role
postgres=# grant all on schema omm2 to role1;
grant
postgres=# grant all on table omm2.ttt to role1;
grant
##将角色role1的权限赋给user4
postgres=# grant role1 to user4;
grant role
##使用user4就可以查看表omm2.ttt中的数据了
postgres=# \c - user4
you are now connected to database "postgres" as user "user4".
postgres=> select * from omm2.ttt;
 id1 | id2 | name  
----- ----- -------
   1 |   1 | test1
(1 row)
##此时删除用户user4,删除操作报错user4用户拥有表test1,以及在db1数据库下存在1个对象
postgres=> \c - postgres
you are now connected to database "postgres" as user "postgres".
postgres=# drop user user4;
error:  role "user4" cannot be dropped because some objects depend on it
detail:  owner of table test1
1 object in database db1
##删除user4拥有的对象后，user4用户就被删除了。
postgres=# drop table public.test1;
drop table
postgres=# \c db1
you are now connected to database "db1" as user "postgres".
db1=# drop schema user4;
drop schema
db1=# drop user user4;
drop role
db1=#
##到这里我们看到在删除用户的时候并不需要额外处理用户通过继承role1而间接拥有的omm2.ttt表的权限，这就是角色继承给删除用户操作带来的方便

注：如果user4用户的系统权限为noinherit,那么user4不会自动继承他所拥有的其他角色的权限，需要使用set role 命令来显示的继承角色权限。系统权限如createdb和createrole等不会被自动继承，只能通过set role命令显示的继承。

方法二：使用ressign owned / drop owned命令来批量转移用户对象/删除用户对象及权限

reassign owned：

转移当前角色拥有的数据库对象，只转移当前数据库的对象，多个数据库分别进行reassign。

reassign owned by { old_role | current_user | session_user } [, ...]
               to { new_role | current_user | session_user }

如果待删除用户下存在需要保留的数据库对象，可以使用ressign owned命令，把当前数据库下该用户的所有数据库对象转移给其他用户。

drop owned：

删除角色所拥有的所有对象并且收回该已经授予给该角色的在其他对象上的特权。只影响当前数据库的对象，多个数据库分别进行drop owned。角色所拥有的数据库、表空间将不会被移除。

drop owned by { name | current_user | session_user } [, ...] [ cascade | restrict ]

如果待删除用户在当前数据库下的所有对象都需要删除，就可以使用 drop owned命令，把当前数据库下该用户的所有数据库对象（数据库和表空间除外，需要单独处理）和权限删除掉。

测试1：

##删除用户user1时报错，待删除用户在当前数据库下仍拥有数据库对象和权限
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
......
privileges for column proacl of table pg_proc
privileges for database db1
owner of database db2
owner of schema user1
owner of table user1.t1
owner of table user1.t2
privileges for function f_inv()
##使用ressign owned 命令将当前数据库对象转移到user4用户下
db1=# reassign owned by user1 to user4;
reassign owned
db1=# \dt user1.*
       list of relations
 schema | name | type  | owner
-------- ------ ------- -------
 user1  | t1   | table | user4
 user1  | t2   | table | user4
(2 rows)
##再次尝试删除用户user1，提示user1还拥有对象权限
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
.....
privileges for column proacl of table pg_proc
privileges for database db1
privileges for function f_inv()
##使用drop owner 命令删除用户user1在当前数据库下的用户权限
db1=# drop owned by user1;
drop owned
##再次删除user1,成功
db1=# drop role user1;
drop role

测试2：

##删除用户user1，失败，提示user1在多个数据库下存在对象和权限
postgres=# drop user user1;
error:  role "user1" cannot be dropped because some objects depend on it
detail:  privileges for tablespace pg_default
privileges for tablespace pg_global
owner of database db1
owner of tablespace tbs1
owner of table tp1
16 objects in database db1
##使用drop owner 命令删除用户user1在当前数据库下的用户权限
postgres=# drop owned by user1;
drop owned
##再次尝试删除用户user1，提示user1在db1数据库下还拥有对象，且还拥有表空间和数据库
postgres=# drop user user1;
error:  role "user1" cannot be dropped because some objects depend on it
detail:  owner of database db1
owner of tablespace tbs1
16 objects in database db1
###登录到db1数据库
postgres=# \c db1 postgres
you are now connected to database "db1" as user "postgres".
##使用drop owner 命令删除用户user1在db1数据库下的用户权限,提示待删除对象具有关联对象，可以使用casecade关键字，级联删除关联对象
db1=# drop owned by user1;
error:  cannot drop desired object(s) because other objects depend on them
detail:  column postal of table user2.us_snail_addy depends on type user1.us_postal_code
column status of table user2.bug depends on type user1.bug_status
server rhnsrv depends on foreign-data wrapper postgres_fdw
user mapping for user2 on server rhnsrv depends on server rhnsrv
user mapping for user3 on server rhnsrv depends on server rhnsrv
foreign table stu depends on server rhnsrv
foreign table test depends on server rhnsrv
foreign table tp1 depends on server rhnsrv
foreign table tp3 depends on server rhnsrv
hint:  use drop ... cascade to drop the dependent objects too.
##确认相关对象可以被删除，使用带有cascade关键字的drop owned命令，成功删除user1关联数据库对象
db1=# drop owned by user1 cascade;
notice:  drop cascades to 9 other objects
detail:  drop cascades to column postal of table user2.us_snail_addy
drop cascades to column status of table user2.bug
drop cascades to server rhnsrv
drop cascades to user mapping for user2 on server rhnsrv
drop cascades to user mapping for user3 on server rhnsrv
drop cascades to foreign table stu
drop cascades to foreign table test
drop cascades to foreign table tp1
drop cascades to foreign table tp3
drop owned
##再次尝试删除用户user1，提示user1还拥有表空间和数据库
db1=# drop user user1;
error:  role "user1" cannot be dropped because some objects depend on it
detail:  owner of database db1
owner of tablespace tbs1
## 使用ressign owned转移db1和tbs1给user2用户
db1=# reassign owned by user1 to user2;
reassign owned
##用户拥有的对象和权限都清理完毕后，删除用户命令成功完成
db1=# drop user user1;
drop role
db1=#