error - postgresql-ver privilegios de esquema

exception postgresql

Question

¿Hay alguna consulta que pueda ejecutar para mostrar los privilegios actualmente asignados en un esquema en particular?

Es decir privilegios que fueron asignados como tal:

GRANT USAGE ON SCHEMA dbo TO MyUser

Yo he tratado

SELECT *
FROM information_schema.usage_privileges;

pero esto solo devuelve las concesiones al rol PUBLIC incorporado. En su lugar, quiero ver a qué usuarios se les han otorgado privilegios en los distintos esquemas.

Nota: en realidad estoy usando Amazon Redshift en lugar de puro PostgreSQL, aunque aceptaré una respuesta pura de PostgreSQL si esto no es posible en Amazon Redshift. (Aunque sospecho que es)

Answer 1

Esto es lo que psql usa internamente :)

SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",
  pg_catalog.array_to_string(n.nspacl, E''/n'') AS "Access privileges",
  pg_catalog.obj_description(n.oid, ''pg_namespace'') AS "Description"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ ''^pg_'' AND n.nspname <> ''information_schema''
ORDER BY 1;

Answer 2

Listar todos los esquemas con sus privilegios para el usuario actual:

WITH "names"("name") AS (
  SELECT n.nspname AS "name"
    FROM pg_catalog.pg_namespace n
      WHERE n.nspname !~ ''^pg_''
        AND n.nspname <> ''information_schema''
) SELECT "name",
  pg_catalog.has_schema_privilege(current_user, "name", ''CREATE'') AS "create",
  pg_catalog.has_schema_privilege(current_user, "name", ''USAGE'') AS "usage"
    FROM "names";

La respuesta será por ejemplo:

  name   | create | usage 
---------+--------+-------
 public  | t      | t
 test    | t      | t
 awesome | f      | f
(3 rows)

En este ejemplo, el usuario actual no es el propietario del awesome esquema.

Como puede adivinar, una solicitud similar para un esquema particular:

SELECT
  pg_catalog.has_schema_privilege(
    current_user, ''awesome'', ''CREATE'') AS "create",
  pg_catalog.has_schema_privilege(
    current_user, ''awesome'', ''USAGE'') AS "usage";

y respuesta:

 create | usage 
--------+-------
 f      | f

Como saben, es posible usar pg_catalog.current_schema() para el esquema actual.

De todos los privilegios posibles.

-- SELECT
-- INSERT
-- UPDATE
-- DELETE
-- TRUNCATE
-- REFERENCES
-- TRIGGER
-- CREATE
-- CONNECT
-- TEMP
-- EXECUTE
-- USAGE

la única CREATE y USAGE permitidos para los esquemas.

Al igual que current_schema() current_user se puede reemplazar con un rol particular.

BONIFICACIÓN con current columna current

WITH "names"("name") AS (
  SELECT n.nspname AS "name"
    FROM pg_catalog.pg_namespace n
      WHERE n.nspname !~ ''^pg_''
        AND n.nspname <> ''information_schema''
) SELECT "name",
  pg_catalog.has_schema_privilege(current_user, "name", ''CREATE'') AS "create",
  pg_catalog.has_schema_privilege(current_user, "name", ''USAGE'')  AS "usage",
  "name" = pg_catalog.current_schema() AS "current"
    FROM "names";

--   name   | create | usage | current
-- ---------+--------+-------+---------
--  public  | t      | t     | t
--  test    | t      | t     | f
--  awesome | f      | f     | f
-- (3 rows)

WITH | Funciones de información del sistema | GRANT (privilegios)

Answer 3

Los privilegios se almacenan en el campo nspacl de pg_namespace. Ya que es un campo de matriz, tienes que hacer un poco de codificación elegante para analizarlo. Esta consulta le dará las declaraciones de concesión utilizadas para los usuarios y grupos:

select 
''grant '' || substring(
          case when charindex(''U'',split_part(split_part(array_to_string(nspacl, ''|''),pu.usename,2 ) ,''/'',1)) > 0 then '',usage '' else '''' end 
          ||case when charindex(''C'',split_part(split_part(array_to_string(nspacl, ''|''),pu.usename,2 ) ,''/'',1)) > 0 then '',create '' else '''' end 
       , 2,10000)
|| '' on schema ''||nspname||'' to "''||pu.usename||''";'' 
from pg_namespace pn,pg_user pu
 where  array_to_string(nspacl,'','') like ''%''||pu.usename||''%'' --and pu.usename=''<username>'' 
and nspowner > 1 
union
select 
''grant '' || substring(
          case when charindex(''U'',split_part(split_part(array_to_string(nspacl, ''|''),pg.groname,2 ) ,''/'',1)) > 0 then '',usage '' else '''' end 
          ||case when charindex(''C'',split_part(split_part(array_to_string(nspacl, ''|''),pg.groname,2 ) ,''/'',1)) > 0 then '',create '' else '''' end 
       , 2,10000)
|| '' on schema ''||nspname||'' to group "''||pg.groname||''";'' 
from pg_namespace pn,pg_group pg
 where array_to_string(nspacl,'','') like ''%''||pg.groname||''%'' --and pg.groname=''<username>'' 
 and nspowner > 1 

Answer 4

Prueba este (funciona para el papel PUBLIC):

SELECT nspname,
       coalesce(nullif(role.name,''''), ''PUBLIC'') AS name,
       substring(
          CASE WHEN position(''U'' in split_part(split_part(('',''||array_to_string(nspacl,'','')), '',''||role.name||''='',2 ) ,''/'',1)) > 0 THEN '', USAGE'' ELSE '''' END 
          || CASE WHEN position(''C'' in split_part(split_part(('',''||array_to_string(nspacl,'','')), '',''||role.name||''='',2 ) ,''/'',1)) > 0 THEN '', CREATE'' ELSE '''' END 
       , 3,10000) AS privileges
FROM pg_namespace pn, (SELECT pg_roles.rolname AS name
   FROM pg_roles UNION ALL SELECT '''' AS name) AS role
 WHERE ('',''||array_to_string(nspacl,'','')) LIKE ''%,''||role.name||''=%''
 AND nspowner > 1;

Answer 5

Sé que esta publicación es antigua pero hice otra consulta basada en las diferentes respuestas para tener una que sea breve y fácil de usar después:

select
    nspname as schema_name
    , r.rolname as role_name
    , pg_catalog.has_schema_privilege(r.rolname, nspname, ''CREATE'') as create_grant
    , pg_catalog.has_schema_privilege(r.rolname, nspname, ''USAGE'') as usage_grant
from pg_namespace pn,pg_catalog.pg_roles r
where array_to_string(nspacl,'','') like ''%''||r.rolname||''%'' 
    and nspowner > 1 

Sigo pensando que un día haré una consulta para tener todos los derechos en una sola vista ... Un día. ;)

Answer 6

Versión combinada (grupos, usuarios, PUBLIC) que funciona para AWS Redshift:

    SELECT *
FROM (SELECT CASE
               WHEN charindex (''U'',SPLIT_PART(SPLIT_PART(ARRAY_TO_STRING(nspacl,''|''),pu.usename,2),''/'',1)) > 0 THEN '' USAGE''
               ELSE ''''
             END ||case WHEN charindex(''C'',SPLIT_PART(SPLIT_PART(ARRAY_TO_STRING(nspacl,''|''),pu.usename,2),''/'',1)) > 0 THEN '' CREATE'' ELSE '''' END AS rights,
             nspname AS schema,
             '''' AS role,
             pu.usename AS user
      FROM pg_namespace pn,
           pg_user pu
      WHERE ARRAY_TO_STRING(nspacl,'','') LIKE ''%'' ||pu.usename|| ''%''
      --and pu.usename=''<username>'' 
      AND   nspowner > 1

  UNION

      SELECT CASE
               WHEN charindex (''U'',SPLIT_PART(SPLIT_PART(ARRAY_TO_STRING(nspacl,''|''),pg.groname,2),''/'',1)) > 0 THEN '' USAGE ''
               ELSE ''''
             END ||case WHEN charindex(''C'',SPLIT_PART(SPLIT_PART(ARRAY_TO_STRING(nspacl,''|''),pg.groname,2),''/'',1)) > 0 THEN '' CREATE'' ELSE '''' END as rights,
             nspname AS schema,
             pg.groname AS role,
             '''' AS user
      FROM pg_namespace pn,
           pg_group pg
      WHERE ARRAY_TO_STRING(nspacl,'','') LIKE ''%'' ||pg.groname|| ''%''
      --and pg.groname=''<username>'' 
      AND   nspowner > 1

  UNION

      SELECT CASE
               WHEN POSITION(''U'' IN SPLIT_PART(SPLIT_PART(('','' ||array_to_string (nspacl,'','')),'','' ||roles.name|| ''='',2),''/'',1)) > 0 THEN '' USAGE''
               ELSE ''''
             END 
      || CASE
               WHEN POSITION(''C'' IN SPLIT_PART(SPLIT_PART(('','' ||array_to_string (nspacl,'','')),'','' ||roles.name|| ''='',2),''/'',1)) > 0 THEN '' CREATE''
               ELSE ''''
             END AS rights,
             nspname AS schema,
             COALESCE(NULLIF(roles.name,''''),''PUBLIC'') AS role,
             '''' AS user
      FROM pg_namespace pn,
           (SELECT pg_group.groname AS name
            FROM pg_group
            UNION ALL
            SELECT '''' AS name) AS roles
      WHERE ('','' ||array_to_string (nspacl,'','')) LIKE ''%,'' ||roles.name|| ''=%''
      AND   nspowner > 1) privs

ORDER BY schema,rights

Answer 7

en consola util psql:

/dn+

te mostrará

     Name      |  Owner   |   Access privileges   |      Description