PostgreSQL 表和列权限(ACL)解读

标签

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

那么函数、类型、语言、数据库、表空间等的权限在哪里呢？

它们在对应的系统视图中

比如

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

3. 行安全策略，来自pg_policy

其他

《PostgreSQL 逻辑结构 和 权限体系 介绍》

《用好PostgreSQL role membership来管理继承组权限》

《PostgreSQL 从源码找出哪些操作需要超级用户权限》