Postgres 14中DDL用户仍可执行DML操作的问题排查

PostgreSQL 的默认权限配置导致了这个情况：

• public schema 的开放权限：PostgreSQL 默认会把 public schema 的 USAGE（访问schema权限）和 SELECT（查询数据权限）授予 PUBLIC 角色——所有数据库用户都自动属于这个角色。你的 tst_ddl 用户自然继承了这些权限，所以能查询表数据。
• 无限制的权限继承：创建用户时如果没明确限制，用户会自动继承数据库层面的默认开放权限，进一步放大了这个问题。

1. 收回 PUBLIC 在 public schema 的多余权限 #

先把所有用户默认拥有的查询和访问权限收回，按需分配：

REVOKE ALL ON SCHEMA public FROM PUBLIC;

2. 给 tst_ddl 仅授予必要的DDL权限 #

明确赋予它创建、修改表结构所需的权限（以public schema为例，如果你的对象在其他schema，替换成对应名称即可）：

GRANT CREATE, USAGE ON SCHEMA public TO tst_ddl;

如果需要让tst_ddl能管理自己创建的表，再设置默认权限：

ALTER DEFAULT PRIVILEGES FOR ROLE tst_ddl IN SCHEMA public
GRANT ALL ON TABLES TO tst_ddl;
ALTER DEFAULT PRIVILEGES FOR ROLE tst_ddl IN SCHEMA public
GRANT ALL ON SEQUENCES TO tst_ddl;
ALTER DEFAULT PRIVILEGES FOR ROLE tst_ddl IN SCHEMA public
GRANT ALL ON FUNCTIONS TO tst_ddl;

3. 检查并撤销额外的读取权限 #

确认tst_ddl没有被授予系统级的读取权限，比如pg_read_all_data，如果有就撤销：

REVOKE pg_read_all_data FROM tst_ddl;

4. 验证效果 #

切换到tst_ddl用户测试：尝试查询表数据应该提示权限不足，而创建、修改表结构的操作可以正常执行。

内容的提问来源于stack exchange，提问作者José Marcelo Dias de Oliveira