标签
PostgreSQL , pg_class.relacl , pg_attribute.attacl
背景
如何查看数据库中的表的相应权限，已经赋予给哪些用户了。
另外，PostgreSQL还可以针对列进行赋权，还可以适应行安全策略，所以如何查看某张表的某些列的相应权限被赋予给哪些用户了。
还有其他的对象，如视图、函数、语言等，他们的权限被赋予给哪些数据库用户了呢？
这些通过psql \dp很容易实现，但是它又是怎么获取的呢？
psql \dp 获取权限列表
postgres=# \dp+ *.*
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------------------+---------------------------------------+----------+---------------------------+-------------------+----------
hint_plan | hints | table | postgres=arwdDxt/postgres+| |
| | | =r/postgres | |
hint_plan | hints_id_seq | sequence | | |
information_schema | _pg_foreign_data_wrappers | view | | |
information_schema | _pg_foreign_servers | view | | |
information_schema | _pg_foreign_table_columns | view | | |
information_schema | _pg_foreign_tables | view | | |
information_schema | _pg_user_mappings | view | | |
information_schema | administrable_role_authorizations | view | postgres=arwdDxt/postgres+| |
| | | =r/postgres | |
information_schema | applicable_roles | view | postgres=arwdDxt/postgres+| |
| | | =r/postgres | |
....
psql \dp 如何获取权限列表的
使用psql -E选项，可以将psql的内部操作也打印出来，这样就能得到\dp都干了什么了?
psql -E
\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' 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 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')
AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1, 2;
**************************
通过这个QUERY我们可以了解到权限是如何获取的
1. 对象权限，获取自pg_class.relacl，注意它只包含了在pg_class的对象（这里只有表、视图、序列、索引、物化视图、复合类型、TOAST表、外部表）
https://www.postgresql.org/docs/9.6/static/catalog-pg-class.html
| Name | Type | References | Description |
|---|---|---|---|
| relpersistence | char | - | p = permanent table, u = unlogged table, t = temporary table |
| relkind | char | - | r = ordinary table, i = index, S = sequence, v = view, m = materialized view, c = composite type, t = TOAST table, f = foreign table |
| relacl | aclitem[] | - | Access privileges; see GRANT and REVOKE for details |
那么函数、类型、语言、数据库、表空间等的权限在哪里呢？
它们在对应的系统视图中
比如
pg_proc , pg_type , pg_language , pg_database , pg_tablespace
1.1 pg_class.relacl的解读
https://www.postgresql.org/docs/9.6/static/sql-grant.html
Use psql's \dp command to obtain information about existing privileges for tables and columns. For example:
=> \dp mytable
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+---------+-------+-----------------------+--------------------------
public | mytable | table | miriam=arwdDxt/miriam | col1:
: =r/miriam : miriam_rw=rw/miriam
: admin=arw/miriam
(1 row)
The entries shown by \dp are interpreted thus:
rolename=xxxx -- privileges granted to a role
=xxxx -- privileges granted to PUBLIC
r -- SELECT ("read")
w -- UPDATE ("write")
a -- INSERT ("append")
d -- DELETE
D -- TRUNCATE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
c -- CONNECT
T -- TEMPORARY
arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
* -- grant option for preceding privilege
/yyyy -- role that granted this privilege
The above example display would be seen by user miriam after creating table mytable and doing:
GRANT SELECT ON mytable TO PUBLIC;
GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;
2. 列权限，来自pg_attribute.attacl，如下
https://www.postgresql.org/docs/9.6/static/catalog-pg-attribute.html
pg_attribute
| Name | Type | References | Description |
|---|---|---|---|
| attacl | aclitem[] | - | Column-level access privileges, if any have been granted specifically on this column |
3. 行安全策略，来自pg_policy