为后端服务器创建PostgreSQL受限权限角色的问题

数据老陈

2026-7-3

PostgreSQL 限定角色权限的正确配置方案

问题根源

你之前先授予全部权限再撤销的操作逻辑有问题，PostgreSQL的权限体系中，这种方式容易因为权限继承、默认权限设置导致残留不必要的权限，正确思路是仅授予所需的最小权限，而非先开全权限再回收。

正确操作步骤

以下是完全符合需求的SQL语句，按顺序执行：

1. 创建目标用户

CREATE USER userx WITH ENCRYPTED PASSWORD 'mypass';

2. 授予数据库连接权限

只给连接权限，避免授予CREATE、ALTER、DROP这类危险权限：

GRANT CONNECT ON DATABASE database_x TO userx;

3. 授予public模式的访问权限

允许用户使用public模式，但不赋予创建对象的权限：

GRANT USAGE ON SCHEMA public TO userx;

4. 授予现有表的增删改权限

直接给需要的INSERT（新增行）、UPDATE（修改行）、DELETE（删除行）权限：

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO userx;

5. 设置默认权限（关键）

这条是为了确保后续在public模式里新建的表，userx也能自动获得增删改权限：

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT INSERT, UPDATE, DELETE ON TABLES TO userx;

6. 清理之前的冗余权限

如果之前已经给过不必要的权限，执行以下语句彻底重置：

-- 重置数据库权限，只保留连接权
REVOKE ALL PRIVILEGES ON DATABASE database_x FROM userx;
GRANT CONNECT ON DATABASE database_x TO userx;

-- 重置模式权限，只保留使用权
REVOKE ALL PRIVILEGES ON SCHEMA public FROM userx;
GRANT USAGE ON SCHEMA public TO userx;

-- 重置表权限，只保留增删改
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM userx;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO userx;