PostgreSQL 权限管理（三）：GRANT、REVOKE 与对象权限详解

一、GRANT 和 REVOKE 解决什么问题

PostgreSQL 中，角色属性和对象权限是两类不同的权限。

角色属性例如：

1
2
3
4
5
6
LOGIN
SUPERUSER
CREATEDB
CREATEROLE
CONNECTION LIMIT
VALID UNTIL

这些通常通过 CREATE ROLE 或 ALTER ROLE 管理。

对象权限例如：

1
2
3
4
5
6
7
8
CONNECT
USAGE
CREATE
SELECT
INSERT
UPDATE
DELETE
EXECUTE

这些通常通过 GRANT 和 REVOKE 管理。

简单说：

1
2
GRANT：授予权限
REVOKE：回收权限

二、数据库级权限

数据库级常见权限有：

1
2
3
CONNECT：允许连接数据库
CREATE：允许在数据库中创建 schema
TEMPORARY / TEMP：允许创建临时表

允许用户连接数据库：

1
GRANT CONNECT ON DATABASE sales_db TO app_user;

回收连接权限：

1
REVOKE CONNECT ON DATABASE sales_db FROM app_user;

允许用户在数据库中创建 schema：

1
GRANT CREATE ON DATABASE sales_db TO dev_user;

允许创建临时表：

1
GRANT TEMPORARY ON DATABASE sales_db TO app_user;

注意：数据库上的 CREATE 不是建表权限，而是创建 schema 的权限。能否建表，要看 schema 上是否有 CREATE。

数据库上不能授予 USAGE

初学时很容易把 schema 的 USAGE 写到 database 上，例如：

1
GRANT USAGE ON DATABASE test_db TO analyst_read;

这条语句会报错：

1
ERROR: invalid privilege type USAGE for database

原因是 USAGE 不是 database 级权限。database 上常用的权限只有：

1
2
3
CONNECT：允许连接数据库
CREATE：允许在数据库中创建 schema
TEMPORARY / TEMP：允许创建临时表

如果目的是允许角色连接 test_db，应该写：

1
GRANT CONNECT ON DATABASE test_db TO analyst_read;

如果目的是允许角色访问某个 schema，才使用 USAGE ON SCHEMA：

1
GRANT USAGE ON SCHEMA public TO analyst_read;

所以要记住：

1
2
DATABASE 用 CONNECT
SCHEMA 用 USAGE

三、schema 级权限

schema 上最常用的是：

1
2
USAGE：允许使用 schema
CREATE：允许在 schema 中创建对象

只允许访问 schema：

1
GRANT USAGE ON SCHEMA sales TO readonly;

允许在 schema 中创建对象：

1
GRANT CREATE ON SCHEMA sales TO app_user;

通常写成：

1
GRANT USAGE, CREATE ON SCHEMA sales TO app_user;

回收创建权限：

1
REVOKE CREATE ON SCHEMA sales FROM app_user;

最常见的权限报错之一是：

1
permission denied for schema sales

很多时候原因不是没有表权限，而是没有 schema 的 USAGE 权限。

正确授权通常需要：

1
2
GRANT USAGE ON SCHEMA sales TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO readonly;

四、表级权限

表上常见权限包括：

1
2
3
4
5
6
7
SELECT
INSERT
UPDATE
DELETE
TRUNCATE
REFERENCES
TRIGGER

给单表查询权限：

1
GRANT SELECT ON TABLE orders TO readonly;

TABLE 关键字可以省略：

1
GRANT SELECT ON orders TO readonly;

给应用用户读写权限：

1
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user;

给 schema 下所有已有表授权：

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

给多个 schema 下所有已有表授权：

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

回收权限：

1
2
REVOKE DELETE ON orders FROM app_user;
REVOKE ALL ON orders FROM old_user;

五、列级权限

PostgreSQL 支持列级授权，常用于限制用户只能修改某些字段。

例如客服用户只能更新订单状态和更新时间：

1
2
3
GRANT UPDATE (status, updated_at)
ON orders
TO support_user;

也可以限制只查询某些列：

1
2
3
GRANT SELECT (id, order_no, status)
ON orders
TO report_user;

列级权限适合保护敏感字段，例如：

1
2
3
4
5
6
身份证号
手机号
银行卡号
工资
成本价
医疗记录

不过实际项目中，敏感字段保护也常结合视图：

1
2
3
4
5
CREATE VIEW orders_public AS
SELECT id, order_no, status, created_at
FROM orders;

GRANT SELECT ON orders_public TO report_user;

这种方式更容易控制查询范围。

六、序列权限

PostgreSQL 中自增列通常依赖序列。

如果用户有表的 INSERT 权限，但插入时报错：

1
permission denied for sequence orders_id_seq

说明缺少序列权限。

常见授权：

1
GRANT USAGE, SELECT ON SEQUENCE orders_id_seq TO app_user;

给 schema 下所有已有序列授权：

1
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA sales TO app_user;

如果使用 nextval()，通常需要 USAGE 权限。

因此应用读写用户的完整授权往往包括：

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

七、函数和过程权限

函数执行权限使用 EXECUTE：

1
GRANT EXECUTE ON FUNCTION calculate_discount(numeric) TO app_user;

如果函数有重载，参数类型必须写清楚：

1
2
GRANT EXECUTE ON FUNCTION calculate_discount(numeric) TO app_user;
GRANT EXECUTE ON FUNCTION calculate_discount(numeric, text) TO app_user;

给 schema 下所有已有函数授权：

1
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA sales TO app_user;

回收函数执行权限：

1
REVOKE EXECUTE ON FUNCTION calculate_discount(numeric) FROM app_user;

安全上要特别注意 SECURITY DEFINER 函数。它会以函数所有者权限执行，如果函数写得不严谨，可能造成越权。

八、ALL TABLES 只影响已有表

这是 PostgreSQL 权限管理中非常重要的坑。

执行：

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

只会给 sales schema 下当前已经存在的表授权。

之后新建的表不会自动继承这个授权。

如果希望未来新建表也自动授权，需要设置默认权限：

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

应用读写用户通常还需要：

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

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

注意：默认权限是对“未来由某个角色创建的对象”生效的。执行 ALTER DEFAULT PRIVILEGES 时要确认当前执行者是谁，必要时使用：

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

这表示：以后 owner_user 在 sales schema 里创建的表，默认授予 readonly 查询权限。

九、WITH GRANT OPTION

普通授权：

1
GRANT SELECT ON orders TO user_a;

user_a 可以查询 orders，但不能把这个权限再授予别人。

如果使用：

1
GRANT SELECT ON orders TO user_a WITH GRANT OPTION;

那么 user_a 可以继续执行：

1
GRANT SELECT ON orders TO user_b;

企业环境中要谨慎使用 WITH GRANT OPTION，否则权限可能扩散，后续审计困难。

十、PUBLIC 授权

PUBLIC 表示所有角色。

例如：

1
GRANT CONNECT ON DATABASE sales_db TO PUBLIC;

表示所有用户都可以连接 sales_db。

回收：

1
REVOKE CONNECT ON DATABASE sales_db FROM PUBLIC;

生产环境中，建议谨慎给 PUBLIC 授权，尤其是：

1
2
GRANT CREATE ON SCHEMA public TO PUBLIC;
GRANT EXECUTE ON FUNCTION sensitive_func() TO PUBLIC;

安全基线中常见操作：

1
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

十一、常见权限拒绝排查顺序

遇到权限拒绝时，不要只盯着表权限。建议按层级排查。

第一步，确认角色能登录：

1
2
3
SELECT rolname, rolcanlogin
FROM pg_roles
WHERE rolname = 'app_user';

第二步，确认能连接数据库：

1
SELECT has_database_privilege('app_user', 'sales_db', 'CONNECT');

第三步，确认有 schema 使用权限：

1
SELECT has_schema_privilege('app_user', 'sales', 'USAGE');

第四步，确认有表权限：

1
SELECT has_table_privilege('app_user', 'sales.orders', 'SELECT');

第五步，确认序列权限：

1
SELECT has_sequence_privilege('app_user', 'sales.orders_id_seq', 'USAGE');

第六步，确认是否有 RLS 策略：

1
2
3
4
SELECT *
FROM pg_policies
WHERE schemaname = 'sales'
  AND tablename = 'orders';

十二、查看已有授权

在 psql 中可以使用：

1
2
3
\du
\dp
\dn+

SQL 查询表权限：

1
2
3
4
SELECT grantee, privilege_type, table_schema, table_name
FROM information_schema.table_privileges
WHERE grantee NOT IN ('postgres', 'PUBLIC')
ORDER BY grantee, table_schema, table_name, privilege_type;

查看角色成员关系：

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
ORDER BY r.rolname, m.rolname;

查看 schema 权限可以结合：

1
2
3
4
5
6
SELECT
    nspname,
    nspacl
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
  AND nspname <> 'information_schema';

十三、总结

GRANT 和 REVOKE 管的是对象权限。

常用授权顺序可以记成：

1
2
3
4
5
6
数据库 CONNECT
schema USAGE
表 SELECT / INSERT / UPDATE / DELETE
序列 USAGE / SELECT
函数 EXECUTE
默认权限 ALTER DEFAULT PRIVILEGES

只读用户至少需要：

1
2
3
GRANT CONNECT ON DATABASE sales_db TO readonly;
GRANT USAGE ON SCHEMA sales TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO readonly;

读写应用用户通常需要：

1
2
3
4
GRANT CONNECT ON DATABASE sales_db TO app_user;
GRANT USAGE ON SCHEMA sales TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA sales TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA sales TO app_user;

如果要覆盖未来新表，还必须配置：

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

理解这些细节，才能避免“已经授权但还是报 permission denied”的常见问题。

一、GRANT 和 REVOKE 解决什么问题#

二、数据库级权限#

数据库上不能授予 USAGE#

三、schema 级权限#

四、表级权限#

五、列级权限#

六、序列权限#

七、函数和过程权限#

八、ALL TABLES 只影响已有表#

九、WITH GRANT OPTION#

十、PUBLIC 授权#

十一、常见权限拒绝排查顺序#

十二、查看已有授权#

十三、总结#

相关文章