【redshift】テーブル権限確認と、viewとテーブルの依存関係確認

概要

redsihftにて以下を実現するためのクエリを書いてみます。

• redshiftでテーブルの権限の確認
• viewとテーブルの依存関係を確認

権限確認

任意のスキーマ配下に存在するテーブルの権限情報を出力します。
誰が作成したテーブルでどんな権限が付与されているかを確認したいときに利用します。

SELECT
  use.usename as subject,   /** 任意のスキーマを指定 */
  nsp.nspname as namespace, /** スキーマ名 */
  c.relname as item,        /** テーブル名 */
  c.relkind as type,        /** テーブルタイプ（view、realtable） */
  use2.usename as owner,    /** 作成者 */
  c.relacl                  /** 権限一覧 */
FROM
  pg_user use
  cross join pg_class c
  left join pg_namespace nsp
  　　　　on c.relnamespace = nsp.oid
  left join pg_user use2
  　　　　on c.relowner = use2.usesysid
WHERE
  c.relowner = use.usesysid
  and nsp.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
  and nsp.nspname in('public','staging','work') /** 任意のスキーマを指定 */
  and type in('r','v')
ORDER BY
  subject,namespace,item;

依存関係確認

任意のテーブルと依存関係にあるviewを確認します。
テーブルを削除したいのに、
viewによってそのテーブルが参照されているために削除できない場合などに
参照先としてどんなviewが存在するかを確認するために利用します。

select distinct　
  base.oid as tbloid,　　　　　　　　　　　　　　　　　　　　　　　　　　　　　    　/** table id */
  name.nspname as schemaname,　　　　　　　　　　　　　    　/** スキーマ名 */
  base.relname as tblname,　　　　　　　　　　　　　　　　　　　　　　　　 　　/** テーブル名 */
  ref_name.nspname as refbyschemaname,　　　　/** 参照先スキーマ名 */
  ref_base.relname as refbyname,　　　　　　　　　　　　　　　　/** 参照先view名 */
  ref_base.oid as viewoid　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　/** view id */
from
  pg_catalog.pg_class base
    join pg_catalog.pg_depend depend
    　　　　on base.relfilenode = depend.refobjid
    join pg_catalog.pg_depend ref_depend
    　　　　on depend.objid = ref_depend.objid
    join pg_catalog.pg_class ref_base
    　　　　on ref_depend.refobjid = ref_base.relfilenode
    left outer join pg_namespace name
    　　　　on base.relnamespace = name.oid
    left outer join pg_namespace ref_name
    　　　　on ref_base.relnamespace = ref_name.oid
where
  ref_depend.deptype = 'i'::"char"
  and ref_base.relkind = 'v'::"char"
  and tblname = '★★テーブル名★★';