Amazon Redshift 全てユーザが持つデフォルトのpublicスキーマに対する権限を剥奪する

ユーザを作成すると public スキーマに対する USAGE と CREATE 権限が付与されます。すぐに public スキーマを使って、テーブル、ビュー、ユーザー定義関数（UDF）を作成したり、データを共有するには便利ですが、参照目的のユーザーにも public スキーマに対する権限が付与されてしまう点では問題になります。この問題を回避しようとした時、デフォルトで付与された public スキーマに対する権限の剥奪するにはどうしたらよいかという疑問が湧いてきます。

今回は、Redshift　の権限管理のしくみと、全てユーザが持つデフォルトの　public　スキーマに対する権限を剥奪する方法について解説します。

Redshift の権限管理

Amazon Redshift は、PostgreSQL 8.0.5 をベースに開発されたという経緯があり、ユーザーや権限の管理は、PostgreSQL 8.0.5の仕様をほぼ踏襲します。よって、各種権限は「ユーザー」と「グループ」に対して付与します。

Redshift の権限管理では、「ユーザー」と「グループ」に各種権限を設定できます。具体的にはデータベースや他のユーザーの作成権限、及びデータベースオブジェクト（スキーマ、テーブル、オブジェクト、ユーザー定義関数（UDF）等）へのアクセス権限や作成権限です。グループは複数のユーザーをグループ化し、アクセス権を一括管理します。

下記の例では、データベース管理者（root）と他に３人のユーザーがいます。そして、それぞれのユーザーに対して、属しているグループの権限が付与されます。

• foo さんは sales グループの権限が付与されます
• bar さんは sales グループと it グループの権限が付与されます
• baz さんは it グループの権限が付与されます

権限管理は基本的に、目的ごとにグループを作成して、グループに対して各種権限を付与します。ユーザーは１つ以上のグループに追加することで、権限を付与します。このように運用しないとユーザーが他のグループに移動したり、グループの権限の追加・削除が煩雑になり、最終的には権限管理の運用が破綻してしまうでしょう。その上で、グループで管理しきれない場合のみ、例外的にユーザーに直接権限を付与します。

テストユーザーの作成

最初に cmtest というユーザーを作成します。public スキーマに対する USAGE と CREATE 権限が付与されます。

cmdb=> CREATE USER cmuser WITH PASSWORD 'zP5b9m4r';
CREATE TABLE

cmdb=> select
cmdb=> nspname,
cmdb=> has_schema_privilege(nspname, 'create') as create,
cmdb=> has_schema_privilege(nspname, 'usage') as usage
cmdb=> from pg_namespace
cmdb=> WHERE nspname = 'public';
nspname | create | usage
------------+--------+-------
public | t | t

public スキーマに対するユーザ権限の剥奪ができない

cmuser から public スキーマに対する USAGE と CREATE 権限を剥奪します。

cmdb=> REVOKE CREATE ON SCHEMA public FROM cmuser CASCADE;
REVOKE
cmdb=> REVOKE USAGE ON SCHEMA public FROM cmuser CASCADE;
REVOKE

cmdb=> select
cmdb=> nspname,
cmdb=> has_schema_privilege(nspname, 'create') as create,
cmdb=> has_schema_privilege(nspname, 'usage') as usage
cmdb=> from pg_namespace
cmdb=> WHERE nspname = 'public';
nspname | create | usage
------------+--------+-------
public | t | t

REVOKE は成功しますが、USAGE と　CREATE 権限は付与されたままです。つまり、ユーザ権限として付与されていないということです。 残るはグループの権限ですが、このユーザーはどこのグループにも属していません。

全てユーザが属する PUBLIC グループ

PostgreSQL のマニュアルには以下の記載があります。 以下、PostgreSQL 8.0.4 文書 - 5.8. スキーマの「5.8.4. スキーマおよび権限」の抜粋

デフォルトでは、public スキーマに関しては全てのユーザがCREATEとUSAGE権限を持っていることに注意してください。 つまり、全てのユーザは、そのユーザが接続できる任意のデータベース上のpublicスキーマにオブジェクトを作成できるということです。 これが好ましくない場合は、以下のように権限を取り消すことができます。

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

(最初の"public"はスキーマです。2番目の"public"は"全てのユーザ"を意味します。 最初のpublicは識別子で、2番目のpublicはキーワードなので、それぞれ小文字、大文字を使用しています。

さらに、「5.8.6. 使用パターン」には以下の記載があります。

この設定を使用する場合は、publicスキーマへのアクセス権を取り消して(またはスキーマを削除して)、ユーザが完全に自分のスキーマにしかアクセスできないようにすることもできます。

これや！

つまり、Redshift にユーザーを作成すると、すべてのユーザーが暗黙的に PUBLIC グループに追加されます。 PUBLIC グループはデフォルトで public スキーマの CREATE 及び USAGE を権限を付与されています。 その為、新規に追加したユーザーは自動的に public スキーマの CREATE 及び USAGE を権限を持っています。 現在は、ユーザーから public スキーマの CREATE 及び USAGE を権限を削除しても、PUBLIC グループが public スキーマのCREATE及びUSAGEを権限を持っているので、参照できるというわけです。

改めて、探すとUDFのマニュアルの中にも、「PUBLIC ユーザーグループ」という表現がありました。

publicスキーマに対する権限を剥奪する

ユーザが持つデフォルトの public スキーマに対する権限を削除するには、以下のコマンドを実行して、全てのユーザー public スキーマの CREATE 及び USAGE を権限を削除する必要があります。

cmdb=> REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE
cmdb=> REVOKE USAGE ON SCHEMA public FROM PUBLIC;
REVOKE

cmdb=> select
cmdb=> nspname,
cmdb=> has_schema_privilege(nspname, 'create') as create,
cmdb=> has_schema_privilege(nspname, 'usage') as usage
cmdb=> from pg_namespace
cmdb=> WHERE nspname = 'public';
nspname | create | usage
---------+--------+-------
public | f | f
(1 row)

権限を剥奪できました。もし、一部のユーザーは従来通り、public スキーマの CREATE 及び USAGE を権限を付与したい場合は、PUBLIC グループではなく、そのユーザーもしくは属しているグループに対して public スキーマの CREATE 及び USAGE を権限を付与することで解決できます。

今更 PUBLIC グループの権限変更は避けたい方

PUBLICグループの権限変更は、全てのデータベースユーザーに対して影響があります。本番の運用が始まり、万が一がトラブル許されない状況や、ユーザー権限・グループ権限が混沌として手が出せないという状況の場合もあるでしょう。

Redshift には、search_path という、オブジェクト (テーブル、ビュー、ユーザ定義関数（UDF）等) がスキーマ指定なしで参照されたときにスキーマを検索する順序を指定することができます。search_path から public スキーマを外してしまえば、テーブル一覧表示（\d）ができなくなり、また、テーブル名だけではクエリーできなくなります。

デフォルトでは、'$user', public と設定されています。

cmdb=> show search_path;
search_path
-----------------
"$user", public

'$user' のように、public を削除することで、public が検索されなくなります。 まずは、いま接続している search_path を以下のように変更して動作を確認してください。

cmdb=> set search_path to "$user";
SET
cmdb=> show search_path;
search_path
-------------
"$user"
(1 row)

動作確認の結果、問題ないようであれば公休的に クラスタの search_path を変更する場合は、クラスタパラメターグループやユーザーの search_path に恒久的に設定します。

最後に

public グループについてはグループの一覧(pg_group)に表示されないため、最初は戸惑いましたが上記の検証結果から仮説のとおりだと考えております。public スキーマの設定は、運用後にトラブらないためにも初期段階で設定することをお勧めします。