一、概述
1.1 角色和用户
PostgreSQL 使用角色的概念管理数据库访问权限。角色是一系列相关权限的集合。为了管理方便，通常把一系列相关的数据库权限赋给一个角色，如果哪个用户需要这些权限，就把角色赋给相应的用户。由于用户也拥有一系列的相关权限，为了简化管理，在 PostgreSQL 中，角色与用户是没有区别的，一个用户也是一个角色，我们可以把一个用户的权限赋给另一个用户。
用户和角色在整个数据库实例中是全局的，在同一个实例中的不同数据库中，看到的用户都是相同的。在初始化数据库系统时有一个预定义的超级用户，这个用户的名称与初始化该数据库的操作系统用户名相同。如果数据库是建在操作系统用户“postgres”（通常我们把数据库安装在此用户下）下的，那么这个数据库超级用户的名称也叫“postgres”。可以用这个超级用户连接数据库，然后创建出更多的普通用户或其他超级用户。
在 SQL 标准中，用户和角色之间的区别很清楚，并且用户不会自动继承权限而角色会继承。这种行为在 PostgreSQL 中也可以实现：为要用作 SQL 角色的角色给予 INHERIT 属性，而为要用作 SQL 用户的角色给予 NOINHERIT 属性。不过，为了向后兼容 8.1 以前的发布（在其中用户总是拥有它们所在组的权限），PostgreSQL 默认给所有的角色 INHERIT 属性。
1.2 用户和用户组
PostgreSQL 可以把用户分组在一起，权限可以被授予一整个组或从一整个组回收。一旦组角色存在，可以使用 GRANT 和 REVOKE 命令增加和移除成员：
GRANT group_role TO role1, ... ;
REVOKE group_role FROM role1, ... ;
注意事项：
- 一个用户可以是多个组的成员。
- 不允许环状的成员关系。
- 不允许把一个用户授予给 PUBLIC。
1.3 PUBLIC 角色
PUBLIC 是 PostgreSQL 数据库中一个特殊的角色组，在元数据表（pg_roles）中都查不到该角色数据库中，所创建的角色都可以理解为是 PUBLIC 角色组成员。而且对 PUBLIC 权限的继承完全不受 NOINHERIT 的控制，一旦创建了一个拥有 login 权限的角色，它会立即继承 PUBLIC 角色组拥有的权限，此时如果想通过 revoke（比如 revoke connect on database）来回收的话不会成功，只能从 PUBLIC 组回收相关权限（比如 revoke connect on database from PUBLIC）。
PUBLIC 默认权限
- 数据库的 connect，temp/temprary 权限。任何新建的数据库，系统会自动为 PUBLIC 角色赋予connect 和在任何 schema 下创建临时表的权限。
- public 模式的 usage，create 权限。在任何新建的数据库的 public 模式下有 usage 和 create 的权限。
- 函数的 execute 权限（仅限于 public 模式下）。
- 语言和数据类型（包括域）的 usage 权限。
二、权限授予
在 PostgreSQL 数据库中，每个数据库对象都有一个所有者（owner）,对于大部分类型的对象，默认只有其所有者和超级用户（管理员用户）能够对该对象做任何事情。为了允许其他角色使用它，必须分配对应的权限。
根据 PostgreSQL 数据库的逻辑结构：实例（集簇）->数据库->schema->数据库对象，可将其权限分为如下几类：
- 实例权限：由 postgresql.conf，pg_hba.conf 文件控制，控制哪些用户哪些IP以哪种方式连接数据库。
- 数据库权限：是否允许连接数据库，是否允许在数据库中创建模式（schema）。
- 模式权限：是否允许查看模式中的对象，是否允许在模式下建表。
- 对象权限：对表来说就是增删改查，对函数来说就是执行，修改等。
- 系统权限：也可以称为角色属性，即创建用户时，自身携带的一些权限。
2.1 实例权限
https://xiaosonggong.blog.csdn.net/article/details/124264877
2.2 数据库权限
为已创建用户授权数据库操作权限。
grant {{create|connect|temporary|temp}|all[ privileges]} on database 数据库名 to 用户名|pubilc [with grant option];
参数说明：
2.3 模式权限
为已创建用户授权模式操作权限。
grant {{create|usage}|all [privileges]} on schema 模式名 to 用户名|pubilc [with grant option];
参数说明：
2.4 对象权限
为已创建用户授权基本对象操作权限。
grant {{select|insert|update|delete|truncate|references|trigger}|all [privileges]} on {[table] 表名|all tables in schema 模式名} to 用户名|pubilc [with grant option];
参数说明：
2.5 系统权限
创建用户时携带属性，创建后也可使用 alter 进行修改。create user 是 create role 的一个别名。唯一的区别是 create user 中 login 被作为默认值，而 create role 中 nologin 是默认值。
create user/role 用户名|public [with] option;
alter user/role 用户名|public [with] option;
option 可以为
三、权限查看
3.1 权限查看
--数据库权限查看
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
appdb | appuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
d1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | u1=c/postgres
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
--模式权限查看
postgres=# \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
sch1 | postgres | |
(2 rows)
--对象权限查看
d1=> select * from information_schema.table_privileges where grantee = 'u1';
grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
u1 | u1 | d1 | d1s1 | t1 | INSERT | YES | NO
u1 | u1 | d1 | d1s1 | t1 | SELECT | YES | YES
u1 | u1 | d1 | d1s1 | t1 | UPDATE | YES | NO
u1 | u1 | d1 | d1s1 | t1 | DELETE | YES | NO
u1 | u1 | d1 | d1s1 | t1 | TRUNCATE | YES | NO
u1 | u1 | d1 | d1s1 | t1 | REFERENCES | YES | NO
u1 | u1 | d1 | d1s1 | t1 | TRIGGER | YES | NO
(7 rows)
--系统权限查看
d1=> select * from pg_roles;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
pg_signal_backend | f | t | f | f | f | f | -1 | ******** | | f | | 4200
pg_read_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4569
postgres | t | t | t | t | t | t | -1 | ******** | | t | | 10
pg_write_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4570
pg_execute_server_program | f | t | f | f | f | f | -1 | ******** | | f | | 4571
pg_read_all_stats | f | t | f | f | f | f | -1 | ******** | | f | | 3375
pg_monitor | f | t | f | f | f | f | -1 | ******** | | f | | 3373
syd | f | t | f | f | t | f | -1 | ******** | | f | | 16387
u1 | f | t | f | f | t | f | -1 | ******** | | f | | 41008
pg_read_all_settings | f | t | f | f | f | f | -1 | ******** | | f | | 3374
pg_stat_scan_tables | f | t | f | f | f | f | -1 | ******** | | f | | 3377
u2 | f | t | f | f | t | f | -1 | ******** | | f | | 41009
repmgr | t | t | f | f | t | f | -1 | ******** | | f | | 32769
(13 rows)
3.2 ACL 格式说明
上述数据库及模式权限均显示为 ACL（访问控制列表）缩写，显示格式为：
接受赋权的用户（省略时表示 PUBLIC 角色组）= 授予的权限/授予的用户
授予的权限具体含义如下：
访问权限摘要：
四、撤销权限
上述所有通过 grant 方式授予的权限，均可通过列语法撤销。
revoke [grant option for] {上述所有权限} from {用户名|public} [cascade|restrict];
五、默认权限修改
5.1 概述
ALTER DEFAULT PRIVILEGES 允许设置将被应用于未来要创建的对象的特权（它不会影响分配给已经存在的对象的特权）。当前，只能修改用于模式、表（包括视图和外部表）、序列、函数和类型（包括域）的特权。其中，可设置权限的函数包括聚集函数和过程函数。当这个命令应用于函数时，单词 FUNCTIONS 和 ROUTINES 是等效的。（推荐使用 ROUTINES，因为它是用来囊括函数和过程的一个标准术语。在较早的 PostgreSQL 发行版中，只允许单词 FUNCTIONS。无法为函数或过程单独设置默认特权。）只能改变你自己或者你属于其中的角色所创建的对象的默认特权。这些特权可以对全局范围设置（即对当前数据库中创建的所有对象），或者只对在指定模式中创建的对象设置。
5.2 语法
ALTER DEFAULT PRIVILEGES
[ FOR { ROLE | USER } target_role [, ...] ]
[ IN SCHEMA schema_name [, ...] ]
abbreviated_grant_or_revoke
其中abbreviated_grant_or_revoke是下列之一：
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON TABLES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON SEQUENCES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { FUNCTIONS | ROUTINES }
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }
ON SCHEMAS
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON TABLES
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON SEQUENCES
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON { FUNCTIONS | ROUTINES }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON TYPES
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | CREATE | ALL [ PRIVILEGES ] }
ON SCHEMAS
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
参数说明：
六、示例
6.1 创建只读用户
创建 readonly 用户，只可查询 test 模式下的表，连入对应数据库执行如下操作。
--创建只读用户
create user readonly with password 'postgres';
--将 schema 中 usage 权限赋予给 readonly 用户,访问所有已存在的表
grant usage on schema test to readonly;
grant select on all tables in schema test to readonly;
--未来访问 test 模式下所有新建的表
alter default privileges in schema test grant select on tables to readonly ;
6.2 删除只读用户
修改过默认特权的用户要进行删除，均先删除修改的默认特权后在进行删除用户操作
--删除授予的默认特权
drop owned by readonly;
--删除用户
drop user readonly;