-- databaseselect
datname
,HAS_DATABASE_PRIVILEGE(datname, 'create') as create
,HAS_DATABASE_PRIVILEGE(datname, 'temporary') as temporary
from pg_database;
結果例
datname | create | temporary
--------------+--------+-----------
dev | f | t
padb_harvest | f | t
template1 | f | f
template0 | f | f
...
スキーマ
-- schema(namespace)select nspname,
has_schema_privilege(nspname, 'create') as create,
has_schema_privilege(nspname, 'usage') as usage
from pg_namespace;
結果例
nspname | create | usage
--------------------+--------+-------
pg_toast | f | f
pg_internal | f | f
pg_temp_1 | f | f
pg_catalog | f | t
information_schema | f | t
public | t | t
...
テーブル
-- tables in current databaseselect tablename,
HAS_TABLE_PRIVILEGE(tablename, 'select') asselect,
HAS_TABLE_PRIVILEGE(tablename, 'insert') as insert,
HAS_TABLE_PRIVILEGE(tablename, 'update') asupdate,
HAS_TABLE_PRIVILEGE(tablename, 'delete') asdelete,
HAS_TABLE_PRIVILEGE(tablename, 'references') asreferences
HAS_TABLE_PRIVILEGE(tablename, 'references') asreferencesfrom pg_tables where schemaname='public'order by tablename;
結果例
tablename | select | insert | update | delete | references
------------+--------+--------+--------+--------+------------
category | t | f | f | f | f
date | t | f | f | f | f
event | t | f | f | f | f
listing | t | f | f | f | f
sales | t | f | f | f | f
users | t | f | f | f | f
venue | t | f | f | f | f
...