本文介绍如何对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都有CREATEUSAGE权限。

权限规划示例

本示例以项目维度进行权限管理示例,同样也适用于团队维度。
  • DBA拥有RDS PostgreSQL实例的高权限账号,名称是dbsuperuser。
  • 业务项目名称是rdspg,新建schema名称是rdspg、rdspg_1。
项目中新增的资源owner账号和Role规划如下:
user/Role schema中表权限 schema中存储过程权限
rdspg_owner (user),是唯一的项目资源owner账号
  • DDL:CREATE、DROP、ALTER
  • DQL:SELECT
  • DML:UPDATE、INSERT、DELETE
  • DDL:CREATE、DROP、ALTER
  • DQL:SELECT,调用存储过程
rdspg_role_readwrite (role)
  • DQL:SELECT
  • DML:UPDATE、INSERT、DELETE
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. 创建项目资源owner账号rdspg_owner和项目Role。
    DBA使用dbsuperuser高权限账号执行如下操作。
    --- rdspg_owner 是项目管理账号,此处密码仅为示例,请注意修改。
    CREATE USER rdspg_owner WITH LOGIN PASSWORD 'asdfy181BASDfadasdbfas';
    
    CREATE ROLE rdspg_role_readwrite;
    CREATE ROLE rdspg_role_readonly;
    
    --- 设置: 对于rdspg_owner 创建的表,rdspg_role_readwrite 有 DQL(SELECT)、DML(UPDATE、INSERT、DELETE)权限。
    ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT ALL ON TABLES TO rdspg_role_readwrite;
    
    --- 设置: 对于rdspg_owner 创建的SEQUENCES,rdspg_role_readwrite 有 DQL(SELECT)、DML(UPDATE、INSERT、DELETE)权限。
    ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT ALL ON SEQUENCES TO rdspg_role_readwrite;
    
    --- 设置: 对于 rdspg_owner 创建的表, rdspg_role_readonly 只有 DQL(SELECT)权限。
    ALTER DEFAULT PRIVILEGES FOR ROLE rdspg_owner GRANT SELECT ON TABLES TO rdspg_role_readonly;
  2. 创建rdspg_readwrite、rdspg_readonly业务账号。
    DBA使用dbsuperuser高权限账号执行如下操作。
    --- rdspg_readwrite只有 DQL(SELECT)、DML(UPDATE、INSERT、DELETE)权限。
    CREATE USER rdspg_readwrite WITH LOGIN PASSWORD 'dfandfnapSDhf23hbEfabf';
    GRANT rdspg_role_readwrite TO rdspg_readwrite;
    
    --- rdspg_readonly只有 DQL(SELECT)权限。
    CREATE USER rdspg_readonly WITH LOGIN PASSWORD 'F89h912badSHfadsd01zlk';
    GRANT rdspg_role_readonly TO rdspg_readonly;
  3. 创建schema rdspg,并授权给项目Role。
    DBA使用dbsuperuser高权限账号执行如下操作。
    --- schema rdspg的owner是 rdspg_owner账号
    CREATE SCHEMA rdspg AUTHORIZATION rdspg_owner;
    
    --- 授权ROLE相关SCHEMA访问权限。
    GRANT USAGE ON SCHEMA rdspg TO rdspg_role_readwrite;
    GRANT USAGE ON SCHEMA rdspg TO rdspg_role_readonly;
    说明 rdspg_readwrite和rdspg_readonly自动继承了相关Role的权限变更,不需要再额外操作。

应用场景示例

场景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:读写实例地址
  • 使用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命令查看:\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;