postgres의 테이블에 대한 허가 쿼리

postgres의 테이블에 대한 허가 쿼리

postgres에서 개체에 부여된 모든 GRANT를 쿼리하려면 어떻게 해야 합니까?

예를 들어 "mytable" 테이블이 있습니다.

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

나는 나에게 주는 것이 필요합니다.

user1: SELECT, INSERT
user2: UPDATE

이미 찾았어요.

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

\z mytablefrom psql은 테이블의 모든 권한을 제공하지만 개별 사용자별로 분할해야 합니다.

아래 쿼리는 스키마의 테이블에 대한 모든 사용자와 해당 사용자의 권한 목록을 제공합니다.

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

자세한 내용은has_table_privilages여기서 찾을 수 있습니다.

사용자당 한 줄을 원하는 경우, 허가자별로 그룹화할 수 있습니다(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)

이 쿼리는 모든 데이터베이스 및 스키마에 있는 모든 테이블을 나열합니다(라인 주석 없음).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 |
...

@shuruti의 답변에 추가

지정된 사용자에 대한 스키마의 모든 테이블에 대한 허가를 쿼리하려면 다음과 같이 하십시오.

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;

다음은 특정 테이블에 대한 허가 쿼리를 생성하는 스크립트입니다.소유자의 권한을 생략합니다.

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;

언급URL : https://stackoverflow.com/questions/7336413/query-grants-for-a-table-in-postgres