PostgreSQL基础之教你如何轻松管理用户角色与权限

一、用户角色

PostgreSQL使用角色的概念管理数据库访问权限，角色是一系列相关权限的集合；

根据角色自身的设置不同，一个角色可以看做是一个数据库用户，或者一组数据库用户；

数据库用户和操作系统用户不同，但是可以通过文件pg_ident.conf做映射；

角色可以拥有数据库对象(比如表)以及可以把这些对象上的权限赋予其它角色， 以控制谁拥有访问哪些对象的权限；

角色在整个cluster中是全局性的，即同一个服务器的不同数据库中，看到的用户角色是相同的。

PostgreSQL的用户可以分为两类：

超级用户 — postgres
普通用户 — 根据需要创建

（一）user、role与group的区别

user：拥有login登陆数据库权限的role

role: 可以拥有数据库对象，如表、索引，也可以把这些对象上的权限赋予其它角色，以控制哪些用户对哪些对象拥有哪些权限

group：不拥有replication/noreplication、connection limit属性的role

（二）创建用户、角色

方式1:在系统命令行使用createuser命令

1. createuser username
   

方式2:在psql命令行使用create user(role)指令

1. CREATE user[ROLE] rolename;
   

CREATE USER name [ [ WITH ] option [ … ] ]

option可以是：

SUPERUSER | NOSUPERUSER

| CREATEDB | NOCREATEDB

| CREATEROLE | NOCREATEROLE

| CREATEUSER | NOCREATEUSER

| INHERIT | NOINHERIT #角色是其他角色的成员，这些子句决定新角色是否从那些角色中“继承”特权

| LOGIN | NOLOGIN

| REPLICATION | NOREPLICATION

| BYPASSRLS | NOBYPASSRLS #决定是否一个角色可以绕过每一条行级安全性（RLS）策略。

| CONNECTION LIMIT connlimit

| [ ENCRYPTED | UNENCRYPTED ] PASSWORD ‘password’

| VALID UNTIL ‘timestamp’

| IN ROLE role_name [, …]

| IN GROUP role_name [, …]

| ROLE role_name [, …]

| ADMIN role_name [, …] #ADMIN子句与ROLE相似，让它们能够把这个角色中的成员关系授予给其他人。

| USER role_name [, …]

| SYSID uid

CREATE ROLE name [ [ WITH ] option [ … ] ]

option可以是：

SUPERUSER | NOSUPERUSER

| CREATEDB | NOCREATEDB

| CREATEROLE | NOCREATEROLE

| CREATEUSER | NOCREATEUSER

| INHERIT | NOINHERIT

| LOGIN | NOLOGIN

| REPLICATION | NOREPLICATION

| CONNECTION LIMIT connlimit

| [ ENCRYPTED | UNENCRYPTED ] PASSWORD ‘password’

| VALID UNTIL ‘timestamp’

| IN ROLE role_name [, …]

| IN GROUP role_name [, …]

| ROLE role_name [, …]

| ADMIN role_name [, …]

| USER role_name [, …]

| SYSID uid

示例:

1. #CREATE ROLE name SUPERUSER;
   
2. postgres=# CREATE ROLE zhh SUPERUSER;
   

1. #CREATE ROLE name CREATEDB;
   
2. postgres=# CREATE ROLE zhh1 CREATEDB;
   

1. #CREATE ROLE name  LOGIN;
   
2. postgres=# CREATE ROLE zhh2  LOGIN;
   

注： 属性LOGIN、SUPERUSER和CREATEROLE被视为特殊权限，它们不会像其它数据库对象的普通权限那样被继承。

(三)查看用户、角色

1. \du 指令显示用户和角色属性
   

用户、角色相关的两张系统表

1. postgres=# \d pg_roles
   
2. postgres=# \d pg_user
   

（四）修改用户、角色

修改用户、角色属性的命令格式

1. ALTER ROLE username WITH attribute_options;
   

示例：

可通过以下方式禁止用户登录

postgres=# ALTER ROLE zhh2 WITH NOLOGIN;

INHERIT该属性使组成员拥有组的所有权限

postgres=# ALTER ROLE zhh2 NOINHERIT;

1. 重命名：
   
   

1. #alter user UserName rename to NewName;
   
2. postgres=# alter user zhh2 rename to test22;
   

2. 该用户的参数配置参数：
   
   

1. #alter user UserName set ParameterName to value;
   
2. postgres=# ALTER USER zhh1 SET statement_timeout TO 300;
   

3. 重置配置参数：
   
   

1. #alter user UserName reset ParameterName / all;
   
2. postgres=# ALTER USER zhh1 RESET statement_timeout;
   
3. postgres=# ALTER USER zhh1 RESET all;
   

（五）删除用户、角色

方式1:在系统命令行使用dropuser命令删除用户

1. #dropuser username
   
2. [postgres@localhost ~]$ dropuser zhh1
   

方式2:在psql命令行使用drop删除

1. #drop role rolename;
   
2. postgres=# drop role test;
   
3. #drop user username;
   
4. postgres=# drop user test22;
   
5. #DROP ROLE IF EXISTS role_name;
   
6. postgres=# DROP ROLE IF EXISTS test1;
   

注意事项：

1、只用超级用户能够删除超级用户

2、只有具有createrole权限的用户能删除非超级用户

3、删除用户前，需要先删除依赖该用户的对象、权限等信息

4、任何属于该组角色的对象都必须先被删除或者将对象的所有者赋予其它角色，任何赋予该组角色的权限也都必须被撤消。

5、删除组role只会删除组的role本身，组的成员并不会被删除

二、权限管控

每个数据库对象都有一个所有者，默认情况下，所有者拥有该对象的所有权限

在数据库中所有的权限都和角色挂钩，postgresql权限分为两部分：

系统权限或者数据库用户的属性
数据库对象上的操作权限(内置权限)

对超级用户postgres不做权限检查，其它用户需检查ACL(Access ControL List)

对于数据库对象，开始只有所有者和超级用户可以做任何操作，其它走ACL

1、权限管控层次结构

2、实例级别的权限

主要由pg_hba.conf来控制，例如 ：

TYPE DATABASE USER ADDRESS METHOD

host all all 127.0.0.1/32 trust

host all postgres 0.0.0.0/0 reject

host all all 0.0.0.0/0 md5

以上配置的解释：

允许任何本地用户无密码连接任何数据库

不允许postgres用户从任何外部地址连接任何数据库

允许其他任何用户从外部地址通过密码连接任何数据库

3、数据库级别的权限

包括允许连接数据库，允许在数据库中创建schema

默认情况下，数据库在创建后

允许public角色连接，即允许任何人连接

不允许除了超级用户和owner之外的任何人在数据库中创建schema

会自动创建名为public 的schema，这个schema的all权限已经赋予给public角色即允许任何人在里面创建对象

基本语法参考

1. GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
   
2. ON DATABASE database_name [, ...]
   
3. TO role_specification [, ...] [ WITH GRANT OPTION ]
   

注：WITH GRANT OPTION表示被赋予权限的用户，拿到对应的权限后，还能将对应的权限赋予给其他人，否则只能自己有这个权限，但是不能再赋予给其他人

4、schema级别的权限

包括允许查看schema中的对象，允许在schema中创建对象。

默认情况下新建的schema的权限不会赋予给public角色

除了超级用户和owner，任何人都没有权限查看schema中的对象或者在schema中新建对象

基本语法参考：

1. GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
   
2. ON SCHEMA schema_name [, ...]
   
3. TO role_specification [, ...] [ WITH GRANT OPTION ]
   

注意 ：当一个用户连接到一个数据库后，正常情况下是不能访问另一个数据库中的对象的；但可以访问当前数据库中其他模式下的对象（在有权限的前提下）。

5、对象级别的权限

每种类型的对象权限都不一样，详细可参考

1. https://www.postgresql.org/docs/9.6/static/sql-grant.html
   

基本语法参考（表对象）：

1. GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
   
2.    [, ...] | ALL [ PRIVILEGES ] }
   
3.    ON { [ TABLE ] table_name [, ...]
   
4.         | ALL TABLES IN SCHEMA schema_name [, ...] }
   
5.    TO role_specification [, ...] [ WITH GRANT OPTION ]
   

\h GRANT显示所有可设置的访问权限

列级别权限控制

序列权限控制

类型域的权限控制(域简单来说就是自定义的带约束的数据类型)

FDW权限控制

FS权限控制

函数权限控制

授予权限

1. GRANT示例:
   
2. GRANT ALL ON database dbname TO rolename;  
   
3. GRANT UPDATE ON tabname TO rolename;
   
4. GRANT SELECT ON ALL TABLES IN SCHEMA schemaname to rolename;
   
5. GRANT ALL ON tabname TO rolename;  
   
6. GRANT ROLE1 TO USER1;     #注：角色的属性不会授予用户
   
7. GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
   
8. GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;  #列授权
   

特殊符号:ALL代表所访问权限，PUBLIC代表所有用户

查看权限

显示角色属性（包含系统权限）

\du 或\du+ [username]

查看系统表 select from pg_roles|pg_user;

查看某用户或角色的权限
SELECT FROM information_schema.table_privileges WHERE grantee = ‘dh’;

显示用户、对象的访问权限列表

\z或\dp [tablename]

权限含义

\z 或\dp [tablename]

1. rolename=xxxx -- privileges granted to a role
   
2.        =xxxx -- privileges granted to PUBLIC
   
3.            r -- SELECT ("read")
   
4.            w -- UPDATE ("write")
   
5.            a -- INSERT ("append")
   
6.            d -- DELETE
   
7.            D -- TRUNCATE
   
8.            x -- REFERENCES
   
9.            t -- TRIGGER
   
10.            X -- EXECUTE
    
11.            U -- USAGE
    
12.            C -- CREATE
    
13.            c -- CONNECT
    
14.            T -- TEMPORARY
    
15.      arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
    
16.            * -- grant option for preceding privilege
    
17.        /yyyy -- role that granted this privilege
    

撤销权限

REVOKE

语法格式如下:

REVOKE permission_type ON table_name FROM user_name;

其中permission_type和table_name含义与GRANT指令中相同

\h revoke