标签
PostgreSQL , pg_permissions , \dp , 权限
背景
通常列出权限可以使用psql的一些简写：
\dp+
\dp+ *.*
列出用户,schema的默认权限。列出表、视图、序列的权限。
这几个简写的介绍如下
\ddp [ pattern ]
Lists default access privilege settings.
An entry is shown for each role (and schema, if applicable)
for which the default privilege settings have been changed
from the built-in defaults. If pattern is specified, only
entries whose role name or schema name matches the pattern are listed.
The ALTER DEFAULT PRIVILEGES (ALTER_DEFAULT_PRIVILEGES(7))
command is used to set default access privileges.
The meaning of the privilege display is explained under GRANT(7).
\dp [ pattern ]
Lists tables, views and sequences with their associated
access privileges. If pattern is specified, only tables,
views and sequences whose names match the pattern are listed.
The GRANT(7) and REVOKE(7) commands are used to set access privileges.
The meaning of the privilege display is explained under GRANT(7).
\z [ pattern ]
Lists tables, views and sequences with their associated access privileges.
If a pattern is specified, only tables, views and sequences
whose names match the pattern are listed.
This is an alias for \dp (“display privileges”).
脱离了psql客户端，怎么看权限呢？
一个小技巧psql -E可以打印psql执行的SQL。
psql -E
psql (11.1)
Type "help" for help.
postgres=# \dp
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' END as "Type",
pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",
pg_catalog.array_to_string(ARRAY(
SELECT attname || E':\n ' || pg_catalog.array_to_string(attacl, E'\n ')
FROM pg_catalog.pg_attribute a
WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
), E'\n') AS "Column privileges",
pg_catalog.array_to_string(ARRAY(
SELECT polname
|| CASE WHEN NOT polpermissive THEN
E' (RESTRICTIVE)'
ELSE '' END
|| CASE WHEN polcmd != '*' THEN
E' (' || polcmd || E'):'
ELSE E':'
END
|| CASE WHEN polqual IS NOT NULL THEN
E'\n (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)
ELSE E''
END
|| CASE WHEN polwithcheck IS NOT NULL THEN
E'\n (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)
ELSE E''
END || CASE WHEN polroles <> '{0}' THEN
E'\n to: ' || pg_catalog.array_to_string(
ARRAY(
SELECT rolname
FROM pg_catalog.pg_roles
WHERE oid = ANY (polroles)
ORDER BY 1
), E', ')
ELSE E''
END
FROM pg_catalog.pg_policy pol
WHERE polrelid = c.oid), E'\n')
AS "Policies"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','m','S','f','p')
AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1, 2;
**************************
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+----------------------+-------+---------------------------+-------------------+----------
public | abc | table | | |
public | all_permissions | view | postgres=arwdDxt/postgres+| |
| | | =r/postgres | |
public | bb | table | | |
public | column_permissions | view | postgres=arwdDxt/postgres+| |
| | | =r/postgres | |
public | database_permissions | view | postgres=arwdDxt/postgres+| |
| | | =r/postgres | |
public | function_permissions | view | postgres=arwdDxt/postgres+| |
| | | =r/postgres | |
public | mytab | table | | |
public | permission_target | table | postgres=arwdDxt/postgres+| |
| | | =arwd/postgres | |
public | pgbench_accounts | table | | |
public | pgbench_branches | table | | |
public | pgbench_history | table | | |
public | pgbench_tellers | table | | |
public | schema_permissions | view | postgres=arwdDxt/postgres+| |
| | | =r/postgres | |
public | sequence_permissions | view | postgres=arwdDxt/postgres+| |
| | | =r/postgres | |
public | ssi | table | | |
public | table_permissions | view | postgres=arwdDxt/postgres+| |
| | | =r/postgres | |
public | tbl | table | | |
public | view_permissions | view | postgres=arwdDxt/postgres+| |
| | | =r/postgres | |
(18 rows)
所以，实际上你已经得到了dp背后查询的SQL，你同样可以使用这个查询去获得权限。
但是好像还是很麻烦，并且有几个功能没法实现（函数、列、数据库、SCHEMA的权限没有办法列出。如果你想DIFF一下当前分配的权限是否是你所想要的权限，没有很便捷的办法。）。
为了解决这两个问题，可以使用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
create extension pg_permissions;
视图
The extension provides a number of views:
-
database_permissions: permissions granted on the current database
-
schema_permissions: permissions granted on schemas
-
table_permissions: permissions granted on tables
-
view_permissions: permissions granted on views
-
column_permissions: permissions granted on table and view columns
-
function_permissions: permissions granted on functions
-
sequence_permissions: permissions granted on sequences
-
all_permissions: permissions on all objects (UNION of the above)
All views have the same columns; a column is NULL if it has no meaning for the current view.
These views can be used to examine the currently granted permissions on database objects.
The granted column of these views can be updated, which causes the appropriate GRANT or REVOKE command to be executed.
Note: Superusers are not shown in the views, as they automatically have all permissions.
表
- permission_target
The extension provides a table permission_target with which you can describe the permissions that should be granted on database objects.
If you set a relevant column in permission_target to NULL (e.g., the object_name and column_name columns in a TABLE entry), the meaning is that the entry refers to all possible objects (in the example above, all tables in the schema).
函数
- permission_diffs()
Functions
The table function permission_diffs() checks the desired permissions in permission_target against the actually granted permissions in the views of the extension and returns a table of differences.
If the first column missing is TRUE, the result is a permission that should be there but isn't; if missing is FALSE, the result row is a permission that is there even though it is not defined in permission_target (an extra permission).
功能1
检查（review）当前对象权限是否为你想要控制的权限。
1、往review表里面输入你想要控制的权限。
2、调用permission_diffs()，对比当前对象权限是否为你想要控制的权限。并列出差异。
Then you need to add entries to permission_target that correspond to your desired permissions.
Let's assume we have a schema appschema, and appuser should have SELECT, UPDATE, DELETE and INSERT permissions on all tables and views in that schema:
INSERT INTO public.permission_target
(id, role_name, permissions,
object_type, schema_name)
VALUES
(1, 'appuser', '{SELECT,INSERT,UPDATE,DELETE}',
'TABLE', 'appschema');
INSERT INTO public.permission_target
(id, role_name, permissions,
object_type, schema_name)
VALUES
(2, 'appuser', '{SELECT,INSERT,UPDATE,DELETE}',
'VIEW', 'appschema');
Of course, the user will need the USAGE privilege on the schema:
INSERT INTO public.permission_target
(id, role_name, permissions,i
object_type, schema_name)
VALUES
(3, 'appuser', '{USAGE}',
'SCHEMA', 'appschema');
The user also needs USAGE privileges on the appseq sequence in that schema:
INSERT INTO public.permission_target
(id, role_name, permissions,
object_type, schema_name, object_name)
VALUES
(4, 'appuser', '{USAGE}',
'SEQUENCE', 'appschema', 'appseq');
Now we can review which permissions are missing and which additional permissions are granted:
SELECT * FROM public.permission_diffs();
missing | role_name | object_type | schema_name | object_name | column_name | permission
---------+-----------+-------------+-------------+-------------+-------------+------------
f | laurenz | VIEW | appschema | appview | | SELECT
t | appuser | TABLE | appschema | apptable | | DELETE
(2 rows)
That means that appuser is missing the DELETE privilege on appschema.apptable which should be granted, while user laurenz has the additional SELECT privilege on appschema.appview (missing is FALSE).
To review the actual permissions on an object, we can use the *_permissions views:
SELECT * FROM schema_permissions
WHERE role_name = 'appuser' AND schema_name = 'appschema';
object_type | role_name | schema_name | object_name | column_name | permissions | granted
-------------+-----------+-------------+-------------+-------------+-------------+---------
SCHEMA | appuser | appschema | | | USAGE | t
SCHEMA | appuser | appschema | | | CREATE | f
(2 rows)
功能2
查看权限
1、列出单项对象权限
postgres=> \dv
List of relations
Schema | Name | Type | Owner
--------+----------------------+------+----------
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
2、列出所有对象权限
postgres=> \dv
List of relations
Schema | Name | Type | Owner
--------+----------------------+------+----------
public | all_permissions | view | postgres
注意：超级用户的权限不列出，因为超级用户默认拥有所有权限。
例子
create role digoal login;
\c postgres digoal
create table bb (id int);
postgres=# select * from table_permissions where object_name='bb';
object_type | role_name | schema_name | object_name | column_name | permission | granted
-------------+---------------------------+-------------+-------------+-------------+------------+---------
TABLE | pg_monitor | public | bb | | SELECT | f
TABLE | pg_monitor | public | bb | | INSERT | f
TABLE | pg_monitor | public | bb | | UPDATE | f
TABLE | pg_monitor | public | bb | | DELETE | f
TABLE | pg_monitor | public | bb | | TRUNCATE | f
TABLE | pg_monitor | public | bb | | REFERENCES | f
TABLE | pg_monitor | public | bb | | TRIGGER | f
TABLE | pg_read_all_settings | public | bb | | SELECT | f
TABLE | pg_read_all_settings | public | bb | | INSERT | f
TABLE | pg_read_all_settings | public | bb | | UPDATE | f
TABLE | pg_read_all_settings | public | bb | | DELETE | f
TABLE | pg_read_all_settings | public | bb | | TRUNCATE | f
TABLE | pg_read_all_settings | public | bb | | REFERENCES | f
TABLE | pg_read_all_settings | public | bb | | TRIGGER | f
TABLE | pg_read_all_stats | public | bb | | SELECT | f
TABLE | pg_read_all_stats | public | bb | | INSERT | f
TABLE | pg_read_all_stats | public | bb | | UPDATE | f
TABLE | pg_read_all_stats | public | bb | | DELETE | f
TABLE | pg_read_all_stats | public | bb | | TRUNCATE | f
TABLE | pg_read_all_stats | public | bb | | REFERENCES | f
TABLE | pg_read_all_stats | public | bb | | TRIGGER | f
TABLE | pg_stat_scan_tables | public | bb | | SELECT | f
TABLE | pg_stat_scan_tables | public | bb | | INSERT | f
TABLE | pg_stat_scan_tables | public | bb | | UPDATE | f
TABLE | pg_stat_scan_tables | public | bb | | DELETE | f
TABLE | pg_stat_scan_tables | public | bb | | TRUNCATE | f
TABLE | pg_stat_scan_tables | public | bb | | REFERENCES | f
TABLE | pg_stat_scan_tables | public | bb | | TRIGGER | f
TABLE | pg_read_server_files | public | bb | | SELECT | f
TABLE | pg_read_server_files | public | bb | | INSERT | f
TABLE | pg_read_server_files | public | bb | | UPDATE | f
TABLE | pg_read_server_files | public | bb | | DELETE | f
TABLE | pg_read_server_files | public | bb | | TRUNCATE | f
TABLE | pg_read_server_files | public | bb | | REFERENCES | f
TABLE | pg_read_server_files | public | bb | | TRIGGER | f
TABLE | pg_write_server_files | public | bb | | SELECT | f
TABLE | pg_write_server_files | public | bb | | INSERT | f
TABLE | pg_write_server_files | public | bb | | UPDATE | f
TABLE | pg_write_server_files | public | bb | | DELETE | f
TABLE | pg_write_server_files | public | bb | | TRUNCATE | f
TABLE | pg_write_server_files | public | bb | | REFERENCES | f
TABLE | pg_write_server_files | public | bb | | TRIGGER | f
TABLE | pg_execute_server_program | public | bb | | SELECT | f
TABLE | pg_execute_server_program | public | bb | | INSERT | f
TABLE | pg_execute_server_program | public | bb | | UPDATE | f
TABLE | pg_execute_server_program | public | bb | | DELETE | f
TABLE | pg_execute_server_program | public | bb | | TRUNCATE | f
TABLE | pg_execute_server_program | public | bb | | REFERENCES | f
TABLE | pg_execute_server_program | public | bb | | TRIGGER | f
TABLE | pg_signal_backend | public | bb | | SELECT | f
TABLE | pg_signal_backend | public | bb | | INSERT | f
TABLE | pg_signal_backend | public | bb | | UPDATE | f
TABLE | pg_signal_backend | public | bb | | DELETE | f
TABLE | pg_signal_backend | public | bb | | TRUNCATE | f
TABLE | pg_signal_backend | public | bb | | REFERENCES | f
TABLE | pg_signal_backend | public | bb | | TRIGGER | f
TABLE | digoal | public | bb | | SELECT | t
TABLE | digoal | public | bb | | INSERT | t
TABLE | digoal | public | bb | | UPDATE | t
TABLE | digoal | public | bb | | DELETE | t
TABLE | digoal | public | bb | | TRUNCATE | t
TABLE | digoal | public | bb | | REFERENCES | t
TABLE | digoal | public | bb | | TRIGGER | t
(63 rows)
参考
https://github.com/cybertec-postgresql/pg_permission
https://www.postgresql.org/docs/current/ddl-priv.html
免费领取阿里云RDS PostgreSQL实例、ECS虚拟机
