PostgreSQL: デフォルトの権限を確認するSQL

3251 ワード
PostgreSQL PostgreSQL逆引きTips PostgreSQL テキストリンク

PostgreSQLでデフォルトの権限設定がどうなっているか確認するSQLです。

SELECT rolname   as owner,
       nspname   as schema,
       case defaclobjtype
           when 'r' then 'table'
           when 'S' then 'sequence'
           when 'f' then 'function'
           when 'T' then 'type'
           when 'n' then 'schema'
           else 'other'
           end   as type,
       defaclacl as access_privileges
FROM pg_default_acl acl
         JOIN pg_namespace ON acl.defaclnamespace = pg_namespace.oid
         JOIN pg_authid on acl.defaclrole = pg_authid.oid
order by owner, schema, type
;

実行結果の例:

      rolename=xxxx -- privileges granted to a role
              =xxxx -- privileges granted to PUBLIC

                  r -- SELECT ("read")
                  w -- UPDATE ("write")
                  a -- INSERT ("append")
                  d -- DELETE
                  x -- REFERENCES
                  t -- TRIGGER
                  X -- EXECUTE
                  U -- USAGE
                  C -- CREATE
                  c -- CONNECT
                  T -- TEMPORARY
             arwdxt -- ALL PRIVILEGES (for tables)
                  * -- grant option for preceding privilege

              /yyyy -- role that granted this privilege