Postgres权限管控：如何限制用户仅能查看指定Schema

Absolutely! PostgreSQL gives you fine-grained control over schema and object permissions, so you can absolutely restrict users to only see specific schemas and their contents. Let's walk through the exact steps to fix this issue:

1. 先撤销默认的公共权限（关键第一步） #

By default, all PostgreSQL users get USAGE access to the public schema, and may have implicit access to objects within it. First, we need to strip away these default permissions to lock things down:

-- 撤销所有用户对public schema的默认对象权限（后续新用户也不会自动获得）
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON TABLES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON SEQUENCES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM PUBLIC;

-- 撤销目标用户对public schema的USAGE权限（让他们看不到这个schema）
REVOKE USAGE ON SCHEMA public FROM your_username;

-- 同时撤销用户对public下现有对象的所有访问权
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM your_username;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM your_username;
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public FROM your_username;

2. 仅授予用户对目标Schema的必要权限 #

Next, we'll explicitly grant access only to the schemas you want the user to see. The USAGE permission is critical here—it's what lets a user even see that a schema exists. Then we'll grant access to the objects inside:

-- 授予用户对目标schema的USAGE权限（让他们能看到这个schema）
GRANT USAGE ON SCHEMA your_target_schema TO your_username;

-- 授予对schema内所有现有表、视图的SELECT权限
GRANT SELECT ON ALL TABLES IN SCHEMA your_target_schema TO your_username;
-- 可选：自动授予未来新建表、视图的SELECT权限（避免每次建表都要手动授权）
ALTER DEFAULT PRIVILEGES IN SCHEMA your_target_schema GRANT SELECT ON TABLES TO your_username;

-- 如果用户需要执行函数，添加这条
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA your_target_schema TO your_username;
ALTER DEFAULT PRIVILEGES IN SCHEMA your_target_schema GRANT EXECUTE ON FUNCTIONS TO your_username;

-- 如果涉及自增序列（比如serial/bigserial类型），授予必要权限
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA your_target_schema TO your_username;
ALTER DEFAULT PRIVILEGES IN SCHEMA your_target_schema GRANT USAGE, SELECT ON SEQUENCES TO your_username;

3. 配置默认搜索路径（可选但推荐） #

To make the user's experience smoother (and avoid them having to type the schema name every time), set their default search path to only include the allowed schema:

ALTER ROLE your_username SET search_path TO your_target_schema;

Now when the user logs in, any queries they run will default to your_target_schema without needing to specify it.

4. 验证权限是否生效 #

Switch to the user account and run these checks to confirm everything is locked down:

-- 查看用户能访问的所有schema（应该只显示你的目标schema）
SELECT nspname FROM pg_namespace WHERE has_schema_privilege(current_user, nspname, 'USAGE');

-- 查看目标schema下的表（应该能看到所有授权的表）
SELECT tablename FROM pg_tables WHERE schemaname = 'your_target_schema';

-- 尝试访问一个受限schema的表（应该返回"permission denied"错误）
SELECT * FROM restricted_schema.some_table;

额外注意事项 #

• If the user is part of a group role, make sure the group doesn't have permissions that override these individual restrictions—PostgreSQL permissions are additive, so group privileges will still apply.
• Double-check that no other roles are granting unintended access to restricted schemas.

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