在PostgreSQL中查找用户是否获得了选择/更新/...表/函数/...的权限。

Question

在PostgreSQL中，如何推荐地确定用户是否获得了某个类（例如表或函数）上的某些权限（例如选择或执行）？

目前我有类似以下的代码：

aclcontains(
    someColumnWithAclitemArray,
    makeaclitem(userOid,grantorOid,someRight,false))

但这很可怕，因为我必须检查每个可能的grantorOid和用户可能属于的每个userOid。

另外一个相关的问题：你可以测试哪些可能的权限？我没有找到任何文档，但阅读源代码我猜测：

INSERT
SELECT
UPDATE
DELETE
TRUNCATE
REFERENCES
TRIGGER
EXECUTE
USAGE
CREATE
CONNECT

似乎还有一个CREATE TEMP权限，但我无法确定在makeaclitem函数中应该使用哪个正确的文本。

- finnsson

Answer 1

我发现一种更好的方法（我似乎记得这是从内置于psql中的一些查询或信息模式视图中获取的），就是使用has_*_privilege函数， 并将其应用于所有可能的用户和对象组合。这将考虑到通过某个组角色访问对象的情况。 例如，这将显示哪些用户对非目录表和视图具有哪些访问权限：

select usename, nspname || '.' || relname as relation,
       case relkind when 'r' then 'TABLE' when 'v' then 'VIEW' end as relation_type,
       priv
from pg_class join pg_namespace on pg_namespace.oid = pg_class.relnamespace,
     pg_user,
     (values('SELECT', 1),('INSERT', 2),('UPDATE', 3),('DELETE', 4)) privs(priv, privorder)
where relkind in ('r', 'v')
      and has_table_privilege(pg_user.usesysid, pg_class.oid, priv)
      and not (nspname ~ '^pg_' or nspname = 'information_schema')
order by 2, 1, 3, privorder;

可能的特权在has_*_privilege函数的说明中详细介绍，该说明位于http://www.postgresql.org/docs/current/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE。

'CREATE TEMP'是数据库级别的权限：它允许用户使用pg_temp_*模式。您可以使用has_database_privilege(useroid, datoid, 'TEMP')进行测试。

- araqnid

Answer 2

请查看“访问权限查询函数”和“GRANT”参考页面。链接如下："Access Privilege Inquiry Functions" 和 "GRANT"。

- Milen A. Radev

Answer 3

因为 Redshift 仅在 INSERT INTO 查询中支持 values() 操作，所以可以使用下面的查询，尽管显然不是很完美：union all select。

select usename, nspname || '.' || relname as relation,
       case relkind when 'r' then 'table' when 'v' then 'view' end as relation_type,
       priv
from pg_class join pg_namespace on pg_namespace.oid = pg_class.relnamespace,
     pg_user,
     (select 'select' as priv,1 as privorder union all select 'insert',2 union all select 'update',3 union all select 'delete',4)
where relkind in ('r', 'v')
      and has_table_privilege(pg_user.usesysid, pg_class.oid, priv)
      and not (nspname ~ '^pg_' or nspname = 'information_schema')
order by 2, 1, 3, privorder;

编辑： 另外，我意识到在我们的数据库中，Dataiku创建的表可以有大写字母，因此，如果出现“表不存在”的错误，您应该使用lower()函数。

- valq

---

您的答案可以通过增加额外的支持信息来改进。请[编辑]以添加更多详细信息，例如引用或文档，以便其他人可以确认您的答案是否正确。您可以在帮助中心中找到有关如何编写良好答案的更多信息。 - Community