PostgreSQL用户权限配置：为应用及迁移用户分配权限

核心问题分析 #

你之前的操作无效，原因在于PostgreSQL的权限是分层生效的：

• 数据库级别的GRANT仅授予连接、创建Schema等顶层权限，无法直接控制表级操作；
• 预定义角色（如pg_read_all_data）是全局生效的，会覆盖所有Schema（包括系统表所在的pg_catalog），不符合你限制访问系统表的需求；
• 若未先授予Schema的USAGE权限，即使给了表的CRUD权限，用户也无法访问该Schema下的表。

分步配置方案 #

1. 创建目标用户

首先创建两个所需用户（替换密码为安全密码）：

-- 创建应用CRUD用户
CREATE ROLE "application-user" WITH LOGIN PASSWORD 'your_secure_app_password';

-- 创建迁移用户
CREATE ROLE "migration-user" WITH LOGIN PASSWORD 'your_secure_mig_password';

2. 调整Public Schema的默认权限

默认情况下，public Schema的权限会开放给所有用户（通过public角色继承），先撤销这个默认权限，避免用户访问到非业务表：

REVOKE ALL ON SCHEMA public FROM public;

3. 配置Application-User的CRUD权限

这个用户仅需对public下的业务表拥有CRUD权限：

-- 授予访问public Schema的权限
GRANT USAGE ON SCHEMA public TO "application-user";

-- 授予现有业务表的CRUD权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO "application-user";

-- 设置默认权限：未来创建的表自动继承CRUD权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "application-user";

-- 若业务表包含自增字段（serial/identity），需授予序列权限
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO "application-user";
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT USAGE, SELECT ON SEQUENCES TO "application-user";

4. 配置Migration-User的迁移权限

这个用户需要创建、修改表结构的权限：

-- 授予public Schema的使用和创建权限（允许创建表、索引等对象）
GRANT USAGE, CREATE ON SCHEMA public TO "migration-user";

-- 授予现有表的ALTER权限（允许修改表结构）
GRANT ALTER ON ALL TABLES IN SCHEMA public TO "migration-user";

-- 设置默认权限：未来创建的表自动允许ALTER
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT ALTER ON TABLES TO "migration-user";

-- 若需要创建函数、触发器等对象，可补充以下权限
GRANT CREATE ON FUNCTIONS IN SCHEMA public TO "migration-user";
GRANT CREATE ON TRIGGERS IN SCHEMA public TO "migration-user";

验证权限 #

完成配置后，可通过以下语句验证权限是否生效：

-- 查看application-user的表权限
SELECT table_name, privilege_type 
FROM information_schema.table_privileges 
WHERE grantee = 'application-user';

-- 查看migration-user的schema权限
SELECT schema_name, privilege_type 
FROM information_schema.schema_privileges 
WHERE grantee = 'migration-user';

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