PostgreSQL读写应用用户创建咨询：角色还是用户？

1. 用户 vs 角色：先搞明白本质 #

在PostgreSQL里，用户就是带LOGIN属性的角色，普通角色可以作为权限组来用——这和SQL Server的角色+用户逻辑类似。推荐先建一个权限角色（用来归集读写权限），再建一个可登录的用户并加入这个角色，后续调整权限时直接改角色就行，不用动用户，更灵活。

2. 完整SQL脚本步骤 #

直接用以下脚本就能完成配置，按需替换占位符：

步骤1：创建读写权限角色（权限组） #

-- 建一个专门的读写权限角色，不需要登录权限
CREATE ROLE app_readwrite;

步骤2：给角色分配指定Schema的读写权限 #

如果你的应用用的是自定义Schema（比如app_schema），就把下面的public替换成你的Schema名；用默认public的话直接用：

-- 授权使用Schema并允许创建对象（如果应用不需要建表，就去掉CREATE）
GRANT USAGE, CREATE ON SCHEMA public TO app_readwrite;

-- 给Schema下已有的所有表授权读写权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;

-- 确保后续新建的表自动继承该角色的读写权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;

-- 如果用到自增ID（依赖序列），必须给序列授权
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO app_readwrite;

步骤3：创建可登录的应用用户 #

-- 建一个带登录权限的用户（这就是PostgreSQL里的"用户"），设置强密码
CREATE ROLE app_user WITH LOGIN PASSWORD 'your_strong_password_here';

步骤4：让用户继承读写角色的权限 #

-- 把用户加入读写权限组，自动获得角色的所有权限
GRANT app_readwrite TO app_user;

3. 关键注意事项 #

• Schema必须指定：不要给全局权限，只针对应用实际用到的Schema授权，这是权限最小化的核心，比用管理员账号安全太多。
• 权限按需调整：如果应用不需要创建表，就把GRANT USAGE, CREATE里的CREATE去掉；如果只需要读，就删掉INSERT, UPDATE, DELETE。
• 验证权限：用新账号登录后执行读写操作测试，或者用以下SQL查看权限配置：

-- 查看用户的角色成员关系
SELECT usename, usesuper FROM pg_user WHERE usename = 'app_user';
-- 查看角色的权限
SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname IN ('app_readwrite', 'app_user');
-- 查看Schema的权限分配
SELECT nspname, privilege_type 
FROM pg_namespace 
JOIN pg_namespace_acl ON pg_namespace.oid = pg_namespace_acl.nspoid 
JOIN pg_user ON pg_namespace_acl.grantee = pg_user.usesysid 
WHERE usename = 'app_user';

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