PostgreSQL 中的权限模板推荐

数据运维 2023-11-16 张二河 手机阅读

因 openGauss 是华为在 2020.06.30 基于 PostgreSQL 9.2.4 开源。因此，此模板也适用于 openGauss。

1. 类 Oracle 权限模板

1.1. 权限模板概述

类 Oracle 权限模板中，user 即 schema。权限逻辑简洁清晰明了，适用于 user 较少的场景。权限模板的逻辑如下图所示：

  1. 用户 app1、app2 为业务用户，仅可在数据库 proda 内同名的 schema 中读写对象。
  2. 用户 app3 为业务系统用户，仅可在数据库 prodb 内同名的 schema 中读写对象。
  3. 用户 dev1 为开发人员专用的只读用户，仅可读取用户 app1、 app2、app3 的数据。

1.2. 授权指令

1.2.1. 创建数据库

[postgres@h221 ~]$ psql -U postgres -d postgres -p 5432
CREATE DATABASE proda WITH ENCODING 'UTF8' template = template0;
CREATE DATABASE prodb WITH ENCODING 'UTF8' template = template0;
REVOKE ALL ON DATABASE proda FROM PUBLIC; -- 撤销角色 PUBLIC（即所有用户）连接数据库 proda 的权限
REVOKE ALL ON DATABASE prodb FROM PUBLIC; -- 撤销角色 PUBLIC（即所有用户）连接数据库 prodb 的权限

1.2.2.创建业务用户（读写）

  • 执行 create user 创建用户时，openGauss 会在所连接的数据库中创建与 user 同名的 schema。
  • 用户 app1、app2、app3 为业务用户，在各自同名的 schema 中拥有读写权限；

c proda postgres -- 通过 postgres 用户连接到 proda 数据库
CREATE USER app1 PASSWORD "Test@123"; -- 创建业务用户，将自动创建同名 schema
CREATE USER app2 password "Test@123"; -- 创建业务用户，将自动创建同名 schema
REVOKE ALL ON SCHEMA public FROM PUBLIC; -- 回收 PUBLIC（所有用户）在 public schema 的 ALL 权限
GRANT CONNECT ON DATABASE proda TO app1; -- 显式授予用户 app1 连接数据库 proda 的权限
GRANT CONNECT ON DATABASE proda TO app2; -- 显式授予用户 app2 连接数据库 proda 的权限

c prodb postgres -- 通过 postgres 用户连接到 prodb 数据库
CREATE USER app3 PASSWORD "Test@123"; -- 创建业务用户，将自动创建同名 schema
REVOKE ALL ON SCHEMA public FROM PUBLIC; -- 回收 PUBLIC（所有用户）在 public schema 的 ALL 权限
GRANT CONNECT ON DATABASE prodb TO app3; -- 显式授予用户 app3 连接数据库 prodb 的权限

openGauss 在创建用户时，会自动创建与用户同名的 schema。

1.2.3.创建只读用户

  • 只读用户 dev1，仅可读取用户 app1、app2、app3 的数据。
  • 为只读用户 dev1 禁用 schema。

c postgres postgres -- 通过 postgres 用户连接到 postgres 数据库
CREATE USER dev1 password "Syzx123"; -- 创建只读用户，将自动创建同名 schema
DROP SCHEMA dev1; -- 删除自动创建的同名 schema
GRANT CONNECT ON DATABASE proda TO dev1; -- 显式授予用户 dev1 连接数据库 proda 的权限
GRANT CONNECT ON DATABASE prodb TO dev1; -- 显式授予用户 dev1 连接数据库 prodb 的权限

c proda postgres -- 通过 postgres 用户连接到 proda 数据库
GRANT USAGE ON SCHEMA app1 TO dev1; -- 授予 dev1 用户可使用 schema app1 的权限
GRANT USAGE ON SCHEMA app2 TO dev1; -- 授予 dev1 用户可使用 schema app2 的权限
GRANT SELECT ON ALL TABLES IN SCHEMA app1 TO dev1; -- 存量表授权
ALTER DEFAULT PRIVILEGES FOR USER app1 IN SCHEMA app1 GRANT SELECT ON TABLES TO dev1; -- 增量表授权
GRANT SELECT ON ALL TABLES IN SCHEMA app2 TO dev1; -- 存量表授权
ALTER DEFAULT PRIVILEGES FOR USER app2 IN SCHEMA app2 GRANT SELECT ON TABLES TO dev1; -- 增量表授权

c prodb postgres -- 通过 postgres 用户连接到 prodb 数据库
GRANT USAGE ON SCHEMA app3 TO dev1; -- 授予 dev1 用户可使用 schema app3 的权限
GRANT SELECT ON ALL TABLES IN SCHEMA app3 TO dev1; -- 存量表授权
ALTER DEFAULT PRIVILEGES FOR USER app3 IN SCHEMA app3 GRANT SELECT ON TABLES TO dev1; -- 增量表授权

  • 存量表 指在执行 GRANT 时，schema 中已创建的表；增量表 指在执行 GRANT 时，schema 中还未创建，但将来会创建的表。
  • 通过 ALTER DEFAULT PRIVILEGES 为用户授权增量表权限时，需指定 FOR USER 子句。

1.2.4.修改 search_path

修改 search_path 为 $user，使每个用户仅可在其各自同名的 schema 中读写对象。

ALTER DATABASE proda SET search_path TO "$user";
ALTER DATABASE prodb SET search_path TO "$user";

2. AWS 权限模板

2.1. 权限模板概述

AWS 权限模板，启用了 role。适用于 user 较多的场景。权限模板逻辑如下图所示。

  1. 禁用 public role 与 public schema。
  2. 创建用于存放数据库对象的 schema。如 app。
  3. 为每个应用创建专用的 role。如创建 readwrite role 用于读写类应用、readonly role 用于只读类应用；
  4. 为 role 添加权限，使其拥有该类应用所需的最小权限。如只为 readonly role 添加 SELECT 权限；
  5. 为每个应用或不同功能，创建专用的 user。如创建 app1_user 和 app2_user 用于读写应用、rpt1_user 和 rpt2_user 用于报表应用；
  6. 为 user 分配适当的 role：如将 readwrite 授予 app1_user 与 app2_user、将 readonly 授予 rpt1_user 与 rpt2_user；
  7. 可以随时从 user 中回收 role，以删除 user 权限。

2.2. 授权指令

2.2.1. 创建数据库

[postgres@h224 ~]$ psql -U postgres -d postgres -p 5432
CREATE DATABASE prod_db WITH ENCODING 'UTF8' template = template0;

2.2.2. 创建用户与角色

postgres=# c prod_db postgres
CREATE SCHEMA app; -- 回收 PUBLIC（所有用户）在 public schema 的 ALL 权限，并显式授予用户连接数据库的权限
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE prod_db FROM PUBLIC;

-- create user
CREATE USER rpt1_user WITH PASSWORD 'Test123';
CREATE USER rpt2_user WITH PASSWORD 'Test123';
CREATE USER app1_user WITH PASSWORD 'Test123';
CREATE USER app2_user WITH PASSWORD 'Test123';

-- readonly role
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE prod_db TO readonly;
GRANT USAGE ON SCHEMA app TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO readonly; -- 存量表授权
ALTER DEFAULT PRIVILEGES for USER app1_user IN SCHEMA app GRANT SELECT ON TABLES TO readonly; -- 增量表授权
ALTER DEFAULT PRIVILEGES for USER app2_user IN SCHEMA app GRANT SELECT ON TABLES TO readonly; -- 增量表授权

-- readwrite
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE prod_db TO readwrite;
GRANT USAGE, CREATE ON SCHEMA app TO readwrite;

GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA app TO readwrite; -- 存量表授权
ALTER DEFAULT PRIVILEGES FOR USER app1_user IN SCHEMA app GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO readwrite; -- 增量表授权
ALTER DEFAULT PRIVILEGES FOR USER app2_user IN SCHEMA app GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO readwrite; -- 增量表授权
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO readwrite;
ALTER DEFAULT PRIVILEGES FOR USER app1_user IN SCHEMA app GRANT USAGE ON SEQUENCES TO readwrite;
ALTER DEFAULT PRIVILEGES FOR USER app2_user IN SCHEMA app GRANT USAGE ON SEQUENCES TO readwrite;

  • c prod_db postgres 以用户 postgres 身份连接数据库 prod_db。
  • 存量表 指在执行 GRANT 时，schema 中已创建的表；增量表 指在执行 GRANT 时，schema 中还未创建，但将来会创建的表。
  • 通过 ALTER DEFAULT PRIVILEGES 为用户授权增量表权限时，需指定 FOR USER 子句。

2.2.3. 为用户授权

postgres=# c prod_db postgres
GRANT readwrite to app1_user;
GRANT readwrite to app2_user;
GRANT readonly to rpt1_user;
GRANT readonly to rpt2_user;

2.2.4. 修改 search_path

修改 search_path 为 $user，使每个用户仅可在其各自同名的 schema 中读写对象。

postgres=# c prod_db postgres
ALTER DATABASE prod_db SET search_path TO "app";