PostgreSQL中如何将用户访问权限隔离至单一Schema

我不是DBA，目标是让用户username仅能访问PostgreSQL 15.3[psql(15.3 (Debian 15.3-0+deb12u1), server 15.4)]中的user_schema下的表和视图，完全无法看到其他Schema的存在——包括自动补全提示、元命令（如\dn、\dt）的输出。

我已执行以下操作：

CREATE ROLE username LOGIN PASSWORD 'user_passwd';
CREATE SCHEMA user_schema;
ALTER USER username SET search_path = user_schema;  -- 设置搜索路径为用户专属Schema
GRANT USAGE ON SCHEMA olga TO username;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA user_schema TO username;
REVOKE SELECT ON ALL TABLES IN SCHEMA other_schema FROM username;

连接数据库后，确认搜索路径正确，也能正常访问user_schema的内容，但存在不符合预期的情况：

• 输入other_schema.后按Tab会出现自动补全提示（实际访问该Schema下的表会报错权限不足）
• 执行\dn能看到所有Schema，而我只希望显示user_schema

解决方案 #

核心原因 #

PostgreSQL默认允许所有用户查看pg_namespace系统表中的Schema信息，这是\dn和自动补全能显示其他Schema的根源。要彻底隐藏，需限制用户对系统元数据的访问权限。

具体操作步骤 #

1. 清理用户对非目标Schema的权限

确保用户没有其他Schema的任何权限：

-- 撤销对other_schema的所有权限
REVOKE ALL ON SCHEMA other_schema FROM username;
-- 撤销对public schema的默认权限（若无需访问public可执行）
REVOKE ALL ON SCHEMA public FROM username;
-- 若之前误授权olga schema，一并撤销
REVOKE ALL ON SCHEMA olga FROM username;

2. 限制用户对系统元数据的访问

修改pg_namespace表的权限，让用户仅能看到user_schema：

-- 撤销用户对pg_namespace的默认查询权限
REVOKE SELECT ON pg_namespace FROM username;
-- 仅授予用户查看user_schema的权限
GRANT SELECT ON pg_namespace TO username WHERE nspname = 'user_schema';

3. 确保未来新表的权限继承

若后续会在user_schema中创建新表，设置默认权限避免手动重复授权：

ALTER DEFAULT PRIVILEGES IN SCHEMA user_schema GRANT ALL PRIVILEGES ON TABLES TO username;

验证效果 #

重新用username登录数据库：

• 执行\dn仅会显示user_schema
• 输入SELECT * FROM 后按Tab，仅会补全user_schema下的表和视图
• 尝试输入other_schema.不会出现自动补全提示，直接访问会报错权限不足

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