Amazon Redshift ストアドプロシージャの所有者の権限で実行する『SECURITY DEFINER』の解説

はじめに

以前のブログでストアドプロシージャが有効なケースの一つとして、「オブジェクトへの権限を持たないユーザーが、プロシージャの所有者の権限でデータを操作したり、アクセスしたい場合」を挙げています。『SECURITY DEFINER』を指定するとアクセス権限を持たないユーザーでもストアドプロシージャの所有者の権限で実行できます。本日は『SECURITY DEFINER』の解説と、この指定を用いて、一般ユーザーがDBのユーザー登録（CREATE USER）する例を紹介します。

Amazon Redshift PL/pgSQLのストアドプロシージャ開発入門

SECURITY DEFINER とは

SECURITY DEFINERとは、プロシージャの所有者の権限でプロシージャを実行する設定です。

ストアドプロシージャには、SECURITY属性があります。SECURITY属性は、データベースオブジェクトにアクセスするためのプロシージャの特権を指定できます。ストアドプロシージャを作成するとき、SECURITY属性はDEFINERまたはINVOKERに設定できます。

• SECURITY INVOKERの場合 プロシージャーはそのプロシージャーを呼び出すユーザーの権限を使用します。ユーザーは、基となるデータベースオブジェクトに対する明示的な権限を持っている必要があります。デフォルトはSECURITY INVOKERです。
• SECURITY DEFINERの場合 SECURITY DEFINERを指定した場合、プロシージャーはそのプロシージャーの所有者の権限を使用します。プロシージャを呼び出すユーザーには、そのプロシージャに対する実行権限が必要ですが、基となるオブジェクトに対する権限は必要ありません。

SECURITY DEFINER の動作確認

SECURITY属性DEFINERとINVOKERの動作の違いを確認するため、実行ユーザーを確認するストアドプロシージャを作成しました。ストアドプロシージャのオーナーはDB管理ユーザーroot、一般ユーザーはcm_userです。

デフォルト（SECURITY INVOKER）の動作

ストアドプロシージャのオーナーであるDB管理ユーザーrootで、ストアドプロシージャを作成して実行します。当然ですがCALL sp_current_user();の実行結果は、root です。

なお、最後の行では、一般ユーザである cm_user にてストアドプロシージャを実行できるように、EXECUTE権限を付与しています。EXECUTE権限に付与の際は、ストアドプロシジャ名だけでなくシグネチャ（引数とその型）も指定を忘れないでください。

cmdb=# DROP PROCEDURE sp_current_user();
DROP PROCEDURE

cmdb=# -- 実行ユーザーを確認するストアドプロシージャを作成
cmdb=# CREATE OR REPLACE PROCEDURE sp_current_user()
cmdb-# AS $$
cmdb$# DECLARE
cmdb$# result text;
cmdb$# BEGIN
cmdb$# SELECT INTO result current_user;
cmdb$# RAISE INFO 'CURRENT USER: %', result;
cmdb$# END
cmdb$# $$ LANGUAGE plpgsql
cmdb-# ;
CREATE PROCEDURE

cmdb=# -- ストアドプロシージャのオーナーであるDB管理ユーザーrootで実行
cmdb=# CALL sp_current_user();
INFO: CURRENT USER: root
CALL

-- 一般ユーザー cm_user にストアドプロシージャの実行権限を付与する
GRANT EXECUTE ON PROCEDURE sp_current_user() TO cm_user;

一般ユーザである cm_user にてストアドプロシージャを実行します。デフォルト（SECURITY INVOKER）では、実行ユーザー cm_user がプロシージャを実行していることが確認できます。

cmdb=> -- 一般ユーザー cm_user で実行
cmdb=> CALL sp_current_user();
INFO: CURRENT USER: cm_user
CALL

SECURITY DEFINER の動作

同様に、ストアドプロシージャのオーナーであるDB管理ユーザーrootで、ストアドプロシージャを作成して実行します。先度ほどのストアドプロシジャとの違いは14行目にSECURITY DEFINERを追加している点です。当然ですがCALL sp_current_user();の実行結果は、root です。

cmdb=# DROP PROCEDURE sp_current_user();
DROP PROCEDURE

cmdb=# -- 実行ユーザーを確認するストアドプロシージャを作成
cmdb=# CREATE OR REPLACE PROCEDURE sp_current_user()
cmdb-# AS $$
cmdb$# DECLARE
cmdb$# result text;
cmdb$# BEGIN
cmdb$# SELECT INTO result current_user;
cmdb$# RAISE INFO 'CURRENT USER: %', result;
cmdb$# END
cmdb$# $$ LANGUAGE plpgsql
cmdb-# SECURITY DEFINER
cmdb-# ;
CREATE PROCEDURE

cmdb=# -- ストアドプロシージャのオーナーであるDB管理ユーザーrootで実行
cmdb=# CALL sp_current_user();
INFO: CURRENT USER: root
CALL

cmdb=# -- ユーザー cm_user にストアドプロシージャの実行権限を付与する
cmdb=# GRANT EXECUTE ON PROCEDURE sp_current_user() TO cm_user;
GRANT

一般ユーザである cm_user にてストアドプロシージャを実行します。SECURITY DEFINER の動作は、実行ユーザーではなくストアドプロシージャの所有者である root がプロシージャを実行していることが確認できます。

cmdb=> -- 一般ユーザー cm_user で実行
cmdb=> CALL sp_current_user();
INFO: CURRENT USER: root
CALL

つまり、SECURITY DEFINER の動作は、権限だけがストアドプロシージャの所有者になるのではなく、実行ユーザーそのものが実行時にスイッチしていると考えられます。

一般ユーザーが DBのユーザー登録（CREATE USER）する例

単にユーザーオブジェクトへの権限を持たないユーザーが、所有者の権限でデータをアクセスするのは、ストアドプロシジャを使わずにできるので、今回はストアドプロシジャでなければできないことにトライします。DBのユーザー登録は、本来DB管理ユーザーでなければできませんが、ストアドプロシジャでカプセル化し、動的SQLによるSQL生成、SECURITY DEFINERで実行ユーザーをDB管理ユーザーにスイッチすることで実現します。

DB管理ユーザーでストアドプロシージャの作成、一般ユーザー cm_user に EXECUTEの権限を付与します。

cmdb=# -- ユーザの登録：ユーザー名、パスワード、グループ名を指定する
cmdb=# CREATE PROCEDURE sp_create_user(user_name text, user_pass text, group_name text)
cmdb-# AS $$
cmdb$# DECLARE
cmdb$# query text;
cmdb$# BEGIN
cmdb$# query := 'CREATE USER ' || user_name || ' WITH PASSWORD ''' || user_pass || ''' IN GROUP ' || group_name || ';';
cmdb$# RAISE INFO 'Run Query: %', query;
cmdb$# EXECUTE query;cmdb$# END;
cmdb$# $$ LANGUAGE plpgsql
cmdb-# SECURITY DEFINER
cmdb-# ;
CREATE PROCEDURE

cmdb=# -- ユーザー cm_user にストアドプロシージャの実行権限（EXECUTE）を付与する
cmdb=# GRANT EXECUTE ON PROCEDURE sp_create_user(text, text, text) TO cm_user;
GRANT

ユーザーの登録は、一般ユーザー cm_user で sp_create_user ストアドプロシージャを実行します。pg_userからユーザーが登録できたことが確認できます。

cmdb=> -- ユーザの登録：ユーザー名、パスワード、グループ名を指定する
cmdb=> CALL sp_create_user('cm_biz', 'DataSicentist2019', 'cm_analytics_group');
INFO: Run Query: CREATE USER cm_biz WITH PASSWORD 'DataSicentist2019' IN GROUP cm_analytics_group;
CALL

cmdb=> -- cm_biz ユーザーが登録されていることを確認
cmdb=> SELECT usename, usesysid FROM pg_user WHERE usename = 'cm_biz';
usename | usesysid
---------+----------
cm_biz | 208
(1 row)

エラーのトラップは、例外処理（EXCEPTION）でハンドリングしようと思いましたが、現時点でサポートされている条件はOTHERSのみでした。また、エラーをWHEN OTHERSでチャッチできましたが、かといってエラーメッセージを取得できないので、例外処理（EXCEPTION）せずにそのまま表示しています。

cmdb=> CALL sp_create_user('cm_biz', 'DataSicentist2019', 'cm_analytics_group');
INFO: Run Query: CREATE USER cm_biz WITH PASSWORD 'DataSicentist2019' IN GROUP cm_analytics_group;
ERROR: user "cm_biz" already exists
CONTEXT: SQL statement "CREATE USER cm_biz WITH PASSWORD 'DataSicentist2019' IN GROUP cm_analytics_group;"
PL/pgSQL function "sp_create_user" line 6 at execute statement

最後に

今回はあえてストアドプロシジャでなければできないことにトライしましたが、ストアドプロシージャの所有者の権限で実行する『SECURITY DEFINER』は、横断的に様々な所有者のオブジェクトをアクセスできる権限のユーザーと、実際に利用するユーザーを明確に分離できることが大きなメリットではないかと考えられます。しかし、ストアドプロシージャとSECURITY DEFINERを乱用すると、どのユーザー/グループが、どのオブジェクトにアクセスできるかがブラックボックス化されるので、一般的な参照権限の付与であればオブジェクトの権限付与で管理したほうが望ましいと考えられます。