再唠唠晕乎的权限体系

xiongcc2023年03月06日约 9 分钟

1前言

日常答疑系列，这次的问题依旧与权限有关，不光是新人，老鸟都经常会被权限给绕晕。

闲话少叙，进入主题，在 2 群有位老铁问了一个关于 default privileges 的问题 👇🏻

2分析

关于权限，很久之前已经写过一篇比较详尽的文章了，👉🏻 又被权限搞晕了？拿捏！，刚刚看了一眼，发现这篇文章的阅读量已经超过一千了，侧面说明了权限的晕乎之处 🤪。

那么 default privileges 是做什么的呢？顾名思义，默认权限。回顾一下之前 AWS 的例子

A PostgreSQL database has been created with primary database named mydatabase.
A new schema has been created named myschema with multiple tables.
Two reporting users must be created with the permissions to read all tables in the schema myschema.
Two app users must be created with permissions to read and write to all tables in the schema myschema and also to create new tables.
The users should automatically get permissions on any new tables that are added in the future.

简而言之，两个 app 用户需要读写并且创建表的权限，report 用户需要读取所有表的权限，并且两个用户会自动拥有对后续新建表的权限。

为了方便，我在每条 SQL 下面添加了注释：

-- Revoke privileges from 'public' role
REVOKE CREATE ON SCHEMA public FROM PUBLIC;   
---回收所有人在public模式下的创建权限，为了防止后面还有人继续创表
REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;  
---回收所有人在数据库下面的所有权限-- Read-only role
CREATE ROLE readonly;        
---新建只读用户readonly
GRANT CONNECT ON DATABASE mydatabase TO readonly; 
---允许只读用户readonly连接数据库
GRANT USAGE ON SCHEMA myschema TO readonly;   
---允许只读用户readonly使用模式myschema
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly; 
---赋予所有myschema模式下的表的查询权限
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;  
---赋予所有myschema模式下未来新建的表的只读权限
-- Read/write role
CREATE ROLE readwrite;        
---新建读写用户readwrite
GRANT CONNECT ON DATABASE mydatabase TO readwrite; 
---允许读写用户readwrite连接数据库
GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;
---允许读写用户readwrite使用模式myschema，并且允许在myschema下面创建对象
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;
---赋予所有myschema模式下的表的增删改查权限
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
---赋予所有myschema模式下未来新建的表的增删改查权限
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
---赋予所有myschema模式下的序列的所有权限
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;
---赋予所有myschema模式下未来新建的序列的所有权限
-- Users creation
CREATE USER reporting_user1 WITH PASSWORD 'some_secret_passwd';
---创建可以读所有表的用户reporting_user1
CREATE USER reporting_user2 WITH PASSWORD 'some_secret_passwd';
---创建可以读所有表的用户reporting_user2
CREATE USER app_user1 WITH PASSWORD 'some_secret_passwd';
---创建可以读写所有表的用户app_user1
CREATE USER app_user2 WITH PASSWORD 'some_secret_passwd';
---创建可以读写所有表的用户app_user2
-- Grant privileges to users
GRANT readonly TO reporting_user1;
---赋予只读角色给只读用户reporting_user1
GRANT readonly TO reporting_user2;
---赋予只读角色给只读用户reporting_user2
GRANT readwrite TO app_user1;
---赋予读写角色给读写用户app_user1
GRANT readwrite TO app_user2;
---赋予读写角色给读写用户app_user2

可以看到包括了几个关键的 default privileges 语句，用于对新建的对象进行默认授权，之后便无需再繁琐人肉指定了。那么这个 for role 是什么意思？看下描述

Parameter	Description
`FOR ROLE name`/`FOR USER name`	Alter the default privileges on objects created by a specific role/user, or a list of roles/users.
`FOR ALL ROLES`	Alter the default privileges on objects created by all users/roles.
`GRANT ...`	Grant a default privilege or list of privileges on all objects of the specified type to a role/user, or a list of roles/users.
`REVOKE ...`	Revoke a default privilege or list of privileges on all objects of the specified type from a role/user, or a list of roles/users.
`IN SCHEMA qualifiable_schema_name`	If specified, the default privileges are altered for objects created in that schema. If an object has default privileges specified at the database and at the schema level, the union of the default privileges is taken.

十分清晰，修改某个具体用户后续创建的数据库对象的默认权限，如果不指定 for 子句，则会更改当前用户创建的对象的默认权限。看个例子

postgres=# create user u1;
CREATE ROLE
postgres=# create user u2;
CREATE ROLE
postgres=# create table t1(id int);
CREATE TABLE
postgres=# insert into t1 values(1);
INSERT 0 1
postgres=# grant select on table t1 to u1;
GRANT
postgres=# grant create on schema public to u1;
GRANT

postgres 用户建的表，u1 默认是没有权限的，所以需要手动授权

postgres=> select * from t1;    ---最开始没有权限
ERROR:  permission denied for table t1
postgres=> select * from t1;    ---需要手动授予
 id 
----
  1
(1 row)

但是每次授权又很麻烦，因此默认权限的好处体现出来了。此处针对 u1，后续 u1 新建的表默认授予查询权限给 u2

postgres=# alter default privileges for role u1 grant select on tables to u2;
ALTER DEFAULT PRIVILEGES

后续 u2 直接查询即可

postgres=# \c postgres u2
You are now connected to database "postgres" as user "u2".
postgres=> select * from u1_t ;
 id 
----
(0 rows)

元命令 \ddp 可以看到默认的权限

postgres=# \ddp
         Default access privileges
 Owner | Schema | Type  | Access privileges 
-------+--------+-------+-------------------
 u1    |        | table | u1=arwdDxt/u1    +
       |        |       | u2=r/u1
(1 row)

那么回到这位筒子的问题，执行 alter table 的用户是不是必须是 schema 的 owner？其实这个问题已经有相关文章了，详情戳 https://blog.csdn.net/weixin_37692493/article/details/124378959，搭配 target role 👇🏻

该参数的意思就是，当我们使用 postgres 数据库用户进行 alter default privilege 授权时，可以通过 target_role 指定我们需要授权 schema 的 owner 角色，这时就如同我们登录 owner 用户进行 alter default 授权。

通过 alter default privilege 授权，被授权用户仅仅会继承当前授权用户在该 schema 下创建的表对象的指定默认权限；如果需要通过超级用户对其他业务 schema 进行默认授权，需要通过 alter default privileges for role ${schema_role_name} 来进行授权。

用户名称	数据库授权操作	现象
postgres	默认super账号
aa	通过postgres账号授权aa用户为schema aa的owner	业务账号，用户表对象创建
bb	通过aa用户授权bb用户拥有 schema aa 所有table的select权限 + alter default	对当前表 + 未来创建表均具有select权限
cc	通过postgres用户授权cc用户拥有schema aa 所有table的select权限 + alter default	对当前表具有select权限，未来创建表没有权限

具体细节直接查看该文章即可，挺清晰的。

3删除用户

默认权限搞明白了，现在又有个新的问题摆在了面前：怎么删用户？删用户并不是简单敲个 drop user 那么简单，数据库会检测被删用户拥有的依赖，👉🏻 刨根问底 | 如何删除用户最优，只有当没有任何对象和权限依赖于该用户时，才可安全删除用户，因此官方推荐的方式是 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.

显式授予的权限和默认权限无法处理，所以还需要再次搭配 drop owned，太繁琐了！那么有没有优雅点的懒人方式呢？老样子，遇事不决找插件，https://github.com/cybertec-postgresql/drop_role_helper，这个插件来帮您，看个例子

postgres=# create user u1;
CREATE ROLE
postgres=# grant select on all tables in schema public to u1;
GRANT
postgres=# alter default privileges in schema public grant select on tables to u1;
ALTER DEFAULT PRIVILEGES
postgres=# \ddp
           Default access privileges
  Owner   | Schema | Type  | Access privileges 
----------+--------+-------+-------------------
 postgres | public | table | u1=r/postgres
(1 row)postgres=# drop user u1;   ---土办法可以打印出所有的依赖对象
ERROR:  role "u1" cannot be dropped because some objects depend on it
DETAIL:  privileges for table master
privileges for table test1
privileges for table test
privileges for table t1
privileges for default privileges on new relations belonging to role postgres in schema public

drop_role_helper 插件提供了一个有用的函数，执行一下便会自动生成 SQL

postgres=# select * from drop_role_helper('u1');
                                     drop_role_helper                                      
-------------------------------------------------------------------------------------------
 REVOKE ALL ON public.master FROM u1;
 REVOKE ALL ON public.t1 FROM u1;
 REVOKE ALL ON public.test FROM u1;
 REVOKE ALL ON public.test1 FROM u1;
 ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE ALL ON TABLES FROM u1;
(5 rows)

所以搭配 \gexec 便轻松多了

postgres=# select * from drop_role_helper('u1');\gexec
                                     drop_role_helper                                      
-------------------------------------------------------------------------------------------
 REVOKE ALL ON public.master FROM u1;
 REVOKE ALL ON public.t1 FROM u1;
 REVOKE ALL ON public.test FROM u1;
 REVOKE ALL ON public.test1 FROM u1;
 ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE ALL ON TABLES FROM u1;
(5 rows)REVOKE
REVOKE
REVOKE
REVOKE
ALTER DEFAULT PRIVILEGES
postgres=# drop user u1;   ---轻松多了，再也不用担心删不掉用户了
DROP ROLE

怎么样？是不是 so easy？当然这个插件和 drop owned/ reassign owned 一样，无法处理其他库下的依赖，需要连到其他库下执行，另外这个插件并且仅仅处理权限，属主必须要自己手动确认，比如是某个表的 owner，这个插件不会自动删除这个表，不然就过于危险了。

postgres=# select * from drop_role_helper('u1');\gexec
                                     drop_role_helper                                      
-------------------------------------------------------------------------------------------
 REVOKE ALL ON public.master FROM u1;
 REVOKE ALL ON public.t1 FROM u1;
 REVOKE ALL ON public.test FROM u1;
 REVOKE ALL ON public.test1 FROM u1;
 ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE ALL ON TABLES FROM u1;
(5 rows)REVOKE
REVOKE
REVOKE
REVOKE
ALTER DEFAULT PRIVILEGES
postgres=# drop user u1;   ---需要再次到mydb库下面执行
ERROR:  role "u1" cannot be dropped because some objects depend on it
DETAIL:  1 object in database mydb

另外，再补充一下经常被人忽略的点，也是十分重要的一点。可能有人发现，为什么我回收了查询权限，为什么还是可以查，这是因为在 PostgreSQL 中，权限是一个并集，一个用户真正拥有的权限是以下几类权限的并集，因为有一些对象有赋予给PUBLIC角色默认权限，所以建好之后，所有人都有这些默认权限，所以还得执行revoke xxx from public的动作才行。

4小结

好了，That's all. 权限真的很让人晕乎，自己动手实验一下加深印象。

交流群里的问题我空了都会看的，空了我就会进行解答，不会的我就去骚扰其他大腿请教。还没有进群的小伙伴，由于前面两个群都满 200 了，所以为了方便，我又拉一个交流 3 群，公众号后台回复"交流群"，直接扫码即可进入，不用再繁琐的手动拉人了，1 群 2 群的小伙伴就不要来凑热闹了哈。

5参考

https://blog.csdn.net/weixin_37692493/article/details/124378959

https://github.com/cybertec-postgresql/drop_role_helper

https://www.cockroachlabs.com/docs/stable/alter-default-privileges.html#required-privileges

1前言

2分析

3删除用户

4小结

5参考

来自同一个声音

PGSimCity - PostgreSQL 工作原理可视化解析

从 Lakebase 到 LTAP，是旧饭新炒，还是新的技术范式？

AI 时代首选数据库：PostgreSQL 入门到进阶实战

pg-slide-harvester：自动收集 PG 大会演讲资料