blocks|key|788801|text|我已经找到了：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|788802|SELECT+grantee,+privilege_type+
FROM+information_schema.role_table_grants+
WHERE+table_name='mytable'|code-block|syntax|javascript|788803|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I already found it:

<pre><code>SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name='mytable'
</code></pre>

blocks|key|788771|text|来自psql的\z+mytable为您提供了一个表中的所有授权，但是您必须按单个用户对其进行拆分。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|788772|entityMap^0|7|A|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

<code>\z mytable</code> from psql gives you all the grants from a table, but you'd then have to split it up by individual user.

blocks|key|788892|text|下面的查询将为您提供模式中所有用户及其对表的权限的列表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|788893|select+a.schemaname,+a.tablename,+b.usename,
++HAS_TABLE_PRIVILEGE(usename,+quote_ident(schemaname)+%7C%7C+'.'+%7C%7C+quote_ident(tablename),+'select')+as+has_select,
++HAS_TABLE_PRIVILEGE(usename,+quote_ident(schemaname)+%7C%7C+'.'+%7C%7C+quote_ident(tablename),+'insert')+as+has_insert,
++HAS_TABLE_PRIVILEGE(usename,+quote_ident(schemaname)+%7C%7C+'.'+%7C%7C+quote_ident(tablename),+'update')+as+has_update,
++HAS_TABLE_PRIVILEGE(usename,+quote_ident(schemaname)+%7C%7C+'.'+%7C%7C+quote_ident(tablename),+'delete')+as+has_delete,+
++HAS_TABLE_PRIVILEGE(usename,+quote_ident(schemaname)+%7C%7C+'.'+%7C%7C+quote_ident(tablename),+'references')+as+has_references+
from+pg_tables+a,+pg_user+b+
where+a.schemaname+=+'your_schema_name'+and+a.tablename='your_table_name';|code-block|syntax|javascript|788894|有关has_table_privilages的更多详细信息，请访问here。|offset|length|style|CODE|788895|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresql.org/docs/current/functions-info.html^0|0|0|2|K|X|4|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@$I|X|J|Y|K|L]]|9|@$I|Z|J|10|1|11]]|A|$]]|$1|M|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

The query below will give you a list of all users and their permissions on the table in a schema.
<pre><code>select a.schemaname, a.tablename, b.usename,
 HAS_TABLE_PRIVILEGE(usename, quote_ident(schemaname) || '.' || quote_ident(tablename), 'select') as has_select,
 HAS_TABLE_PRIVILEGE(usename, quote_ident(schemaname) || '.' || quote_ident(tablename), 'insert') as has_insert,
 HAS_TABLE_PRIVILEGE(usename, quote_ident(schemaname) || '.' || quote_ident(tablename), 'update') as has_update,
 HAS_TABLE_PRIVILEGE(usename, quote_ident(schemaname) || '.' || quote_ident(tablename), 'delete') as has_delete, 
 HAS_TABLE_PRIVILEGE(usename, quote_ident(schemaname) || '.' || quote_ident(tablename), 'references') as has_references 
from pg_tables a, pg_user b 
where a.schemaname = 'your_schema_name' and a.tablename='your_table_name';
</code></pre>
More details on <code>has_table_privilages</code> can be found <a href="https://www.postgresql.org/docs/current/functions-info.html" rel="noreferrer">here</a>.

blocks|key|788847|text|如果您确实希望每个用户使用一行，则可以按被授权者进行分组(对于string_agg，需要PG9%2B+)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|788848|SELECT+grantee,+string_agg(privilege_type,+',+')+AS+privileges
FROM+information_schema.role_table_grants+
WHERE+table_name='mytable'+++
GROUP+BY+grantee;|code-block|syntax|javascript|788849|这应该会输出如下内容：|788850|+grantee+%7C+++privileges+++
---------%2B----------------
+user1+++%7C+INSERT,+SELECT
+user2+++%7C+UPDATE
(2+rows)|788851|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

If you really want one line per user, you can group by grantee (require PG9+ for string_agg)

<pre><code>SELECT grantee, string_agg(privilege_type, ', ') AS privileges
FROM information_schema.role_table_grants 
WHERE table_name='mytable' 
GROUP BY grantee;
</code></pre>

This should output something like :

<pre><code> grantee | privileges 
---------+----------------
 user1 | INSERT, SELECT
 user2 | UPDATE
(2 rows)
</code></pre>

blocks|key|572908|text|此查询将列出所有数据库和模式中的所有表(取消注释WHERE子句中的行，以筛选特定的数据库、模式或表)，并按顺序显示特权，以便很容易地查看是否授予了特定的特权：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|572909|SELECT+grantee
++++++,table_catalog
++++++,table_schema
++++++,table_name
++++++,string_agg(privilege_type,+',+'+ORDER+BY+privilege_type)+AS+privileges
FROM+information_schema.role_table_grants+
WHERE+grantee+!=+'postgres'+
--++and+table_catalog+=+'somedatabase'+/*+uncomment+line+to+filter+database+*/
--++and+table_schema++=+'someschema'+++/*+uncomment+line+to+filter+schema++*/
--++and+table_name++++=+'sometable'++++/*+uncomment+line+to+filter+table++*/
GROUP+BY+1,+2,+3,+4;|code-block|syntax|javascript|572910|示例输出：|572911|grantee+%7Ctable_catalog+++%7Ctable_schema++%7Ctable_name+++++%7Cprivileges+++++%7C
--------%7C----------------%7C--------------%7C---------------%7C---------------%7C
PUBLIC++%7Cadventure_works+%7Cpg_catalog++++%7Cpg_sequence++++%7CSELECT+++++++++%7C
PUBLIC++%7Cadventure_works+%7Cpg_catalog++++%7Cpg_sequences+++%7CSELECT+++++++++%7C
PUBLIC++%7Cadventure_works+%7Cpg_catalog++++%7Cpg_settings++++%7CSELECT,+UPDATE+%7C
...|572912|entityMap^0|O|5|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|P|$]]

This query will list all of the tables in all of the databases and schemas (uncomment the line(s) in the <code>WHERE</code> clause to filter for specific databases, schemas, or tables), with the privileges shown in order so that it's easy to see if a specific privilege is granted or not:

<pre><code>SELECT grantee
 ,table_catalog
 ,table_schema
 ,table_name
 ,string_agg(privilege_type, ', ' ORDER BY privilege_type) AS privileges
FROM information_schema.role_table_grants 
WHERE grantee != 'postgres' 
-- and table_catalog = 'somedatabase' /* uncomment line to filter database */
-- and table_schema = 'someschema' /* uncomment line to filter schema */
-- and table_name = 'sometable' /* uncomment line to filter table */
GROUP BY 1, 2, 3, 4;
</code></pre>

Sample output:

<pre><code>grantee |table_catalog |table_schema |table_name |privileges |
--------|----------------|--------------|---------------|---------------|
PUBLIC |adventure_works |pg_catalog |pg_sequence |SELECT |
PUBLIC |adventure_works |pg_catalog |pg_sequences |SELECT |
PUBLIC |adventure_works |pg_catalog |pg_settings |SELECT, UPDATE |
...
</code></pre>

blocks|key|789067|text|补充@shruti的答案|type|unstyled|depth|inlineStyleRanges|entityRanges|data|789068|查询给定用户的架构中所有表的授权|789069|select+a.tablename,+
+++++++b.usename,+
+++++++HAS_TABLE_PRIVILEGE(usename,tablename,+'select')+as+select,
+++++++HAS_TABLE_PRIVILEGE(usename,tablename,+'insert')+as+insert,+
+++++++HAS_TABLE_PRIVILEGE(usename,tablename,+'update')+as+update,+
+++++++HAS_TABLE_PRIVILEGE(usename,tablename,+'delete')+as+delete,+
+++++++HAS_TABLE_PRIVILEGE(usename,tablename,+'references')+as+references+
from+pg_tables+a,+
+++++pg_user+b+
where+schemaname='your_schema_name'+
++++++and+b.usename='your_user_name'+
order+by+tablename;|code-block|syntax|javascript|789070|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Adding on to @shruti's answer

To query grants for all tables in a schema for a given user

<pre><code>select a.tablename, 
 b.usename, 
 HAS_TABLE_PRIVILEGE(usename,tablename, 'select') as select,
 HAS_TABLE_PRIVILEGE(usename,tablename, 'insert') as insert, 
 HAS_TABLE_PRIVILEGE(usename,tablename, 'update') as update, 
 HAS_TABLE_PRIVILEGE(usename,tablename, 'delete') as delete, 
 HAS_TABLE_PRIVILEGE(usename,tablename, 'references') as references 
from pg_tables a, 
 pg_user b 
where schemaname='your_schema_name' 
 and b.usename='your_user_name' 
order by tablename;
</code></pre>

blocks|key|788928|text|下面是一个为特定表生成授权查询的脚本。它省略了所有者的特权。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|788929|SELECT+
++++format+(
++++++'GRANT+%25s+ON+TABLE+%25I.%25I+TO+%25I%25s;',
++++++string_agg(tg.privilege_type,+',+'),
++++++tg.table_schema,
++++++tg.table_name,
++++++tg.grantee,
++++++CASE
++++++++WHEN+tg.is_grantable+=+'YES'+
++++++++THEN+'+WITH+GRANT+OPTION'+
++++++++ELSE+''+
++++++END
++++)
++FROM+information_schema.role_table_grants+tg
++JOIN+pg_tables+t+ON+t.schemaname+=+tg.table_schema+AND+t.tablename+=+tg.table_name
++WHERE
++++tg.table_schema+=+'myschema'+AND
++++tg.table_name='mytable'+AND
++++t.tableowner+<>+tg.grantee
++GROUP+BY+tg.table_schema,+tg.table_name,+tg.grantee,+tg.is_grantable;|code-block|syntax|javascript|788930|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is a script which generates grant queries for a particular table. It omits owner's privileges.

<pre><code>SELECT 
 format (
 'GRANT %s ON TABLE %I.%I TO %I%s;',
 string_agg(tg.privilege_type, ', '),
 tg.table_schema,
 tg.table_name,
 tg.grantee,
 CASE
 WHEN tg.is_grantable = 'YES' 
 THEN ' WITH GRANT OPTION' 
 ELSE '' 
 END
 )
 FROM information_schema.role_table_grants tg
 JOIN pg_tables t ON t.schemaname = tg.table_schema AND t.tablename = tg.table_name
 WHERE
 tg.table_schema = 'myschema' AND
 tg.table_name='mytable' AND
 t.tableowner &lt;&gt; tg.grantee
 GROUP BY tg.table_schema, tg.table_name, tg.grantee, tg.is_grantable;
</code></pre>

How can I query all GRANTS granted to an object in postgres?

For example I have table "mytable":

<pre><code>GRANT SELECT, INSERT ON mytable TO user1
GRANT UPDATE ON mytable TO user2 
</code></pre>

I need somthing which gives me:

<pre><code>user1: SELECT, INSERT
user2: UPDATE
</code></pre>

Query grants for a table in postgres

如何查询postgres中授予对象的所有授权？例如，我有一个表"mytable"：GRANT SELECT, INSERT ON mytable TO user1GRANT UPDATE ON mytable TO user2 我需要一些能给我的东西user1: SELECT, INSERTuser2: UPDATE

问postgres中表的查询授权
EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问postgres中表的查询授权EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问postgres中表的查询授权
EN