AWS Redshift ユーザ単位で各テーブルに対する権限の有無を確認してみた

こんにちは、DI部の岩澤です。

AWS Redshiftでユーザーを複数作成することになった場合、 直接またはグループを利用してユーザーごとに権限を設定すると思います。
そうしたとき、「このユーザーの権限設定できた（と思う）けど、想定通りに権限を付与できているかな？」と確認したくなることがあると思います。
（チェックは大事ですからね）
そんなときのためにユーザ単位でテーブルごとの権限設定を簡単に確認できるSQLを書いてみました。

※このSQLを実行するユーザーは参照するスキーマへのUSAGE権限を持っている必要があります。

select
  usename
  , schemaname
  , tablename
  , has_table_privilege(usename, schemaname || '.' || tablename, 'select') as select
  , has_table_privilege(usename, schemaname || '.' || tablename, 'insert') as insert
  , has_table_privilege(usename, schemaname || '.' || tablename, 'update') as update
  , has_table_privilege(usename, schemaname || '.' || tablename, 'delete') as delete
  , has_table_privilege(usename, schemaname || '.' || tablename, 'references') as references
  , has_schema_privilege(usename, schemaname, 'usage') as usage
from
  pg_tables, pg_user
where
  schemaname in ('schema1', 'schema2')  -- 確認したいスキーマで絞り込む
and
  usename in ('blogtest')  -- 確認したいユーザで絞り込む
order by
  1, 2, 3
;

以下が実行結果です。
ユーザごとの各テーブルに対するSELECT/INSERT/UPDATE/DELETE/REFERENCE/USAGE権限の有無が一目でわかると思います。
※t:権限あり、f:権限なし

 usename  | schemaname | tablename | select | insert | update | delete | references | usage
----------+------------+-----------+--------+--------+--------+--------+------------+-------
 blogtest | schema1    | test1     | t      | f      | f      | f      | f          | f
 blogtest | schema1    | test2     | t      | f      | f      | f      | f          | f
 blogtest | schema2    | test3     | f      | f      | f      | f      | f          | f

解説

Redshiftにはhas_table_privilege関数とhas_schema_privilege関数があります。 HAS_TABLE_PRIVILEGE - Amazon Redshift
HAS_SCHEMA_PRIVILEGE - Amazon Redshift

has_table_privilege関数は、ユーザー・テーブル・確認したい権限を渡すと、ユーザーがそのテーブルに対して権限を持っているかを返してくれる関数です(true=権限を持っている)。
has_schema_privilege関数は、上記関数のスキーマ版です。
これらの関数と、DB内のテーブル情報を持つpg_tablesテーブル、ユーザ一情報を持つpg_userテーブルを組み合わせています。

まとめと補足

というわけで、今回はユーザーを主とした各テーブルへの権限確認用SQLについてのご紹介でした。

テーブルを主とした実行権限の確認については以下のエントリを参照してください。
Amazon Redshift Useful SQL: テーブル別実行権限設定の一覧をもう少し分かり易く表示する ｜ DevelopersIO