PG用户角色权限管理

简介

用户（user）：user是拥有login权限的role。
角色（role）：role可以拥有数据库对象，并将拥有对象的权限赋予其他角色。
组（group）：group是一个特殊的role,不拥有replication/noreplication、connection limit属性的role。

方式一：createuser -U postgres -p 5432 ROLERNAME --forwindows

[postgres@pg1 49162]$ createuser janu1 -U postgres -p 5432 
[postgres@pg1 49162]$ psql -U janu1
psql: error: could not connect to server: FATAL:  database "janu1" does not exist
[postgres@pg1 49162]$ psql -U janu1 postgres
psql (12.2)
Type "help" for help.

postgres=> \c
You are now connected to database "postgres" as user "janu1".

方式二（psql）：create user ROLENAME

[postgres@pg1 49162]$ psql
psql (12.2)
Type "help" for help.

postgres=# create user janu2;
CREATE ROLE


postgres=# \c
You are now connected to database "postgres" as user "postgres".
postgres=# exit


[postgres@pg1 49162]$ psql -U janu2 postgres
psql (12.2)
Type "help" for help.

postgres=> \c
You are now connected to database "postgres" as user "janu2".

修改密码

postgres=> alter role janu1 password 'janu1';
ALTER ROLE


postgres=> exit
[postgres@pg1 49162]$ psql -U janu1 -d postgres
psql (12.2)
Type "help" for help.

postgres=> \c
You are now connected to database "postgres" as user "janu1".

查询role信息

postgres=> \dS+ pg_user
                                     View "pg_catalog.pg_user"
    Column    |           Type           | Collation | Nullable | Default | Storage  | Description 
--------------+--------------------------+-----------+----------+---------+----------+-------------
 usename      | name                     |           |          |         | plain    | 
 usesysid     | oid                      |           |          |         | plain    | 
 usecreatedb  | boolean                  |           |          |         | plain    | 
 usesuper     | boolean                  |           |          |         | plain    | 
 userepl      | boolean                  |           |          |         | plain    | 
 usebypassrls | boolean                  |           |          |         | plain    | 
 passwd       | text                     |           |          |         | extended | 
 valuntil     | timestamp with time zone |           |          |         | plain    | 
 useconfig    | text[]                   | C         |          |         | extended | 
View definition:
 SELECT pg_shadow.usename,
    pg_shadow.usesysid,
    pg_shadow.usecreatedb,
    pg_shadow.usesuper,
    pg_shadow.userepl,
    pg_shadow.usebypassrls,
    '********'::text AS passwd,
    pg_shadow.valuntil,
    pg_shadow.useconfig
   FROM pg_shadow;

postgres=> select * from pg_user where rolname='janu1';;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  |        valuntil        | useconfig 
----------+----------+-------------+----------+---------+--------------+----------+------------------------+-----------
 janu1    |    49175 | f           | f        | f       | f            | ******** |                       | 
(9 rows)

    
postgres=> select * from pg_roles where rolname='janu1';
 rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword |
 rolvaliduntil | rolbypassrls | rolconfig |  oid  
---------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
 janu1   | f        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 49175
(1 row)

权限简介

实例权限：实例权限通过pg_hba.conf配置。
数据库权限：数据库权限通过grant和revoke操作schema配置。
表空间权限：表空间权限通过grant和revoke操作表、物化视图、索引、临时表配置。
模式权限：模式权限通过grant和revoke操作模式下的对象配置。
对象权限：对象权限通过grant和revoke配置。

搜索路径

postgres=> show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

模式精细化控制

[postgres@pg1 ~]$ psql
psql (12.2)
Type "help" for help.

postgres=# grant create on database jan_db to janu1;
GRANT

授予创建模式权限给janu1用户。

postgres=# exit
[postgres@pg1 ~]$ psql -U janu1 -d jan_db
psql (12.2)
Type "help" for help.

jan_db=> create schema s1;
CREATE SCHEMA


jan_db=> \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
 s1     | janu1
(2 rows)



jan_db=> create table s1.t2 (id int,name varchar(20));
CREATE TABLE

默认情况下，psql在public模式下搜索模式信息，可通过search_path修改。

jan_db=> \d
Did not find any relations.


jan_db=> show search_path;
   search_path   
-----------------
 "$user", public
(1 row)


jan_db=> set search_path="$user",public,s1;
SET
jan_db=> \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
 s1     | janu1
(2 rows)


jan_db=> \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 s1     | t2   | table | janu1
(1 row)

从用户角度，通过用户查看系统表information_schema.table_privileges方式，查看用户对表所具有的权限。

jan_db=> select * from information_schema.table_privileges;
 grantor  | grantee | table_catalog |    table_schema    |              table_name               | privilege_type | is_grantable | with_hierarchy 
----------+---------+---------------+--------------------+---------------------------------------+----------------+--------------+----------------
 janu1    | janu1   | jan_db        | s1                 | t2                                    | INSERT         | YES          | NO
 janu1    | janu1   | jan_db        | s1                 | t2                                    | SELECT         | YES          | YES
 janu1    | janu1   | jan_db        | s1                 | t2                                    | UPDATE         | YES          | NO
 janu1    | janu1   | jan_db        | s1                 | t2                                    | DELETE         | YES          | NO
 janu1    | janu1   | jan_db        | s1                 | t2                                    | TRUNCATE       | YES          | NO
 janu1    | janu1   | jan_db        | s1                 | t2                                    | REFERENCES     | YES          | NO
 janu1    | janu1   | jan_db        | s1                 | t2                                    | TRIGGER        | YES          | NO
 postgres | PUBLIC  | jan_db        | pg_catalog         | pg_type                               | SELECT         | NO           | YES
......
......

从表级别看，都有哪些用户对表有哪些权限。

[postgres@pg1 ~]$ psql -U janu1 -d jan_db
psql (12.2)
Type "help" for help.

jan_db=> grant select on s1.t2 to janu1;
GRANT


jan_db=> \dp s1.t2
                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies 
--------+------+-------+---------------------+-------------------+----------
 s1     | t2   | table | janu1=arwdDxt/janu1 |                   | 
(1 row)