Postgres 中表的查询授权

来自 psql 的 \z mytable为您提供来自一个表的所有授权，但随后您必须将其按个别用户进行分割。

我已经找到了:

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='mytable'

如果您确实希望每个用户使用一行，则可以按受让人进行分组(string _ agg 需要 PG9 +)

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

这应该输出如下内容:

 grantee |   privileges
---------+----------------
user1   | INSERT, SELECT
user2   | UPDATE
(2 rows)

下面的查询将提供架构中所有用户及其对表的权限的列表。

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';

More details on has_table_privilages can be found 给你.

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

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 <> tg.grantee
GROUP BY tg.table_schema, tg.table_name, tg.grantee, tg.is_grantable;

这个查询将列出所有数据库和模式中的所有表(取消 WHERE子句中的行的注释，以筛选特定的数据库、模式或表) ，并按顺序显示特权，以便很容易看到是否授予了特定的特权:

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;

输出样本:

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 |
...

Adding on to @shruti's answer

查询给定用户的架构中所有表的授权

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;