goodbyegangsterのブログ

備忘録的な

Redshiftにて、DBユーザーの権限をいい感じに管理する方法

Redshiftにおいて、DBユーザ向けに、あるスキーマ内にあるオブジェクト(tableとかviewとか)に対する権限を付与しようとした場合、今まで以下のSQLを利用していました。

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO GROUP db_user_group;

しかし、このSQLでは このGrant文を実行した後に作成したテーブルには、権限が反映されない という問題があります。(あります、というか、そんな事知らずに運用していました。。。)

この事を考慮して ALTER DEFAULT PRIVILEGES文 を利用すべきとなります。これは、PostgreSQL 9.0から利用できる機能で、Redshiftでも利用できます。公式ドキュメントはこちらです。

ALTER DEFAULT PRIVILEGES - Amazon Redshift

この機能を利用することで、スキーマ内のオブジェクトを作成したタイミングで、指定したDBユーザに任意の権限を与えることができます。以下は、その検証作業内容です。

ユーザ作成

Redshiftに接続して、DBユーザグループとDBユーザを作成します。尚、作業はDBマスターユーザにて実行しています。

db_masters db_editors db_readers という3つのグループを作成しています。

test=# CREATE GROUP db_masters;
CREATE GROUP
test=# CREATE GROUP db_editors;
CREATE GROUP
test=# CREATE GROUP db_readers;
CREATE GROUP

master editor1 editor2 reader1 という4つのユーザを作成しています。

test=# CREATE USER master WITH PASSWORD 'Passw0rd' IN GROUP db_masters;
CREATE USER
test=# CREATE USER editor1 WITH PASSWORD 'Passw0rd' IN GROUP db_editors;
CREATE USER
test=# CREATE USER editor2 WITH PASSWORD 'Passw0rd' IN GROUP db_editors;
CREATE USER
test=# CREATE USER reader1 WITH PASSWORD 'Passw0rd' IN GROUP db_readers;

スキーマ作成

所有者を master として、スキーマを作成しています。

test=# CREATE SCHEMA sample_schema AUTHORIZATION master;
CREATE SCHEMA

権限付与

各権限を付与していきます。

まず、publicスキーマは、全DBユーザが無条件にアクセスできるスキーマとなるため、アクセスできないように権限をRevokeしておきます。このSQL実行後、publicスキーマにアクセスできるのは、redshiftマスターユーザのみとなります。

test=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE
test=# REVOKE USAGE ON SCHEMA public FROM PUBLIC;
REVOKE

スキーマに対する権限を付与します。db_editors はオブジェクトの作成/アクセスできる権限を、 db_readers にはオブジェクトにアクセスできる権限のみを与えます。

test=# GRANT CREATE ON SCHEMA sample_schema TO GROUP db_editors;
GRANT
test=# GRANT USAGE ON SCHEMA sample_schema TO GROUP db_editors;
GRANT
test=# GRANT USAGE ON SCHEMA sample_schema TO GROUP db_readers;
GRANT

そして、ここで実行されるのが今回の本題となる ALTER DEFAULT PRIVILEGES コマンドです。1行目のGrant文を説明すると、 editor1 ユーザが sample_schema 内に作成したオブジェクトは、 GRANT ALL ON TABLES TO GROUP db_editors の権限を与えてくれていることになります。権限内容は、 PG_DEFAULT_ACL テーブルにて確認できます。

test=# ALTER DEFAULT PRIVILEGES FOR USER editor1 IN SCHEMA sample_schema GRANT ALL ON TABLES TO GROUP db_editors;
ALTER DEFAULT PRIVILEGES
test=# ALTER DEFAULT PRIVILEGES FOR USER editor2 IN SCHEMA sample_schema GRANT ALL ON TABLES TO GROUP db_editors;
ALTER DEFAULT PRIVILEGES
test=# ALTER DEFAULT PRIVILEGES FOR USER editor1 IN SCHEMA sample_schema GRANT SELECT ON TABLES TO GROUP db_readers;
ALTER DEFAULT PRIVILEGES
test=#
test=# select * from PG_DEFAULT_ACL;
 defacluser | defaclnamespace | defaclobjtype |                             defaclacl
------------+-----------------+---------------+-------------------------------------------------------------------
        106 |          299676 | r             | {"group db_editors=arwdRxt/editor2"}
        105 |          299676 | r             | {"group db_editors=arwdRxt/editor1","group db_readers=r/editor1"}
(2 rows)

確認

実際に確認してみます。

editor1での操作

editor1 ユーザにて、 sample_editor1 というテーブルを作成して、データをINSERTします。

test=> CREATE TABLE sample_schema.sample
test->   (
test(>     voice_id VARCHAR(3),
test(>     name VARCHAR(20)
test(>   )
test->   BACKUP YES
test->   DISTSTYLE EVEN
test->   SORTKEY(voice_id);
CREATE TABLE
test=> INSERT INTO sample_schema.sample values('001','zunchan');
INSERT 0 1

editor2での操作

editor2 ユーザにて、 editor1 ユーザが作成したテーブルを参照してみます。

test=> SELECT * FROM sample_schema.sample;
 voice_id |  name
----------+---------
 001      | zunchan
(1 row)

できますね。同様に、INSERTとDELETEもできますが、テーブルのDROPはできません。テーブルのdropは、Redshiftマスターユーザか、テーブル所有者でないとできません。

test=> INSERT INTO sample_schema.sample values('002','kiritan');
INSERT 0 1
test=> COMMIT;
COMMIT
test=> DELETE FROM sample_schema.sample WHERE name = 'kiritan';
DELETE 1
test=> DROP TABLE sample_schema.sample;
ERROR:  must be owner of relation sample

最後に sample_editor2 というテーブルを作成しておきます。

test=> CREATE TABLE sample_schema.sample_editor2
test->   (
test(>     voice_id VARCHAR(3),
test(>     name VARCHAR(20)
test(>   )
test->   BACKUP YES
test->   DISTSTYLE EVEN
test->   SORTKEY(voice_id);
CREATE TABLE

reader1での操作

reader1 ユーザにて、editor1 ユーザが作成したテーブルを参照してみます。

test=> SELECT * FROM sample_schema.sample;
 voice_id |  name
----------+---------
 001      | zunchan
 002      | kiritan
(2 rows)

これはDEFAULT PRIVILEGES権限を与えているので、参照できます。では、権限を与えていないデータのINSERTの処理、 editor2 ユーザが作成したテーブルの参照はどうでしょうか。

test=> INSERT INTO sample_schema.sample values('003','yukarisan');
ERROR:  permission denied for relation sample
test=> SELECT * FROM sample_schema.sample_editor2;
ERROR:  permission denied for relation sample_editor2

想像通り、できませんね。これにて確認完了です。