数据库账号权限管理最佳实践_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

本文介绍如何对PolarDB PostgreSQL版集群中的数据库账号权限进行精细化管理。

配置原则

创建角色（Role）作为权限集合，对权限管理的细分操作集中在Role上。Role没有登录权限。在Role的基础上创建user账号来作为登录账号使用。使用user = role + login权限的账号权限管理模型，当Role的权限变化时，user账号权限会自动跟随Role变化。

权限管理设计模型

该模型简单有效，推荐绝大多数客户使用。

1个PolarDB 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拥有PolarDB PostgreSQL版集群的高权限账号，名称是dbsuperuser。
业务项目名称是polardbpg，新建Schema名称是polardbpg、polardbpg_1。

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

user/Role	Schema中表权限	Schema中存储过程权限
`polardbpg_owner (user)`，是唯一的项目资源Owner账号	DDL：CREATE、DROP、ALTER DQL：SELECT DML：UPDATE、INSERT、DELETE	DDL：CREATE、DROP、ALTER DQL：SELECT，调用存储过程
`polardbpg_role_readwrite (Role)`	DQL：SELECT DML：UPDATE、INSERT、DELETE	DQL（SELECT，调用存储过程），若存储过程有DDL操作，会抛出权限相关错误。
`polardbpg_role_readonly (Role)`	DQL(SELECT)	DQL（SELECT，调用存储过程），若存储过程有DDL或者DML操作，会抛出权限相关错误。

新增业务账号时，根据不同需求，采用如下管理模式创建：

polardbpg_readwrite = polardbpg_role_readwrite + login权限
polardbpg_readonly = polardbpg_role_readonly + login权限

配置步骤

创建高权限账号dbsuperuser。

创建项目资源Owner账号polardbpg_owner和项目Role。

DBA使用dbsuperuser高权限账号执行如下操作。

---创建项目管理账号 polardbpg_owner ,此处密码仅为示例，请注意修改。
CREATE USER polardbpg_owner WITH LOGIN PASSWORD 'asdfy181BASDfadasdbfas';

CREATE ROLE polardbpg_role_readwrite;
CREATE ROLE polardbpg_role_readonly;

--- 设置：对于 polardbpg_owner 创建的表，polardbpg_role_readwrite 有 DQL（SELECT）、DML（UPDATE、INSERT、DELETE）权限。
ALTER DEFAULT PRIVILEGES FOR ROLE polardbpg_owner GRANT ALL ON TABLES TO polardbpg_role_readwrite;

--- 设置：对于 polardbpg_owner 创建的SEQUENCES，polardbpg_role_readwrite 有 DQL（SELECT）、DML（UPDATE、INSERT、DELETE）权限。
ALTER DEFAULT PRIVILEGES FOR ROLE polardbpg_owner GRANT ALL ON SEQUENCES TO polardbpg_role_readwrite;

--- 设置：对于 polardbpg_owner 创建的表， polardbpg_role_readonly 只有 DQL（SELECT）权限。
ALTER DEFAULT PRIVILEGES FOR ROLE polardbpg_owner GRANT SELECT ON TABLES TO polardbpg_role_readonly;

创建polardbpg_readwrite、polardbpg_readonly业务账号。

DBA使用dbsuperuser高权限账号执行如下操作。

--- polardbpg_readwrite只有 DQL（SELECT）、DML（UPDATE、INSERT、DELETE）权限。
CREATE USER polardbpg_readwrite WITH LOGIN PASSWORD 'dfandfnapSDhf23hbEfabf';
GRANT polardbpg_role_readwrite TO polardbpg_readwrite;

--- polardbpg_readonly只有 DQL（SELECT）权限。
CREATE USER polardbpg_readonly WITH LOGIN PASSWORD 'F89h912badSHfadsd01zlk';
GRANT polardbpg_role_readonly TO polardbpg_readonly;

创建Schema polardbpg，并授权给项目Role。

DBA使用dbsuperuser高权限账号执行如下操作。

--- Schema polardbpg 的 owner 是 polardbpg_owner 账号
CREATE SCHEMA polardbpg AUTHORIZATION polardbpg_owner;

--- 授权 ROLE 相关 SCHEMA 访问权限。
GRANT USAGE ON SCHEMA polardbpg TO polardbpg_role_readwrite;
GRANT USAGE ON SCHEMA polardbpg TO polardbpg_role_readonly;

说明

polardbpg_readwrite和polardbpg_readonly自动继承了相关Role的权限变更，不需要再额外操作。

应用场景示例

场景1：使用polardbpg_owner账号：对Schema polardbpg中的表进行DDL（CREATE、DROP、ALTER）操作

CREATE TABLE polardbpg.test(id bigserial primary key, name text);
CREATE INDEX idx_test_name on polardbpg.test(name);

场景2：使用polardbpg_readwrite/polardbpg_readonly账号进行业务开发

业务开发遵循最小权限原则，尽量使用polardbpg_readonly账号，需要DML操作的地方才使用polardbpg_readwrite账号。这样也方便在业务层做读写分离。

说明

业务层做读写分离，可避免集群代理Proxy带来的额外成本和性能损耗。
建议区分readonly客户端、readwrite客户端。readonly客户端建议使readonly账号，最小权限原则，规避权限误用。
- readonly客户端，使用readonly账号，设置JDBC URL：集群只读地址1,集群只读地址2,集群读写地址。
- readwrite客户端，使用readwrite账号，设置JDBC URL：集群读写地址。

使用polardbpg_readwrite账号，对Schema polardbpg中的表进行DQL（SELECT）、DML（UPDATE、INSERT、DELETE）操作：

INSERT INTO polardbpg.test (name) VALUES('name0'),('name1');
SELECT id,name FROM polardbpg.test LIMIT 1;

--- polardbpg_readwrite没有 DDL（CREATE、DROP、ALTER）权限
CREATE TABLE polardbpg.test2(id int);
ERROR:  permission denied for schema polardbpg
LINE 1: create table polardbpg.test2(id int);

DROP TABLE polardbpg.test;
ERROR:  must be owner of table test

ALTER TABLE polardbpg.test ADD id2 int;
ERROR:  must be owner of table test

CREATE INDEX idx_test_name on polardbpg.test(name);
ERROR:  must be owner of table test

使用polardbpg_readonly账号，对Schema polardbpg中的表进行DQL（SELECT）操作：

INSERT INTO polardbpg.test (name) VALUES('name0'),('name1');
ERROR:  permission denied for table test

SELECT id,name FROM polardbpg.test LIMIT 1;
 id | name
----+-------
  1 | name0
(1 row)

场景3：不同项目交叉授权

如果已存在另外1个项目employee，需求为账号employee_readwrite增加polardbpg项目的表只读权限。DBA可使用dbsuperuser高权限账号做如下操作：

说明

employee项目的权限规划和配置步骤与polardbpg项目一致，您可按需配置。

--- 给账号 employee_readwrite 加上 polardbpg_role_readonly 权限集合。
GRANT polardbpg_role_readonly TO employee_readwrite;

场景4：项目新增Schema polardbpg_1，并授权给项目Role

polardbpg_readwrite、polardbpg_readonly、employee_readwrite账号自动继承了相关Role的权限变更，不需要再额外操作。DBA使dbsuperuser高权限账号做如下操作：

CREATE SCHEMA polardbpg_1 AUTHORIZATION polardbpg_owner;

--- 授权ROLE相关SCHEMA访问权限。
--- 使得 polardbpg_role_admin 对 Schema polardbpg_1中的表有 DDL（CREATE、DROP、ALTER）权限。
GRANT USAGE ON SCHEMA polardbpg_1 TO polardbpg_role_readwrite;
GRANT USAGE ON SCHEMA polardbpg_1 TO polardbpg_role_readonly;

账号权限查询

通过本文介绍的账号权限管理模型创建的账号，可以通过如下方式查询具体权限信息。

使用命令行连接PolarDB集群，然后使用\du命令查看：
从上述查询结果示例中可以看出：employee_readwrite账号的Member of列中，内容为polardbpg_role_readonly,employee_role_readwrite，因此，此账号对employee项目表具有DQL和DML权限，对polardbpg项目表具有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;