【 PG 入门系列 】PostgreSQL 的权限管理（七）

PostgreSQL 系列博文讲解（B站同步介绍视频：https://space.bilibili.com/282421760）
支持一下，关注一波微信公众号：【 钥道不止 】

1. 超级用户

初始化数据库会创建与操作系统名一样的超级用户。也可以用 -U：指定超级用户

# 创建数据库实例
initdb -D /xxoo -W
# 查看pg_user视图结构
\d pg_user
# 查询用户权限
select usename,usecreatedb,usesuper,userepl from pg_user;  
或用 \du

2. 用户与角色

# 创建用户
create user xxoo;
alter user password 'xxoo';
create user xxoo with ooxx password 'xxoo';
# 创建角色，区别：创建用户默认有login登录权限，而创建角色无，需指定login
create role xxoo;
alter role xxoo login;

3. 属主与权限层次关系

【 PG 入门系列 】PostgreSQL体系结构（三）

数据库实例与数据库，数据库与模式，模式与对象都是一对多的关系

• 每个数据库实例可以包含多个数据库，但每个数据库只能属于一个数据库实例
• 每个数据库可以包含多个schema，但每个schema总是属于一个数据库
• 每个模式可以包含表、视图、函数等对象，但这些对象都是属于一个schema的
  如果用户有在schema的create权限，就可以在这个schema中创建表、视图、函数等数据库对象。

4. public schema

在刚初使化数据库实例后，数据库中默认就存在一个名称为“public”的schema，任何用户都有在这个schema上的create权限，因为这时任何用户都有创建表的权限，而通常我们需要把这个权限收回来。

#回收 public 权限
REVOKE CREATE ON SCHEMA public from public;
#授权 public 权限
GRANT CREATE ON SCHEMA public to public;

# 授权
grant xxoo to ooxx;
# 回收	
revoke xxoo from ooxx;

5. 创建只读用户

# 回收public模式的创建权限
REVOKE CREATE ON SCHEMA public from public;
# 创建只读用户 readonly，密码为 readonly
CREATE USER readonly PASSWORD 'readonly';
# 授权 public 模式给 readonly 用户
GRANT USAGE ON SCHEMA public TO readonly;
# 授权 public 模式的所有表查询权限给 readonly 用户
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
# 切换到 readonly 用户进行测试
\c - readonly
# 切换到 postgres 用户
\c - postgres
# 将 public 模式的默认表查询权限授予 readonly 用户（关键）
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;  

对于后面在 public 模式中新创建的对象该用户仍有访问权限，无需重新授权

6. 属主变更，权限回收，用户删除

REASSIGN OWNED BY 中可以将对象的拥有者修改为postgres或是其他较高权限的业务账号。

# 修改数据库对象的属主
reassign owned by u1 to u2;
# 先删除角色拥有的所有数据库对象的所有权（谨慎操作） 
drop owned by u1;
# 再删角色
drop user u1;

7. 禁用用户（删除不如禁用）

# nologin方式：禁止用户登录
alter user u1 nologin;
# 限制连接数为0：限制用户连接
alter user u1 connection limit 0;
# 修改有效期：用户密码的过期时间
alter user u1 valid until '2022-01-15';

# 加锁/解锁（Oracle加锁方式，PG不适用）
alter user u1 account lock/unlock;