PostgreSQL 角色与用户管理实战：创建、属性设置及权限继承

一、角色与用户的区别

在 PostgreSQL 的逻辑概念中，“角色”好比岗位（如经理、助理），而“用户”则是具体的人。但系统层面并不区分这两者：CREATE USER 实际上是 CREATE ROLE 的别名。

核心差异：

• CREATE USER：默认带有 LOGIN 属性，可直接连接数据库。
• CREATE ROLE：默认不带 LOGIN 属性，通常仅作为权限集合或角色组使用。

二、创建角色与验证属性

第1步：创建基础角色与用户

目的：测试无登录权限的角色与有登录权限的用户在系统中的表现。

执行以下 SQL 语句：

postgres=# CREATE ROLE david;   // 创建一个不带登录权限的角色david
postgres=# CREATE USER sandy;   // 创建一个自带登录权限的用户sandy

查看当前角色列表，观察 Attributes 列：

postgres=# \du
             List of roles
 Role name | Attributes                | Member of
-----------+---------------------------+-----------
 david     | Cannot login              | {}
 postgres  | Superuser...              | {}
 sandy     |                           | {}

此时查询系统表可发现，david 仅在 pg_roles 表中存在，未出现在 pg_user 表中（因为不具登录能力）：

postgres=# SELECT usename FROM pg_user;
 usename 
----------
 postgres
 sandy

第2步：验证 LOGIN 属性的生效逻辑

尝试直接使用客户端连接两个账户：

# 试图登录 david 角色（失败）
$ psql -U david
psql: FATAL: role "david" is not permitted to log in

# 试图登录 sandy 用户（成功，并指定进入 postgres 库）
$ psql -U sandy -d postgres
psql (version)
Type "help" for help.

说明：只有具备 LOGIN 属性的主体才能发起数据库连接请求。

第3步：动态修改角色登录权限

如果原本设定的角色后期需要登录连接，可以通过 ALTER ROLE 动态增加权限。

操作入口：psql 命令行或数据库应用连接。

动作：

postgres=# ALTER ROLE david WITH LOGIN;

执行后再次检查 pg_user 表，你会发现系统自动将 david 加入了用户视图。此时再执行 psql -U david 即可正常登录。

三、授予数据库操作权限

新建的角色默认没有任何操作权限。若要让用户拥有建库、建表等特权，需显式授权。

第1步：在创建时直接赋予属性

利用 WITH 参数可在建号的同时完成权限初始化。

示例1：赋予 bella 创建数据库的权限：

postgres=# CREATE ROLE bella WITH CREATEDB;

示例2：赋予 renee 建库权限及带密码登录的能力：

postgres=# CREATE ROLE renee WITH CREATEDB LOGIN PASSWORD 'abc123';

第2步：验证密码策略的影响

注意：Password 的拦截效力取决于认证模式。若服务器端的 pg_hba.conf 中 local 连接的 METHOD 设置为 trust，则输入密码时不会触发提示。

为确保密码校验生效，建议将 pg_hba.conf 中的对应记录改为 password 或 md5：

local   all   all             password

保存并重启服务后，重新以 renee 登录：

此后系统将强制要求输入正确密码方可进入数据库。此外，验证该用户建库功能：

# 切换至 renee 登录后执行
renee=# CREATE DATABASE test_db;
CREATE DATABASE

四、已存在用户权限变更（ALTER ROLE）

使用 ALTER ROLE 可以对已有角色的各项元数据进行精细化调整。

第1步：赋予登录与其他基础权限

之前没有登录权的 bella 角色，现在赋予其登录能力：

postgres=# ALTER ROLE bella WITH LOGIN;

赋予 renee 创建角色的能力，使其能充当小型管理员：

postgres=# ALTER ROLE renee WITH CREATEROLE;

第2步：设定密码与安全策略

为 david 配置强口令：

postgres=# ALTER ROLE david WITH PASSWORD 'ufo456';

限制 sandy 账号的存活时间（过期后必须重置或会被拒绝登录）：

postgres=# ALTER ROLE sandy VALID UNTIL '2026-12-31';

五、基于角色组的权限继承模型

在实际业务中，逐个给几十上百个用户授权极其低效且易出错。最佳实践是建立“角色组”（父角色），将权限赋予组内成员共享。

第1步：创建代表“团队”的父角色

定义一个具有基本连接能力的角色组 father：

postgres=# CREATE ROLE father WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT ENCRYPTED PASSWORD 'group123';

第2步：为父角色组分配资源使用权

这里创建一个测试库 test，并以管理员身份运行赋予赋权：

# 1. 允许父角色连接该库
GRANT CONNECT ON DATABASE test TO father;

# 2. 切换到 test 库，赋予 schema 使用权限
\c test
GRANT USAGE ON SCHEMA public TO father;

# 3. 允许查询 emp 表数据
GRANT SELECT ON TABLE public.emp TO father;

第3步：创建普通成员并加入组

方式A：先建角色，后加组（推荐，便于后续维护撤销）：

postgres=# CREATE ROLE son1 WITH LOGIN INHERIT PASSWORD 'son1';
# 将其纳入父亲的管理组
postgres=# GRANT father TO son1;

方式B：在建号时直接绑定：

postgres=# CREATE ROLE son2 WITH LOGIN INHERIT PASSWORD 'son2' IN ROLE father;

第4步：验证权限继承效果

用 son1 登录测试库进行实操验证：

$ psql -U son1 -d test

# 1. 可以列出所有表（包含受保护的 emp）
test=> \dt
public | emp | table

# 2. 能够读取数据
select * from emp;

排错提醒：如果在 emp 表上查询正常，但去查刚由其他人建的表（如 dept）却报错 permission denied，这是符合预期的结果。这说明 son1 继承了组的权限，但无权访问未向组开放的新增对象。

六、总结

PostgreSQL 的角色管理是一个灵活的体系。通过控制 LOGIN 属性区分“人”与“岗”，借助 ALTER ROLE 完善安全锁（密码、期限），并利用 GRANT ... TO 实现权限继承，可以构建出既严密又易于扩展的数据库访问架构。

以上就是 PostgreSQL 角色与用户管理的详细内容，更多关于 PostgrSQL 权限分配的资料请关注本站其它相关文章！