找出用户是否有权在PostgreSQL中选择/更新/...表/函数/... [英] Find out if user got permission to select/update/... a table/function/... in PostgreSQL

问题描述

弄清楚用户是否对PostgreSQL中的某个类(例如表或函数)拥有某种权利(例如选择或执行)的推荐方法是什么?

What is the recommended way to figure out if a user got a certain right (e.g. select or execute) on a certain class (e.g. table or function) in PostgreSQL?

此刻我得到了类似的东西

At the moment I got something like

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

但是这很糟糕，因为我必须检查每个可能的grantorOid和用户可以属于的每个userOid.

but it's terrible since I have to check for every grantorOid that is possible and for every userOid the user can belong to.

相关说明:您可以测试哪些权利? 我没有找到任何文档，但正在阅读我猜的源代码:

On a related note: what are the possible rights you can test for? I haven't found any documentation but reading the source code I guess:

INSERT
SELECT
UPDATE
DELETE
TRUNCATE
REFERENCES
TRIGGER
EXECUTE
USAGE
CREATE
CONNECT

似乎也有CREATE TEMP的权利，但是我想不出要在makeaclitem功能中使用的正确文本.

There also seems to be a CREATE TEMP right, but I can't figure out the correct text to use in the makeaclitem-function.

推荐答案

我发现可以使用一种更好的方法(而且我似乎还记得这是从psql内置的一些查询中获得的，或者也许是information_schema视图) has_*_privilege函数，并将其简单地应用于一组用户和对象的所有可能组合.这还将考虑通过某些组角色也可以访问对象.

I've found that a better approach (and I seem to remember this was taken from some queries built into psql, or maybe the information_schema views) is to use the has_*_privilege functions, and simply apply them to a set of all possible combinations of user and object. This will take account of having access to an object via some group role as well.

例如，这将显示哪些用户有权访问非目录表和视图:

For example, this will show which users have which access to non-catalogue tables and views:

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;

可能的特权在'CREATE TEMP'是数据库级特权:它允许用户使用pg_temp_*模式.可以使用has_database_privilege(useroid, datoid, 'TEMP')进行测试.

'CREATE TEMP' is a database-level privilege: it permits a user to use a pg_temp_* schema. It can be tested with has_database_privilege(useroid, datoid, 'TEMP').

这篇关于找出用户是否有权在PostgreSQL中选择/更新/...表/函数/...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！