PostgreSQL02-用户管理
1、相关概念
- 角色是权限的集合，可被赋予其他角色或用户，用于批量管理权限
- 用户本质是具有LOGIN属性的角色，用于登录数据库并执行操作，
- 两者的区别仅在于是否默认拥有LOGIN权限
2、角色/用户相关命令
2.1、创建角色/用户
1、命令语法
-- 创建角色
CREATE ROLE 角色名 属性;
-- 创建用户（等价于带LOGIN的角色）
CREATE USER 用户名 属性;
2、常用属性
- LOGIN/NOLOGIN：是否允许登录（用户默认LOGIN，角色默认NOLOGIN）。
- PASSWORD '密码'：设置登录密码（若需远程登录，可能需配合pg_hba.conf配置）。
- SUPERUSER/NOSUPERUSER：是否为超级用户（拥有所有权限，谨慎分配）。
- CREATEDB/NOCREATEDB：是否允许创建数据库。
- CREATEROLE/NOCREATEROLE：是否允许创建 / 管理其他角色。
- INHERIT/NOINHERIT：是否继承所属角色的权限（默认INHERIT）。
- VALID UNTIL '时间'：密码有效期（如'2024-12-31'）。
3、示例
-- 创建一个可登录、能创建数据库的用户
CREATE USER alice WITH LOGIN PASSWORD '123456' CREATEDB;
-- 创建一个角色，作为"只读权限集合"，不允许登录
CREATE ROLE read_only NOLOGIN;
-- 创建一个角色，它的口令有效期截止到 2004 年底。在进入 2005 年第一秒时，该口令会失效。
CREATE ROLE miriam WITH LOGIN PASSWORD '123456' VALID UNTIL '2005-01-01';
-- 创建一个能够创建数据库并且管理角色的角色：
CREATE ROLE admin WITH CREATEDB CREATEROLE;
4、相关命令
SELECT rolname FROM pg_roles; -- 列出所有角色
SELECT rolname FROM pg_roles WHERE rolcanlogin; -- 查看那些能够登录的角色
2.2、删除角色 / 用户
1、命令语法
- 注意：若用户正在连接数据库，需先终止连接（SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename='用户名';）再删除。
-- 删除用户/角色（需确保无活跃连接，且未被其他对象依赖）
DROP ROLE [IF EXISTS] 角色名/用户名;
3、用户属性的修改
- 使用ALTER ROLE或ALTER USER（等价）修改属性：
-- 修改密码
ALTER USER alice WITH PASSWORD 'NewPass456';
-- 禁止用户登录（移除LOGIN属性）
ALTER ROLE alice NOLOGIN;
-- 授予超级用户权限（谨慎使用）
ALTER USER bob WITH SUPERUSER;
-- 限制密码有效期
ALTER ROLE charlie VALID UNTIL '2025-01-01';
4、权限管理
- PG 的权限控制精细到 “对象级别”（数据库、表、列、函数等），通过GRANT（授予）和REVOKE（撤销）操作管理。
4.1、 常用权限类型
- 数据库级别：CONNECT（连接数据库）、CREATE（创建表 / 视图等）。
- 表 / 视图级别：SELECT（查询）、INSERT（插入）、UPDATE（更新）、DELETE（删除）、TRUNCATE、REFERENCES（外键引用）、TRIGGER（触发器）。
- 列级别：针对表中特定列授予SELECT/UPDATE权限。
- 其他对象：序列（USAGE、SELECT、UPDATE）、函数（EXECUTE）等。
4.2、授予权限（GRANT）
1、语法
-- 授予用户/角色权限
GRANT 权限类型 ON 对象 TO 用户/角色 [WITH GRANT OPTION];
WITH GRANT OPTION：允许被授权者将权限再授予他人（慎用）。
2、示例
-- 允许alice连接数据库mydb
GRANT CONNECT ON DATABASE mydb TO alice;
-- 授予bob对表users的查询和插入权限
GRANT SELECT, INSERT ON TABLE users TO bob;
-- 授予read_only角色对表orders的查询权限（后续可将角色赋予用户）
GRANT SELECT ON TABLE orders TO read_only;
-- 授予charlie对users表中name列的更新权限
GRANT UPDATE (name) ON TABLE users TO charlie;
-- 授予序列使用权限（常用于自增ID）
GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO alice;
4.3、 撤销权限（REVOKE）
1、语法：
-- 撤销用户/角色的权限
REVOKE [GRANT OPTION FOR] 权限类型 ON 对象 FROM 用户/角色 [CASCADE];
-- CASCADE：级联撤销（若被授权者已将权限授予他人，一并撤销）。
2、示例：
-- 撤销bob对users表的插入权限
REVOKE INSERT ON TABLE users FROM bob;
-- 级联撤销read_only角色的查询权限（包括其授予的用户）
REVOKE SELECT ON TABLE orders FROM read_only CASCADE;
5、角色继承与成员关系
- 将用户加入某个角色，用户可继承角色的权限（需角色或用户开启INHERIT属性，默认开启）。
1. 添加成员（授予角色给用户）
-- 将read_only角色赋予alice，alice继承其权限
GRANT read_only TO alice;
2. 移除成员
REVOKE read_only FROM alice;
3. 角色嵌套
角色可包含其他角色，形成权限层级：
-- 创建管理员角色
CREATE ROLE admin_role NOLOGIN;
-- 授予admin_role管理表的权限
GRANT ALL PRIVILEGES ON TABLE users TO admin_role;
-- 将admin_role赋予角色manager_role
GRANT admin_role TO manager_role;
-- 最终，属于manager_role的用户将继承所有权限
6、查看用户与权限信息
1、查看所有角色 / 用户：
SELECT rolname, rolsuper, rologin FROM pg_roles;
-- rolsuper：是否为超级用户；rologin：是否允许登录。
2、查看角色成员关系：
SELECT r1.rolname AS 角色, r2.rolname AS 成员
FROM pg_auth_members m
JOIN pg_roles r1 ON m.roleid = r1.oid
JOIN pg_roles r2 ON m.member = r2.oid;
3、查看表的权限：
SELECT grantee, privilege_type
FROM information_schema.table_privileges
WHERE table_name = 'users';
4、查看当前用户权限：
SELECT * FROM pg_permissions WHERE grantee = current_user;
7、命令行创建角色/用户
7.1、创建用户/角色
# 创建一个名为 newuser 的非超级用户，newuser 继承自 pg_monitor 系统角色，只能有 1 个连接，没有创建数据库的权限 ，没有创建用户的权限，并且立即给它设置密码，
/opt/pgsql/bin/createuser -h pghost1 -p 5432 -c 1 -g pg_monitor -D -R -S -P -e newuser
# 是否超级用户、是否允许创建数据库、是否允许创建用户这三个权限可以使用--interactive 参数提供交互界面
/opt/pgsql/bin/createuser -h pghost1 -p 54322 -c 1 -g pg_monitor --interavtive -e -P newuser
/opt/pgsql/bin/createuser # PostgreSQL的用户创建工具路径
-h pghost1 # 指定数据库主机地址（远程服务器名/IP）
-p 5432 # 指定数据库端口（默认5432，此处显式指定）
-c 1 # 设置用户的并发连接数上限为1
-g pg_monitor # 将新用户加入pg_monitor角色（继承其权限）
-D # 禁止用户创建数据库（等价于NOCREATEDB）
-R # 禁止用户创建其他角色（等价于NOCREATEROLE）
-S # 禁止用户成为超级用户（等价于NOSUPERUSER）
-P # 强制提示输入新用户的密码
-e # 显示执行的SQL语句（用于调试/确认）
newuser # 新用户的名称
7.2、删除用户/角色
/opt/pgsql/bin/dropuser -h pghost1 -p 5432 newuser
8、会话用户和当前用户
1. session_user（会话用户）
- 指实际登录数据库的用户（即建立连接时使用的用户名），整个会话期间不会改变。
- 例如用 psql -U alice ... 登录，那么整个会话中 session_user 始终是 alice。
2. current_user（当前用户）
- 指当前执行 SQL 语句时有效的权限主体（即以哪个用户的权限执行操作），可能因权限切换而改变。
- 它决定了当前操作拥有的权限范围（例如，是否能查询某张表、修改某条数据等）
- 默认情况，current_user 初始值与 session_user 相同都是登录用户
- 通过 SET ROLE 角色名; 可以临时切换 current_user 为指定角色，此时操作权限将以该角色为准（但 session_user 仍保持不变）。
3、示例
-- 假设用用户 alice 登录（session_user 为 alice）
SELECT session_user, current_user;
-- 结果：session_user = 'alice', current_user = 'alice'
-- 切换当前用户为角色 read_only（需 alice 被授予 read_only 角色）
SET ROLE read_only;
-- 再次查询
SELECT session_user, current_user;
-- 结果：session_user = 'alice', current_user = 'read_only'
-- 切换回原用户
SET ROLE alice;
4、区别
- session_user 是 “登录者身份”，固定不变；
- current_user 是 “当前权限身份”，可动态切换，决定操作权限。
Bob用户获得SUPERUSER权限的方法
create role manager inherit;
CREATE ROLE Bob LOGIN PASSWORD '1234' VALID UNTIL 'infinity';
GRANT Manager TO Bob;
Alter ROLE Manager SUPERUSER;
以Bob身份登陆后执行
SET ROLE Manager;
ALTER ROLE Bob SUPERUSER;
浙公网安备 33010602011771号