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_prj2。

项目中新增的资源owner账号和Role规划如下：

配置操作步骤

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;

   

   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、db_prj1_user_readwrite 账号自动继承了相关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命令查询用户角色
  

  从查询结果中可以看出：db_prj_user_readwrite 账号的Member of列中，内容为db_prj_role2_readonly, db_prj1_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查询用户角色