用户&角色

PostgreSQL 中使用角色（ role） 机制来处理用户身份认证。 
拥有登录数据库权限的角色称为可登录角色（ login role）。 一个角色可以继承其他角色的权限从而成为其成员角色（ member role）。
拥有成员角色的角色称为 组角色 （ group role）。
一个组角色可以是另一个组角色的成员角色，并且这种角色间的继承关系可以有无限多层。
拥有登录权限的组角色称为可登录的组角色（ group login role）。 
然而，基于安全性的考虑，数据库管理员一般不会为组角色授予登录权限，因为设计组角色的本意是将其作为 一个“权限 集合” 使用， 
而不是将其作为一个真正需要登录权限的用户角色来使用。一个角色可被授予超级用户（ SUPERUSER）。

PostgreSQL 从最近的几个版本开始不再使用 “用户” 和 “组” 这两个术语。 
但你还会看到有人使用这两个术语，请记住 “用户” 和 “组” 分别代表 “可登录角色” 和 “组 角色” 就好了。 
为保持前向兼容， CREATE USER 和 CREATE GROUP 这两个命令在当前版本中也是支持的，但最好不要使用它们，请使用 CREATE ROLE。

在 PostgreSQL 安装过程中的数据初始化阶段，系统会默认创建一个名为 postgres 的可登录角色（ 同时会创建一个名为 postgres 的同名 database）。 
你可以通过前面介绍过的 ident 或者 peer 身份验证机制来将操作系统 的 root 用户映射到数据库的 postgres 角色，
这样可以实现 root 用户免密登录，或者通过设置为 trust 模式的效果也是一样。 
数据库安装完成后，以 postgres 角色身份 登录，然后创建其他已规划好的角色。 

创建可登录角色
CREATE ROLE leo LOGIN PASSWORD 'king' VALID UNTIL 'infinity' CREATEDB;

VALID 子句是可选的，其功能是为此角色的权限设定有效期，如果不写则该角色永久有效。 
CREATEDB 子句表明为此角色赋予了创建新数据库的权限。
如果需要创建不可登录的角色，省略掉 LOGIN PASSWORD 子句即可。

创建具备超级用户权限的角色
CREATE ROLE regina LOGIN PASSWORD 'queen' VALID UNTIL '2020-01-01 00:00:00' SUPERUSER;
 
创建组角色
CREATE ROLE royalty INHERIT;

INHERIT 表示组角色 royalty 的任何一个成员角色都将自动继承其除 “超级用户权限” 外的所有权限。 
出于安全考虑，PostgreSQL 不允许超级用户权限通过继承的方式传递。 
如果不写 INHERIT，默认也会有 INHERIT 的效果，但为了清晰起见，建议还是写上。
如果希望禁止组角色将其权限授予成员角色，可以加上 NOINHERIT 关键字。

为组角色添加成员角色
GRANT royalty TO leo; 
GRANT royalty TO regina;

授予 royalty 组角色超级用户权限：
ALTER ROLE royalty SUPERUSER;

执行以下语句即可获取 SUPERUSER 权限：
这种方法获取的 SUPERUSER 权限仅在会话存续期间有效。
SET ROLE royalty;


所有用户都可以使用 SET ROLE 这个命令，但还有一个比它更强大的命令： SET SESSION AUTHORIZATION，该命令只允许具备 SUPERUSER 权限的用户执行。


查看用户
postgres=# select * from pg_user;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig 
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 postgres |       10 | t           | t        | t       | t            | ******** |          | 
 admin    |    16384 | f           | f        | f       | f            | ******** |          | 
(2 rows)

 
创建某个库用户：
create user admin with password 'chengce243';
create database mydb with encoding='utf8' owner=admin;
GRANT ALL PRIVILEGES ON DATABASE mydb TO admin;
 
创建一个超级用户
create user dbaadmin superuser;
 
修改用户名
alter user tuser to testuser;
 
修改用户密码
alter user testuser password 'test';
或者：
alter user testuser with password 'chengce243';
 
修改用户为超级用户
alter user testuser superuser;
 
将超级用户修改为普通用户
alter user testuser nosuperuser;
 
锁定/解锁用户,不允许/允许其登录
alter user testuser nologin;
alter user testuser login;
 
修改数据库所属用户
alter database database_name OWNER TO new_user;
 
设置用户的连接数,其中0表示不允许登录,-1表示无限制
alter user test connection limit 10;
 
 
直接删除用户
drop user testuser;
 
如果用户在数据库中有相关对象,不能直接删除,需要将相关对象所属修改到其它用户中
test=# drop user testuser;
ERROR: role "testuser" cannot be dropped because some objects depend on it
DETAIL: owner of table zzz.kkk
privileges for schema zzz
 
将testuser的所属用户修改为test:
 
test=# reassign owned by testuser to test;
REASSIGN OWNED
还需要把权限进行收回,再进行删除:
 
test=# revoke all on schema zzz from testuser;
REVOKE
test=# drop user testuser;
DROP ROLE
 
创建一个schema,并且设置所属用户为 testuser:
create schema zzz authorization testuser;
 
删除schema,如果schema中存在对象,则需要使用cascade选项: 
test=# drop schema zzz;
ERROR: cannot drop schema zzz because other objects depend on it
DETAIL: table zzz.test depends on schema zzz
HINT: Use DROP ... CASCADE to drop the dependent objects too.
test=# drop schema zzz cascade;
NOTICE: drop cascades to table zzz.test
DROP SCHEMA


如下所示，通过 “\du” 命令可以看到，具有登录权限的角色 user1 相对于不具有登录权限的角色（ role1），在属性中少了 Cannot login 的说明
postgres=# CREATE ROLE user1 LOGIN;
CREATE ROLE
postgres=# CREATE ROLE role1;
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 admin     |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 user1     |                                                            | {}

 
REASSIGN OWNED 命令，将待删除角色拥有的对象的所有权重新分配给其他角色。
因为 REASSIGN OWNED 命令不能访问其他数据库中的对象，所以必须在角色拥有的对象所归属的每个数据库中运行。
REASSIGN OWNEDBY old_role [,...] TO new_role;