PostgreSQL 创建普通用户并配置权限（含修改密码）

PostgreSQL 创建普通用户并配置权限（含修改密码、回收权限、删除用户）

本文档介绍如何在 PostgreSQL 中创建一个仅拥有基础操作权限的普通用户，以及如何修改该用户的密码、回收用户权限、删除用户。适用于 PostgreSQL 12 及以上版本，包括 15/16 等高版本。

一、前置准备

在开始操作前，请确保满足以下条件：

已安装并正常运行 PostgreSQL 数据库服务。
拥有 PostgreSQL 超级管理员账号（默认用户为 postgres）。
准备好数据库客户端工具（推荐 DBeaver、pgAdmin 或 Navicat 高版本）。

二、创建普通用户并配置基础权限

2.1 权限说明

本文创建的普通用户将拥有以下权限，兼顾实用性与安全性：

✅ 可正常登录指定数据库
✅ 可在 public 模式下创建、删除表
✅ 可对表执行增/删/改/查（CRUD）操作
✅ 可使用自增序列（适配主键场景）
❌ 无超级管理员权限
❌ 无法创建新数据库/其他用户
❌ 无法修改系统配置

2.2 执行SQL脚本

使用超级管理员账号连接 PostgreSQL（默认连接 postgres 数据库）。
打开 SQL 编辑器，执行以下脚本（替换脚本中的占位符为自定义内容）：

-- 1. 创建普通用户
-- 【注意】请将下方的【自定义用户名】和【自定义密码】替换为你的实际信息
CREATE ROLE "【自定义用户名】" WITH
  LOGIN
  PASSWORD '【自定义密码】'
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE;

-- 2. 授予数据库连接权限
-- 若需限制用户仅操作指定数据库，请替换下方的数据库名
GRANT CONNECT ON DATABASE postgres TO "【自定义用户名】";

-- 3. 授予 public 模式操作权限（创建/删除表）
GRANT USAGE, CREATE ON SCHEMA public TO "【自定义用户名】";

-- 4. 授予现有表的增删改查及序列权限
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public TO "【自定义用户名】";
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO "【自定义用户名】";

-- 5. 设置默认权限（未来新建的表/序列自动授权）
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO "【自定义用户名】";

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO "【自定义用户名】";

三、修改普通用户密码

3.1 超级管理员修改密码（推荐）

无需旧密码，使用超级管理员账号即可重置用户密码，适用于忘记密码或定期更换密码的场景：

-- 修改用户密码
-- 【注意】请将下方的【目标用户名】和【新密码】替换为实际信息
ALTER ROLE "【目标用户名】" WITH PASSWORD '【新密码】';

执行后，新密码立即生效，后续新建数据库连接需使用新密码。

3.2 用户自行修改密码

使用目标用户登录数据库后，可自行修改密码（需知道当前密码）：

-- 用户自行修改密码
-- 【注意】请将下方的【当前用户名】和【新密码】替换为实际信息
ALTER ROLE "【当前用户名】" WITH PASSWORD '【新密码】';

四、验证用户配置

配置完成后，请按以下步骤验证用户权限是否正常：

在数据库客户端中新建连接，使用刚创建的普通用户信息登录：
1. 主机：数据库服务器IP/域名
2. 端口：默认 5432
3. 数据库：目标数据库名（如 postgres）
4. 用户名：自定义的普通用户名
5. 密码：设置的用户密码
基础操作测试：
1. 新建表、插入/修改/删除数据，确认操作正常。
2. 尝试创建数据库/其他用户，确认提示权限不足。
密码修改验证：修改密码后，使用旧密码登录，确认连接失败；使用新密码登录，确认连接成功。

五、安全注意事项

密码复杂度：建议密码长度不低于8位，包含大小写字母、数字和特殊字符，避免使用弱密码。
特殊字符转义：若密码包含单引号'，需用两个单引号转义，例如 P@ss''word123。
连接生效规则：密码修改仅影响新建立的数据库连接，已建立的连接不受影响。
权限精细化控制：如需限制用户仅操作指定表或模式，可修改脚本中的 GRANT 语句，缩小授权范围。
定期更换密码：建议每3-6个月更换一次用户密码，降低安全风险。

六、进阶操作：回收用户权限

当用户权限需要调整（如收回删除表、修改数据的权限）时，使用超级管理员账号执行以下SQL，可灵活回收部分或全部权限。

6.1 回收部分权限

根据需求，精准回收用户的某一项或某几项权限（替换占位符为实际信息）：

-- 1. 回收删除表的权限（保留其他权限）
REVOKE DROP ON SCHEMA public FROM "【目标用户名】";

-- 2. 回收修改、删除数据的权限（保留查询、插入权限）
REVOKE UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM "【目标用户名】";

-- 3. 回收序列操作权限
REVOKE USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public FROM "【目标用户名】";

-- 4. 取消未来新建表的默认授权（仅影响后续新建的表，已授权的表不受影响）
ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON TABLES FROM "【目标用户名】";

6.2 回收全部权限

若需完全收回用户的所有操作权限（仅保留登录权限），执行以下脚本：

-- 1. 回收数据库连接权限（用户无法再登录该数据库）
REVOKE CONNECT ON DATABASE postgres FROM "【目标用户名】";

-- 2. 回收模式操作权限
REVOKE USAGE, CREATE ON SCHEMA public FROM "【目标用户名】";

-- 3. 回收所有表和序列的权限
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM "【目标用户名】";
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM "【目标用户名】";

-- 4. 回收默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE ALL PRIVILEGES ON TABLES FROM "【目标用户名】";

ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE ALL PRIVILEGES ON SEQUENCES FROM "【目标用户名】";

GRANT说明：回收全部权限后，用户虽仍存在，但无法登录数据库或执行任何操作，如需恢复权限，重新执行对应语句即可。

七、进阶操作：删除普通用户

当用户不再需要时，使用超级管理员账号删除用户，删除前需确保用户无活跃连接，避免删除失败。

7.1 常规删除用户

适用于用户无活跃连接、无关联对象（如用户创建的表）的场景：

-- 删除普通用户
-- 【注意】请将下方的【目标用户名】替换为实际信息
DROP ROLE "【目标用户名】";

7.2 强制删除用户

若用户有活跃连接、或拥有关联对象（如创建的表、视图），常规删除会失败，需先清理关联对象或强制删除：

-- 方法1：先终止用户的所有活跃连接（替换【目标用户名】）
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE usename = '【目标用户名】';

-- 方法2：强制删除用户（同时删除该用户创建的所有对象，谨慎使用！）
-- 【注意】此操作会删除用户创建的表、视图等所有关联对象，不可逆
DROP ROLE "【目标用户名】" CASCADE;

重要提醒：CASCADE 会强制删除用户及所有关联对象，操作前请确认该用户的所有对象已无使用价值，避免误删数据。

菜单

分享