检索特定角色的所有对象特权 [英] Retrieving all object privileges for specific role

问题描述

是否有一种简单的方法来枚举特定角色具有某些访问权限的所有对象？我知道pg_catalog中的 has _ * _ privilege 函数集，但是它们无法完成工作，我想换一种方式。实际上，我想拥有一个为特定角色的pg_class中存储的任何东西赋予oid和访问特权的视图。

Is there an easy way to enumerate all objects that a specific role has some access privilege to? I know of the set of has_*_privilege functions in pg_catalog but they don't do the job, I want to work the other way around. Effectively I want to have a view that gives oid and access privilege for anything stored in pg_class for a specific role.

这样的视图非常便于检查安全性数据库设置正确。通常，角色比关系少得多，因此检查角色的IMHO繁琐得多。

Such a view would be extremely handy to check if the security of the database is correctly set up. Typically there are far fewer roles than relations so checking the roles is much less onerous IMHO. Should such an utility not be available in the standard PostgreSQL distribution?

根据源代码（acl.h），该环境是一个结构：

According to the source code (acl.h) the aclitem is a struct:

typedef struct AclItem
{ Oid         ai_grantee;     /* ID that this item grants privs to */
  Oid         ai_grantor;     /* grantor of privs */
  AclMode     ai_privs;       /* privilege bits */
} AclItem;

易于使用。但是，pg_type将此列为用户定义的非复合类型。这是为什么？我现在看到的唯一方法是使用字符串函数解析aclitem []数组。

Easy to work with. However, pg_type lists this as a user-defined, non-composite type. Why is that? The only way I see right now is to parse the aclitem[] array using string functions. Is there a better way to analyze the aclitem array?

添加的信息
遍历各个PG列表，这显然是一种更好的方法吗？至少自1997年以来，这个问题一直以各种形式出现（当时我们是否有计算机？当时是否在电视机旁？），此问题与2011年初关于pgsql黑客的讨论线程二进制输入/输出以解决问题最为相关。我很感谢PG的用户（而不是黑客），我感谢开发人员维护稳定接口的关注，但是线程中表达的一些关注对我的口味来说有点过头了。在系统目录中没有定义与源代码中的AclItem结构相等的pg_acl表的真正原因是什么？该结构何时最后更改？我还知道SE的发展可能会改变处理安全性的方式-大概是在用户选择时-因此，我将解决一些呈现acl信息的问题，以便于枚举已授予的特权。特定用户，例如：

Added information Trawling through the various PG lists, it is obvious that this issue keeps popping up in various forms at least since 1997 (did we have computers then? was tv around?), most relevant in the discussion thread "Binary in/out for aclitem" on pgsql-hackers in early 2011. As a (technically skilled) user - rather than a hacker - of PG I appreciate the concern of the developers to maintain a stable interface, but some of the concern voiced in the thread goes a little far for my tastes. What is the real reason not to have a pg_acl table in the system catalogs with definition equal to the AclItem struct in the source code? When did that struct last change? I am also aware of SE developments that will likely introduce changes to the way security is handled - when a users opts to, presumably - so I will settle for something that presents acl information in such a way that it is easy to enumerate granted privileges for a specific user, such as:

SELECT * FROM pg_privileges WHERE grantee = 16384;

就像这样，它仍然可以是底层结构的抽象，因此幕后的任何变化都可以（大概）仍将转换为公开的界面。我会说，它与information_schema方法没有太大区别。

Like so it can still be an abstraction of the underlying structures so any changes under the hood could then (presumably) still be translated into the exposed interface. Not too different from the information_schema approach, I would say.

干杯，
Patrick

Cheers, Patrick

推荐答案

没有开箱即用的视图，但是创建视图所需的数据在系统目录中：

There's no such view out of the box, but the data needed to create it is in the system catalogs:

http://www.postgresql.org/docs/current/static/catalogs.html

例如，在 pg_class 中有一个 relacl 字段：

For instance, there's a relacl field in pg_class:

select oid::regclass, relacl from pg_class;

其他目录中也有类似的字段，即 typacl pg_type 中的c>和在 pg_proc 中的 proacl 中。

There are similar fields in other catalogs, namely typacl in pg_type and proacl in pg_proc.

您可能想再使用两个目录，即 pg_authid 来知道哪些角色具有超级用户特权，而 pg_auth_members 知道谁扮演了什么角色。

You'll presumably want to use two more catalogs, namely pg_authid to know which roles are have superuser privileges, and pg_auth_members to know who has what role.

（ pg_default_acl 仅在对象创建期间使用，因此没有用。）

(The pg_default_acl is only used during object creation, so is not useful.)

在创建视图时可能会使用一些与环境相关的内部函数。您可以像这样在 psql 中列出它们：

There are a couple of aclitem-related internal functions that may come in handy in creating the view. You can list them in psql like so:

\df+ *acl*

尤其是 aclexplode（）。以下示例有望足以帮助您入门：

In particular aclexplode(). The following example will hopefully be enough to get you started:

select oid::regclass,
       (aclexplode(relacl)).grantor,
       (aclexplode(relacl)).grantee,
       (aclexplode(relacl)).privilege_type,
       (aclexplode(relacl)).is_grantable
from pg_class
where relacl is not null;

可以通过首先扩展acl行来对其进行优化，例如：

It can be optimized by expanding the acl rows first, e.g.:

select oid::regclass,
       aclitem.grantee
from (select oid, aclexplode(relacl) as aclitem from pg_class) sub

它将直接带您获得所需的结果。

It will lead you straight to the desired result.

据我所知，这与使用内置工具所获得的效果差不多。 （自然，如果您想进一步优化它，可以在C中编写自己的一组运算符。）

Insofar as I'm aware, that's about as good as it'll get using the built-in tools. (Naturally, you could write your own set of operators in C if you'd like to try to optimize this further.)

关于您的其他问题，我恐怕世界上只有少数人（也就是核心开发者自己）可以回答这些问题。他们在pg骇客名单上的闲逛次数比这里多。

With respect to your extra questions, I'm afraid they can only be answered by a handful of people in the world, aka the core devs themselves. They hang out on the pg hackers list more often than they do here.

这篇关于检索特定角色的所有对象特权的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！