因 openGauss 是华为在 2020.06.30 基于 PostgreSQL 9.2.4 开源。因此，此模板也适用于 openGauss。
1. 类 Oracle 权限模板
1.1. 权限模板概述
类 Oracle 权限模板中，user 即 schema。权限逻辑简洁清晰明了，适用于 user 较少的场景。权限模板的逻辑如下图所示：
- 用户
app1、app2为业务用户，仅可在数据库proda内同名的schema中读写对象。 - 用户
app3为业务系统用户，仅可在数据库prodb内同名的schema中读写对象。 - 用户
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 较多的场景。权限模板逻辑如下图所示。
- 禁用
public role与public schema。 - 创建用于存放数据库对象的
schema。如app。 - 为每个应用创建专用的
role。如创建readwrite role用于读写类应用、readonly role用于只读类应用； - 为
role添加权限，使其拥有该类应用所需的最小权限。如只为readonly role添加SELECT权限； - 为每个应用或不同功能，创建专用的
user。如创建app1_user和app2_user用于读写应用、rpt1_user和rpt2_user用于报表应用； - 为
user分配适当的role：如将readwrite授予app1_user与app2_user、将readonly授予rpt1_user与rpt2_user； - 可以随时从
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";
复制最后修改时间：2023-11-17 11:10:05
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
目录
