# RDS for PostgreSQL权限管理最佳实践 #### 基本概念 PostgreSQL 的用户权限管理基于角色（Role）和权限（Privilege）两个概念开展的，这两个概念具体解释如下： - 角色（Role） 角色是权限的集合载体，可理解为 "用户" 或 "用户组"： - 登录角色（Login Role）：具备登录数据库的权限（LOGIN属性），类似传统意义上的 "用户"。 - 组角色（Group Role）：不直接登录（无LOGIN属性），用于批量管理权限，其他角色可加入该组继承权限。 - 所有角色默认属于public角色（特殊组角色），需注意其默认权限。 - 权限（Privilege） 权限是对数据库对象（表、函数、schema 等）的操作许可，常见类型包括： - 表 / 视图：SELECT（查询）、INSERT（插入）、UPDATE（更新）、DELETE（删除）、TRUNCATE（清空）等。 - 数据库：CONNECT（连接）、CREATE（创建 schema）等。 - Schema：CREATE（创建对象）、USAGE（访问对象）等。 - 函数 / 存储过程：EXECUTE（执行）。 - 序列：USAGE（使用）、UPDATE（修改）等。 #### 权限管理基本原理 RDS for PostgreSQL 对用户User 权限管理的细分操作集中在Role上，需要注意的是，Role本身并不赋予登录权限，创建User后，赋予对应的Role，才能通过User登录数据库，并获得Role对应的权限，User本身的权限随Role的权限的变化而变化。 #### 权限管理建议 - RDS for PostgreSQL默认提供一个高权限账号root，该账号具有高级别权限，建议由少量资深DBA掌握。 - 团队管理者可以创建1个资源用户账号，用于对Role进行管理，可以根据实际情况创建多个Role实现权限的细分管理。 - 在细分Role的基础上创建多个业务账号，可以使用业务账号登录和操作数据库。 - 如果团队prj项目涉及多个Schema，Role的权限划分设计尽量以Schema为单位，例如{prj}_{role}_{schema}_readonly / {prj}_{role}_{schema}_write 等。需要注意的是，业务表请勿放到public中。因为PostgreSQL默认所有用户对public都有CREATE和USAGE权限。 #### 权限规划样例 - 资深DBA拥有RDS for PostgreSQL实例的高权限账号，名称是 root。 - 项目管理者拥有1个资源账号，用于账号管理和Role管理，名称是 db_prj_owner。 - 业务项目名称是db_prj，新建schema名称是db_prj、db_prj_1和db_prj_2。 项目中新增的资源owner账号和Role规划如下： 表1权限说明 | user/Role | schema中表权限 | schema中存储过程权限 | |:---|:---|:---| | root，最高权限账号，默认创建完实例后拥有。 | - DDL：CREATE、DROP、ALTER\* - DQL：SELECT\* - DML：UPDATE、INSERT、DELETE | - DDL：CREATE、DROP、ALTER\* - DQL：SELECT，调用存储过程。 | | db_prj_owner，是唯一的项目资源owner账号。 | - DDL：CREATE、DROP、ALTER\* - DQL：SELECT\* - DML：UPDATE、INSERT、DELETE | - DDL：CREATE、DROP、ALTER\* - DQL：SELECT，调用存储过程。 | | db_prj_role1_readwrite (role) | - DQL：SELECT\* - DML：UPDATE、INSERT、DELETE | DQL（SELECT，调用存储过程），若存储过程有DDL操作，会抛出权限相关错误。 | | db_prj_role2_readonly (role) | DQL(SELECT) | DQL（SELECT，调用存储过程），若存储过程有DDL或者DML操作，会抛出权限相关错误。 | #### 配置操作步骤 1. 创建项目资源owner账号db_prj_owner和项目Role。 DBA使用 root 高权限账号执行如下操作。 ``` --- db_prj_owner 是项目管理账号,此处密码仅为示例，请注意修改。 CREATE USER db_prj_owner WITH LOGIN PASSWORD 'XXXXXXX'; CREATE ROLE db_prj_role1_readwrite; CREATE ROLE db_prj_role2_readonly; --- 设置: 对于db_prj_owner 创建的表，db_prj_role1_readwrite 有 DQL（SELECT）、DML（UPDATE、INSERT、DELETE）权限。 ALTER DEFAULT PRIVILEGES FOR ROLE db_prj_owner GRANT ALL ON TABLES TO db_prj_role1_readwrite; --- 设置: 对于db_prj_owner创建的SEQUENCES，db_prj_role1_readwrite 有 DQL（SELECT）、DML（UPDATE、INSERT、DELETE）权限。 ALTER DEFAULT PRIVILEGES FOR ROLE db_prj_owner GRANT ALL ON SEQUENCES TO db_prj_role1_readwrite; --- 设置: 对于 db_prj_owner创建的表， db_prj_role2_readonly 只有 DQL（SELECT）权限。 ALTER DEFAULT PRIVILEGES FOR ROLE db_prj_owner GRANT SELECT ON TABLES TO db_prj_role2_readonly; ``` 2. 创建db_prj_user_readwrite、db_prj_user_readonly业务账号。 DBA使用root高权限账号执行如下操作。 ``` --- db_prj_user_readwrite只有 DQL（SELECT）、DML（UPDATE、INSERT、DELETE）权限。 CREATE USER db_prj_user_readwrite WITH LOGIN PASSWORD 'XXXXXXX'; GRANT db_prj_role1_readwrite TO db_prj_user_readwrite; --- db_prj_user_readonly只有 DQL（SELECT）权限。 CREATE USER db_prj_user_readonly WITH LOGIN PASSWORD 'XXXXXXXX'; GRANT db_prj_role2_readonly TO db_prj_user_readonly; ``` 3. 创建schema，并授权给项目Role。 DBA使用root高权限账号执行如下操作。 ``` --- schema db_prj 的owner是 db_prj_owner 账号 CREATE SCHEMA db_prj AUTHORIZATION db_prj_owner; --- 授权ROLE相关SCHEMA访问权限。 GRANT USAGE ON SCHEMA db_prj TO db_prj_role1_readwrite; GRANT USAGE ON SCHEMA db_prj TO db_prj_role2_readonly; ``` ![](https://support.huaweicloud.com/bestpractice-rds-pg/public_sys-resources/note_3.0-zh-cn.png) db_prj_user_readwrite 和 db_prj_user_readonly 自动继承了相关Role的权限变更，不需要再额外操作。 #### 开发应用场景 **场景1：使用 db_prj_owner 账号：对schema db_prj 中的表进行DDL操作** ``` CREATE TABLE db_prj.table1(col1 bigserial primary key, col2 int); DROP TABLE db_prj.table1; ``` **场景2：使用 db_prj_user_readwrite/db_prj_user_readonly 账号进行业务开发** 业务开发遵循最小权限原则，在不需要改变现有数据的场景中，使用 db_prj_user_readonly 账号，需要改变数据（DML操作，Insert，update，delete）的操作的地方才使用db_prj_user_readwrite 账号，这样便于处理好不同账号的数据操作安全隔离。 - 使用 db_prj_user_readwrite 账号，对schema db_prj中的表进行增删查改操作： ``` # 普通DML和DQL操作不受影响。 INSERT INTO db_prj.table1 (col2) VALUES(88),(99); SELECT * FROM db_prj.table1; --- db_prj_user_readwrite 没有 DDL（CREATE、DROP、ALTER）权限 CREATE TABLE db_prj.table2(id int); ERROR: permission denied for schema db_prj LINE 1: create table db_prj.table2(id int); DROP TABLE db_prj.table1; ERROR: must be owner of table test ALTER TABLE db_prj.table1 ADD col3 int; ERROR: must be owner of table test CREATE INDEX idx_xxxx on db_prj.table1(col1); ERROR: must be owner of table test ``` - 使用 db_prj_user_readonly 账号，对schema db_prj 中的表进行操作： ``` INSERT INTO db_prj.table1 (col2) VALUES(88),(99); ERROR: permission denied for table table1 SELECT id,name FROM db_prj.table1 limit 1; col1 | col2 ----+------- 1 | 88 (1 row) ``` **场景3：给其他项目用户访问授权** 如果有另外1个项目 db_prj1，需求为账号 db_prj1_user_readwrite 增加 db_prj 项目的表只读权限。DBA使用 root 账号做如下操作： ``` --- 给账号 db_prj1_user_readwrite 加上 db_prj_role2_readonly 权限集合。 GRANT db_prj_role2_readonly TO db_prj1_user_readwrite; ``` **场景4：项目新增 schema db_prj1，并授权给项目Role** db_prj1_user_readwrite 和 db_prj1_user_readonly 账号自动继承了相关Role的权限变更，不需要再额外操作。DBA使用 root 高权限账号做如下操作： ``` CREATE SCHEMA db_prj1 AUTHORIZATION db_prj_owner; --- 授权ROLE相关SCHEMA访问权限。 --- CREATE 使得 db_prj_owner 对schema db_prj1 中的表有 DDL（CREATE、DROP、ALTER）权限。 GRANT USAGE ON SCHEMA db_prj1 TO db_prj_role1_readwrite; GRANT USAGE ON SCHEMA db_prj1 TO db_prj_role2_readonly; ``` #### 账号权限查询 - 使用PostgreSQL客户端命令行终端连接RDS for PostgreSQL数据库。然后使用\\du命令查看所有用户和拥有的角色，例如： 图1使用\\du命令查询用户角色 ![](https://support.huaweicloud.com/bestpractice-rds-pg/zh-cn_image_0000002396526638.png "点击放大") 从查询结果中可以看出：db_prj_user_readonly账号的Member of列中，内容为db_prj_role2_readonly；db_prj_user_readwrite账号的Member of列中，内容为db_prj_role1_readwrite。 - 使用SQL查询用户角色： ``` SELECT r.rolname, r.rolsuper, r.rolinherit, r.rolcreaterole, r.rolcreatedb, r.rolcanlogin, r.rolconnlimit, r.rolvaliduntil, ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as memberof , r.rolreplication , r.rolbypassrls FROM pg_catalog.pg_roles r WHERE r.rolname !~ '^pg_' ORDER BY 1; ``` 图2使用SQL查询用户角色 ![](https://support.huaweicloud.com/bestpractice-rds-pg/zh-cn_image_0000002430007217.png "点击放大")