PostgreSQL 表和列权限(ACL)解读

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介:

标签

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

其他

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

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

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

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
14天前
|
SQL 关系型数据库 数据库
SQL 42501: Postgresql查询中的权限不足错误
SQL 42501: Postgresql查询中的权限不足错误
|
14天前
|
安全 关系型数据库 数据库
postgresql|数据库|角色(用户)管理工作---授权和去权以及usage和select两种权限的区别
postgresql|数据库|角色(用户)管理工作---授权和去权以及usage和select两种权限的区别
75 0
|
SQL 安全 前端开发
PostgreSQL 高权限命令执行 (CVE-2019-9193)漏洞复现&实战
PostgreSQL 高权限命令执行 (CVE-2019-9193)漏洞复现&实战
|
SQL 关系型数据库 MySQL
如何迁移自建库用户密码和权限到RDS MySQL/PolarDB MySQL
如何迁移自建库用户密码和权限到RDS MySQL/PolarDB MySQL
LXJ
|
关系型数据库 数据库 PostgreSQL
PostgreSQL PRIVILEGES(权限)
PostgreSQL PRIVILEGES(权限)
LXJ
121 0
|
关系型数据库 数据库 PostgreSQL
postgresql动态赋予用户库,表权限
postgresql动态赋予用户库,表权限
444 0
postgresql动态赋予用户库,表权限
|
安全 关系型数据库 PostgreSQL
postgresql:远程权限开启
postgresql:远程权限开启
108 0
|
SQL Cloud Native 搜索推荐
PolarDB-X 1.0-用户指南-私有RDS管理-创建高权限账号
本文介绍如何在控制台上为私有定制RDS实例创建高权限账号。
269 0
PolarDB-X 1.0-用户指南-私有RDS管理-创建高权限账号
|
SQL 监控
PolarDB-X 1.0-用户指南-SQL审计与分析-为子账号授予SQL审计权限
子账号开通或使用PolarDB-X SQL 审计功能之前,需要由主账号为其授权。
123 0

相关产品

  • 云原生数据库 PolarDB