PostgreSQL创建带DML/DDL/DQL权限的3个用户权限配置求助

前提说明 #

操作需要使用PostgreSQL超级用户（如postgres）执行，普通用户无权限创建用户及分配模式级权限。

1. 创建三个用户 #

替换示例密码为安全的自定义密码，创建所需用户：

-- 创建DML用户（拥有SELECT、UPDATE、DELETE权限）
CREATE USER dml_user WITH PASSWORD 'your_secure_password';

-- 创建DDL用户（拥有CREATE、DROP、ALTER、TRUNCATE、INSERT权限）
CREATE USER ddl_user WITH PASSWORD 'your_secure_password';

-- 创建DQL用户（仅拥有SELECT权限）
CREATE USER dql_user WITH PASSWORD 'your_secure_password';

2. 配置DDL用户的模式权限 #

你之前无法授予CREATE/DROP权限，是因为这些是模式级权限，而非表级权限。需给DDL用户授予public模式的创建和使用权限：

-- 允许DDL用户在public模式下创建、删除对象
GRANT CREATE, USAGE ON SCHEMA public TO ddl_user;

DDL用户作为自己创建表的所有者，默认拥有该表的ALTER/TRUNCATE/DROP/INSERT权限，无需额外授权。

3. 设置默认权限继承（关键） #

要让DDL用户创建的新表自动赋予其他用户对应权限，需配置默认权限规则：

-- 让DML用户自动获得DDL用户新表的SELECT、UPDATE、DELETE权限
ALTER DEFAULT PRIVILEGES FOR USER ddl_user IN SCHEMA public
GRANT SELECT, UPDATE, DELETE ON TABLES TO dml_user;

-- 让DQL用户自动获得DDL用户新表的SELECT权限
ALTER DEFAULT PRIVILEGES FOR USER ddl_user IN SCHEMA public
GRANT SELECT ON TABLES TO dql_user;

-- （可选）如果表包含自增序列，需授权序列权限给DML用户，避免更新报错
ALTER DEFAULT PRIVILEGES FOR USER ddl_user IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO dml_user;

4. 授予现有表的权限（如果数据库已有表） #

全新数据库可跳过此步；若public模式已有表，需手动授予权限：

-- 给DML用户现有表的SELECT、UPDATE、DELETE权限
GRANT SELECT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO dml_user;

-- 给DQL用户现有表的SELECT权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dql_user;

-- 给DDL用户现有表的INSERT权限（如需操作非自己创建的表）
GRANT INSERT ON ALL TABLES IN SCHEMA public TO ddl_user;

验证配置 #

• 切换到ddl_user创建测试表并插入数据：

  CREATE TABLE test_table (id SERIAL, name VARCHAR(50));
  INSERT INTO test_table (name) VALUES ('test');
  

• 切换到dml_user执行更新/删除操作：

  UPDATE test_table SET name = 'updated' WHERE id = 1;
  DELETE FROM test_table WHERE id = 1;
  

• 切换到dql_user执行查询操作：

  SELECT * FROM test_table;
  

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