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