PostgreSQL集群创建只读用户的权限配置问题求助

针对你遇到的三个问题，以下是逐个解决的具体操作：

1. 让只读用户能访问service_user未来创建的表 #

你之前的alter default privileges仅对执行命令的用户（super_user）创建的对象生效，而所有实体归属于service_user，必须指定对service_user的对象设置默认权限：

-- 针对service_user未来创建的表授予只读用户SELECT权限
ALTER DEFAULT PRIVILEGES FOR USER service_user IN SCHEMA public GRANT SELECT ON TABLES TO read_only_user;

同时保留对现有表的授权：

GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_user;

2. 禁止只读用户创建表 #

PostgreSQL默认所有用户在public schema拥有CREATE权限，需要显式撤销：

-- 撤销只读用户在public schema的创建权限
REVOKE CREATE ON SCHEMA public FROM read_only_user;
-- 确保所有用户未来不会自动获得public schema的创建权限
REVOKE CREATE ON SCHEMA public FROM public;

3. 隐藏其他数据库名称 #

默认public角色拥有pg_database表的SELECT权限，导致所有用户能看到所有数据库名称，需要调整权限并添加行级限制：

-- 撤销所有用户对pg_database的公共查询权限
REVOKE SELECT ON pg_database FROM public;
-- 给超级用户和service_user保留查看所有数据库的权限
GRANT SELECT ON pg_database TO super_user, service_user;
-- 仅允许只读用户查看当前连接的数据库
GRANT SELECT (datname, datallowconn, datconnlimit) ON pg_database TO read_only_user;
-- 添加行级安全策略，限制只读用户只能看到当前数据库
ALTER TABLE pg_database ENABLE ROW LEVEL SECURITY;
CREATE POLICY read_only_db_access ON pg_database FOR SELECT TO read_only_user
USING (datname = current_database());

完整的单数据库执行流程 #

用super_user连接到目标数据库后，依次执行以下命令：

-- 创建只读用户（若未创建）
CREATE USER read_only_user WITH PASSWORD 'your_secure_password';

-- 授予现有表的SELECT权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_user;
-- 授予service_user未来创建表的SELECT权限
ALTER DEFAULT PRIVILEGES FOR USER service_user IN SCHEMA public GRANT SELECT ON TABLES TO read_only_user;

-- 禁止创建表
REVOKE CREATE ON SCHEMA public FROM read_only_user;
REVOKE CREATE ON SCHEMA public FROM public;

-- 隐藏其他数据库
REVOKE SELECT ON pg_database FROM public;
GRANT SELECT ON pg_database TO super_user, service_user;
GRANT SELECT (datname, datallowconn, datconnlimit) ON pg_database TO read_only_user;
ALTER TABLE pg_database ENABLE ROW LEVEL SECURITY;
CREATE POLICY read_only_db_access ON pg_database FOR SELECT TO read_only_user
USING (datname = current_database());

验证要点 #

用read_only_user连接数据库后执行以下操作验证：

• 尝试创建表：CREATE TABLE test (id int); 应返回权限不足报错
• 查看数据库列表：SELECT datname FROM pg_database; 应仅显示当前连接的数据库
• 测试新表访问：用service_user创建新表后，read_only_user可正常执行SELECT * FROM new_table;

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