PostgreSQL 权限管理 101
用户与角色
角色属性
数据库的用户或角色与系统无关，只是习惯上喜欢对应地设置。
PostgreSQL 中授权的对象是角色。而「用户」其实是一个特殊的「角色」，即它额外拥有登录属性。
-- 创建角色
CREATE ROLE name;
-- 修改角色
ALTER ROLE name CREATEDB;
-- 删除角色
DROP ROLE name;
要创建用户，只需要顺便给予 LOGIN 属性就行了，CREATE USER 语法糖就做了这件事情。
-- 创建用户
CREATE ROLE name LOGIN;
-- 等价写法
CREATE USER name;
上面提到的「属性」可以理解为特殊的权限，与常规权限不同，这些属性作用域是整个数据库集群，而不是绑定到具体的对象（例如一个表或一个库）。大部分属性默认都是不赋予的，要想知道具体默认值可参考 CREATE ROLE，常用的有：
LOGIN: 可登录。更严谨的说法是可用作数据库连接的初始角色名。拥有此属性的「角色」即视为「用户」。SUPERUSER: 上帝模式，绕过所有权限检查，可以做任何事情。CREATEDB: 允许创建数据库。CREATEROLE: 允许创建角色。PASSWORD: 口令，也就是密码啦。记得配合LOGIN。
比如我们现在想创建一个用户 user1，并让他有权创建新角色：
CREATE ROLE user1 LOGIN CREATEROLE PASSWORD 'password';
角色继承
角色可以继承，就像 RBAC 八股文一样。但 PostgreSQL 中引入了一个单独的属性 INHERIT（默认）与 NOINHERIT 来控制当前角色是否继承其父角色（如果有）的权限。当角色 B 是 A 的成员时，可以说 B 继承了 A。
要想使用非继承的权限，需要显式地设置当前用户的角色身份。例如我们创建 2 个普通角色与 1 个用户：
CREATE ROLE joe LOGIN;
CREATE ROLE dev;
CREATE ROLE test NOINHERIT;
GRANT dev TO test;
GRANT test TO joe;
-- 此时继承关系为：dev <- test <- joe
这时候 joe 默认就能使用 test 的权限，但不能使用 dev 的权限。因为 dev 是通过 test 间接继承的，而 test 通过 NOINHERIT 属性配置了不继承上游角色的权限。如果想使用 dev 的权限，需要执行：
SET ROLE dev;
但是注意，执行之后将暂时失去 test 以及直接授权给 joe 的权限，要想恢复默认状态，可任选下面其一执行：
SET ROLE joe;
SET ROLE NONE;
RESET ROLE;
注意，上面那些「属性」作为特殊权限是不可继承的。例如即使角色
admin拥有CREATEDB属性，它的成员依然不能创建数据库。要行使这一权限，需要显式地设置角色身份，并且创建的数据库的所有者也是角色，而不是具体用户。
权限
权限管理
在 PostgreSQL 中，权限是针对对象而言的。而对象有一个「所有者」，所有者默认拥有此对象的全部权限（包括删除）。比如一个最朴素的例子，以用户 A 的身份创建一个表，那么自然用户 A 可以随意操作这个表，而无需显式授权。
对象的权限是可以继承的普通权限。所以哪怕对象的所有者是角色，默认情况下它的成员用户无需显式切换就可以修改此对象。
权限有很多种，每一种的可用性以及具体的含意根据对象的不同而不同，文档列出了具体的解释。
-- 授予角色 joe 对 messages 表的 UPDATE 权限
GRANT UPDATE ON messages TO joe;
ALL 可以表示与对象相关的所有权限，而 PUBLIC 表示系统中所有角色：
-- 撤销 PUBLIC 关于表 messages 的一切权限
REVOKE ALL ON messages FROM PUBLIC;
注意
PUBLIC 并不是角色的集合，实际上它类似于 java 中的 Object，是一个公共父角色。所以撤销
PUBLIC是不会影响一个角色从其他渠道获得的权限的。比如：
GRANT UPDATE ON messages TO joe; REVOKE ALL ON messages FROM PUBLIC; -- 此时 joe 依然有 messages 上的 UPDATE 权限。
psql 中有一些命令可以查看当前权限配置：
-
\l+列出所有数据库的详细信息。 -
\dn+列出当前数据库中所有模式 (schema) 的详细信息。 -
\dp列出当前数据库中所有表、视图与 sequence 的权限信息。 -
\ddp查看默认权限 (DEFAULT PRIVILEGES) 的配置。
默认权限
所有的权限管理语句都只对当前已经存在的对象生效，如果想设置未来创建的对象的权限，就需要通过 ALTER DEFAULT PRIVILEGES 配置。当前的默认权限可通过 psql 的 \ddp 命令查看。基本语法为
ALTER DEFAULT PRIVILEGES
[ FOR { ROLE | USER } target_role [, ...] ]
[ IN SCHEMA schema_name [, ...] ]
abbreviated_grant_or_revoke
这里要强调一下很多人遗漏的 FOR ROLE xxx 部分。设置的默认权限只能对指定的角色所创建的对象生效，不允许全局设置。若省略 FOR 部分，则默认是当前的角色。很多时候我们人工管理数据库时登录的用户与业务系统使用的不一样，这就造成了「默认权限不生效」的问题。
Recipe
创建只读用户
假设我们已有角色 readonly，希望将其设置为可只读访问数据库 db （包括所有表）。
PostgreSQL 默认允许所有角色在 public 模式下创建表（即 PUBLIC 对于 模式 public 有 UC 权限），所以首先要撤销这一默认行为:
-- 禁止在 public 模式下创建对象
REVOKE CREATE ON SCHEMA public FROM PUbLIC;
-- 以防万一，撤销 PUBLIC 对数据库的所有权限
REVOKE ALL ON DATABASE db FROM PUBLIC;
注意上述语句中小写的 public 指的是模式，大写的 PUBLIC 是一个关键字，指所有角色。后者也可以小写。
接下来给 readonly 授予数据库级别的权限 CONNECT 与 TEMPORARY：
-- TEMPORARY 是可选的，允许创建临时表在分析复杂数据的时候可能有用
GRANT CONNECT, TEMPORARY ON DATABASE pc_dev TO readonly;
然后授予模式的 USAGE 权限：
GRANT USAGE ON SCHEMA public TO readonly;
-- 不支持类似 GRANT xxx ON ALL SCHEMAS 的写法，
-- 如果有多个 schema，必须分别配置。
-- PUBLIC 默认对 public 模式拥有 USAGE 权限，可以不用显式授予。
最后授予所有表的所需权限：
GRANT SELECT ON ALL TABLES IN SCHEMA public [,...] TO readonly;
别忘了修改默认权限，让未来创建的对象也能够被读取：
-- 允许读取新的 SCHEMA
ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO readonly;
-- 允许读取新的表（任意 SCHEMA）
ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO readonly;