Redshiftでの接続アカウント管理 (read-onlyユーザとグループの作成)

背景

Redshiftをチームで使うときにはユーザアカウントとパスワードを共通で使うのではなく個々人に別々に付与するのがセキュリティ的にも好ましいです。
ここでは、どのようにユーザをRedshiftで管理するかの一例を紹介します。

はじめに

Redshiftは接続で使うコマンドなど、PostgreSQLがベースなのでMySQLの管理とは異なっています。
PostgresSQLはpsqlコマンドで接続する際にデータベースを指定します。接続後は、そのデータベースにつなぎにいっていることになり、他のデータベースへのクエリは投げられません。

MySQLはmysqlコマンドでサーバに接続後に、データベースをまたいでのselectもできるので、そこはMySQLとは異なった点です。

また、PostgresSQLではデータベースの下にスキーマという概念が存在しており、そのスキーマの下にテーブルを作るイメージです。

上図はこちらの記事から引用しました。

Redshiftはデフォルトでpublicのスキーマが定義されております。

権限について

Redshiftでは以下の権限があります。各権限の詳細はここに書いてあります。

• SELECT: SELECT ステートメントを使用して、テーブルまたはビューからデータを選択する権限
• INSERT: INSERT ステートメントまたは COPY ステートメントを使用して、データをテーブルにロードする権限
• UPDATE: UPDATE ステートメントを使用して、テーブルの列を更新する権限
• DELETE: テーブルからデータ行を削除する権限
• REFERENCES: 外部キーの制約を作成する権限
• CREATE: データベースの場合は、データベース内にスキーマを作成する権限、スキーマの場合はスキーマ内にオブジェクトを作成することをユーザーに許可する
• TEMPORARY: 指定したデータベースに一時テーブルを作成する権限
• USAGE: 特定のスキーマに対して USAGE 権限を付与します。これによって、そのスキーマ内のオブジェクトにユーザーがアクセスできるようになる

まとめると、テーブルをSELECT/INSERT/UPDATE/DELETEする権限はテーブルに紐付き、テーブルを作成する権限はスキーマに紐付きます。
スキーマを作成する権限はデータベースの権限に紐付きます。

各権限を確認するには以下のようにします（testデータベースに接続)。

データベースの権限の確認

select
    datname,
    HAS_DATABASE_PRIVILEGE(datname, 'create') as create,
    HAS_DATABASE_PRIVILEGE(datname, 'temporary') as temporary
from 
    pg_database
WHERE
    datname = 'test';

-- 結果は以下
 datname | create | temporary 
---------+--------+-----------
 test    | t      | t

スキーマの権限の確認

select 
    nspname,
    has_schema_privilege(nspname, 'create') as create,
    has_schema_privilege(nspname, 'usage') as usage
from 
    pg_namespace
WHERE
    nspname = 'public';

-- 結果は以下
 nspname | create | usage 
---------+--------+-------
 public  | t      | t

read-onlyユーザの作成

publicのスキーマの特定のテーブル(test_1)に対して、SELECTとUSAGEのみできるユーザを作成します。

まず、rootアカウントで、test_1というテーブルを作り、データを1行いれておきます。

CREATE TABLE "test_1" (
  "user_id" varchar(20) NOT NULL DEFAULT ''
);
INSERT INTO "test_1" VALUES ('user_1');

その後、同じくrootアカウントで、ユーザを作成します。(テストなのでパスワードは適当です)

-- ユーザの作成と、ユーザへのスキーマの追加
CREATE USER user_ro PASSWORD 'Abcd1234';
GRANT USAGE ON SCHEMA public TO user_ro;

-- SELECT権限をユーザに付与する
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user_ro;

ユーザの権限を確認してみましょう。以下のように正しく付与されています。

select 
    tablename,
    has_table_privilege('user_ro', 'test_1', 'select') as select,
    has_table_privilege('user_ro', 'test_1', 'insert') as insert,
    has_table_privilege('user_ro', 'test_1', 'update') as update,
    has_table_privilege('user_ro', 'test_1', 'delete') as delete,
    has_table_privilege('user_ro', 'test_1', 'references') as references
from pg_tables where schemaname='public' order by tablename;

-- 結果は以下
 tablename | select | insert | update | delete | references 
-----------+--------+--------+--------+--------+------------
 test_1    | t      | f      | f      | f      | f
(1 行)

ただし、この状態だと、user_roはpublicスキーマ以下にテーブルが作れてしまいます。デフォルトではすべてのユーザはPUBLICスキーマに対して、CREATE 権限と USAGE 権限を所有しているためです。
参考

よってまず public スキーマからCREATEの権限を削除し、その後に各ユーザにCREATE権限を付与する必要があります。
参考

-- publicスキーマのすべてのユーザ(PUBLIC)からCREATE権限を削除
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 特定のユーザのみCREATE権限を付与
GRANT CREATE ON SCHEMA public TO {user-name};

まとめると、以下のようにコマンドを実行すると作成できます。
もちろん２人目以降のユーザからは、CREATE USERから実行すれば大丈夫です。

-- CREATE権限をpublicから削除
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- CREATE権限が必要なユーザがいるなら各ユーザに以下を実行
GRANT CREATE ON SCHEMA public TO {user-name};

-- ユーザの作成と、ユーザへのスキーマの追加
CREATE USER user_ro PASSWORD 'Abcd1234';
GRANT USAGE ON SCHEMA public TO user_ro;

-- SELECT権限をユーザに付与する
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user_ro;

じつはこのままだと、GRANT SELECT後に新規で作成されたテーブルにはSELECTができません。
解決するには、都度GRANT SELECTを新規テーブルを作成された後にしてやるかグループを使うのがいいと思います。

ユーザの削除

いらなくなったユーザの削除は以下で出来ます。

DROP user user_ro;
ERROR:  user "user_ro" cannot be dropped because the user has a privilege on some object

と、思ったらエラーが出ました。各種付与した権限はとってからでないと削除することは出来ません。
以下のようにすると削除できます。

revoke select on all tables in schema public from user_ro;
DROP user user_ro2;

グループの作成と権限

多くのユーザがRedshiftを使う状態になってくると、ユーザがグループに紐付けて管理したくなります。
ここではグループの作成と権限の付与の仕方を紹介します。
rootでログインし、以下を実行します。

CREATE GROUP public_ro;

-- ユーザの作成
CREATE USER user_ro_2 PASSWORD 'Abcd1234';

-- ユーザをグループに紐付ける
ALTER GROUP public_ro ADD USER user_ro_2;

追加されたか確認してみましょう。

SELECT * FROM pg_group;

-- 結果は以下
  groname  | grosysid | grolist 
-----------+----------+---------
 public_ro |      101 | {105}

IDは数字ですが、所属するユーザ一覧からユーザ名が確認できます。

SELECT * FROM pg_user WHERE usesysid = 105;

-- 結果は以下
  usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig 
-----------+----------+-------------+----------+-----------+----------+----------+-----------
 user_ro_2 |      105 | f           | f        | f         | ******** |          | 

この状態でuser_ro_2でログインし、selectをしてみると、期待どおりできない事がわかります。

再度、rootユーザでグループにSELECT権限を付与してみます。

GRANT SELECT ON ALL TABLES IN SCHEMA public TO GROUP public_ro;

user_ro_2でログインし、selectできることを確かめます。

グループからユーザの削除

ユーザがプロジェクトを離れた場合などはグループから削除する必要があるので、以下のようにします。

ALTER GROUP public_ro DROP USER user_ro_2;

グループの削除

DROP GROUP public_ro;