PostgreSQL用户与权限2¶

从PostgreSQL1我们对 pgsql 的权限做了基本介绍，最后配置出来的账号大于等同于 MySQL中的XX账号具有连接XX数据库的能力，我们现在介绍其他权限

一. 组权限¶

我们这里构建一个场景，2个(甚至更多)应用使用同一个数据库，各自都有自己的密码，权限有组进行统一管理

1. 创建组角色并授予数据库权限¶

-- 创建组角色(无需登录权限，仅用于权限聚合)
CREATE ROLE app_group NOLOGIN;

-- 授予组角色对目标数据库的连接权限
GRANT CONNECT ON DATABASE 目标库名 TO app_group;

-- 切换到目标数据库(必须线连接才能授予库内权限)
\c 目标库名;

-- 授予组角色对public模式的访问权限
GRANT USAGE ON SCHEMA public TO app_group;

-- 授予组角色对现有表的操作权限(根据需求调整，如CRUD)
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA TO app_group;

2. 为应用创建用户角色并加入组¶

-- 创建应用1的用户(带登陆权限和密码)
CREATE USER app1_user WITH LOGIN PASSWORD 'app1_pwd';

-- 创建应用2的用户
CREATE USER app2_user WITH LOGIN PASSWORD 'app2_pwd';

-- 将用户加入组角色，继承组的所有权限
GRANT app_group TO app1_user;
GRANT app_group TO app2_user;

权限统一管理: 修改app_group权限，所有应用用户自动继承，无需逐个调整。
最小权限隔离: 应用用户仅拥有组角色赋予的权限，避免直接授予超级权限。
灵活扩展: 新增应用时，只需要创建新用户加入组即可，无需重复配置权限。

3. 权限继承¶

如上操作我们可以配置少量库，但是如果库太多操作量就会很大。且新增库表后上面的配置账号也没有权限操作新表。

这时PostgreSQL的一个重要特性 --- 权限不会自动应用于未来新建的对象，必须通过额外配置才能让新表继承权限

如果希望myuser对未来在public模式中新建的表也自动拥有ALL权限，则需要使用ALTER DEFAULT PRIVILEGES命令:

-- 对未来在public模式中新建的表，自动授予myuser所有权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT ALL ON TABLES TO myuser;

-- 也可以指定新创建指定的权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT,INSERT ON TABLES TO myuser;

4. 权限回收(REVOKE)¶

首先要说明一个概念，权限赋予 GRANT 是"叠加"而非"覆盖"权限，所以多次执行权限只会增加权限而不会减少权限。要减少权限就必须用到权限回收REVOKE

假设用户原本拥有 SELECT,INSERT,UPDATE,DELETE 权限

执行REVOKE DELECT ON TABLE users FROM myuser; 用户当前剩余权限为SELECT,INSERT,UPDATE
执行GRANT SELECT,INSERT,UPDATE ON TABLE users TO myuser; 此时用户的实际权限是不变的。

对于通过橘色继承获得的权限，必须从被继承的角色(父角色)中回收权限，才能彻底移除该用户的对应权限。直接对用户执行 REVOKE 无效，因为用户的权限来源于角色而非直接授予。

示例场景¶

角色 app_group 被授予了 user 表的 DELETE 权限

正确操作: 从父角色回收权限

-- 从app_group中回收DELECT权限
REVOKE DELECT ON TABLE users FROM app_group;

错误操作: 直接对用户回收(无效)

-- 直接对myuser执行REVOKE无法移除继承的权限
REVOKE DELETE ON TABLE users FROM myuser;   -- 无效！