Postgresql中如何正确删除role

魂醉

已于 2022-05-05 17:21:18 修改

阅读量4k

点赞数 1

分类专栏： # PostgreSQL 文章标签： postgresql 数据库 database

于 2022-05-05 15:15:00 首次发布

本文链接：https://blog.csdn.net/dazuiba008/article/details/124584035

版权

PostgreSQL 专栏收录该内容

206 篇文章

订阅专栏

关于删除role的一些理论

首先，删除用户不能使用DROP ROLE … CASCADE，不能级联删除用户。也就是不能删除依赖的对象。

因为角色可以拥有数据库对象，并且可以拥有访问其他数据库对象的权限，所以删除角色通常不仅是执行DROP role的问题。该角色拥有的任何对象必须先被删除或重新分配给其他角色;并且必须回收授予该角色的一切权限。

对象的所有权可以通过alter命令修改，如下：

ALTER TABLE bobs_table OWNER TO alice;

这里就该REASSIGN OWNED BY出场了，该命令可以把要被删除角色拥有的所有对象的所有权限重新分配给另一个角色。因为REASSIGN OWNED BY不能访问其他数据库中的对象，所以要在每个包含该角色所拥有对象的数据库中运行一遍。
当转移权限后，可以运行DROP OWNED BY命令，一旦需要转移的对象被转移给新的角色，任何要被删除的角色所拥有的剩余对象都可以使用DROP owned命令删除。同样，这个命令不能访问其他数据库中的对象，因此必须在包含该角色拥有的对象的每个数据库中运行它。此外，DROP OWNED不会删除整个数据库或表空间，所以要被的删除角色，如果还拥有相关数据库或表空间的权限，就必须手动处理。

REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;
-- 在每个和该角色相关的数据库，重复执行以上命令，然后删除角色。
DROP ROLE doomed_role;

尝试删除数据库中的postgres角色

数据库中的postgres角色无法删除，因为该角色是数据库内部的引导角色object ID为10，该角色只能重命名。
如下，不能删除，可以重命名

# 查看postgres橘色oid为10
postgres=# select * from pg_roles where rolname = 'postgres';
-[ RECORD 1 ]--+---------
rolname        | postgres
rolsuper       | t
rolinherit     | t
rolcreaterole  | t
rolcreatedb    | t
rolcanlogin    | t
rolreplication | t
rolconnlimit   | -1
rolpassword    | ********
rolvaliduntil  | 
rolbypassrls   | t
rolconfig      | 
oid            | 10
postgres=# drop role postgres ;
ERROR:  cannot drop role postgres because it is required by the database system

postgres=# alter role postgres rename to hank;
ALTER ROLE

下面举一个删除用户的实例：

#创建角色role01和role02,分别连接postgres库，各自创建表role01_tbl和role02_tbl
postgres=# create role role01 createdb login;
CREATE ROLE
postgres=# create role role02 createdb login;
CREATE ROLE
postgres=# \c postgres role01
psql (13.6, server 12.6)
You are now connected to database "postgres" as user "role01".
postgres=> create table role01_tbl (a int);
CREATE TABLE
postgres=> \c postgres role02
psql (13.6, server 12.6)
You are now connected to database "postgres" as user "role02".
postgres=> create table role02_tbl (a int);
CREATE TABLE


#使用hank用户连接hank库，并赋予一张表的只读权限给role01
postgres=> \c  hank hank 
psql (13.6, server 12.6)

hank=> grant select on account to role01;
GRANT
hank=> \z account
                               Access privileges
 Schema |  Name   | Type  |  Access privileges   | Column privileges | Policies 
--------+---------+-------+----------------------+-------------------+----------
 hank   | account | table | hank=arwdDxt/hank   +| password:        +| 
        |         |       | auditor=arwdDxt/hank+|   auditor=r/hank  | 
        |         |       | role01=r/hank        |                   | 
(1 row)


#使用超级用户postgres连入postgres库，删除role01和role02报错
hank=> \c postgres postgres
psql (13.6, server 12.6)
You are now connected to database "postgres" as user "postgres".
postgres=# drop role role01;
ERROR:  role "role01" cannot be dropped because some objects depend on it
DETAIL:  owner of table role01_tbl
1 object in database hank
postgres=# drop role role02;
ERROR:  role "role02" cannot be dropped because some objects depend on it
DETAIL:  owner of table role02_tbl

#创建用户role03,把role01的所有权转移给role03,然后DROP OWNED BY删除对象的所有权
postgres=# create role role03 createdb login;
CREATE ROLE
postgres=# REASSIGN OWNED BY role01 TO role03;
REASSIGN OWNED
postgres=# \dt+ role01_tbl 
                             List of relations
 Schema |    Name    | Type  | Owner  | Persistence |  Size   | Description 
--------+------------+-------+--------+-------------+---------+-------------
 public | role01_tbl | table | role03 | permanent   | 0 bytes | 
postgres=# DROP OWNED BY role01;
DROP OWNED

#连如数据库hank,只读权限还在，我们再次执行REASSIGN OWNED BY和DROP OWNED BY
postgres=# \c  hank postgres
psql (13.6, server 12.6)
You are now connected to database "hank" as user "hank".
hank=> \z  hank.account 
                               Access privileges
 Schema |  Name   | Type  |  Access privileges   | Column privileges | Policies 
--------+---------+-------+----------------------+-------------------+----------
 hank   | account | table | hank=arwdDxt/hank   +| password:        +| 
        |         |       | auditor=arwdDxt/hank+|   auditor=r/hank  | 
        |         |       | role01=r/hank        |                   | 
(1 row)

hank=# REASSIGN OWNED BY role01 TO role03;
REASSIGN OWNED
hank=# \z hank.account 
                               Access privileges
 Schema |  Name   | Type  |  Access privileges   | Column privileges | Policies 
--------+---------+-------+----------------------+-------------------+----------
 hank   | account | table | hank=arwdDxt/hank   +| password:        +| 
        |         |       | auditor=arwdDxt/hank+|   auditor=r/hank  | 
        |         |       | role01=r/hank        |                   | 

#可见无法转移grant的权限，类似的还有alter default privileges,但是通过DROP OWNED BY回收掉相关权限，如下：
hank=> \c  hank postgres 
psql (13.6, server 12.6)
You are now connected to database "hank" as user "postgres".
hank=#   DROP OWNED BY role01;
DROP OWNED
hank=# \z  hank.account 
                               Access privileges
 Schema |  Name   | Type  |  Access privileges   | Column privileges | Policies 
--------+---------+-------+----------------------+-------------------+----------
 hank   | account | table | hank=arwdDxt/hank   +| password:        +| 
        |         |       | auditor=arwdDxt/hank |   auditor=r/hank  | 
(1 row)

#删除role,报错，查询发现是整个库有权限，回收权限后，再次删除role成功。
hank=# drop role role01 ;
ERROR:  role "role01" cannot be dropped because some objects depend on it
DETAIL:  privileges for database hank
postgres=# \l+
                                                                     List of databases
   Name    |   Owner   | Encoding |  Collate   |   Ctype    |    Access privileges     |  Size   | Tablespace |                Description                 
-----------+-----------+----------+------------+------------+--------------------------+---------+------------+--------------------------------------------
 hank      | postgres  | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/postgres            +| 639 MB  | tbs_hank   | 
           |           |          |            |            | postgres=CTc/postgres   +|         |            | 
           |           |          |            |            | hank=C*T*c*/postgres    +|         |            | 
           |           |          |            |            | role01=CTc/hank         +|         |            | 
           |           |          |            |            | role03=CTc/hank          |         |            |

hank=# revoke all on database hank from role01 cascade;
hank=# drop role role01;
DROP ROLE


#另外可以查看相关角色的表权限，根据自己需求进一步操作，如下，角色role01有对表account的select权限。
hank=> select * from information_schema.table_privileges where grantee='role01';
-[ RECORD 1 ]--+--------
grantor        | hank
grantee        | role01
table_catalog  | hank
table_schema   | hank
table_name     | account
privilege_type | SELECT
is_grantable   | NO
with_hierarchy | YES

#相关对象权限的视图如下，根据自己需求查询
postgres=# \dvt *.*privileges*
                      List of relations
       Schema       |         Name         | Type |  Owner   
--------------------+----------------------+------+----------
 information_schema | column_privileges    | view | postgres
 information_schema | data_type_privileges | view | postgres
 information_schema | routine_privileges   | view | postgres
 information_schema | table_privileges     | view | postgres
 information_schema | udt_privileges       | view | postgres
 information_schema | usage_privileges     | view | postgres

可以通过以下链接，找出数据库对象的依赖关系

https://github.com/digoal/blog/blob/master/201607/20160725_01.md

另外如果觉的删除用户风险大，也可以通过其他方式控制要删除的用户，如：

修改用户的连接限制为0

postgres=#alter role role01 connection limit 0;  
postgres=# \c postgres role01
FATAL:  too many connections for role "role01"
Previous connection kept

不允许用户登陆

postgres=# alter role role01 nologin;  
postgres=# \c hank role01
FATAL:  role "role01" is not permitted to log in
Previous connection kept

将用户名重命名

postgres=# alter role role01 renmae to role01_bak;

参考：
https://www.cybertec-postgresql.com/en/drop-role-or-drop-user-postgresql/
https://www.postgresql.org/docs/current/sql-reassign-owned.html
https://www.postgresql.org/docs/current/role-removal.html
https://www.postgresql.org/docs/current/sql-drop-owned.html