PostgreSQL创建仅DDL/仅DML用户的权限配置问题

问题根源 #

PostgreSQL中，对象创建者默认拥有该对象的所有权限，单纯用ALTER DEFAULT PRIVILEGES REVOKE无法自动撤销创建者的隐含权限，必须明确针对创建者配置默认权限的撤销规则，同时要确保DDL用户仅持有Schema的DDL操作权限，无DML权限。

正确配置步骤 #

1. 创建用户与Schema #

先完成用户和目标Schema的创建，并分配基础Schema权限：

CREATE USER ddl WITH PASSWORD '1234';
CREATE USER dml WITH PASSWORD '1234';

CREATE SCHEMA new_schema;
-- 授予DDL用户Schema的创建、使用权限（允许建表/删表/修改表结构）
GRANT CREATE, USAGE ON SCHEMA new_schema TO ddl;
-- 授予DML用户Schema的使用权限（允许访问Schema内的表）
GRANT USAGE ON SCHEMA new_schema TO dml;

2. 配置默认权限：分离两类用户权限 #

针对DDL用户创建的表，同时完成DML用户授权、DDL用户自身DML权限撤销：

-- 给DML用户授予DDL用户新建表的增删改查权限
ALTER DEFAULT PRIVILEGES FOR ROLE ddl IN SCHEMA new_schema
GRANT INSERT, DELETE, SELECT, UPDATE ON TABLES TO dml;

-- 撤销DDL用户对自己新建表的所有DML权限
ALTER DEFAULT PRIVILEGES FOR ROLE ddl IN SCHEMA new_schema
REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON TABLES FROM ddl;

3. 权限验证 #

• 使用ddl用户登录创建表：

  CREATE TABLE new_schema.test_table(id INT);
  

  尝试执行SELECT * FROM new_schema.test_table;会提示权限不足，符合预期。
• 使用dml用户登录，可正常执行INSERT INTO new_schema.test_table VALUES(1);、SELECT * FROM new_schema.test_table;等操作，权限生效。

补充说明 #

• 默认权限仅对配置后新建的对象生效，若之前已有DDL用户创建的表，需手动批量撤销权限：

  REVOKE SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA new_schema FROM ddl;
  

• 上述配置仅限制DDL用户的DML操作，保留了其修改表结构、删除表的DDL权限，符合需求设定。

内容的提问来源于stack exchange，提问作者atx