利用插件PG_PERMISSIONS列出权限

列出用户,schema的默认权限。列出表、视图、序列的权限。

这几个简写的介绍如下

\ddp [ pattern ]
Lists default access privilege settings.

\dp [ pattern ]
Lists tables, views and sequences with their associated access privileges. 

\z [ pattern ]
Lists tables, views and sequences with their associated access privileges.
This is an alias for \dp (“display privileges”).

除了以上元命令，还可以通过插件pg_permissions实现。

插件pg_permissions的功能：
1.列出列出单项对象权限；
2.检查当前对象权限是否为你想要控制的权限；
3.快速修改权限；

1.安装：

--root用户
source /home/postgres/.bash_profile 
unzip pg_permissions-master.zip 
cd pg_permissions-master
make install USE_PGXS=1

[postgres@du101 ~]$ psql
psql (14.7)
Type "help" for help.

postgres=# CREATE EXTENSION pg_permissions;
CREATE EXTENSION
postgres=# \dx
                                      List of installed extensions
      Name      | Version |   Schema   |                           Description                           
----------------+---------+------------+-----------------------------------------------------------------
 pg_permissions | 1.2     | public     | view object permissions and compare them with the desired state
 plpgsql        | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

2,pg_permissions插件的表视图函数：

此插件会在数据库中创建8个视图，1张表，2个函数，重点为函数permission_diffs，函数permissions_trigger_func为此插件内部使用的。

postgres=# \dv *permissions
                List of relations
 Schema |         Name         | Type |  Owner   
--------+----------------------+------+----------
 public | all_permissions      | view | postgres
 public | column_permissions   | view | postgres
 public | database_permissions | view | postgres
 public | function_permissions | view | postgres
 public | schema_permissions   | view | postgres
 public | sequence_permissions | view | postgres
 public | table_permissions    | view | postgres
 public | view_permissions     | view | postgres
(8 rows)

postgres=# 
说明：
database_permissions: 当前数据库权限的授予记录
schema_permissions: schema权限的授予记录
table_permissions: 表权限的授予记录
view_permissions:视图权限的授予记录
column_permissions: 表和视图列权限的授予记录
function_permissions:函数权限的授予记录
sequence_permissions: 序列权限的授予记录
all_permissions：所有对象权限的授予记录（UNION以上）

所有视图都具有相同的列；如果某列对当前视图没有意义，则该列为 NULL。
这些视图可用于检查当前授予的对象的权限。
可以更新这些视图的granted列，这会导致执行与修改相对应的 GRANT 或 REVOKE 命令。

注意：超级用户不会显示在视图中，因为他们自动拥有所有权限。


postgres=# \dt *permission*
               List of relations
 Schema |       Name        | Type  |  Owner   
--------+-------------------+-------+----------
 public | permission_target | table | postgres
(1 row)

postgres=# 
说明：
permission_target表，用于记录应该授予数据库用户的对象权限。
如果将相关列设置permission_target为 NULL，则意味着该条目引用所有可能的对象。

postgres=# \df *permission* 
                                                                                             List of functions
 Schema |           Name           |                                                             Result data type                                                        
     | Argument data types | Type 
--------+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------
-----+---------------------+------
 public | permission_diffs         | TABLE(missing boolean, role_name name, object_type obj_type, schema_name name, object_name text, column_name name, permission perm_t
ype) |                     | func
 public | permissions_trigger_func | trigger                                                                                                                             
     |                     | func
(2 rows)

postgres=# 
说明：
permission_diffs()函数根据扩展视图中实际授予的权限检查permission_target表中记录的用户权限，并返回一个差异结果。
如果第一列missing是TRUE，则结果是应该存在但不存在的权限，即在permission_target表中有记录但用户实际不具有的权限；如果missing是FALSE，结果行是一个实际已分配但是没有记录在permission_target表中的权限。

3.使用示例

1.查询user01用户所有权限

通过查询all_permissions可以知晓用户的所有权限。

postgres=# create user user01 password 'User@123';
CREATE ROLE
postgres=# \c - user01
You are now connected to database "postgres" as user "user01".
postgres=> create table t1 (id int);
CREATE TABLE
postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres=# select * from all_permissions where role_name='user01' and object_name not like '%permission%';
 object_type | role_name | schema_name | object_name | column_name | permission | granted 
-------------+-----------+-------------+-------------+-------------+------------+---------
 TABLE       | user01    | public      | t1          |             | SELECT     | t
 TABLE       | user01    | public      | t1          |             | INSERT     | t
 TABLE       | user01    | public      | t1          |             | UPDATE     | t
 TABLE       | user01    | public      | t1          |             | DELETE     | t
 TABLE       | user01    | public      | t1          |             | TRUNCATE   | t
 TABLE       | user01    | public      | t1          |             | REFERENCES | t
 TABLE       | user01    | public      | t1          |             | TRIGGER    | t
 COLUMN      | user01    | public      | t1          | id          | SELECT     | f
 COLUMN      | user01    | public      | t1          | id          | INSERT     | f
 COLUMN      | user01    | public      | t1          | id          | UPDATE     | f
 COLUMN      | user01    | public      | t1          | id          | REFERENCES | f
(11 rows)

postgres=# 

###2 .查看user01用户权限是否符合要求
检查当前对象权限是否为你想要控制的权限。
1、往permission_target表里面输入你想要控制的权限。
2、调用permission_diffs()，对比当前对象权限是否为你想要控制的权限。并列出差异。

postgres=# create schema test1;
CREATE SCHEMA
postgres=
postgres=# INSERT INTO public.permission_target (id,role_name, permissions,object_type, schema_name) VALUES (1,'user01', '{USAGE}','SCHEMA', 'public');     
INSERT 0 1
postgres=# INSERT INTO public.permission_target (id,role_name, permissions,object_type, schema_name) VALUES (2,'user01', '{USAGE}','SCHEMA', 'test1');
INSERT 0 1
postgres=# SELECT * FROM public.permission_diffs() where role_name='user01';
 missing | role_name | object_type | schema_name | object_name | column_name | permission 
---------+-----------+-------------+-------------+-------------+-------------+------------
 t       | user01    | SCHEMA      | test1       |             |             | USAGE
 f       | user01    | SCHEMA      | public      |             |             | CREATE
(2 rows)
postgres=# 

根据missing列的输出可以得出：
用户user01对schema：public的create权限实际已分配但是没有记录在permission_target表中；
用户user01对schema：test1的usage权限在permission_target表中有记录但用户实际不具有;

3.快速修改权限

通过修改all_permissions的granted使得权限马上生效。

postgres=# select * from all_permissions where role_name='user01' and object_name not like '%permission%';
 object_type | role_name | schema_name | object_name | column_name | permission | granted 
-------------+-----------+-------------+-------------+-------------+------------+---------
 TABLE       | user01    | public      | t1          |             | SELECT     | t
 TABLE       | user01    | public      | t1          |             | INSERT     | t
 TABLE       | user01    | public      | t1          |             | UPDATE     | t
 TABLE       | user01    | public      | t1          |             | DELETE     | t
 TABLE       | user01    | public      | t1          |             | TRUNCATE   | t
 TABLE       | user01    | public      | t1          |             | REFERENCES | t
 TABLE       | user01    | public      | t1          |             | TRIGGER    | t
 COLUMN      | user01    | public      | t1          | id          | SELECT     | f
 COLUMN      | user01    | public      | t1          | id          | INSERT     | f
 COLUMN      | user01    | public      | t1          | id          | UPDATE     | f
 COLUMN      | user01    | public      | t1          | id          | REFERENCES | f
(11 rows)

postgres=# 
postgres=#  update all_permissions set granted='f' where object_type='TABLE' and role_name='user01' and object_name='t1' and permission='UPDATE';
UPDATE 1
postgres=# select * from all_permissions where role_name='user01' and object_name not like '%permission%' and object_type='TABLE' and role_name='user01' and object_name='t1' and permission='UPDATE';
 object_type | role_name | schema_name | object_name | column_name | permission | granted 
-------------+-----------+-------------+-------------+-------------+------------+---------
 TABLE       | user01    | public      | t1          |             | UPDATE     | f
(1 row)

postgres=# \c - user01
You are now connected to database "postgres" as user "user01".
postgres=> select user;
  user  
--------
 user01
(1 row)

postgres=> select * from t1;
 id 
----
  1
  2
(2 rows)

postgres=> update t1 set id=3 where id=2;
ERROR:  permission denied for table t1
postgres=>