PostgreSql 用户及权限管理

一、概述

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]} ondatabase 数据库名 to 用户名|pubilc [withgrantoption];

参数说明：

create：对于数据库，允许在数据库中创建新的schema、table、index。
connect：允许用户连接到指定的数据库。在连接启动时检查此权限。
temporary、temp：允许在使用指定数据库时创建临时表。
all privileges：一次授予所有可用权限。
public：所有用户。

2.3 模式权限

为已创建用户授权模式操作权限。

grant {{create|usage}|all[privileges]} onschema 模式名 to 用户名|pubilc [withgrantoption];

参数说明：

create：对于schema，允许在模式中创建新的 table、index等。
usage：对于schema，允许访问指定模式中包含的对象；对于 sequence，允许使用 currval 和nextval 函数。对于类型和域，允许在创建表，函数和其他模式对象时使用类型或域。
all privileges：一次授予所有可用权限。
public：所有用户。

2.4 对象权限

为已创建用户授权基本对象操作权限。

grant {{select|insert|update|delete|truncate|references|trigger}|all [privileges]} on {[table] 表名|all tables in schema 模式名} to 用户名|pubilc [with grant option];

参数说明：

select：允许从指定表，视图或序列的任何列或列出的特定列进行 select。也允许使用 copy to。在 update 或 delete 中引用现有列值也需要此权限。对于序列，此权限还允许使用 currval函数。对于大对象，此权限允许读取对象。
insert：允许将新行 insert 到指定的表中。如果列出了特定列，则只能在 insert 命令中为这些列分配（因此其他列将接收默认值）。也允许 copy from。
update：允许更新指定表的任何列或列出的特定列，需要 select 权限。
delete：允许删除指定表中的行，需要 select 权限。
truncate：允许清空指定表。
references：允许创建引用指定表或表的指定列的外键约束。
trigger：允许在指定的表上创建触发器。
execute：允许使用指定的函数或过程以及在函数。
all privileges：一次授予所有可用权限。
public：所有用户。

2.5 系统权限

  创建用户时携带属性，创建后也可使用 alter 进行修改。create user 是 create role 的一个别名。唯一的区别是 create user 中 login 被作为默认值，而 create role 中 nologin 是默认值。

createuser/role 用户名|public[with]option;alteruser/role 用户名|public[with]option;

option 可以为

superuser|nosuperuser：超级权限，拥有所有权限，默认 nosuperuser。
inherit|noinherit：继承权限，可以把除superuser权限继承给其他用户/角色，默认 inherit。
login|nologin：登录权限，作为连接的用户，默认 nologin，除非是 create user（默认登录）。
password ‘password’|null：设置密码，密码仅用于有 login 属性的用户，不使用密码身份验证，则可以省略此选项，可以选择将空密码显式写为password null。
valid until ‘timestamp’：密码有效期时间，不设置则用不失效。
createdb|nocreatedb：建库权限，默认 nocreatedb。
createrole|nocreaterole：建角色权限，拥有创建、修改、删除角色，默认nocreaterole。
replication|noreplication：复制权限，用于物理或则逻辑复制（复制和删除 slots），默认是 noreplication。
bypassrls|nobypassrls：安全策略rls权限，默认nobypassrls。
connection limit connlimit：限制用户并发数，默认-1，不限制。正常连接会受限制，后台连接和prepared事务不受限制。
public：所有用户。

三、权限查看

3.1 权限查看

--数据库权限查看
postgres=# \l
                                  List ofdatabases
   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
(5rows)--模式权限查看
postgres=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges|      Description
--------+----------+----------------------+------------------------public| postgres | postgres=UC/postgres+| standard publicschema||=UC/postgres         |
 sch1   | postgres ||(2rows)--对象权限查看
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(7rows)--系统权限查看
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(13rows)

3.2 ACL 格式说明

上述数据库及模式权限均显示为 ACL（访问控制列表）缩写，显示格式为：

接受赋权的用户（省略时表示 PUBLIC 角色组）= 授予的权限/授予的用户

授予的权限具体含义如下：
权限缩写适用对象类型SELECTrLARGE OBJECT, SEQUENCE, TABLE (and table-like objects), table columnINSERTaTABLE, table columnUPDATEwLARGE OBJECT, SEQUENCE, TABLE, table columnDELETEdTABLETRUNCATEDTABLEREFERENCESxTABLE, table columnTRIGGERtTABLECREATECDATABASE, SCHEMA, TABLESPACECONNECTcDATABASETEMPORARYTDATABASEEXECUTEXFUNCTION, PROCEDUREUSAGEUDOMAIN, FOREIGN DATA WRAPPER, FOREIGN SERVER, LANGUAGE, SCHEMA, SEQUENCE, TYPE
访问权限摘要：
对象类型所有权限默认 PUBLIC 权限psql 命令DATABASECTcTc\lDOMAINUU\dD+FUNCTION or PROCEDUREXX\df+FOREIGN DATA WRAPPERUnone\dew+FOREIGN SERVERUnone\des+LANGUAGEUU\dL+LARGE OBJECTrwnonenoneSCHEMAUCnone\dn+SEQUENCErwUnone\dpTABLE (and table-like objects)arwdDxtnone\dpTable columnarwxnone\dpTABLESPACECnone\db+TYPEUU\dT+

四、撤销权限

上述所有通过 grant 方式授予的权限，均可通过列语法撤销。

revoke [grant option for] {上述所有权限} from {用户名|public} [cascade|restrict];

public：所有用户。
cascade：级联回收权限。
restrict：不级联回收权限。

五、默认权限修改

5.1 概述

  ALTER DEFAULT PRIVILEGES 允许设置将被应用于未来要创建的对象的特权（它不会影响分配给已经存在的对象的特权）。当前，只能修改用于模式、表（包括视图和外部表）、序列、函数和类型（包括域）的特权。其中，可设置权限的函数包括聚集函数和过程函数。当这个命令应用于函数时，单词 FUNCTIONS 和 ROUTINES 是等效的。（推荐使用 ROUTINES，因为它是用来囊括函数和过程的一个标准术语。在较早的 PostgreSQL 发行版中，只允许单词 FUNCTIONS。无法为函数或过程单独设置默认特权。）只能改变你自己或者你属于其中的角色所创建的对象的默认特权。这些特权可以对全局范围设置（即对当前数据库中创建的所有对象），或者只对在指定模式中创建的对象设置。

5.2 语法

ALTERDEFAULTPRIVILEGES[FOR { ROLE |USER } target_role [,...]][INSCHEMA schema_name [,...]]
    abbreviated_grant_or_revoke

其中abbreviated_grant_or_revoke是下列之一：

GRANT { { SELECT|INSERT|UPDATE|DELETE|TRUNCATE|REFERENCES|TRIGGER }
    [,...]|ALL[PRIVILEGES] }
    ONTABLESTO { [GROUP] role_name |PUBLIC } [,...][WITHGRANTOPTION]GRANT { { USAGE|SELECT|UPDATE }
    [,...]|ALL[PRIVILEGES] }
    ON SEQUENCES
    TO { [GROUP] role_name |PUBLIC } [,...][WITHGRANTOPTION]GRANT { EXECUTE|ALL[PRIVILEGES] }
    ON { FUNCTIONS | ROUTINES }
    TO { [GROUP] role_name |PUBLIC } [,...][WITHGRANTOPTION]GRANT { USAGE|ALL[PRIVILEGES] }
    ONTYPESTO { [GROUP] role_name |PUBLIC } [,...][WITHGRANTOPTION]GRANT { USAGE|CREATE|ALL[PRIVILEGES] }
    ON SCHEMAS
    TO { [GROUP] role_name |PUBLIC } [,...][WITHGRANTOPTION]REVOKE[GRANTOPTIONFOR]
    { { SELECT|INSERT|UPDATE|DELETE|TRUNCATE|REFERENCES|TRIGGER }
    [,...]|ALL[PRIVILEGES] }
    ONTABLESFROM { [GROUP] role_name |PUBLIC } [,...][CASCADE|RESTRICT]REVOKE[GRANTOPTIONFOR]
    { { USAGE|SELECT|UPDATE }
    [,...]|ALL[PRIVILEGES] }
    ON SEQUENCES
    FROM { [GROUP] role_name |PUBLIC } [,...][CASCADE|RESTRICT]REVOKE[GRANTOPTIONFOR]
    { EXECUTE|ALL[PRIVILEGES] }
    ON { FUNCTIONS | ROUTINES }
    FROM { [GROUP] role_name |PUBLIC } [,...][CASCADE|RESTRICT]REVOKE[GRANTOPTIONFOR]
    { USAGE|ALL[PRIVILEGES] }
    ONTYPESFROM { [GROUP] role_name |PUBLIC } [,...][CASCADE|RESTRICT]REVOKE[GRANTOPTIONFOR]
    { USAGE|CREATE|ALL[PRIVILEGES] }
    ON SCHEMAS
    FROM { [GROUP] role_name |PUBLIC } [,...][CASCADE|RESTRICT]

参数说明：

target_role：一个现有角色的名称，当前角色是它的一个成员。如果 FOR ROLE 被忽略，将假定为当前角色。
schema_name：一个现有模式的名称。如果被指定，以后在那个模式中创建的对象的默认特权会被修改。如果 IN SCHEMA 被忽略，全局默认特权会被修改。 当设置特权给模式时不能使用 IN SCHEMA，因为模式不能嵌套。
role_name：要为其授予或者收回特权的一个现有角色的名称。这个参数以及所有 abbreviated_grant_or_revoke 中的其他参数会按照 GRANT 或者 REVOKE 中描述的方式运作，不过这里是为一整类的对象而不是特别指定的对象设置权限。

六、示例

6.1 创建只读用户

创建 readonly 用户，只可查询 test 模式下的表，连入对应数据库执行如下操作。

--创建只读用户createuser readonly with password 'postgres';--将 schema 中 usage 权限赋予给 readonly 用户,访问所有已存在的表grantusageonschema test to readonly;grantselectonalltablesinschema test to readonly;--未来访问 test 模式下所有新建的表alterdefaultprivilegesinschema test grantselectontablesto readonly ;

6.2 删除只读用户

修改过默认特权的用户要进行删除，均先删除修改的默认特权后在进行删除用户操作

--删除授予的默认特权drop owned by readonly;--删除用户dropuser readonly;

七、补充默认角色

  PostgreSQL提供了一组默认角色，它们提供对特定的、通常需要的、需要特权的功能和信息的访问。管理员可以把这些角色GRANT给其环境中的用户或者其他角色，让这些用户能够访问指定的功能和信息。在授予这些角色时应当非常小心，以确保它们只被用在需要的地方。
角色允许的访问pg_read_all_settings读取所有配置变量，甚至是那些通常只对超级用户可见的变量。pg_read_all_stats读取所有的 pg_stat_* 视图并且使用与扩展相关的各种统计信息，甚至是那些通常只对超级用户可见的信息。pg_stat_scan_tables执行可能会在表上取得ACCESS SHARE锁的监控函数（可能会持锁很长时间）。pg_monitor读取/执行各种不同的监控视图和函数。这角色是pg_read_all_settings，pg_read_all_stats和pg_stat_scan_tables的成员。pg_signal_backend发信号到其他后端以取消查询或中止它的会话。pg_read_server_files允许使用COPY以及其他文件访问函数从服务器上该数据库可访问的任意位置读取文件。pg_write_server_files允许使用COPY以及其他文件访问函数在服务器上该数据库可访问的任意位置中写入文件。pg_execute_server_program允许用运行该数据库的用户执行数据库服务器上的程序来配合COPY和其他允许执行服务器端程序的函数。