PostgreSQL如何实现类似SQL Server的用户创建及权限配置操作

模型对应关系说明 #

PostgreSQL的权限体系和SQL Server略有区别：

• 全局可登录的角色（对应SQL Server的Login）是实例级共享的，不需要为每个库单独创建用户，只要给该角色授予目标库的对应权限即可，逻辑上和SQL Server的「Login映射到数据库User」效果完全一致。

前提 #

如果还没有创建对应全局登录角色（对应SQL Server提前创建的Login），先执行以下语句创建：

-- 请自行替换密码和角色名，角色名建议小写避免大小写敏感问题
CREATE ROLE myuser WITH LOGIN PASSWORD '自定义安全密码';

为指定数据库授权 #

首先切换到你要操作的目标数据库（示例中目标库名为mydb，请替换为实际库名），如果是用psql工具执行的话，切换命令为\c mydb，如果是用其他客户端直接连接到目标数据库即可。

然后依次执行以下SQL：

-- 授予数据库连接权限
GRANT CONNECT ON DATABASE mydb TO myuser;

-- 授予Schema权限（示例用默认public schema，替换为实际使用的schema即可）
-- USAGE 是访问schema下对象的前提，CREATE 对应你要求的创建权限
GRANT USAGE, CREATE ON SCHEMA public TO myuser;

-- 授予schema下现有所有表的查询、更新、删除权限
GRANT SELECT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;

-- 授予schema下现有所有序列的使用权限（存在自增主键场景必须加，否则写入会报错）
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO myuser;

-- 可选配置：设置默认权限，后续该schema下新建的表/序列会自动给该角色授权，无需每次手动赋权
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, UPDATE, DELETE ON TABLES TO myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO myuser;

快速方案（PostgreSQL 14+ 适用） #

如果你需要的是该角色拥有所有数据库的读写权限，可以直接使用PostgreSQL内置的预定义角色，和SQL Server加db_datareader/db_datawriter的逻辑一致：

GRANT pg_read_all_data, pg_write_all_data TO myuser;

注意：该方案授予的是全局所有库的读写权限，仅适合不需要限制数据库访问范围的场景

权限校验 #

可以通过以下语句验证权限是否配置正确：

-- 查看指定角色的表权限
SELECT table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'myuser';

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