构建最小权限角色层次（GRANT + ALTER DEFAULT PRIVILEGES）

命令

-- Group roles (NOLOGIN): object owner, read-only, read-write
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_readonly NOLOGIN;
CREATE ROLE app_readwrite NOLOGIN;
GRANT app_readonly TO app_readwrite;
CREATE SCHEMA app AUTHORIZATION app_owner;

-- Restrict PUBLIC's default privileges
REVOKE ALL ON DATABASE mydb FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Existing objects
GRANT CONNECT ON DATABASE mydb TO app_readonly;
GRANT USAGE ON SCHEMA app TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_readwrite;

-- Objects app_owner will create IN THE FUTURE
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT USAGE, SELECT ON SEQUENCES TO app_readwrite;

-- Login users get privileges only through group membership
CREATE ROLE web_app LOGIN PASSWORD 'Change#2026' IN ROLE app_readwrite;
CREATE ROLE report_app LOGIN PASSWORD 'Change#2026' IN ROLE app_readonly;

-- Configured default privileges (psql: \ddp)
SELECT pg_get_userbyid(d.defaclrole) AS owner_role,
       n.nspname AS schema_name,
       d.defaclobjtype AS object_type,
       d.defaclacl
FROM pg_default_acl d
LEFT JOIN pg_namespace n ON n.oid = d.defaclnamespace
ORDER BY 1, 2, 3;

作用

构建 owner、只读和读写组角色的层次结构；对现有和未来的表/序列授予正确的权限，并只通过组成员关系为应用用户授权。

适用场景

在建立新的应用 schema、修复以超级用户或表所有者身份连接的应用，以及在审计前简化权限时。

注意

ALTER DEFAULT PRIVILEGES 只适用于 FOR ROLE 中指定的角色之后创建的对象；如果迁移工具以其他用户身份建表，权限不会被应用，因此请用 SET ROLE app_owner 执行 DDL。GRANT ... ON ALL TABLES 只覆盖当时已存在的对象。在 PG15+ 上创建的数据库默认已不再向 PUBLIC 授予 public schema 的 CREATE 权限；从旧版本升级的数据库需要执行 REVOKE。REVOKE ALL ON DATABASE ... FROM PUBLIC 也会移除 CONNECT 和 TEMP；请单独授予需要它们的其他角色。请使用 psql \password，而不是在命令行中写密码（这样密码不会进入日志和历史记录）。