PostgreSQL中如何创建仅拥有视图创建权无表创建权的用户

需求概述 #

需要创建一个PostgreSQL用户，具备以下权限：

• 对public模式下所有表的SELECT权限（包括未来新增的表）
• 在public模式下创建视图的权限
• 无法在public模式下创建表

正确实现步骤（管理员身份执行） #

注意：直接授予CREATE权限到public模式会允许创建表，因此需要使用更精细的权限控制：

-- 创建专门的角色用于管理视图创建和查询权限
CREATE ROLE view_creator;

-- 授予对public模式下现有所有表的SELECT权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO view_creator;

-- 配置默认权限，确保未来新增的表也能被该角色查询
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO view_creator;

-- 仅授予在public模式下创建视图的权限（而非全量CREATE权限）
GRANT CREATE VIEW ON SCHEMA public TO view_creator;

-- 创建目标用户并设置密码
CREATE USER test WITH PASSWORD 'test123';

-- 将角色权限赋予test用户
GRANT view_creator TO test;

权限验证（以test用户身份执行） #

合法操作：创建视图（应成功）

CREATE VIEW test_2 AS SELECT * FROM public.users_test;

非法操作：创建表（应失败，提示权限不足）

CREATE TABLE public.users (
    id serial4 NOT NULL,
    "name" varchar(50) NULL,
    age int4 NULL,
    CONSTRAINT users_pkey PRIMARY KEY (id)
);

关键说明 #

• 使用CREATE VIEW权限而非CREATE权限：这是实现"仅能创建视图"的核心，避免了授予创建表、函数等其他对象的权限。
• 默认权限配置：确保后续新增的表自动对该角色开放SELECT权限，无需手动重复授权。

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