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

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;