查明用户是否有权在 PostgreSQL 中选择/更新/...一个表/函数/...
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/946804/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find out if user got permission to select/update/... a table/function/... in PostgreSQL
提问by finnsson
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?
确定用户是否在 PostgreSQL 中的某个类（例如表或函数）上拥有某种权限（例如选择或执行）的推荐方法是什么？
At the moment I got something like
目前我得到了类似的东西
aclcontains(
someColumnWithAclitemArray,
makeaclitem(userOid,grantorOid,someRight,false))
but it's terrible since I have to check for every grantorOidthat is possible and for every userOidthe user can belong to.
但这很糟糕，因为我必须检查所有grantorOid可能的内容以及每个userOid用户可以属于的内容。
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
There also seems to be a CREATE TEMPright, but I can't figure out the correct text to use in the makeaclitem-function.
似乎也有一个CREATE TEMP权利，但我无法弄清楚要在makeaclitem-function 中使用的正确文本。
回答by araqnid
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_*_privilegefunctions, 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.
我发现更好的方法（我似乎记得这是从 psql 中内置的一些查询中获取的，或者可能是 information_schema 视图）是使用这些has_*_privilege函数，并将它们简单地应用于一组所有可能的用户和目的。这也将考虑通过某些组角色访问对象。
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;
The possible privileges are detailed in the description of the has_*_privilegefunctions at http://www.postgresql.org/docs/current/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE.
可能的权限has_*_privilege在http://www.postgresql.org/docs/current/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE中的函数描述中有详细说明。
'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').
“CREATE TEMP”是一种数据库级权限：它允许用户使用pg_temp_*模式。可以用has_database_privilege(useroid, datoid, 'TEMP').
回答by Milen A. Radev
Take a look at the "Access Privilege Inquiry Functions"and also the "GRANT"reference page.
查看“访问权限查询功能”和“GRANT”参考页面。
