【PG如何查看用户拥有的各类对象的操作权限】

最新推荐文章于 2024-09-22 07:28:20 发布

小怪兽ysl

最新推荐文章于 2024-09-22 07:28:20 发布

阅读量9.3k

点赞数 29

分类专栏： PostgreSQL openGauss 文章标签：数据库 postgresql

本文链接：https://blog.csdn.net/weixin_47308871/article/details/130067337

版权

PostgreSQL 同时被 2 个专栏收录

105 篇文章 5 订阅

订阅专栏

openGauss

32 篇文章 2 订阅

订阅专栏

在PostgreSQL数据库平时会赋予很多的权限，对于这些权限是否真正赋予，并且对于各个对象，用户到底有着什么权限，有着不同的方式去验证，例如\dp+ , 查询视图，查询函数，或者使用extenson（PG_PERMISSIONS）等

一、元命令(\dp+)

通常可以使用\dp+ 列出用户,schema的默认权限。列出表、视图、序列的权限,但是它读起来比较不太方便。

postgres=# \dp+
                                           Access privileges
 Schema |           Name           |   Type   |    Access privileges    | Column privileges | Policies 
--------+--------------------------+----------+-------------------------+-------------------+----------
 public | a1                       | view     | xmaster=arwdDxt/xmaster+|                   | 
        |                          |          | chpt=r/xmaster          |                   | 
 public | all_permissions          | view     | xmaster=arwdDxt/xmaster+|                   | 
        |                          |          | =r/xmaster              |                   | 
 public | column_permissions       | view     | xmaster=arwdDxt/xmaster+|                   | 
        |                          |          | =r/xmaster              |                   | 
 public | database_permissions     | view     | xmaster=arwdDxt/xmaster+|                   | 
        |                          |          | =r/xmaster              |                   | 
 public | demotable1               | table    | xmaster=arwdDxt/xmaster+|                   | 
        |                          |          | chpt=r/xmaster          |                   | 
 public | dual                     | view     | xmaster=arwdDxt/xmaster+|                   | 
        |                          |          | chpt=r/xmaster          |                   | 
 public | factdns60                | table    | xmaster=arwdDxt/xmaster+|                   | 
        |                          |          | chpt=r/xmaster          |                   | 
 public | function_permissions     | view     | xmaster=arwdDxt/xmaster+|                   | 
        |                          |          | =r/xmaster              |                   | 
 public | heaptest                 | table    | xmaster=arwdDxt/xmaster+|                   | 
        |                          |          | chpt=r/xmaster          |                   | 
 public | l1                       | table    | xmaster=arwdDxt/xmaster+|

二、查询视图

在PostgreSQL里，对于一些创建用户、创建数据库、登陆、replication等权限，可以查询pg_roles去查看。

而在PostgreSQL数据库里，也有着类似于MySQL数据库的information_schema。Information_schema自动的存在于每个database中，里面包含了数据库中所有对象的定义信息。Information_schema默认不存在于任何用户的search_path中，所以对所有用户都是隐藏的，\dn看不到。

在Information_schema下有着几个x x x.privileges的视图，其中包含了一些对象的权限。包含用户在某列的权限，能够访问的数据类型，存储过程函数的执行权限，表的权限，自定义类型上授予的USAGE权限，某用户的usage权限。

下边拿比较常用的表权限举例

表的权限

在PostgreSQL数据库中，information_schema.role_table_grants和information_schema.table_privileges是用来描述表的权限信息的两个系统表。

information_schema.role_table_grants表列出了授权给角色的表级别权限信息，包括授权角色、被授权表、授权类型（SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES）、授权是否可授予别人、授权者、授权时间等。
information_schema.table_privileges表列出了表的权限信息，包括授权者、被授权表、授权角色、授权类型、授权是否可授予别人、授权时间等。
可以看出，这两个系统表都包含了关于表的权限信息，但是它们的数据来源略有不同。

在想获取一个用户对于一张表的权限的时候，我们可以通过元数据视图information_schema.table_privileges

其实查看 information_schema.role_table_grants这个视图的结果也是大致相同的

可以使用 string_agg函数对用户进行分组，便于我们的查看，例如我们想看t1这张表：

SELECT grantee,table_schema,table_name,string_agg( privilege_type,', ' ) as privilege_type
FROM information_schema.role_table_grants
WHERE table_name='t1'
group by grantee,table_schema,table_name;

或者不针对某张表，根据条件查看用户对部分表的权限：

SELECT grantee,table_schema,table_name, string_agg( privilege_type,', ' ) as privilege_type FROM information_schema.role_table_grants where grantee='xiaoguaishou' group by table_name,table_schema,grantee;

如果去看两个系统表的定义，可以发现information_schema.role_table_grants视图，是通过information_schema.table_privileges和information_schema.enabled_roles两个视图关联得到的。

information_schema.role_table_grants表从授权角色的视角来记录了授权信息，表中包含了授权角色信息以及被授权表的信息。
information_schema.table_privileges表从被授权表的视角来记录了授权信息，表中包含了被授权表的信息以及授权角色的信息。

因此，这两个系统视图提供了不同的视角来描述表的权限信息，可以根据具体的需求来选择使用哪一个。

schema的权限

schema的权限有时候我们更多关注usage的权限，因为有时候我们没有授予用户schema的usage权限，那我们访问schema下的对象时候是有问题的。

select a.nspname,b.rolname,string_agg(a.pri_t,',') from (select nspname,(aclexplode(COALESCE(nspacl, acldefault('n'::"char",nspowner)))).grantee as grantee,(aclexplode(COALESCE(nspacl, acldefault('n'::"char",nspowner)))).privilege_type as pri_t from pg_namespace where nspname not like 'pg%' and nspname <> 'information_schema') a,pg_authid b where (a.grantee=b.oid or a.grantee=0) and b.rolname='xiaoguaishou' group by a.nspname,b.rolname;

三、常用权限函数验证

在PostgreSQL里，有着一些权限验证相关的函数，这些函数以’has_'开头，'privilege’结尾。这些函数可以帮我们验证，比如用户对于数据库的权限、列上的权限、参数的权限、表的权限、schema的权限、function的权限、sequence的权限等等。

DDL类权限ALTER、DROP、COMMENT、INDEX、VACUUM属于所有者固有的权限，隐式拥有，这些权限在PG的此类函数是查询不到的。

这些对象因为涉及的权限不同，所以其入参也有细微区别。并且同一个函数也存在函数重载，即他们的入参不一样。

此处列举几个常用的函数：

1.数据库权限

select has_database_privilege(user,database,privilege);
select has_database_privilege(database,privilege);

上边两种，如果入参包含用户，则获取该用户是否有对应的权限，否则就查询当前用户是否包含该权限。

除了user和database名外，也可以使用oid入参，此处不做具体举例。

数据库的权限：访问权限类型必须是CREATE、CONNECT、TEMPORARY、TEMP（等价于TEMPORARY）的一些组合

2.schema的权限

select has_schema_privilege(user,schema,privilege);
select has_schema_privilege(schema,privilege);

除了user和schema名外，也可以使用oid入参，此处不做具体举例。

schema的权限：has_schema_privilege检查用户是否能以特定方式访问一个模式。其参数类似has_table_privilege。访问权限类型必须是CREATE、USAGE。

3.表的权限

select has_table_privilege(user,table,privilege);
select has_table_privilege(table,privilege);

除了user和table名外，也可以使用oid入参，此处不做具体举例。

has_table_privilege检查用户是否以特定方式访问表。如果使用文本字符串来声明所希望的权限类型，这个文本字符串必须是SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER。

而ALTER、DROP、COMMENT、INDEX或VACUUM这些在PG里是不可以的。

也可以用逗号分隔列出的多个权限类型，如果拥有任何所列出的权限，则结果便为t。

四、MogDB/openGauss对此类函数进行的优化

DDL类权限ALTER、DROP、COMMENT、INDEX、VACUUM属于所有者固有的权限，隐式拥有。

PG的has_*privilege函数是不能查询用户对于一个对象是否有ALTER、DROP或COMMENT等权限的而MogDB在此类函数上做了一定优化，在这些函数的基础上，做了一些调整，函数相对于PG可以查看更多的权限。

例如：

1.新增数据库相关权限检查项

在PG的基础上增加了ALTER、DROP、COMMENT权限的检查。

2.新增schema相关权限检查项

在PG的基础上增加了ALTER、DROP或COMMENT的权限检查。

3.新增table相关权限检查项

在PG的基础上增加了ALTER、DROP、COMMENT、INDEX或VACUUM的权限检查。

五、PG_PERMISSIONS插件使用

PG_PERMISSIONS插件是一个帮助我们查看数据库里的用户拥有什么权限的工具，

包括：数据库权限，模式权限，表权限，视图权限，列权限，函数权限以及序列权限。

但是Superuser不会显示在视图中，因为他们自动拥有所有权限。

1.安装

安装PG_PERMISSIONS插件

[xmaster@mogdb-kernel-0005 pg_permissions-master]$ pwd /home/xmaster/postgresql-14.1/contrib/pg_permissions-master [xmaster@mogdb-kernel-0005 pg_permissions-master]$ make install USE_PGXS=1 pg_config=/opt/ysl_pg14/soft/bin/pg_config /usr/bin/mkdir -p '/opt/ysl_pg14/soft/share/postgresql/extension' /usr/bin/mkdir -p '/opt/ysl_pg14/soft/share/postgresql/extension' /usr/bin/mkdir -p '/opt/ysl_pg14/soft/share/doc/postgresql/extension' /usr/bin/install -c -m 644 .//pg_permissions.control '/opt/ysl_pg14/soft/share/postgresql/extension/' /usr/bin/install -c -m 644 .//pg_permissions--*.sql '/opt/ysl_pg14/soft/share/postgresql/extension/' /usr/bin/install -c -m 644 .//README.pg_permissions '/opt/ysl_pg14/soft/share/doc/postgresql/extension/' [xmaster@mogdb-kernel-0005 pg_permissions-master]$ psql psql (14.1) Type "help" for help. postgres=# create extension pg_permissions; CREATE EXTENSION

2.使用

安装之后，就会产生多个视图，视图包含相应的一些对象的权限信息，其中all_permissions里包含几个其他视图的所有的权限。

除此之外，还有一张permission_target表，用于记录应该授予数据库用户的对象权限。以及一个函数permission_diffs()，这张表和函数可以结合使用：你可以把表里面输入你想要控制的权限，调用permission_diffs()，对比当前对象权限是否为你想要控制的权限。并列出差异。