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

#Amazon Redshift

#AWS

岩澤暁海

2018.11.27

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、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テーブルを組み合わせています。