sql - tablas - Consultar concesiones para una tabla en postgres

procedimientos almacenados en postgresql pdf

Question

¿Cómo puedo consultar todas las BECAS otorgadas a un objeto en postgres?

Por ejemplo, tengo la tabla "mytable":

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

Necesito algo que me dé:

user1: SELECT, INSERT
user2: UPDATE

Answer 1

Aquí hay una secuencia de comandos que genera consultas de concesión para una tabla en particular. Omite los privilegios del propietario.

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;

Answer 2

Esta consulta mostrará una lista de todas las tablas en todas las bases de datos y esquemas (elimine la (s) línea (s) en la cláusula WHERE para filtrar bases de datos, esquemas o tablas específicas), con los privilegios mostrados en orden para que sea fácil ver si se concede o no un privilegio específico:

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;

Muestra de salida:

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

Answer 3

Pruebe la consulta a continuación. Le dará una lista de todos los usuarios y sus permisos en la tabla.

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

Answer 4

Si realmente desea una línea por usuario, puede agrupar por beneficiario (requiere PG9 + para cadena_agg)

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

Esto debería generar algo así como:

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

Answer 5

Ya lo encontré:

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

Answer 6

/z mytable de psql le da todas las concesiones de una tabla, pero luego tendría que dividirlas por usuario individual.