SQL 在 postgres 中查询表的授权

Query grants for a table in postgres

提问by markus

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

如何在 postgres 中查询授予对象的所有 GRANTS？

For example I have table "mytable":

例如我有表“mytable”：

GRANT SELECT, INSERT ON mytable TO user1
GRANT UPDATE ON mytable TO user2 

I need somthing which gives me:

我需要一些东西给我：

user1: SELECT, INSERT
user2: UPDATE

回答by markus

I already found it:

我已经找到了：

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

回答by CPJ

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

\z mytablefrom psql 为您提供表中的所有授权，但您必须将其按单个用户拆分。

回答by Nicolas Payart

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

如果您真的想要每个用户一行，您可以按受赠者分组（string_agg 需要 PG9+）

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

This should output something like :

这应该输出类似：

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

回答by shruti

Try the query below. It will give you list of all users and their permissions on the table.

试试下面的查询。它将为您提供表中所有用户及其权限的列表。

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 a.tablename='your_table_name';

回答by isapir

This query will list all of the tables in all of the databases and schemas (uncomment the line(s) in the WHEREclause 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:

此查询将列出所有数据库和模式中的所有表（取消注释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;

Sample output:

示例输出：

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

回答by vishnu narayanan

Adding on to @shruti's answer

添加到@shruti 的答案

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

为给定用户查询模式中所有表的授权

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;

回答by Sahap Asci

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;