用户管理

前言

PostgreSQL的用户管理是数据库安全的第一道防线。想象一下，如果你的数据库是一座城堡，那么用户就是进出城堡的访客。谁可以进入？谁可以查看什么？谁可以修改什么？这些问题都需要通过精细的用户管理来回答。

用户与角色

在PostgreSQL中，用户和角色本质上是相同的，区别仅在于用户默认具有登录权限。PostgreSQL采用"角色"作为权限管理的基本单位，这为权限管理提供了极大的灵活性。

PostgreSQL内置了几种预定义的角色：

superuser：超级用户，拥有所有权限
readall：可以读取所有数据库
writeall：可以读写所有数据库
pg_read_all_files：可以读取所有文件
pg_read_all_settings：可以读取所有配置参数
pg_read_all_stats：可以读取所有统计信息
pg_monitor：可以监控服务器
pg_signal_backend：可以终止后端进程

创建用户

创建用户是用户管理的起点。在PostgreSQL中，创建用户的基本语法如下：

CREATE USER username [ WITH ] option [ ... ] 

其中，常见的选项包括：

PASSWORD 'password'：设置用户密码
SUPERUSER | NOSUPERUSER：是否为超级用户
CREATEDB | NOCREATEDB：是否可以创建数据库
CREATEROLE | NOCREATEROLE：是否可以创建角色
INHERIT | NOINHERIT：是否继承父角色的权限
LOGIN | NOLOGIN：是否可以登录
REPLICATION | NOREPLICATION：是否可以进行复制
CONNECTION LIMIT connlimit：连接数限制
VALIDATE PASSWORD password：密码验证策略

例如，创建一个具有创建数据库权限的用户：

CREATE analyst WITH PASSWORD 'securePassword123' CREATEDB LOGIN;

删除用户

当不再需要某个用户时，应该及时删除以减少安全风险。删除用户的语法很简单：

DROP USER username;

但需要注意的是，删除用户前应确保该用户没有拥有任何数据库对象，否则删除操作会失败。如果需要强制删除用户及其拥有的所有对象，可以使用：

DROP USER username CASCADE;

管理用户密码

密码是用户认证的第一道防线。PostgreSQL提供了多种密码管理方式：

设置密码

ALTER USER username WITH PASSWORD 'newPassword';

强制用户在下次连接时修改密码

ALTER USER username PASSWORD '';

设置密码过期策略

ALTER USER username VALID UNTIL 'timestamp';
-- 例如：
ALTER USER analyst VALID UNTIL '2023-12-31';

检查密码过期策略

SELECT usename, usecreatedb, userepl, valuntil 
FROM pg_shadow 
WHERE usename = 'username';

管理角色

角色是PostgreSQL权限管理的核心。通过角色，可以实现权限的集中管理和继承。

创建角色

CREATE ROLE rolename [ WITH ] option [ ... ] 

角色与用户的区别在于，角色默认没有登录权限。

为用户分配角色

GRANT rolename TO username;

撤销角色分配

REVOKE rolename FROM username;

删除角色

DROP ROLE rolename;

查看用户和角色

了解数据库中的用户和角色是管理的基础。PostgreSQL提供了多种系统视图来查询这些信息：

查看所有用户

-- 查看所有用户（包括角色）
SELECT usename, usecreatedb, usesuper, usecatupd, valuntil 
FROM pg_user;

查看所有角色

-- 查看所有角色
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin 
FROM pg_roles;

查看角色成员关系

-- 查看角色成员关系
SELECT r.rolname AS role_name, 
       m.rolname AS member_name,
       m.rolcanlogin AS is_user
FROM pg_auth_members a
JOIN pg_roles r ON a.roleid = r.oid
JOIN pg_roles m ON a.member = m.oid;

查看用户直接拥有的权限

-- 查看用户直接拥有的权限
SELECT grantee, privilege_type 
FROM information_schema.role_grants 
WHERE grantee = 'username';

查看用户通过角色继承的权限

-- 查看用户通过角色继承的权限
SELECT r.rolname AS role_name, 
       p.privilege_type
FROM pg_auth_members a
JOIN pg_roles r ON a.roleid = r.oid
JOIN information_schema.role_grants p ON r.rolname = p.grantee
WHERE a.member = (SELECT oid FROM pg_roles WHERE rolname = 'username');

使用SET ROLE

SET ROLE命令允许会话期间临时切换当前角色，这对于需要临时拥有额外权限的场景非常有用：

基本用法

-- 切换到指定角色
SET ROLE role_name;

-- 切换回默认角色
SET ROLE DEFAULT;

-- 切换到超级用户角色
SET ROLE ALL;

在事务中使用SET ROLE

BEGIN;
-- 使用普通用户权限执行操作
-- ...
-- 临时切换到管理员角色执行特定操作
SET ROLE admin;
-- 执行需要管理员权限的操作
-- ...
-- 切换回普通用户角色
SET ROLE normal_user;
-- 继续执行普通用户权限的操作
-- ...
COMMIT;

应用场景

临时权限提升：普通用户需要临时执行需要更高权限的操作
权限隔离：在同一会话中不同阶段使用不同权限
测试：临时切换到不同角色测试权限设置

注意事项

SET ROLE只影响当前会话，不影响其他会话
只有能够切换到目标角色的用户才能使用SET ROLE
使用SET ROLE后，后续操作将使用目标角色的权限
在事务中使用SET ROLE时，回滚会恢复到之前设置的角色

最佳实践

最小权限原则：只给用户完成工作所需的最小权限
定期审查权限：定期检查用户和角色的权限分配
密码策略：实施强密码策略，并定期更换密码
默认角色：避免使用默认角色，特别是超级用户角色
审计用户活动：启用审计功能，记录用户的关键操作

记住，良好的用户管理不仅是技术问题，更是安全意识的体现。正如城堡的守卫需要知道谁可以进入哪些区域一样，数据库管理员也需要清楚地了解每个用户和角色的权限范围。只有这样，才能确保数据库的安全与稳定。

前言​

用户与角色​

创建用户​

删除用户​

管理用户密码​

设置密码​

强制用户在下次连接时修改密码​

设置密码过期策略​

检查密码过期策略​

管理角色​

创建角色​

为用户分配角色​

撤销角色分配​

删除角色​

查看用户和角色​

查看所有用户​

查看所有角色​

查看角色成员关系​

查看用户直接拥有的权限​

查看用户通过角色继承的权限​

使用SET ROLE​

基本用法​

在事务中使用SET ROLE​

应用场景​

注意事项​

最佳实践​

前言