PostgreSQL如何创建指定数据库权限的用户并查看其权限？

一、正确查看用户权限的方法 #

\du命令仅能显示角色的全局属性（比如超级用户、登录权限、创建数据库权限等），库级、Schema级、表级的权限需要用以下查询语句查看：

1. 查看角色全局属性（和\du输出一致）：

SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls
FROM pg_roles
WHERE rolname = 'your_username';

2. 查看数据库级权限：

SELECT grantee, privilege_type
FROM information_schema.database_privileges
WHERE datname = 'your_database_name';

3. 查看Schema级权限：

SELECT grantee, privilege_type
FROM information_schema.schema_privileges
WHERE schema_name = 'your_schema_name' AND grantee = 'your_username';

4. 查看表级权限（指定Schema下所有表）：

SELECT grantee, table_name, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'your_schema_name' AND grantee = 'your_username';

另外，在psql中可以用\dp your_schema.*快速查看指定Schema下所有表的权限。

二、为单个数据库创建不同权限的用户 #

pg_read_all_data这类预定义角色是全局生效的，要实现单库权限，需在目标数据库内搭建自定义权限体系：

操作前提 #

先切换到目标数据库执行命令（psql中用\c your_database_name），或者连接时直接指定该数据库。

1. 创建只读用户 #

-- 1. 创建登录用户
CREATE ROLE read_only_user WITH LOGIN PASSWORD 'your_secure_password';

-- 2. 授予数据库连接权限
GRANT CONNECT ON DATABASE your_database_name TO read_only_user;

-- 3. 授予Schema使用权限（替换为你的Schema名，比如public）
GRANT USAGE ON SCHEMA public TO read_only_user;

-- 4. 授予现有表的SELECT权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_user;

-- 5. 授予未来新建表的默认SELECT权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only_user;

2. 创建读写用户 #

-- 1. 创建登录用户
CREATE ROLE read_write_user WITH LOGIN PASSWORD 'your_secure_password';

-- 2. 授予数据库连接权限
GRANT CONNECT ON DATABASE your_database_name TO read_write_user;

-- 3. 授予Schema使用和创建权限
GRANT USAGE, CREATE ON SCHEMA public TO read_write_user;

-- 4. 授予现有表的读写权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write_user;

-- 5. 授予未来新建表的默认读写权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO read_write_user;

-- 6. 授予序列权限（处理自增ID等场景）
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO read_write_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO read_write_user;

3. 创建单库管理员用户 #

单库管理员拥有该数据库内所有对象的管理权限，无需全局超级用户权限：

-- 1. 创建登录用户
CREATE ROLE db_admin_user WITH LOGIN PASSWORD 'your_secure_password';

-- 2. 授予数据库全部权限
GRANT ALL PRIVILEGES ON DATABASE your_database_name TO db_admin_user;

-- 3. 授予Schema全部权限
GRANT ALL PRIVILEGES ON SCHEMA public TO db_admin_user;

-- 4. 授予现有表、序列的全部权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO db_admin_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO db_admin_user;

-- 5. 授予未来新建对象的默认全部权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO db_admin_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO db_admin_user;

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