PostgreSQL：如何让特定用户组查询表时隐藏指定列且支持SELECT *

问题场景 #

需要对restricted_group用户组隐藏users表的password列，让该组用户执行SELECT * FROM users时仅能看到id、description字段，同时不限制底层表的DDL操作，即便操作导致视图失效也可后续修复。

核心需求 #

• 对指定用户组隐藏表的特定列
• 支持SELECT *语法，自动过滤未授权列
• 底层表的DDL操作不受限制，视图失效后可快速修复

已尝试方案的不足 #

1. 视图方案：

   CREATE VIEW restricted_view AS SELECT "id", "description" FROM users;
   GRANT SELECT ON restricted_view to restricted_group;
   

   • PostgreSQL中，视图依赖会导致修改原表结构时需先删除视图，否则部分DDL操作会被阻止；Oracle中仅会使视图失效，但用户需使用视图名而非原表名，体验不佳。
2. 列级授权方案：

   GRANT SELECT ("ID", "description") ON users TO restricted_group;
   

   • 用户无法使用SELECT *语法，必须显式列出授权列，体验差。

解决方案 #

针对PostgreSQL #

通过专用schema+同名视图+默认搜索路径实现用户无感知访问：

1. 创建专门用于存放受限视图的schema：

   CREATE SCHEMA restricted_access;
   

2. 创建与原表同名的视图，仅包含授权列：

   CREATE VIEW restricted_access.users AS
   SELECT id, description FROM public.users;
   

3. 给用户组授予视图的SELECT权限：

   GRANT SELECT ON restricted_access.users TO restricted_group;
   

4. 修改用户组的默认搜索路径，优先使用受限schema：

   ALTER ROLE restricted_group SET search_path = restricted_access, public;
   

   • 效果：用户执行SELECT * FROM users时，实际访问的是restricted_access.users视图，自动过滤password列；管理员直接操作public.users不受任何限制。
   • DDL处理：修改原表结构后，视图会变为invalid状态，只需执行CREATE OR REPLACE VIEW restricted_access.users AS SELECT id, description FROM public.users;即可快速修复。

针对Oracle #

通过视图+同义词实现原表名访问：

1. 创建仅包含授权列的视图：

   CREATE VIEW restricted_users AS
   SELECT id, description FROM users;
   

2. 给用户组创建同义词，将users指向视图：

   CREATE SYNONYM restricted_group.users FOR restricted_users;
   

3. 授予用户组视图的SELECT权限：

   GRANT SELECT ON restricted_users TO restricted_group;
   

   • 效果：用户执行SELECT * FROM users时，实际访问的是restricted_users视图，自动隐藏password列；管理员操作原users表无限制。
   • DDL处理：原表结构修改后视图失效，执行ALTER VIEW restricted_users COMPILE;即可完成修复。

补充说明 #

• 两种方案均完全满足需求：用户无需修改查询语句，SELECT *自动过滤未授权列；底层表DDL操作不受限制，视图失效后可快速重建/编译修复。
• 若后续需调整授权列，只需修改视图定义并重新授权（或编译）即可。

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