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
想像通り、できませんね。これにて確認完了です。
