'포스트그레스 권한'에 해당되는 글 1건

PostgreSQL Object 접근 권한 조회 스크립트


SELECT n.nspname as "Schema",

  c.relname as "Name",

  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'foreign table' END as "Type",

  pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",

  pg_catalog.array_to_string(ARRAY(

    SELECT attname || E':\n  ' || pg_catalog.array_to_string(attacl, E'\n  ')

    FROM pg_catalog.pg_attribute a

    WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL

  ), E'\n') AS "Column privileges",

  pg_catalog.array_to_string(ARRAY(

    SELECT polname

    || CASE WHEN polcmd != '*' THEN

           E' (' || polcmd || E'):'

       ELSE E':' 

       END

    || CASE WHEN polqual IS NOT NULL THEN

           E'\n  (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)

       ELSE E''

       END

    || CASE WHEN polwithcheck IS NOT NULL THEN

           E'\n  (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)

       ELSE E''

       END    || CASE WHEN polroles <> '{0}' THEN

           E'\n  to: ' || pg_catalog.array_to_string(

               ARRAY(

                   SELECT rolname

                   FROM pg_catalog.pg_roles

                   WHERE oid = ANY (polroles)

                   ORDER BY 1

               ), E', ')

       ELSE E''

       END

    FROM pg_catalog.pg_policy pol

    WHERE polrelid = c.oid), E'\n')

    AS "Policies"

FROM pg_catalog.pg_class c

     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')

  AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)

ORDER BY 1, 2;



블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,