刨根问底 | 如何删除用户最优
刨根问底 | 如何删除用户最优
前言
之前分享了一篇关于PostgreSQL中的权限，反响非常不错，经常有后台朋友提到这篇。不过昨天我在回过头来看的时候，发现里面遗漏了一些比较重要的内容，是关于删除用户的。
那么刨根问底一下，删除用户这个动作有哪些注意事项？
现象
在官网上，关于删除drop role有过简单说明，意思是假如要删除用户，需要确保用户和当前实例内的任何数据库没有瓜葛了，可以通过reassign owned和drop owned命令进行"资产转移"和"一股脑删除用户拥有的资产"
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 on other objects. The REASSIGN OWNED and DROP OWNED commands can be useful for this purpose;
假如直接删除已经分配了某些权限的用户的话，是会报错的
postgres=# create user u1;
CREATE ROLE
postgres=# create table test(id int);
CREATE TABLE
postgres=# grant select on test to u1;
GRANT
postgres=# drop user u1;
ERROR: role "u1" cannot be dropped because some objects depend on it
DETAIL: privileges for table test
官网建议的操作如下
REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;
-- repeat the above commands in each database of the cluster
DROP ROLE doomed_role;
当然，也可以使用土办法，正如上面所演示的，drop user会打印出具体的细节，比如此例的test表的select权限，手动移除再次删除即可。
那么，有什么注意事项呢？闲话少叙，先看一下现象
操作步骤如下
1.新建两个用户u1，u2，同时赋予u1用户允许创建数据库createdb2.使用u1用户创建新的数据库mydb3.然后u1、u2和postgres超级用户分别在mydb新建库下面创建一些对象4.使用drop owned by删除用户资产，观察现象
postgres=# create user u1;
CREATE ROLE
postgres=# create user u2;
CREATE ROLE
postgres=# alter user u1 createdb;
ALTER ROLE
postgres=# \c postgres u1
You are now connected to database "postgres" as user "u1".
postgres=> create database mydb;
CREATE DATABASE
postgres=> \c mydb u1
You are now connected to database "mydb" as user "u1".
mydb=> create table t1(id int);
CREATE TABLE
mydb=> create schema myschema;
CREATE SCHEMA
mydb=> \c mydb u2
You are now connected to database "mydb" as user "u2".
mydb=> create table t2(id int);
CREATE TABLE
mydb=> \c mydb postgres
You are now connected to database "mydb" as user "postgres".
mydb=# create table t3(id int);
CREATE TABLE
mydb=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------+-------+----------+-------------+---------------+---------+-------------
public | t1 | table | u1 | permanent | heap | 0 bytes |
public | t2 | table | u2 | permanent | heap | 0 bytes |
public | t3 | table | postgres | permanent | heap | 0 bytes |
(3 rows)
mydb=# \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# drop owned by u1;
DROP OWNED
postgres=# \c mydb u1
You are now connected to database "mydb" as user "u1".
mydb=> \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------+-------+----------+-------------+---------------+---------+-------------
public | t1 | table | u1 | permanent | heap | 0 bytes |
public | t2 | table | u2 | permanent | heap | 0 bytes |
public | t3 | table | postgres | permanent | heap | 0 bytes |
(3 rows)
mydb=> \dn
List of schemas
Name | Owner
----------+----------
myschema | u1
public | postgres
(2 rows)
不属于u1用户的资产比如t2表没有被删属于正常现象，那为啥没有删除掉u1用户所拥有的模式myschema和表t1呢？
换个姿势，再来一遍，切个库
mydb=> \c mydb u1
You are now connected to database "mydb" as user "u1".
mydb=> drop owned by u1;
DROP OWNED
mydb=> \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------+-------+----------+-------------+---------------+---------+-------------
public | t2 | table | u2 | permanent | heap | 0 bytes |
public | t3 | table | postgres | permanent | heap | 0 bytes |
(2 rows)
mydb=> \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
可以看到，换了个库之后，就正常删除了u1用户创建的t1表和myschema模式。
为什么会有这种差异呢？遇事不决看官网，下来又仔细读了一遍，原来官网写的很明白
Once any valuable objects have been transferred to new owners, any remaining objects owned by the role-to-be-dropped can be dropped with the DROP OWNED command. Again, this command cannot access objects in other databases, so it is necessary to run it in each database that contains objects owned by the role. Also, DROP OWNED will not drop entire databases or tablespaces, so it is necessary to do that manually if the role owns any databases or tablespaces that have not been transferred to new owners.
drop owned命令无法访问其他库的对象，所以假如某用户在A库进行drop owned操作，假设B库还存在该用户的某些对象权限，那么是无法删除的！需要切换到其他库下面分别执行一次
postgres=# drop owned by u1;
DROP OWNED
postgres=# drop user u1;
ERROR: role "u1" cannot be dropped because some objects depend on it
DETAIL: owner of database mydb
2 objects in database mydb
仔细琢磨一下，其实也是正常的现象，本身PostgreSQL就不支持直接跨库访问，得通过dblink、fdw的方式，那么drop owned这样的现象也就不足为奇了。
除了drop owned和reassign owned的介绍做了说明，drop role也做了说明，repeat the above commands in each database of the cluster
REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;
-- repeat the above commands in each database of the cluster
DROP ROLE doomed_role;
如何删除最优
因为上述操作我们看到了，drop owned by风险很大，因为你并不知道到底有哪些对象是隶属于被删除角色的，例如有些业务库的owner隶属于你要删的role，然后被你执行了一把drop owned by，那就惨了。
并且我们可以看到，drop owned by会直接将表和schema删了，并且！并且，划重点，在PostgreSQL中，因为独特的实现机制，官方称之为特性feature，我更愿称之为BUG，因为你可以将模式的CREATE权限赋予其他用户，那么另外的用户就可以在这个模式下创建对象，这样就会导致，一个对象有两个owner，模式的owner也可以删除这个对象。具体细节可以参考之前的权限文章。
不过我们可以看到，drop owned还是会贴心打印出来，而不是一股脑删除，甚好甚好。
postgres=# drop user u1;
DROP ROLE
postgres=# create user u1;
CREATE ROLE
postgres=# alter user u1 createdb;
ALTER ROLE
postgres=# \c postgres u1
You are now connected to database "postgres" as user "u1".
postgres=> create database mydb;
CREATE DATABASE
postgres=> \c mydb u1
You are now connected to database "mydb" as user "u1".
mydb=> create schema myschema;
CREATE SCHEMA
mydb=> grant create on schema myschema to u2;
GRANT
mydb=> \c mydb u2
You are now connected to database "mydb" as user "u2".
mydb=> create table myschema.t1(id int);
CREATE TABLE
mydb=> \dn
List of schemas
Name | Owner
----------+----------
myschema | u1
public | postgres
(2 rows)
mydb=> \dt+ myschema.t1
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
----------+------+-------+-------+-------------+---------------+---------+-------------
myschema | t1 | table | u2 | permanent | heap | 0 bytes |
(1 row)
mydb=> \c mydb u1
You are now connected to database "mydb" as user "u1".
mydb=> drop owned by u1;
ERROR: cannot drop schema myschema because other objects depend on it
DETAIL: table myschema.t1 depends on schema myschema
HINT: Use DROP ... CASCADE to drop the dependent objects too.
mydb=> drop owned by u1 cascade;
NOTICE: drop cascades to table myschema.t1
DROP OWNED
另外，drop owned不会删除数据库和表空间，这个也需要自己手动处理一下，删库删表空间这种动作着实令人蛋疼。
mydb=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
mydb | u1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
mydb=> drop user u1;
ERROR: permission denied to drop role
mydb=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# drop user u1;
ERROR: role "u1" cannot be dropped because some objects depend on it
DETAIL: owner of database mydb
REASSIGN OWNED
那么，如何删除最优呢？官网上的建议是使用REASSIGN OWNED进行资产转移，这不就是继承老夫的蚂蚁花呗吗？
mydb=# \c mydb u1
You are now connected to database "mydb" as user "u1".
mydb=> create table t1(id int);
CREATE TABLE
mydb=> create schema myschema;
CREATE SCHEMA
mydb=# reassign owned by u1 to u2;
REASSIGN OWNED
mydb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | t1 | table | u2
(1 row)
mydb=# \dn
List of schemas
Name | Owner
----------+----------
myschema | u2
public | postgres
(2 rows)
可以看到，owner都转移给了u2用户。不过，关于REASSIGN OWNED也有注意事项
The REASSIGN OWNED command does not affect any privileges granted to the old_roles on objects that are not owned by them. Likewise, it does not affect default privileges created with ALTER DEFAULT PRIVILEGES. Use DROP OWNED to revoke such privileges.
mydb=# \c mydb u1
You are now connected to database "mydb" as user "u1".
mydb=> create table t1(id int);
CREATE TABLE
mydb=> \c mydb postgres
You are now connected to database "mydb" as user "postgres".
mydb=# create table t2(id int);
CREATE TABLE
mydb=# grant select on t2 to u1;
GRANT
mydb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | u1
public | t2 | table | postgres
(2 rows)
mydb=# \dp t2
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+---------------------------+-------------------+----------
public | t2 | table | postgres=arwdDxt/postgres+| |
| | | u1=r/postgres | |
(1 row)
mydb=# reassign owned by u1 to u2;
REASSIGN OWNED
mydb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | u2
public | t2 | table | postgres
(2 rows)
mydb=# \dp t2
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+---------------------------+-------------------+----------
public | t2 | table | postgres=arwdDxt/postgres+| |
| | | u1=r/postgres | |
(1 row)
mydb=# drop user u1;
ERROR: role "u1" cannot be dropped because some objects depend on it
DETAIL: privileges for table t2
可以看到，满足第一条，显式授予的权限没有被移除，需要使用drop owned来删除
The REASSIGN OWNED command does not affect any privileges granted to the old_roles on objects that are not owned by them.
mydb=# drop user u1;
ERROR: role "u1" cannot be dropped because some objects depend on it
DETAIL: privileges for table t2
mydb=# drop owned by u1;
DROP OWNED
mydb=# \dp t2
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+---------------------------+-------------------+----------
public | t2 | table | postgres=arwdDxt/postgres | |
(1 row)
同理，还有DEFAULT PRIVILEGES
mydb=# \c mydb u1
You are now connected to database "mydb" as user "u1".
mydb=> create table t1(id int);
CREATE TABLE
mydb=> \c mydb postgres
You are now connected to database "mydb" as user "postgres".
mydb=# create table t2(id int);
CREATE TABLE
mydb=# grant select on t2 to u1;
GRANT
mydb=# alter default privileges in schema public grant select on tables TO u1;
ALTER DEFAULT PRIVILEGES
mydb=# create table t3(id int);
CREATE TABLE
mydb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | u1
public | t2 | table | postgres
public | t3 | table | postgres
(3 rows)
mydb=# \dp t2
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+---------------------------+-------------------+----------
public | t2 | table | postgres=arwdDxt/postgres+| |
| | | u1=r/postgres | |
(1 row)
mydb=# \dp t3
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+---------------------------+-------------------+----------
public | t3 | table | postgres=arwdDxt/postgres+| |
| | | u1=r/postgres | |
(1 row)
当然，假如为了更安全，可以参照德哥的帖子https://github.com/digoal/blog/blob/master/201607/20160725_01.md，一次性查出某个用户拥有的对象，自己手动处理。
小结
所以，学习官网真的是一门大学问。假如，有人问你如何学习PostgreSQL比较好，不妨推荐一下，先从阅读官方文档开始吧！
朋友MLB的公众号《PostgreSQL数据库工作学习随笔》就是这么做的，点个赞????
另外，再补一下之前权限忘记提醒的一点，也是十分重要的一点，可能有人发现，为什么我revoke了select的权限，为什么还是有查询的权限，这是因为在PostgreSQL中，权限是一个并集，一个用户真正拥有的权限是以下几类权限的并集，因为有一些对象有赋予给PUBLIC角色默认权限，所以建好之后，所有人都有这些默认权限，所以还得执行revoke xxx from public的动作才行。
- 发表于 2022-01-26 01:46
- 阅读 ( 35 )
