1. 云图网首页
  2. 技术专区
  3. 数据库

How to drop a role in PostgreSQL Server ?

• 数据库

在 PostgreSQL 数据库中，” role “ 可以理解为 user，即数据库用户， 当删除 PostgreSQL 的角色时， PostgreSQL 会谨慎对待，当这个用户还拥有数据库对像，或者这个用户在某些数据库对像上还拥有权限时，则不能删除，下面简单演示下：

环境准备

创建新用户并赋予权限

1
2
3
4
5
6
7
8
 
CREATE ROLE readonly LOGIN ENCRYPTED PASSWORD 'readonly'  
nosuperuser noinherit nocreatedb nocreaterole ;
grant connect on database skytf to readonly;  
grant usage on schema skytf to readonly;  
grant select on all tables in schema skytf to readonly;
grant connect on database beha_db to readonly;  
grant usage on schema beha_db to readonly;  
grant select on all tables in schema beha_db to readonly;

备注：这步创建一个新用户 readonly，并赋予数据库 skytf 和 beha_db 所有表的查询权限。

查询用户 readonly 权限信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
skytf=> /c beha_db beha_db  
You are now connected to database "beha_db" as user "beha_db".

select grantor,grantee,table_schema,table_name,privilege_type from  
information_schema.table_privileges where grantee='readonly';  
 grantor | grantee | table_schema | table_name | privilege_type  
-----------+----------+--------------+----------------------------------+----------------  
beha_db | readonly | beha_db | tbl_beha_db_stat_20111119 | SELECT  
beha_db | readonly | beha_db | tbl_beha_db_stat_20111123 | SELECT  
beha_db | readonly | beha_db | tbl_beha_db_stat_20111225 | SELECT  
beha_db | readonly | beha_db | tbl_beha_db_stat_20120210 | SELECT  
beha_db | readonly | beha_db | tbl_beha_db_stat_20120328 | SELECT  
beha_db | readonly | beha_db | tbl_beha_db_stat_20120514 | SELECT  
beha_db | readonly | beha_db | tbl_beha_db_stat_20120701 | SELECT  
  
beha_db=> /c skytf skytf  
You are now connected to database "skytf" as user "skytf".

skytf=> select grantor,grantee,table_schema,table_name,privilege_type from  
skytf-> information_schema.table_privileges where grantee='readonly';  
grantor | grantee | table_schema | table_name | privilege_type  
---------+----------+--------------+------------------------------+----------------  
skytf | readonly | skytf | test_65 | SELECT  
skytf | readonly | skytf | table_b | SELECT  
skytf | readonly | skytf | test_null | SELECT  
skytf | readonly | skytf | test_14 | SELECT  
skytf | readonly | skytf | test_aesc | SELECT

尝试删除用户

尝试删除用户 readonly，如下：

1
2
3
4
5
6
 
postgres=# drop role readonly;  
ERROR: role "readonly" cannot be dropped because some objects depend on it  
DETAIL: privileges for database beha_db  
privileges for database skytf  
2506 objects in database beha_db  
265 objects in database skytf

备注：当删除用户 readonly 时， ERROR 产生，信息如上，根据提示信息，很容易知道是因为用户 readonly 上还拥有数据库对像，那么如何处理以上问题呢，下面提供两种方法：

方法一：使用 “DROP OWNED” 删除用户对像和权限

Drop owned 命令用来删除用户的对像信息和权限信息，这个命令具有很大风险，因为此命令作用是删除用户下的所有对像，使用此命令时，务必谨慎，不要删错数据库对像，关于此命令详细信息，请参考文档 http://www.postgresql.org/docs/9.1/static/sql-drop-owned.html ，下面继续演示：

连接 beha_db 库，删除用户 readonly 的权限信息

1
2
3
4
5
 
beha_db=> /c beha_db postgres  
You are now connected to database "beha_db" as user "postgres".  

beha_db=# drop owned by readonly;  
DROP OWNED

连接 skytf 库，删除用户 readonly 的权限信息

1
2
3
4
5
 
postgres=# /c skytf postgres  
You are now connected to database "skytf" as user "postgres"..  

skytf=# drop owned by readonly;  
DROP OWNED

尝试再次删除用户 readonly

1
2
3
 
postgres=# drop role readonly;  
ERROR: role "readonly" cannot be dropped because some objects depend on it  
DETAIL: privileges for database beha_db

备注：提示用户 readonly 还有权限信息存在，删除失败。

删除 readonly 用户的数据库连接权限

1
2
3
4
 
postgres=# revoke connect on database beha_db from readonly;  
REVOKE  
postgres=# revoke connect on database skytf from readonly;  
REVOKE

再次删除用户，终于删除成功

1
2
 
postgres=# drop role readonly;  
DROP ROLE

方法二：REVOKE 权限信息

创建用户并赋予权限

1
2
3
4
5
 
CREATE ROLE readonly LOGIN ENCRYPTED PASSWORD 'readonly'  
nosuperuser noinherit nocreatedb nocreaterole ;
grant connect on database skytf to readonly;  
grant usage on schema skytf to readonly;  
grant select on all tables in schema skytf to readonly;

REVOKE权限

1
2
3
4
5
6
7
8
 
skytf=# /c skytf skytf  
You are now connected to database "skytf" as user "skytf".
skytf=> revoke connect on database skytf from readonly;  
REVOKE  
skytf=> revoke select on all tables in schema skytf from readonly;  
REVOKE
skytf=> revoke usage on schema skytf from readonly;  
REVOKE

删除用户

1
2
 
postgres=# drop role readonly;  
DROP ROLE

备注：在删除用户前，需先删除用户的对像和权限信息，才能删除用户，关于这一点，可以参考手册上的

附：手册上的解释

Drop Role
A role cannot be removed if it is still referenced in any database of the cluster; an error will be
raised if so. Before dropping the role, you must drop all the objects it owns (or reassign their ownership)
and revoke any privileges the role has been granted.

总结

PostgreSQL 在处理删除用户的动作时持谨慎态度，当删除数据库时，首先得保证被删除的用户没有数据库对像，同时 被删除的用户不拥有任何数据库对像的权限，只有保证了以上两点才能删除成功，而 Oracle 处理此问题没有这么复杂，当删除用户时，提供一个 cascade 属性，删除用户时可以级连删除这个用户拥有的数据库一切对像。

原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/237817.html

赞 (0)
0
上一篇 2022年1月29日
下一篇 2022年1月29日

相关推荐

发表回复

登录后才能评论