标簽
PostgreSQL , pg_permissions , \dp , 權限
https://github.com/digoal/blog/blob/master/201901/20190123_01.md#%E8%83%8C%E6%99%AF 背景
通常列出權限可以使用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插件。
https://github.com/digoal/blog/blob/master/201901/20190123_01.md#pg_permission 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;
https://github.com/digoal/blog/blob/master/201901/20190123_01.md#%E8%A7%86%E5%9B%BE 視圖
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.
https://github.com/digoal/blog/blob/master/201901/20190123_01.md#%E8%A1%A8 表
- 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).
https://github.com/digoal/blog/blob/master/201901/20190123_01.md#%E5%87%BD%E6%95%B0 函數
- 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).
https://github.com/digoal/blog/blob/master/201901/20190123_01.md#%E5%8A%9F%E8%83%BD1 功能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)
https://github.com/digoal/blog/blob/master/201901/20190123_01.md#%E5%8A%9F%E8%83%BD2 功能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
注意：超級使用者的權限不列出，因為超級使用者預設擁有所有權限。
https://github.com/digoal/blog/blob/master/201901/20190123_01.md#%E4%BE%8B%E5%AD%90 例子
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/digoal/blog/blob/master/201901/20190123_01.md#%E5%8F%82%E8%80%83 參考
https://github.com/cybertec-postgresql/pg_permission https://www.postgresql.org/docs/current/ddl-priv.htmlhttps://github.com/digoal/blog/blob/master/201901/20190123_01.md#%E5%85%8D%E8%B4%B9%E9%A2%86%E5%8F%96%E9%98%BF%E9%87%8C%E4%BA%91rds-postgresql%E5%AE%9E%E4%BE%8Becs%E8%99%9A%E6%8B%9F%E6%9C%BA 免費領取阿裡雲RDS PostgreSQL執行個體、ECS虛拟機
![SQL優化SQL語句優化的目的[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)