一、为什么 PostgreSQL 权限容易混乱

很多人刚接触 PostgreSQL 权限时，会下意识拿 MySQL 来类比：

1
2

MySQL：用户 + 数据库 + 表权限
PostgreSQL：角色 + 数据库 + schema + 对象权限 + 默认权限 + RLS

PostgreSQL 的权限体系更细，灵活性也更高，但如果没有先建立层级概念，就很容易出现下面这些问题：

  • 用户明明有表的 SELECT 权限，却仍然提示 permission denied for schema
  • 创建了只读用户，但用户还能在 public schema 里建表
  • 给了 GRANT SELECT ON ALL TABLES，新建表却无法查询
  • 不知道 CREATE USER 和 CREATE ROLE 到底有什么区别
  • 不知道 public schema 和 PUBLIC 角色是不是同一个东西
  • 删除用户时提示仍然有对象依赖，无法 DROP ROLE

要理解这些问题，必须先把 PostgreSQL 的权限模型分层看清楚。

二、PostgreSQL 的对象层级

PostgreSQL 常见层级如下：

1
2
3
4
5
6
7
8

PostgreSQL 实例
└── database
    └── schema
        ├── table
        ├── view
        ├── sequence
        ├── function
        └── type

一个 PostgreSQL 实例里可以有多个数据库。每个数据库内部又可以有多个 schema。表、视图、序列、函数等对象通常位于某个 schema 下面。

因此权限检查也不是只看“有没有表权限”，而是多层判断：

1
2
3
4
5

1. 角色能不能登录？
2. 角色能不能连接这个数据库？
3. 角色能不能使用这个 schema？
4. 角色能不能操作这个表、视图、序列或函数？
5. 如果启用了 RLS，角色能不能看到或修改某些行？

这也是 PostgreSQL 权限比 MySQL 更容易“看似授权了但仍然报错”的主要原因。

三、角色和用户的关系

PostgreSQL 中，用户和角色在底层是一套机制。

1
2

CREATE ROLE app_user;
CREATE USER app_user;

二者的核心区别是：

1
2

CREATE USER 默认带 LOGIN 属性
CREATE ROLE 默认不带 LOGIN 属性

也就是说：

1

CREATE USER app_user PASSWORD 'secret';

大致等价于：

1

CREATE ROLE app_user WITH LOGIN PASSWORD 'secret';

所以可以这样理解：

1
2

角色 role：权限主体，可以被授权，也可以授权给别人
用户 user：能够登录的角色

企业实践中，通常会把角色分成两类：

1
2

登录角色：给人或应用程序使用，例如 app_user、john、backup_user
权限角色：不允许登录，只承载权限，例如 report_readers、app_writers

例如：

1
2
3
4
5

CREATE ROLE report_readers NOLOGIN;

CREATE USER john WITH PASSWORD 'StrongPassword';

GRANT report_readers TO john;

这里 report_readers 是权限组，john 是实际登录用户。把权限授予 report_readers，再把 report_readers 授予 john，后续维护会更清晰。

四、角色是实例级别的

PostgreSQL 的角色是实例级别对象，不属于某一个 database。

例如一个实例中有三个数据库：

1
2
3

postgres
sales_db
test_db

执行：

1

CREATE USER app_user PASSWORD 'secret';

这个 app_user 在整个 PostgreSQL 实例中存在。

但是角色存在，不等于它能访问所有数据库。是否能连接某个数据库，还要看数据库级权限：

1

GRANT CONNECT ON DATABASE sales_db TO app_user;

所以要牢记：

1
2

角色存在是实例级概念
数据库访问是权限控制结果

五、超级用户

PostgreSQL 安装完成后通常会有一个超级用户：

1

postgres

它类似 MySQL 中的 root，拥有极高权限：

  • 可以创建和删除数据库
  • 可以创建和删除角色
  • 可以修改权限
  • 可以访问几乎所有对象
  • 可以绕过普通权限检查
  • 可以执行高危管理操作

生产环境中，不建议应用程序使用超级用户连接数据库。

错误示例：

1

应用程序使用 postgres 用户连接业务库

合理做法：

1
2
3
4
5

postgres / DBA 管理账号：只用于数据库管理
app_user：应用程序读写业务表
readonly_user：报表、查询、分析
backup_user：备份
monitor_user：监控

超级用户应当少用、受控、可审计。

六、角色属性权限

PostgreSQL 角色有一些属性，这些属性不是通过普通 GRANT SELECT 这类命令控制的，而是在创建或修改角色时设置。

常见角色属性包括：

1
2
3
4
5
6
7
8

SUPERUSER / NOSUPERUSER
CREATEDB / NOCREATEDB
CREATEROLE / NOCREATEROLE
LOGIN / NOLOGIN
INHERIT / NOINHERIT
REPLICATION / NOREPLICATION
CONNECTION LIMIT
VALID UNTIL

示例：

1
2
3
4
5

CREATE ROLE app_user
WITH LOGIN
PASSWORD 'StrongPassword'
CONNECTION LIMIT 50
VALID UNTIL '2026-12-31';

表示：

  • app_user 可以登录
  • 密码是 StrongPassword
  • 最多允许 50 个并发连接
  • 密码或角色有效期到 2026-12-31

修改角色属性使用 ALTER ROLE：

1
2
3

ALTER ROLE app_user CONNECTION LIMIT 100;
ALTER ROLE app_user VALID UNTIL '2027-12-31';
ALTER ROLE app_user NOLOGIN;

如果要给某个用户创建数据库的能力，也不是用 GRANT CREATE DATABASE，而是：

1

ALTER ROLE dev_user CREATEDB;

这是 PostgreSQL 权限管理中非常重要的分界线。

七、对象权限

另一类权限是对象权限，由 GRANT 和 REVOKE 控制。

常见对象权限包括：

权限 作用对象 说明
CONNECT database 是否允许连接数据库
CREATE database / schema 是否允许创建 schema 或在 schema 中创建对象
USAGE schema / sequence / type 是否允许使用 schema、序列、类型等
SELECT table / view / sequence 是否允许查询
INSERT table 是否允许插入
UPDATE table / column 是否允许更新
DELETE table 是否允许删除
TRUNCATE table 是否允许清空表
REFERENCES table / column 是否允许创建外键引用
TRIGGER table 是否允许创建触发器
EXECUTE function / procedure 是否允许执行函数或存储过程

示例：

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 EXECUTE ON FUNCTION calculate_discount(numeric) TO app_user;

对象权限的特点是更细，可以精确到数据库、schema、表、列、函数。

八、角色属性权限和对象权限的区别

这是 PostgreSQL 权限管理最容易混淆的点。

角色属性权限：

1
2
3

ALTER ROLE app_user CREATEDB;
ALTER ROLE app_user CREATEROLE;
ALTER ROLE app_user NOLOGIN;

对象权限：

1
2
3

GRANT CONNECT ON DATABASE sales_db TO app_user;
GRANT USAGE ON SCHEMA sales TO app_user;
GRANT SELECT ON orders TO app_user;

可以总结为：

1
2

角色自身能力：CREATE ROLE / ALTER ROLE 管理
数据库对象访问：GRANT / REVOKE 管理

例如：

1
2
3
4

能否创建数据库：角色属性 CREATEDB
能否连接数据库：数据库对象权限 CONNECT
能否在 schema 里建表：schema 对象权限 CREATE
能否查询表：表对象权限 SELECT

九、权限继承

PostgreSQL 支持把一个角色授予另一个角色：

1

GRANT report_readers TO john;

如果 john 具有 INHERIT 属性，那么登录后会自动继承 report_readers 的权限。

查看角色是否继承权限：

1
2
3

SELECT rolname, rolinherit
FROM pg_roles
WHERE rolname = 'john';

显式设置继承：

1

ALTER ROLE john INHERIT;

如果设置为 NOINHERIT：

1

ALTER ROLE john NOINHERIT;

那么 john 不会自动拥有 report_readers 的权限，需要手动切换：

1

SET ROLE report_readers;

企业环境中，大多数普通用户会使用默认的 INHERIT，这样权限组模型更直观。

十、推荐的权限设计思路

不要直接给每个用户授予一堆表权限。

不推荐：

1
2
3

GRANT SELECT ON ALL TABLES IN SCHEMA sales TO john;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO sarah;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO tom;

推荐：

1
2
3
4
5
6
7

CREATE ROLE sales_readers NOLOGIN;

GRANT CONNECT ON DATABASE sales_db TO sales_readers;
GRANT USAGE ON SCHEMA sales TO sales_readers;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO sales_readers;

GRANT sales_readers TO john, sarah, tom;

这样后续如果要调整权限，只需要调整 sales_readers，不需要逐个用户修改。

十一、总结

PostgreSQL 权限管理可以先抓住三句话：

1
2
3

1. 用户也是角色，能登录的角色就是用户。
2. 角色属性权限用 CREATE ROLE / ALTER ROLE 管理。
3. 数据库对象权限用 GRANT / REVOKE 管理。

在企业实践中，建议遵循：

1
2
3
4
5

使用 NOLOGIN 角色承载权限
使用 LOGIN 用户承载身份
通过 GRANT role TO user 建立权限继承
避免业务程序使用超级用户
坚持最小权限原则

后续再继续理解 database、schema、public、表权限、默认权限、RLS 和审计，就会顺很多。