SQL postgresql - 查看模式权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22715053/
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
postgresql - view schema privileges
提问by mwrichardson
Is there a query I can run to show currently assigned privileges on a particular schema?
是否可以运行查询来显示当前分配给特定架构的权限？
i.e. privileges that were assigned like so:
即像这样分配的权限：
GRANT USAGE ON SCHEMA dbo TO MyUser
I have tried
我试过了
SELECT *
FROM information_schema.usage_privileges;
but this only returns grants to the built-in PUBLIC role. Instead, I want to see which users have been granted privileges on the various schema.
但这只会返回对内置 PUBLIC 角色的授权。相反，我想查看哪些用户已被授予对各种架构的权限。
Note: I'm actually using Amazon Redshift rather than pure PostgreSQL, although I will accept a pure PostgreSQL answer if this is not possible in Amazon Redshift. (Though I suspect it is)
注意：我实际上使用的是 Amazon Redshift 而不是纯 PostgreSQL，但如果在 Amazon Redshift 中无法实现，我将接受纯 PostgreSQL 答案。（虽然我怀疑是）
回答by senz
in console util psql:
在控制台 util psql 中：
\dn+
will show you
会告诉你
Name | Owner | Access privileges | Description
回答by Ivan Black
List all schemas with their priveleges for current user:
列出所有模式及其当前用户的特权：
WITH "names"("name") AS (
SELECT n.nspname AS "name"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_'
AND n.nspname <> 'information_schema'
) SELECT "name",
pg_catalog.has_schema_privilege(current_user, "name", 'CREATE') AS "create",
pg_catalog.has_schema_privilege(current_user, "name", 'USAGE') AS "usage"
FROM "names";
The response will be for example:
响应将是例如：
name | create | usage
---------+--------+-------
public | t | t
test | t | t
awesome | f | f
(3 rows)
In this example current user is not owner of the awesomeschema.
在此示例中，当前用户不是awesome架构的所有者。
As you could guess, similar request for particular schema:
如您所料，对特定架构的类似请求：
SELECT
pg_catalog.has_schema_privilege(
current_user, 'awesome', 'CREATE') AS "create",
pg_catalog.has_schema_privilege(
current_user, 'awesome', 'USAGE') AS "usage";
and response:
和回应：
create | usage
--------+-------
f | f
As you know, it's possible to use pg_catalog.current_schema()for current schema.
如您所知，可以pg_catalog.current_schema()用于当前模式。
Of all the possible privileges
在所有可能的特权中
-- SELECT
-- INSERT
-- UPDATE
-- DELETE
-- TRUNCATE
-- REFERENCES
-- TRIGGER
-- CREATE
-- CONNECT
-- TEMP
-- EXECUTE
-- USAGE
the only CREATEand USAGEallowed for schemas.
唯一CREATE和USAGE允许的模式。
Like the current_schema()the current_usercan be replaced with particular role.
喜欢current_schema()的current_user可以与特定角色所取代。
BONUSwith currentcolumn
带current柱子的奖励
WITH "names"("name") AS (
SELECT n.nspname AS "name"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_'
AND n.nspname <> 'information_schema'
) SELECT "name",
pg_catalog.has_schema_privilege(current_user, "name", 'CREATE') AS "create",
pg_catalog.has_schema_privilege(current_user, "name", 'USAGE') AS "usage",
"name" = pg_catalog.current_schema() AS "current"
FROM "names";
-- name | create | usage | current
-- ---------+--------+-------+---------
-- public | t | t | t
-- test | t | t | f
-- awesome | f | f | f
-- (3 rows)
回答by mike_pdb
The privileges are stored in the nspacl field of pg_namespace. Since it's an array field, you have to do a little fancy coding to parse it. This query will give you the grant statements used for users and groups:
权限存储在 pg_namespace 的 npacl 字段中。由于它是一个数组字段，你必须做一些花哨的编码来解析它。此查询将为您提供用于用户和组的授权语句：
select
'grant ' || substring(
case when charindex('U',split_part(split_part(array_to_string(nspacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',usage ' else '' end
||case when charindex('C',split_part(split_part(array_to_string(nspacl, '|'),pu.usename,2 ) ,'/',1)) > 0 then ',create ' else '' end
, 2,10000)
|| ' on schema '||nspname||' to "'||pu.usename||'";'
from pg_namespace pn,pg_user pu
where array_to_string(nspacl,',') like '%'||pu.usename||'%' --and pu.usename='<username>'
and nspowner > 1
union
select
'grant ' || substring(
case when charindex('U',split_part(split_part(array_to_string(nspacl, '|'),pg.groname,2 ) ,'/',1)) > 0 then ',usage ' else '' end
||case when charindex('C',split_part(split_part(array_to_string(nspacl, '|'),pg.groname,2 ) ,'/',1)) > 0 then ',create ' else '' end
, 2,10000)
|| ' on schema '||nspname||' to group "'||pg.groname||'";'
from pg_namespace pn,pg_group pg
where array_to_string(nspacl,',') like '%'||pg.groname||'%' --and pg.groname='<username>'
and nspowner > 1
回答by Dennis
This is what psql uses internally :)
这是 psql 在内部使用的 :)
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",
pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges",
pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
回答by Andrei Petrovici
Try this one (works for PUBLIC role):
试试这个（适用于 PUBLIC 角色）：
SELECT nspname,
coalesce(nullif(role.name,''), 'PUBLIC') AS name,
substring(
CASE WHEN position('U' in split_part(split_part((','||array_to_string(nspacl,',')), ','||role.name||'=',2 ) ,'/',1)) > 0 THEN ', USAGE' ELSE '' END
|| CASE WHEN position('C' in split_part(split_part((','||array_to_string(nspacl,',')), ','||role.name||'=',2 ) ,'/',1)) > 0 THEN ', CREATE' ELSE '' END
, 3,10000) AS privileges
FROM pg_namespace pn, (SELECT pg_roles.rolname AS name
FROM pg_roles UNION ALL SELECT '' AS name) AS role
WHERE (','||array_to_string(nspacl,',')) LIKE '%,'||role.name||'=%'
AND nspowner > 1;
回答by xvga
Combined version (groups, users, PUBLIC) that works for AWS Redshift:
适用于 AWS Redshift 的组合版本（组、用户、PUBLIC）：
SELECT *
FROM (SELECT CASE
WHEN charindex ('U',SPLIT_PART(SPLIT_PART(ARRAY_TO_STRING(nspacl,'|'),pu.usename,2),'/',1)) > 0 THEN ' USAGE'
ELSE ''
END ||case WHEN charindex('C',SPLIT_PART(SPLIT_PART(ARRAY_TO_STRING(nspacl,'|'),pu.usename,2),'/',1)) > 0 THEN ' CREATE' ELSE '' END AS rights,
nspname AS schema,
'' AS role,
pu.usename AS user
FROM pg_namespace pn,
pg_user pu
WHERE ARRAY_TO_STRING(nspacl,',') LIKE '%' ||pu.usename|| '%'
--and pu.usename='<username>'
AND nspowner > 1
UNION
SELECT CASE
WHEN charindex ('U',SPLIT_PART(SPLIT_PART(ARRAY_TO_STRING(nspacl,'|'),pg.groname,2),'/',1)) > 0 THEN ' USAGE '
ELSE ''
END ||case WHEN charindex('C',SPLIT_PART(SPLIT_PART(ARRAY_TO_STRING(nspacl,'|'),pg.groname,2),'/',1)) > 0 THEN ' CREATE' ELSE '' END as rights,
nspname AS schema,
pg.groname AS role,
'' AS user
FROM pg_namespace pn,
pg_group pg
WHERE ARRAY_TO_STRING(nspacl,',') LIKE '%' ||pg.groname|| '%'
--and pg.groname='<username>'
AND nspowner > 1
UNION
SELECT CASE
WHEN POSITION('U' IN SPLIT_PART(SPLIT_PART((',' ||array_to_string (nspacl,',')),',' ||roles.name|| '=',2),'/',1)) > 0 THEN ' USAGE'
ELSE ''
END
|| CASE
WHEN POSITION('C' IN SPLIT_PART(SPLIT_PART((',' ||array_to_string (nspacl,',')),',' ||roles.name|| '=',2),'/',1)) > 0 THEN ' CREATE'
ELSE ''
END AS rights,
nspname AS schema,
COALESCE(NULLIF(roles.name,''),'PUBLIC') AS role,
'' AS user
FROM pg_namespace pn,
(SELECT pg_group.groname AS name
FROM pg_group
UNION ALL
SELECT '' AS name) AS roles
WHERE (',' ||array_to_string (nspacl,',')) LIKE '%,' ||roles.name|| '=%'
AND nspowner > 1) privs
ORDER BY schema,rights
回答by Volodymyr Vintonyak
For current question can try this one:
对于当前问题可以试试这个：
SELECT r.rolname AS role_name,
n.nspname AS schema_name,
p.perm AS privilege
FROM pg_catalog.pg_namespace AS n
CROSS JOIN pg_catalog.pg_roles AS r
CROSS JOIN (VALUES ('USAGE'), ('CREATE')) AS p(perm)
WHERE has_schema_privilege(r.oid, n.oid, p.perm)
-- AND n.nspname <> 'information_schema'
-- AND n.nspname !~~ 'pg\_%'
-- AND NOT r.rolsuper
Could be pretty low in performance at database with a lot of objects and users with which I have come across. So i've got possible workaround using aclexplode()default function like this:
在我遇到过很多对象和用户的数据库中，性能可能非常低。所以我有可能使用这样的aclexplode()默认函数的解决方法：
SELECT oid_to_rolname(a.grantee) AS role_name,
n.nspname AS schema_name,
a.privilege_type AS privilege_type
FROM pg_catalog.pg_namespace AS n,
aclexplode(nspacl) a
WHERE n.nspacl IS NOT NULL
AND oid_to_rolname(a.grantee) IS NOT NULL
-- AND n.nspname <> 'information_schema'
-- AND n.nspname !~~ 'pg\_%'
But, be careful, last one doesn't include privileges which users have obtained from PUBLICrole.
Where oid_to_rolname()is simple custom function SELECT rolname FROM pg_roles WHERE oid = $1.
但是，请注意，最后一个不包括用户从PUBLIC角色中获得的权限。oid_to_rolname()简单的自定义函数在哪里SELECT rolname FROM pg_roles WHERE oid = $1。
And, like @Jaisus, my task required to have all privileges which all users have. So i have similar to schemaprivileges queries for table, views, columns, sequences, functions, databaseand even defaultprivileges.
而且，就像@Jaisus一样，我的任务需要拥有所有用户拥有的所有权限。所以我有类似的schema权限查询table, views, columns, sequences, functions,database甚至是default权限。
Also, there is helpful extension pg_permissionwhere I get logic for provided queries and just upgraded it for my purposes.
此外，还有一个有用的扩展pg_permission，我可以为所提供的查询获取逻辑，并仅为我的目的升级它。
回答by Jaisus
I know this post is old but I made another query based on the different answers to have one that is short and easy to use afterward :
我知道这篇文章很旧，但我根据不同的答案进行了另一个查询，以便找到一个简短且易于使用的帖子：
select
nspname as schema_name
, r.rolname as role_name
, pg_catalog.has_schema_privilege(r.rolname, nspname, 'CREATE') as create_grant
, pg_catalog.has_schema_privilege(r.rolname, nspname, 'USAGE') as usage_grant
from pg_namespace pn,pg_catalog.pg_roles r
where array_to_string(nspacl,',') like '%'||r.rolname||'%'
and nspowner > 1
I keep thinking one day I will make a query to have all rights in only one view... One day. ;)
我一直在想，有一天我会查询只在一种视图中拥有所有权利......有一天。;)
