Redshiftのデフォルト権限設定でハマったこと

はじめに

DI部のおおたきです。Redshiftではユーザーにアクセス権限を設定することでテーブルへのアクセスを制御することができます。 テーブルへのアクセス制御はテーブル単位で設定する他にスキーマ単位で行うことができます。 テーブル単位でアクセス制限を設定すると管理が大変になるため、スキーマ単位でのアクセス制限をすることが多いです。 スキーマ単位でアクセス制限を行う場合、「xxxxスキーマ配下のテーブル全てに対して読取権限のみ付与する」と言った感じで権限を設定します。その際、現在作成されているテーブルに対してその権限を付与することが出来るのですが、新規で作成したテーブルに対しては権限が設定されません。 そのため、新規でテーブルを作成する度に権限を設定するとう作業が発生してしまい、面倒になってしまいます。それを解決する方法としてデフォルトで適用するアクセス権限のセットを定義することができます。今回、その設定でハマったことがあったので備忘録として記載しておきます。

ユーザーに権限を設定する

それではまず、ユーザーを作成して権限を設定したいと思います。Redshift上には「test」スキーマとその配下に「tbl001」テーブルがあります。このtestスキーマに対して読み取り専用のユーザーを作成します。 ユーザーを作成します。

CREATE USER select_user password 'xxxxxxxxxxx'

testスキーマ配下にアクセス出来る権限を設定します。

GRANT USAGE ON SCHEMA test TO select_user

testスキーマ配下のテーブルに対してSELECT出来る権限を設定します。

GRANT SELECT ON ALL TABLES IN SCHEMA test TO select_user

これでselect_userユーザはtestスキーマ配下のテーブルに対してselectのみ実行出来る状態になりました。 select_userユーザで接続し、tbl001にアクセスしてみます。問題なくアクセスできることが確認できます。

SELECT * FROM test.tbl001;

 id | code 
----+------
  5 | a005
  8 | a008
  1 | a001
  3 | a003
  2 | a002
  9 | a009
 10 | a010
  4 | a004
  6 | a006
  7 | a007

次にtestスキーマに対してtbl002テーブルを作成します。

CREATE TABLE test.tbl002(
  id int,
  code varchar(64)
);

select_userユーザでこのテーブルにアクセスしてみます。

SELECT * FROM test.tbl002;

想定通りアクセス権限エラーが発生します。

ERROR:  permission denied for relation tbl002

デフォルト権限を設定する

GRANT文をもう一度実行すればtbl002もアクセス出来るようになりますが、毎回テーブルを作成する度に設定するのでは手間になるため冒頭で説明したデフォルト権限設定をtestスキーマに対して設定します。

ALTER DEFAULT PRIVILEGES IN SCHEMA test GRANT SELECT ON TABLES TO select_user

ALTER DEFAULT PRIVILEGES文を使うことで、今後testスキーマに対して作成したテーブルに対して、select_userユーザーにはSELECTの権限が設定されるようになります。 tbl003テーブルを作成して再度確認してみます。

CREATE TABLE test.tbl003(
  id int,
  code varchar(64)
);

select_userユーザでこのテーブルにアクセスしてみます。

SELECT * FROM test.tbl003;

問題なくアクセスすることができました。

 id | code 
----+------
(0 rows)

※先ほど作成したtbl002テーブルに対してはALTER DEFAULT PRIVILEGES文を実行する前に作成したテーブルなので、このままではアクセスはできません。アクセスするにはGRANT文を再度実行する必要があります。

ここまでは特に問題なく権限設定ができました。次にCREATE権限を持った別なユーザーでtestスキーマに対してテーブルを作成して、select_userユーザーでアクセスしたいと思います。 今まではrootユーザーでテーブルを作成したりユーザーを作成したりしていました。今度はtest_adminというユーザーで接続し、testスキーマに対してテーブルを作成します。

CREATE TABLE test.tbl004(
  id int,
  code varchar(64)
);

作成したテーブルに対して、select_userユーザーでアクセスしてみます。

SELECT * FROM test.tbl004;

問題なくアクセス出来るかと思いきやアクセス権限エラーが発生します。

ERROR:  permission denied for relation tbl004

今回ハマったポイントはここでした。testスキーマに対して権限設定しているんだから、アクセスできるはずなのに何でかなと悩みました。 そこで改めてドキュメントを読んでみました。 ALTER DEFAULT PRIVILEGES文には「FOR USER」と「IN SCHEMA」の設定があります。今まであまり「FOR USER」については気にしていなかったのですが、以下のように記載があります。

オプション。デフォルト権限が定義されているユーザーの名前。他のユーザーに対しては、スーパーユーザーのみがデフォルト権限を指定できます。デフォルト値は現在のユーザーです。

どうやらデフォルト権限はテーブルを作成するユーザー毎に設定が必要な感じです。今まで指定をしてなかったのでデフォルトであるALTER DEFAULT PRIVILEGES文を実行したrootユーザーが作成したテーブルに対して設定されているようです。そのため、ユーザーを変えてtest_adminで作成したテーブルに対してはデフォルト権限設定が設定されず、アクセス権限エラーが発生したようです。 ではtest_adminを指定して実行してみたいと思います。 rootユーザーで再度接続します。今度はユーザーを指定して実行します。

ALTER DEFAULT PRIVILEGES FOR USER root,test_admin
IN SCHEMA test GRANT SELECT ON TABLES TO select_user

次にtest_adminで接続し、テーブルを作成します。

CREATE TABLE test.tbl005(
  id int,
  code varchar(64)
);

最後にselect_userユーザーで接続し、問題なくテーブルにアクセスできることを確認します。

SELECT * FROM test.tbl005;

 id | code 
----+------
(0 rows)

問題なくアクセスすることができました。

まとめ

いかがでしたでしょうか。デフォルト権限設定はテーブルを作成するユーザーが増えるとその度に設定が必要なことがわかりました。それでもテーブル作成する度に権限を設定するよりは遥かに便利かと思います。残念なのはALTER DEFAULT PRIVILEGESで指定できるのがユーザーのみでグループを指定出来ない点です。GROUPが指定できればより便利な機能になるなと個人的には思いました。 今回は以上です。