PostgreSQL , pg_class.relacl , pg_attribute.attacl
如何查看数据库中的表的相应权限，已经赋予给哪些用户了。
另外，PostgreSQL还可以针对列进行赋权，还可以适应行安全策略，所以如何查看某张表的某些列的相应权限被赋予给哪些用户了。
还有其他的对象，如视图、函数、语言等，他们的权限被赋予给哪些数据库用户了呢？
这些通过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
NameTypeReferencesDescriptionrelpersistencechar-p = permanent table, u = unlogged table, t = temporary tablerelkindchar-r = ordinary table, i = index, S = sequence, v = view, m = materialized view, c = composite type, t = TOAST table, f = foreign tablerelaclaclitem[]-Access privileges; see GRANT and REVOKE for details那么函数、类型、语言、数据库、表空间等的权限在哪里呢？
它们在对应的系统视图中
比如
pg_proc , pg_type , pg_language , pg_database , pg_tablespace1.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
NameTypeReferencesDescriptionattaclaclitem[]-Column-level access privileges, if any have been granted specifically on this column3. 行安全策略，来自pg_policy
《PostgreSQL 逻辑结构 和 权限体系 介绍》
《用好PostgreSQL role membership来管理继承组权限》
《PostgreSQL 从源码找出哪些操作需要超级用户权限》
