PostgreSQL 权限管理（四）：只读用户、应用读写用户与默认权限实践

一、为什么要单独设计只读用户和应用用户

生产环境中，不建议所有程序和人员都使用同一个数据库账号。

常见错误做法：

1
应用、报表、开发、DBA 都使用 postgres 或同一个 app_user

这样会带来几个问题：

无法区分是谁执行了操作
报表查询账号可能误删或误改数据
应用账号权限过大，一旦泄露风险很高
权限审计困难
无法做到最小权限

更合理的设计是：

1
2
3
4
5
6
7
readonly_user：只读查询
app_user：应用读写
etl_user：数据导入导出
backup_user：备份
monitor_user：监控
dba_user：日常管理
postgres：超级管理，不用于业务连接

本篇重点整理只读用户和应用读写用户的标准创建方法。

二、准备示例环境

假设数据库名为：

1
sales_db

业务 schema 为：

1
sales

创建 schema：

1
CREATE SCHEMA IF NOT EXISTS sales;

示例表：

1
2
3
4
5
6
7
8
9
CREATE TABLE sales.orders (
    id bigserial PRIMARY KEY,
    order_no text NOT NULL,
    customer_id bigint NOT NULL,
    amount numeric(12,2) NOT NULL,
    status text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

注意 bigserial 会创建序列，因此应用插入数据时除了表权限，还可能需要序列权限。

三、先处理 public schema 默认创建权限

企业环境中建议先收回所有用户在 public schema 中默认创建对象的权限：

1
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

这条语句的意思是：

1
从所有用户那里，收回在 public schema 中创建对象的权限。

它不会删除 public schema，也不会影响已经单独授权的用户。

如果某个用户确实需要在 public 中创建对象，可以单独授权：

1
GRANT USAGE, CREATE ON SCHEMA public TO app_user;

不过更推荐的做法是让业务对象放在独立 schema 中，例如 sales、app、auth，不要全部放在 public。

四、创建只读权限组

推荐先创建一个 NOLOGIN 角色作为权限组：

1
CREATE ROLE sales_readonly NOLOGIN;

这个角色不能直接登录，只用来承载权限。

授予数据库连接权限：

1
GRANT CONNECT ON DATABASE sales_db TO sales_readonly;

授予 schema 使用权限：

1
GRANT USAGE ON SCHEMA sales TO sales_readonly;

授予已有表查询权限：

1
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO sales_readonly;

如果只读用户需要读取序列当前值，可以授予序列查询权限：

1
GRANT SELECT ON ALL SEQUENCES IN SCHEMA sales TO sales_readonly;

普通只读查询多数情况下不需要序列权限，但报表 SQL 如果访问了序列，就需要单独授权。

五、让未来新表自动给只读权限

上面的：

1
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO sales_readonly;

只对已经存在的表生效。

如果以后新建表：

1
CREATE TABLE sales.customers (...);

sales_readonly 不会自动拥有 customers 的查询权限。

因此需要设置默认权限：

1
2
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
GRANT SELECT ON TABLES TO sales_readonly;

更严谨的写法是指定对象创建者：

1
2
ALTER DEFAULT PRIVILEGES FOR ROLE owner_user IN SCHEMA sales
GRANT SELECT ON TABLES TO sales_readonly;

这表示：

1
以后 owner_user 在 sales schema 中创建的新表，默认授予 sales_readonly 查询权限。

注意：默认权限只影响未来创建的对象，不会补授权历史对象。

所以完整逻辑是：

1
2
已有表：GRANT SELECT ON ALL TABLES
未来表：ALTER DEFAULT PRIVILEGES

六、创建具体只读用户

创建登录用户：

1
CREATE USER report_user WITH PASSWORD 'StrongPassword';

把只读权限组授予该用户：

1
GRANT sales_readonly TO report_user;

如果角色默认继承权限，report_user 登录后即可拥有 sales_readonly 的权限。

可以确认：

1
2
3
SELECT rolname, rolinherit
FROM pg_roles
WHERE rolname = 'report_user';

如果不是继承权限，可以设置：

1
ALTER ROLE report_user INHERIT;

analyst_read 和 john 的完整理解

再看一个更贴近练习环境的例子：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
-- 创建只读角色
CREATE ROLE analyst_read NOLOGIN;

GRANT CONNECT ON DATABASE test_db TO analyst_read;
GRANT USAGE ON SCHEMA schematest TO analyst_read;
GRANT SELECT ON ALL TABLES IN SCHEMA schematest TO analyst_read;

-- 分配用户
CREATE USER john WITH PASSWORD 'SecurePass123!';
GRANT analyst_read TO john;

这段 SQL 里有两个主体：

1
2
analyst_read：不能登录的权限组角色
john：可以登录的具体用户

analyst_read 的作用不是给人直接登录，而是承载一组权限：

1
2
3
能连接 test_db
能使用 schematest schema
能查询 schematest 下已经存在的所有表

john 是真实登录用户。执行：

1
GRANT analyst_read TO john;

表示让 john 成为 analyst_read 的成员。只要 john 是 INHERIT，登录后就会继承 analyst_read 的权限。

最终效果是：

1
2
3
4
5
6
john 可以登录
john 可以连接 test_db
john 可以访问 schematest
john 可以查询 schematest 下已有表
john 不能插入、更新、删除
john 不能建表

注意这里数据库级权限应该使用 CONNECT：

1
GRANT CONNECT ON DATABASE test_db TO analyst_read;

不能写成：

1
GRANT USAGE ON DATABASE test_db TO analyst_read;

因为 USAGE 是 schema 级常用权限，不是 database 级权限。

七、验证只读用户

切换或使用只读用户连接后，验证查询：

1
SELECT * FROM sales.orders LIMIT 10;

应该成功。

验证插入：

1
2
INSERT INTO sales.orders(order_no, customer_id, amount, status)
VALUES ('NO001', 1001, 99.90, 'created');

应该失败。

验证建表：

1
CREATE TABLE sales.test_readonly(id int);

应该失败。

验证权限函数：

1
2
3
SELECT has_schema_privilege('report_user', 'sales', 'USAGE');
SELECT has_table_privilege('report_user', 'sales.orders', 'SELECT');
SELECT has_table_privilege('report_user', 'sales.orders', 'INSERT');

预期：

1
2
3
USAGE = true
SELECT = true
INSERT = false

为什么 table_privileges 里看不到 john

如果使用下面这种权限组方式：

1
2
GRANT SELECT ON ALL TABLES IN SCHEMA schematest TO analyst_read;
GRANT analyst_read TO john;

再查询：

1
2
3
SELECT grantee, privilege_type, table_schema, table_name
FROM information_schema.table_privileges
WHERE grantee = 'john';

可能看不到结果。

这不是 john 没有权限，而是因为表权限直接授给了 analyst_read，john 是通过角色成员关系继承来的。information_schema.table_privileges 更适合看“权限直接授给了谁”。

应该先查权限角色本身：

1
2
3
4
SELECT grantee, privilege_type, table_schema, table_name
FROM information_schema.table_privileges
WHERE grantee = 'analyst_read'
ORDER BY table_schema, table_name, privilege_type;

再查 john 是否属于这个角色：

1
2
3
4
5
6
7
SELECT
    r.rolname AS role_name,
    m.rolname AS member_name
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
WHERE r.rolname = 'analyst_read';

如果返回：

1
2
3
role_name     | member_name
--------------+------------
analyst_read  | john

就说明 john 是 analyst_read 的成员。

如果想验证 john 实际有没有某张表的查询权限，最直接使用：

1
SELECT has_table_privilege('john', 'schematest.t1', 'SELECT');

返回 true 或 t，就说明 john 实际可以查询这张表。

如果想把 john 继承来的表权限展开查看，可以使用：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT
    m.rolname AS user_name,
    r.rolname AS inherited_role,
    tp.table_schema,
    tp.table_name,
    tp.privilege_type
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
JOIN information_schema.table_privileges tp
  ON tp.grantee = r.rolname
WHERE m.rolname = 'john'
ORDER BY tp.table_schema, tp.table_name, tp.privilege_type;

这条 SQL 的逻辑是：

1
2
先找 john 继承了哪些角色
再找这些角色对哪些表有什么权限

这样就能看到类似：

1
2
3
user_name | inherited_role | table_schema | table_name | privilege_type
----------+----------------+--------------+------------+---------------
john      | analyst_read   | schematest   | t1         | SELECT

如果你一定想在 information_schema.table_privileges 里直接看到 john，就必须直接给 john 授权：

1
GRANT SELECT ON ALL TABLES IN SCHEMA schematest TO john;

但企业实践中，更推荐把权限授给 analyst_read 这类权限组，再把具体用户加入权限组。这样用户多了以后更容易管理。

八、创建应用读写权限组

应用用户通常需要对业务表进行增删改查。

先创建权限组：

1
CREATE ROLE sales_app_rw NOLOGIN;

授予数据库连接权限：

1
GRANT CONNECT ON DATABASE sales_db TO sales_app_rw;

授予 schema 使用权限：

1
GRANT USAGE ON SCHEMA sales TO sales_app_rw;

授予已有表读写权限：

1
2
3
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA sales
TO sales_app_rw;

授予已有序列权限：

1
2
3
GRANT USAGE, SELECT
ON ALL SEQUENCES IN SCHEMA sales
TO sales_app_rw;

如果应用需要清空表，才考虑：

1
GRANT TRUNCATE ON ALL TABLES IN SCHEMA sales TO sales_app_rw;

但 TRUNCATE 风险较高，通常不建议给普通应用用户。

九、应用用户的默认权限

为了让未来新表也自动给应用用户读写权限：

1
2
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO sales_app_rw;

为了让未来新序列自动给应用用户使用权限：

1
2
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
GRANT USAGE, SELECT ON SEQUENCES TO sales_app_rw;

同样，更严谨时要指定对象创建者：

1
2
3
4
5
ALTER DEFAULT PRIVILEGES FOR ROLE owner_user IN SCHEMA sales
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO sales_app_rw;

ALTER DEFAULT PRIVILEGES FOR ROLE owner_user IN SCHEMA sales
GRANT USAGE, SELECT ON SEQUENCES TO sales_app_rw;

十、创建应用登录用户

创建用户：

1
2
3
CREATE USER app_user
WITH PASSWORD 'StrongPassword'
CONNECTION LIMIT 50;

授予权限组：

1
GRANT sales_app_rw TO app_user;

设置默认搜索路径：

1
2
ALTER ROLE app_user IN DATABASE sales_db
SET search_path = sales, public;

这样应用 SQL 可以直接写：

1
SELECT * FROM orders;

PostgreSQL 会优先查找：

1
sales.orders

不过从严谨性看，核心业务 SQL 显式写 sales.orders 更不容易产生歧义。

十一、限制应用用户不该有的权限

应用用户通常不应该有：

1
2
3
4
5
6
7
SUPERUSER
CREATEDB
CREATEROLE
REPLICATION
public schema 的任意 CREATE 权限
非业务 schema 权限
高危函数 EXECUTE 权限

检查角色属性：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT
    rolname,
    rolsuper,
    rolcreatedb,
    rolcreaterole,
    rolreplication,
    rolcanlogin,
    rolconnlimit
FROM pg_roles
WHERE rolname = 'app_user';

普通应用用户应该类似：

1
2
3
4
5
rolsuper = false
rolcreatedb = false
rolcreaterole = false
rolreplication = false
rolcanlogin = true

十二、读写用户是否需要 CREATE 权限

大多数生产应用用户不需要在 schema 中创建表。

建表、变更表结构通常由：

1
2
3
4
DBA
迁移工具账号
发布流水线账号
schema owner

来完成。

如果应用运行时确实需要创建临时业务表或分区表，可以考虑单独设计：

1
2
app_runtime_user：普通运行账号，无 CREATE
app_migration_user：变更账号，有 CREATE / ALTER 相关能力

在 PostgreSQL 中，能否建表主要看：

1
GRANT CREATE ON SCHEMA sales TO app_migration_user;

不建议把这个权限给所有应用运行实例。

十三、完整示例

下面是一个较完整的只读和读写用户授权示例。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- 安全基线：取消所有用户在 public 中默认创建对象
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 业务 schema
CREATE SCHEMA IF NOT EXISTS sales;

-- 只读权限组
CREATE ROLE sales_readonly NOLOGIN;
GRANT CONNECT ON DATABASE sales_db TO sales_readonly;
GRANT USAGE ON SCHEMA sales TO sales_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO sales_readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA sales
GRANT SELECT ON TABLES TO sales_readonly;

-- 应用读写权限组
CREATE ROLE sales_app_rw NOLOGIN;
GRANT CONNECT ON DATABASE sales_db TO sales_app_rw;
GRANT USAGE ON SCHEMA sales TO sales_app_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA sales TO sales_app_rw;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA sales TO sales_app_rw;

ALTER DEFAULT PRIVILEGES IN SCHEMA sales
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO sales_app_rw;

ALTER DEFAULT PRIVILEGES IN SCHEMA sales
GRANT USAGE, SELECT ON SEQUENCES TO sales_app_rw;

-- 登录用户
CREATE USER report_user WITH PASSWORD 'StrongPassword';
CREATE USER app_user WITH PASSWORD 'StrongPassword' CONNECTION LIMIT 50;

-- 授予权限组
GRANT sales_readonly TO report_user;
GRANT sales_app_rw TO app_user;

-- 应用用户默认搜索路径
ALTER ROLE app_user IN DATABASE sales_db
SET search_path = sales, public;

十四、总结

创建只读用户不要只写：

1
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

更完整的思路是：

1
2
3
4
5
6
7
8
9
1. 回收 public schema 的默认 CREATE 权限
2. 创建 NOLOGIN 权限角色
3. 授予 CONNECT
4. 授予 schema USAGE
5. 授予已有表权限
6. 配置未来对象默认权限
7. 创建 LOGIN 用户
8. 把权限角色授予登录用户
9. 验证权限是否符合预期

应用读写用户也应遵循最小权限：

1
2
3
4
5
6
能读写业务表即可
不要给超级用户
不要给无关 schema 权限
不要默认给 CREATE
序列权限不要遗漏
未来新表要配置默认权限

这样设计出来的权限体系才适合企业环境长期维护。

一、为什么要单独设计只读用户和应用用户#

二、准备示例环境#

三、先处理 public schema 默认创建权限#

四、创建只读权限组#

五、让未来新表自动给只读权限#

六、创建具体只读用户#

analyst_read 和 john 的完整理解#

七、验证只读用户#

为什么 table_privileges 里看不到 john#

八、创建应用读写权限组#

九、应用用户的默认权限#

十、创建应用登录用户#

十一、限制应用用户不该有的权限#

十二、读写用户是否需要 CREATE 权限#

十三、完整示例#

十四、总结#

相关文章