PostgreSQL权限神器pg_permissions

原创贺晓群 2021-09-10

2476

PostgreSQL的权限（list\grant\revoke）一直以来都是它的糟点之一，权限的可视化不直观，不方便查询，特别对新手来说更困难，需要了解很多概念、系统表和系统函数，即便如此实操起来还是很麻烦；如果你想比较一下当前分配的权限是否是你所想要的权限，也没有很便捷的办法。pg_permissions就是为了解决这些问题而设计的，其实它的实现逻辑比较简单，针对不同的对象封装了不同的视图，便于直观地查询对象上面的权限，同时在视图上创建了触发器，当修改视图列granted时就触发对应的权限分配或回收语句；pg_permissions另外一个比较实用的功能就是权限对比，设计系统时设计人员在设计阶段就可以设计好各个对象的权限，只要把设计好的权限导入计划权限表permission_target，后期开发人员或DBA在开发阶段可能会随意的分配权限，设计人员只需要运行对比函数就能发现权限是否符合设计要求。

下面示例详细介绍pg_permissions的使用：

--安装扩展pg_permission
git clone https://github.com/cybertec-postgresql/pg_permission  
cd pg_permission  
USE_PGXS=1 make  
USE_PGXS=1 make install 

--创建测试库
postgres=# CREATE DATABASE contrib_regression;
CREATE DATABASE

--切换到测试库
postgres=# \c contrib_regression postgres;
You are now connected to database "contrib_regression" as user "postgres".

--创建扩展pg_permissions
contrib_regression=# CREATE EXTENSION pg_permissions; 
CREATE EXTENSION

--创建3个测试用户
contrib_regression=# CREATE ROLE users;
CREATE ROLE
contrib_regression=# CREATE ROLE user1 LOGIN IN ROLE users;
CREATE ROLE
contrib_regression=# CREATE ROLE user2 LOGIN IN ROLE users;
CREATE ROLE


/* database */
--设计授予3个用户 数据库的'CONNECT','TEMPORARY'权限
contrib_regression=# INSERT INTO permission_target
contrib_regression-#    (role_name, permissions, object_type, schema_name, object_name, column_name)
contrib_regression-# VALUES ('users', ARRAY['CONNECT','TEMPORARY']::perm_type[], 'DATABASE', NULL, NULL, NULL),
contrib_regression-#        ('user1', ARRAY['CONNECT','TEMPORARY']::perm_type[], 'DATABASE', NULL, NULL, NULL),
contrib_regression-#        ('user2', ARRAY['CONNECT','TEMPORARY']::perm_type[], 'DATABASE', NULL, NULL, NULL);
INSERT 0 3

--以下语句会报错，因为违法了表permission_target的检查约束，下文会给出permission_target表的结构
--这里报错的原因是：对DATABASE级别的授权不应该再指定具体的模式
contrib_regression=# INSERT INTO permission_target
contrib_regression-#    (role_name, permissions, object_type, schema_name, object_name, column_name)
contrib_regression-# VALUES ('user2', ARRAY['CREATE']::perm_type[], 'DATABASE', 'public', NULL, NULL);
ERROR:  new row for relation "permission_target" violates check constraint "permission_target_valid"
DETAIL:  Failing row contains (4, user2, {CREATE}, DATABASE, public, null, null).

--以下是当前赋予的权限，可能与设计的权限有出入，最后可以通过一个函数对比出具体的差异项，再通过更新对应的视图修正误差的权限
--对数据库contrib_regression回收PUBLIC所有权限
contrib_regression=# REVOKE ALL ON DATABASE contrib_regression FROM PUBLIC;
REVOKE
--给users用户分配数据库contrib_regression的CONNECT, TEMPORARY权限，其中的角色组成员（user1和user2）也具有此权限，这个与设计的权限一致
contrib_regression=# GRANT CONNECT, TEMPORARY ON DATABASE contrib_regression TO users;
GRANT
--给user2用户额外分配了数据库contrib_regression的CREATE权限，这个与设计的权限不一致，用户user2多了一个CREATE权限
contrib_regression=# GRANT CREATE ON DATABASE contrib_regression TO user2;
GRANT


/* schema */
--设计授予用户模式相关的权限
contrib_regression=# INSERT INTO permission_target
contrib_regression-#    (role_name, permissions, object_type, schema_name, object_name, column_name)
contrib_regression-# VALUES ('users', ARRAY['USAGE']::perm_type[], 'SCHEMA', 'appschema', NULL, NULL),
contrib_regression-#        ('user1', ARRAY['USAGE','CREATE']::perm_type[], 'SCHEMA', 'appschema', NULL, NULL),
contrib_regression-#        ('user2', ARRAY['USAGE']::perm_type[], 'SCHEMA', 'appschema', NULL, NULL);
INSERT 0 3

--报错原因同上
contrib_regression=# INSERT INTO permission_target
contrib_regression-#    (role_name, permissions, object_type, schema_name, object_name, column_name)
contrib_regression-# VALUES ('user2', ARRAY['CREATE']::perm_type[], 'SCHEMA', 'appschema', 'sometable', NULL);
ERROR:  new row for relation "permission_target" violates check constraint "permission_target_valid"
DETAIL:  Failing row contains (8, user2, {CREATE}, SCHEMA, appschema, sometable, null).

--创建测试模式
contrib_regression=# CREATE SCHEMA appschema;
CREATE SCHEMA
--这里的当前授权与设计授权比缺失了CREATE权限（用户：user1）
contrib_regression=# GRANT USAGE ON SCHEMA appschema TO PUBLIC;
GRANT
--这里的当前授权与设计授权比多了CREATE权限（用户：user2）
contrib_regression=# GRANT CREATE ON SCHEMA appschema TO user2;
GRANT


/* table */
--设计授予用户 表相关的权限
contrib_regression=# INSERT INTO permission_target
contrib_regression-#    (role_name, permissions, object_type, schema_name, object_name, column_name)
contrib_regression-# VALUES ('user1', ARRAY['SELECT','INSERT','UPDATE','DELETE']::perm_type[], 'TABLE', 'appschema', NULL, NULL),
contrib_regression-#        ('user2', ARRAY['SELECT']::perm_type[], 'TABLE', 'appschema', NULL, NULL);
INSERT 0 2

--报错原因同上
contrib_regression=# INSERT INTO permission_target
contrib_regression-#    (role_name, permissions, object_type, schema_name, object_name, column_name)
contrib_regression-# VALUES ('user2', ARRAY['INSERT']::perm_type[], 'TABLE', 'appschema', 'apptable', 'acolumn');
ERROR:  new row for relation "permission_target" violates check constraint "permission_target_valid"
DETAIL:  Failing row contains (11, user2, {INSERT}, TABLE, appschema, apptable, acolumn).

--创建测试表
contrib_regression=# CREATE TABLE appschema.apptable (
contrib_regression(#    id integer PRIMARY KEY,
contrib_regression(#    val text NOT NULL,
contrib_regression(#    created timestamp with time zone NOT NULL DEFAULT current_timestamp
contrib_regression(# );
CREATE TABLE
contrib_regression=# CREATE TABLE appschema.apptable2 (
contrib_regression(#    id integer PRIMARY KEY,
contrib_regression(#    val text NOT NULL,
contrib_regression(#    created timestamp with time zone NOT NULL DEFAULT current_timestamp
contrib_regression(# ); -- missing all permissions on this one
CREATE TABLE

--这里的当前授权与设计授权比缺失了DELETE权限（用户：user1）
contrib_regression=# GRANT SELECT, INSERT, UPDATE ON appschema.apptable TO user1;
GRANT
--这里的当前授权与设计授权比多了INSERT权限（用户：user2）
contrib_regression=# GRANT SELECT, INSERT ON appschema.apptable TO user2;
GRANT


/* column */
--设计授予用户 表列相关的权限
contrib_regression=# INSERT INTO permission_target
contrib_regression-#    (role_name, permissions, object_type, schema_name, object_name, column_name)
contrib_regression-# VALUES ('user1', ARRAY['SELECT','INSERT','UPDATE','REFERENCES']::perm_type[], 'COLUMN', 'appschema', 'apptable2', 'val');
INSERT 0 1

--报错原因同上
contrib_regression=# INSERT INTO permission_target
contrib_regression-#    (role_name, permissions, object_type, schema_name, object_name, column_name)
contrib_regression-# VALUES ('user2', ARRAY['DELETE']::perm_type[], 'COLUMN', 'appschema', 'apptable2', 'val');
ERROR:  new row for relation "permission_target" violates check constraint "permission_target_valid"
DETAIL:  Failing row contains (13, user2, {DELETE}, COLUMN, appschema, apptable2, val).

--由于没有对user1用户赋权，这里丢失了user1用户应该拥有的列上的所有权限
--这里的当前授权与设计授权比多了UPDATE (val)权限（用户：user2）
contrib_regression=# GRANT UPDATE (val) ON appschema.apptable2 TO user2;
GRANT


/* view */
--设计授予用户 视图相关的权限
contrib_regression=# INSERT INTO permission_target
contrib_regression-#    (role_name, permissions, object_type, schema_name, object_name, column_name)
contrib_regression-# VALUES ('user1', ARRAY['SELECT','INSERT','UPDATE','DELETE']::perm_type[], 'VIEW', 'appschema', 'appview', NULL),
contrib_regression-#        ('user2', ARRAY['SELECT']::perm_type[], 'VIEW', 'appschema', 'appview', NULL);
INSERT 0 2

--创建测试视图
contrib_regression=# CREATE VIEW appschema.appview AS
contrib_regression-# SELECT id, val FROM appschema.apptable;
CREATE VIEW
--这里的当前授权与设计授权比多了SELECT权限（用户：users）
contrib_regression=# GRANT SELECT ON appschema.appview TO users;
GRANT
--这里的当前授权与设计授权比缺失了UPDATE权限（用户：user1）
contrib_regression=# GRANT INSERT, DELETE ON appschema.appview TO user1;


/* sequence */
--设计授予用户 序列相关的权限
contrib_regression=# INSERT INTO permission_target
contrib_regression-#    (role_name, permissions, object_type, schema_name, object_name, column_name)
contrib_regression-# VALUES ('users', ARRAY['USAGE']::perm_type[], 'SEQUENCE', 'appschema', 'appseq', NULL),
contrib_regression-#        ('user1', ARRAY['USAGE','SELECT']::perm_type[], 'SEQUENCE', 'appschema', 'appseq', NULL),
contrib_regression-#        ('user2', ARRAY['USAGE']::perm_type[], 'SEQUENCE', 'appschema', 'appseq', NULL);
INSERT 0 3

--创建测试序列
contrib_regression=# CREATE SEQUENCE appschema.appseq;
CREATE SEQUENCE
--这里的当前授权与设计授权比缺失了SELECT权限（用户：user1）
contrib_regression=# GRANT USAGE ON SEQUENCE appschema.appseq TO users; 
GRANT
--这里的当前授权与设计授权比多了UPDATE权限（用户：user2）
contrib_regression=# GRANT UPDATE ON SEQUENCE appschema.appseq TO user2; 
GRANT


/* function */
--设计授予用户 函数相关的权限
contrib_regression=# INSERT INTO permission_target
contrib_regression-#    (role_name, permissions, object_type, schema_name, object_name, column_name)
contrib_regression-# VALUES ('user1', ARRAY['EXECUTE']::perm_type[], 'FUNCTION', 'appschema', 'appfun(integer)', NULL),
contrib_regression-#        ('user2', ARRAY['EXECUTE']::perm_type[], 'FUNCTION', 'appschema', 'appfun(integer)', NULL);
INSERT 0 2

--报错原因同上
contrib_regression=# INSERT INTO permission_target
contrib_regression-#    (role_name, permissions, object_type, schema_name, object_name, column_name)
contrib_regression-# VALUES ('users', ARRAY['UPDATE']::perm_type[], 'FUNCTION', 'appschema', 'appfun(integer)', NULL);
ERROR:  new row for relation "permission_target" violates check constraint "permission_target_valid"
DETAIL:  Failing row contains (21, users, {UPDATE}, FUNCTION, appschema, appfun(integer), null).

--在此测试案例中3个用户拥有函数的执行权限，本来设计是只授予user1和user2的执行权限，由于没有回收users的权限，users用户也拥有执行权限
contrib_regression=# CREATE FUNCTION appschema.appfun(i integer) RETURNS integer
contrib_regression-#    LANGUAGE sql IMMUTABLE AS
contrib_regression-#    'SELECT i + 2'; 
CREATE FUNCTION


--查询所有当前分配的权限
contrib_regression=# SELECT object_type, role_name, schema_name, object_name, column_name, permission
contrib_regression-# FROM all_permissions
contrib_regression-# WHERE granted
contrib_regression-#   AND role_name IN ('users', 'user1', 'user2')
contrib_regression-#   AND coalesce(schema_name, 'appschema') = 'appschema'
contrib_regression-# ORDER BY object_type, role_name, schema_name, object_name, column_name, permission;
 object_type | role_name | schema_name |   object_name   | column_name | permission 
-------------+-----------+-------------+-----------------+-------------+------------
 TABLE       | user1     | appschema   | apptable        |             | SELECT
 TABLE       | user1     | appschema   | apptable        |             | INSERT
 TABLE       | user1     | appschema   | apptable        |             | UPDATE
 TABLE       | user2     | appschema   | apptable        |             | SELECT
 TABLE       | user2     | appschema   | apptable        |             | INSERT
 VIEW        | user1     | appschema   | appview         |             | SELECT
 VIEW        | user1     | appschema   | appview         |             | INSERT
 VIEW        | user1     | appschema   | appview         |             | DELETE
 VIEW        | user2     | appschema   | appview         |             | SELECT
 VIEW        | users     | appschema   | appview         |             | SELECT
 COLUMN      | user2     | appschema   | apptable2       | val         | UPDATE
 SEQUENCE    | user1     | appschema   | appseq          |             | USAGE
 SEQUENCE    | user2     | appschema   | appseq          |             | UPDATE
 SEQUENCE    | user2     | appschema   | appseq          |             | USAGE
 SEQUENCE    | users     | appschema   | appseq          |             | USAGE
 FUNCTION    | user1     | appschema   | appfun(integer) |             | EXECUTE
 FUNCTION    | user2     | appschema   | appfun(integer) |             | EXECUTE
 FUNCTION    | users     | appschema   | appfun(integer) |             | EXECUTE
 SCHEMA      | user1     | appschema   |                 |             | USAGE
 SCHEMA      | user2     | appschema   |                 |             | USAGE
 SCHEMA      | user2     | appschema   |                 |             | CREATE
 SCHEMA      | users     | appschema   |                 |             | USAGE
 DATABASE    | user1     |             |                 |             | CONNECT
 DATABASE    | user1     |             |                 |             | TEMPORARY
 DATABASE    | user2     |             |                 |             | CREATE
 DATABASE    | user2     |             |                 |             | CONNECT
 DATABASE    | user2     |             |                 |             | TEMPORARY
 DATABASE    | users     |             |                 |             | CONNECT
 DATABASE    | users     |             |                 |             | TEMPORARY
(29 rows)

--对比设计权限和当前分配权限之间的差异
--missing列的t值代表当前权限与设计时权限相比缺失了某个权限，f值代表当前权限与设计时相比多出了某个权限
contrib_regression=# SELECT * FROM permission_diffs()
contrib_regression-# WHERE role_name IN ('users', 'user1', 'user2')
contrib_regression-# ORDER BY object_type, schema_name, object_name, column_name, role_name, permission, missing;
 missing | role_name | object_type | schema_name |   object_name   | column_name | permission 
---------+-----------+-------------+-------------+-----------------+-------------+------------
 t       | user1     | TABLE       | appschema   | apptable        |             | DELETE
 f       | user2     | TABLE       | appschema   | apptable        |             | INSERT
 t       | user1     | TABLE       | appschema   | apptable2       |             | SELECT
 t       | user1     | TABLE       | appschema   | apptable2       |             | INSERT
 t       | user1     | TABLE       | appschema   | apptable2       |             | UPDATE
 t       | user1     | TABLE       | appschema   | apptable2       |             | DELETE
 t       | user2     | TABLE       | appschema   | apptable2       |             | SELECT
 t       | user1     | VIEW        | appschema   | appview         |             | UPDATE
 f       | users     | VIEW        | appschema   | appview         |             | SELECT
 t       | user1     | COLUMN      | appschema   | apptable2       | val         | SELECT
 t       | user1     | COLUMN      | appschema   | apptable2       | val         | INSERT
 t       | user1     | COLUMN      | appschema   | apptable2       | val         | UPDATE
 t       | user1     | COLUMN      | appschema   | apptable2       | val         | REFERENCES
 f       | user2     | COLUMN      | appschema   | apptable2       | val         | UPDATE
 t       | user1     | SEQUENCE    | appschema   | appseq          |             | SELECT
 f       | user2     | SEQUENCE    | appschema   | appseq          |             | UPDATE
 f       | users     | FUNCTION    | appschema   | appfun(integer) |             | EXECUTE
 t       | user1     | SCHEMA      | appschema   |                 |             | CREATE
 f       | user2     | SCHEMA      | appschema   |                 |             | CREATE
 f       | user2     | DATABASE    |             |                 |             | CREATE
(20 rows)

--这里举两个修正权限的例子，只需要把对应数据行上面的granted列值作对应修改即可
contrib_regression=# UPDATE column_permissions SET
contrib_regression-#    granted = TRUE
contrib_regression-# WHERE role_name = 'user1'
contrib_regression-#   AND schema_name = 'appschema'
contrib_regression-#   AND object_name = 'apptable2'
contrib_regression-#   AND column_name = 'val'
contrib_regression-#   AND permission = 'REFERENCES';
UPDATE 1
--all_permissions此视图是多个视图的UNION ALL
contrib_regression=# UPDATE all_permissions SET
contrib_regression-#    granted = FALSE
contrib_regression-# WHERE object_type = 'TABLE'
contrib_regression-#   AND role_name = 'user2'
contrib_regression-#   AND schema_name = 'appschema'
contrib_regression-#   AND object_name = 'apptable'
contrib_regression-#   AND permission = 'INSERT';
UPDATE 1

--上面修正了两条权限，对比后应该少了两条误差数据
contrib_regression=# SELECT * FROM permission_diffs()
contrib_regression-# WHERE role_name IN ('users', 'user1', 'user2')
contrib_regression-# ORDER BY object_type, schema_name, object_name, column_name, role_name, permission, missing;
 missing | role_name | object_type | schema_name |   object_name   | column_name | permission 
---------+-----------+-------------+-------------+-----------------+-------------+------------
 t       | user1     | TABLE       | appschema   | apptable        |             | DELETE
 t       | user1     | TABLE       | appschema   | apptable2       |             | SELECT
 t       | user1     | TABLE       | appschema   | apptable2       |             | INSERT
 t       | user1     | TABLE       | appschema   | apptable2       |             | UPDATE
 t       | user1     | TABLE       | appschema   | apptable2       |             | DELETE
 t       | user2     | TABLE       | appschema   | apptable2       |             | SELECT
 t       | user1     | VIEW        | appschema   | appview         |             | UPDATE
 f       | users     | VIEW        | appschema   | appview         |             | SELECT
 t       | user1     | COLUMN      | appschema   | apptable2       | val         | SELECT
 t       | user1     | COLUMN      | appschema   | apptable2       | val         | INSERT
 t       | user1     | COLUMN      | appschema   | apptable2       | val         | UPDATE
 f       | user2     | COLUMN      | appschema   | apptable2       | val         | UPDATE
 t       | user1     | SEQUENCE    | appschema   | appseq          |             | SELECT
 f       | user2     | SEQUENCE    | appschema   | appseq          |             | UPDATE
 f       | users     | FUNCTION    | appschema   | appfun(integer) |             | EXECUTE
 t       | user1     | SCHEMA      | appschema   |                 |             | CREATE
 f       | user2     | SCHEMA      | appschema   |                 |             | CREATE
 f       | user2     | DATABASE    |             |                 |             | CREATE
(18 rows)


--以上多个语句报错都是违反了表约束
contrib_regression=# \d permission_target
                                 Table "public.permission_target"
   Column    |    Type     | Collation | Nullable |                    Default                    
-------------+-------------+-----------+----------+-----------------------------------------------
 id          | integer     |           | not null | nextval('permission_target_id_seq'::regclass)
 role_name   | name        |           | not null | 
 permissions | perm_type[] |           | not null | 
 object_type | obj_type    |           | not null | 
 schema_name | name        |           |          | 
 object_name | text        |           |          | 
 column_name | name        |           |          | 
Indexes:
    "permission_target_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "permission_target_valid" CHECK (
CASE
    WHEN object_type = 'DATABASE'::obj_type THEN schema_name IS NULL AND object_name IS NULL AND column_name IS NULL AND ARRAY['CONNECT'::perm_type, 'CREATE'::perm_type, 'TEMPORARY'::perm_type] @> permissions
    WHEN object_type = 'SCHEMA'::obj_type THEN object_name IS NULL AND column_name IS NULL AND ARRAY['CREATE'::perm_type, 'USAGE'::perm_type] @> permissions
    WHEN object_type = ANY (ARRAY['TABLE'::obj_type, 'VIEW'::obj_type]) THEN column_name IS NULL AND ARRAY['SELECT'::perm_type, 'INSERT'::perm_type, 'UPDATE'::perm_type, 'DELETE'::perm_type, 'TRUNCATE'::perm_type, 'REFERENCES'::perm_type, 'TRIGGER'::perm_type] @> permissions
    WHEN object_type = 'SEQUENCE'::obj_type THEN column_name IS NULL AND ARRAY['SELECT'::perm_type, 'USAGE'::perm_type, 'UPDATE'::perm_type] @> permissions
    WHEN object_type = 'FUNCTION'::obj_type THEN column_name IS NULL AND ARRAY['EXECUTE'::perm_type] @> permissions
    WHEN object_type = 'COLUMN'::obj_type THEN ARRAY['SELECT'::perm_type, 'INSERT'::perm_type, 'UPDATE'::perm_type, 'REFERENCES'::perm_type] @> permissions
    ELSE NULL::boolean
END)
复制