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.