本文介绍如何对RDS PostgreSQL实例中的账号权限进行精细化管理。
配置原则
创建角色（Role）作为权限集合，对权限管理的细分操作集中在Role上。Role没有登录权限。在Role的基础上创建user账号来作为登录账号使用。使用user = role + login权限的账号权限管理模型，当Role的权限变化时，user账号权限会自动跟随Role变化。
权限管理设计模型
该模型简单有效，推荐绝大多数客户使用。
- 1个RDS PostgreSQL高权限账号，具有所有权限，由少量资深DBA掌握。
- 项目或者团队维度，1个资源owner账号，2个基本Role：{project}_role_readwrite、{project}_role_readonly。
说明 如果有更多细分需求，可以根据需要新建Role。
- 在Role的基础上创建业务账号：
业务账号user = role + login权限。 - 1个项目或团队可以有多个Schema。权限分配尽量以Schema或Role为单位。
- 业务表请勿放到
schema public中。因为PostgreSQL默认所有用户对schema public都有CREATE和USAGE权限。
权限规划示例
本示例以项目维度进行权限管理示例，同样也适用于团队维度。
- DBA拥有RDS PostgreSQL实例的高权限账号，名称是dbsuperuser。
- 业务项目名称是rdspg，新建schema名称是rdspg、rdspg_1。
项目中新增的资源owner账号和Role规划如下：
| user/Role | schema中表权限 | schema中存储过程权限 |
|---|---|---|
| rdspg_owner (user)，是唯一的项目资源owner账号 |
|
|
| rdspg_role_readwrite (role) |
|
DQL（SELECT，调用存储过程） ，若存储过程有DDL操作，会抛出权限相关错误。 |
| rdspg_role_readonly (role) | DQL(SELECT) | DQL（SELECT，调用存储过程），若存储过程有DDL或者DML操作，会抛出权限相关错误。 |
新增业务账号时，根据不同需求，采用如下管理模式创建：
- rdspg_readwrite = rdspg_role_readwrite + login权限
- rdspg_readonly = rdspg_role_readonly + login权限
配置步骤
应用场景示例
场景1：使用rdspg_owner账号：对schema rdspg中的表进行DDL（CREATE、DROP、ALTER）操作
CREATE TABLE rdspg.test(id bigserial primary key, name text);
CREATE INDEX idx_test_name on rdspg.test(name);场景2：使用 rdspg_readwrite/rdspg_readonly 账号进行业务开发
业务开发遵循最小权限原则，尽量使用rdspg_readonly账号，需要DML操作的地方才使用rdspg_readwrite账号。这样也方便在业务层做读写分离。
说明
- 业务层做读写分离，避免了自动读写分离中间件proxy带来的额外成本和性能损耗。
- 即使目前还没有使用只读实例，也建议区分 readonly客户端、readwrite客户端，为使用只读实例做准备。readonly客户端建议使用readonly账号，最小权限原则，规避权限误用。
- readonly客户端，使用readonly账号，设置JDBC URL：
只读实例1地址,只读实例2地址,读写实例地址。 - readwrite客户端，使用readwrite账号，设置JDBC URL：
读写实例地址。
- readonly客户端，使用readonly账号，设置JDBC URL：
- 使用rdspg_readwrite账号，对schema rdspg中的表进行DQL（SELECT）、DML（UPDATE、INSERT、DELETE）操作：
INSERT INTO rdspg.test (name) VALUES('name0'),('name1'); SELECT id,name FROM rdspg.test LIMIT 1; --- rdspg_readwrite没有 DDL（CREATE、DROP、ALTER）权限 CREATE TABLE rdspg.test2(id int); ERROR: permission denied for schema rdspg LINE 1: create table rdspg.test2(id int); DROP TABLE rdspg.test; ERROR: must be owner of table test ALTER TABLE rdspg.test ADD id2 int; ERROR: must be owner of table test CREATE INDEX idx_test_name on rdspg.test(name); ERROR: must be owner of table test - 使用rdspg_readonly账号，对schema rdspg中的表进行DQL（SELECT）操作：
INSERT INTO rdspg.test (name) VALUES('name0'),('name1'); ERROR: permission denied for table test SELECT id,name FROM rdspg.test LIMIT 1; id | name ----+------- 1 | name0 (1 row)
场景3：不同项目交叉授权
如果有另外1个项目employee，需求为账号employee_readwrite增加rdspg项目的表只读权限。DBA使用dbsuperuser高权限账号做如下操作：
--- 给账号 employee_readwrite 加上 rdspg_role_readonly 权限集合。
GRANT rdspg_role_readonly TO employee_readwrite;场景4：项目新增 schema rdspg_2，并授权给项目Role
rdspg_readwrite、rdspg_readonly、employee_readwrite账号自动继承了相关Role的权限变更，不需要再额外操作。DBA使用dbsuperuser
高权限账号做如下操作：
CREATE SCHEMA rdspg_1 AUTHORIZATION rdspg_owner;
--- 授权ROLE相关SCHEMA访问权限。
--- CREATE 使得 rdspg_role_admin 对schema rdspg_1中的表有 DDL（CREATE、DROP、ALTER）权限。
GRANT USAGE ON SCHEMA rdspg_1 TO rdspg_role_readwrite;
GRANT USAGE ON SCHEMA rdspg_1 TO rdspg_role_readonly;账号权限查询
通过本文介绍的账号权限管理模型创建的账号，可以通过如下方式查询具体权限信息。
- 使用PostgreSQL客户端命令行终端连接RDS PostgreSQL数据库，具体请参见连接PostgreSQL实例。然后使用
\du命令查看：
从上述查询结果示例中可以看出：employee_readwrite账号的Member of列中，内容为
rdspg_role_readonly,employee_role_readwrite，因此，此账号对employee项目表具有DQL和DML权限，对rdspg项目表具有DQL权限。 - 使用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;