row-level security controlとpg_rewindの実装
![](https://thinkit.co.jp/sites/default/files/styles/main_image_730x/public/main_images/Postgre_main_2.jpg?itok=TEATuugk)
今回は、PostgreSQL 9.5(以下、9.5)で実装された以下2つの機能について、実際に動かしながら紹介します。
- row-level security controlの実装
- pg_rewindの実装
row-level security control(RLS)の実装
各ユーザのアクセス権(ユーザがアクセス可能なデータ範囲)を行ごとに設定する「RLS」機能が実装されました。これにより、対象ユーザは特定の行に指定された操作(SELECT、INSERT、UPDATE、またはDELETE)のみを実行できるようになります。
この機能は、アクセス権を適用したいテーブルに対象ユーザ、対象行、対象操作(SELECTやINSERTなど)といったセキュリティポリシーを定義することで実現します。これに伴い、テーブルにセキュリティポリシーを定義するためのCREATE POLICY文やセキュリティポリシーの適用を制御する(有効化/無効化を指定する)ALTER TABLE ~ ENABLE/DISABLE ROW LEVEL SECURITY文なども新しく実装されました。
これまでは、テーブルやカラムを対象としたアクセス権の設定しかなく、RLS機能と同等の機能(行レベルのアクセス権)を実現するためにはアプリケーション等で制御する必要があり、実装が困難でした。9.5からはテーブルにセキュリティポリシィーを定義して、RLS機能を利用するだけで容易に実現できるようになります。
以下に定義されたevent_logテーブルには、全ユーザのイベント(操作)のログが蓄積されています。
一般的にこのようなログへのアクセス権は、管理者であれば全ユーザのログを参照できますが、一般ユーザは自分自身のログのみ参照できるといった設定になることが予想されます。そこで、このようなアクセス権をRLS機能で設定します。
今回の例では、上記のevent_logテーブルに以下の要件が与えられているものとします。
ユーザ名(ロール名) | アクセス権の要件 |
---|---|
master | すべてログにすべての操作が可能 |
user_a | 自分自身(user_a)のログのみ参照可能 |
user_b | 自分自身(user_b)のログのみ参照・更新可能 |
user_c | master以外のログが参照可能で、自分自身(user_c)のログを更新可能 |
※ユーザはあらかじめ作成済みとします。また、event_logテーブルは利用可能なすべての権限がすべてのユーザに与えられている状態とします。
これらの要件を満たすためには、対象テーブルに以下のようなセキュリティポリシーを定義する必要があります。なお、セキュリティポリシーを定義・変更できるのはテーブル所有者のみであることに注意してください。
(1) masterのセキリュティポリシーの定義
要件:「すべてログにすべての操作が可能」
USING句を用いることで対象行を絞り込むことができます。今回はどの行に対しても真(true)となる条件なので、全行が操作対象になります。この定義により、masterユーザによる操作は以下のような結果になります。
(2) user_aのセキリュティポリシーの定義
要件:「自分自身(user_a)のログのみ参照可能」
このUSING句では、user_nameカラムと現在の実行ユーザ(user_a)が等しい行を操作(SELECT)対象とします。この定義により、user_aユーザによる操作は以下のような結果になります。
(3) user_b のセキリュティポリシーの定義
要件:「自分自身(user_b)のログのみ参照・更新可能」
この定義により、user_bユーザによる操作は以下のような結果になります。
(4) user_c のセキリュティポリシーの定義
要件:「master以外のログが参照可能で、自分自身(user_c)のログを更新可能」
「master以外のログが参照可能」という定義はUSING句で行っています。USING句は参照処理(SELECTやUPDATE、DELETEの参照処理も含む)のみに適用される構文、言い換えれば既存行の評価のみを行う構文です。そのため、「自分自身(user_c)のログを更新可能」という更新処理に関する定義はWITH CHECK句で行います。WITH CHECK句は、更新処理(INSERTやUPDATEなど)のみに適用される構文、言い換えれば新規行の評価のみを行う構文です。
もう少し簡単な例で説明します。「user_cは自分自身の行にすべての処理が可能」とする場合、UPDATEで既存の行がuser_cであることを評価する式はUSING句に定義し、新しく追加する行がuser_cであることを評価する式はWITH CHECK句に定義する必要があります。
ここで、 (3)のuser_bの定義(自分自身のログのみ参照・更新可能)を思い出して、「WITH CHECK句が存在しない」と疑問に思った方がいるかもしれません。(3)のuser_bはWITH CHECK句の定義がないため、代わりにUSING句のセキュリティポリシーが新規行の評価に適用されているのです(ただし、これが適用されるのは対象操作がALLとUPDATEの場合のみです。対象操作がINSERTであるにもかかわらずWITH CHECK句が存在しない場合は定義の段階でエラーとなります)。
この定義により、user_cユーザによる操作(SELECT)は以下のような結果になります。
ここで、これまでの例と同様に、全件にUPDATEを実施します。
すると、これまではUSING句で指定した対象行のみが更新されていましたが、この例ではセキュリティポリシーを違反したことによるエラーとなってしまいました。これは、UPDATEの参照処理におけるUSING句で絞り込まれたすべての行(user_a, user_b, user_c)にUPDATEの更新処理を実施しようとし、それらの行にWITH CHECK句の条件に一致しない行(user_a, user_b)が存在したことで発生したエラーです。そのため、このような場合は以下の例のようにWHERE句で更新対象を絞り込む必要があります。
セキュリティポリシーの定義後、最後に以下のクエリを発行することで、対象テーブにおけるRLS機能を有効化できます。
pg_rewindの実装
あるタイムラインのデータディレクトリと、そこから派生した他のタイムラインのデータディレクトリの差分同期を実現する「pg_rewind」機能が実装されました。例えばフェイルバック(代替システムから復旧した元システムへ処理を移行する作業)を行う際、これまでは元システムに代替システムからのフルベースバックアップが必要で、すべてのデータを操作するためフェイルバックに長時間を費やす恐れがありました。
pg_rewindを利用すると、元システムのデータ(あるタイムラインのデータディレクトリ)に代替システムのデータ(派生した他のタイムラインのデータディレクトリ)との差分(元システムが停止している間に更新された内容)を適用するだけで、フェイルバック(同期)が実現できるようになります。つまり、この方法では差分データのみを操作すれば良いことになり、フェイルバックの高速化が期待されます。
pg_rewindの使用方法とデータの振る舞いを確認してみましょう。本記事の例では、マスタサーバ(以降、マスタ)とスタンバイサーバ(以降、スタンバイ)の2台でレプリケーションが構成されているシステムを使用します。なお、ここでレプリケーションの構築手順については省略します。
①wal_log_hintsパラメータの有効化
pg_rewindを利用するには、wal_log_hintsパラメータを有効にする必要があります。デフォルトでは無効になっており、変更するにはPostgreSQLの再起動を伴うため注意が必要です。
②初期データの投入
pg_rewindの振る舞いを確認するためのデータをマスタに投入します。
このとき、マスタとスタンバイは同じデータ内容になっています。
③マスタの異常終了
マスタに以下の値を挿入した後、スタンバイにデータが伝わる前にマスタが異常終了したと仮定します(今回は異常終了の代わりにimmediateモードでPostgreSQLを強制終了させています)。
異常終了直前のデータ内容は、それぞれ以下のようになります。
④フェイルオーバの実行
マスタが異常終了したため、スタンバイをマスタの代替システムとして稼動(フェイルオーバ)させます(今回は以下のコマンドでスタンバイを代替システムとして起動させています)。
このとき、システム内ではタイムラインIDが変更されます。タイムラインIDが変更されないと(同期元と同期先のタイムラインIDが同じ)pg_rewindが使用できないことに注意してください。データ内容は、以下のようにスタンバイではデータ「3」が存在しない状態で稼働します。
⑤代替システムにおけるデータ更新
このまま稼動を続けると、いずれマスタは復旧しますが、以下の例のようにスタンバイには新しいデータが蓄積(または更新や削除)されていくことが予想されます。
⑥フェイルバック
マスタが復旧し、稼働するシステムを代替システムから元システムに戻します。このときにpg_rewindを利用してフェイルバックを実施します。
※pg_rewindを使用する際、ターゲットとなるデータディレクトリ(マスタのデータディレクトリ)は正常にシャットダウンされている必要があります。今回は強制終了させたマスタのデータディレクトリを一度起動し、クラッシュリカバリ処理を完了させた後に再度停止させています。
このとき、pg_rewindは以下の図のように元々マスタに挿入されていたデータ「3」を削除し(巻き戻し)、代替システム稼働時に挿入されたデータ「4」と「5」を追加(同期)します。
⑦マスタの起動
pg_rewind完了後、マスタを再起動することでフェイルバックが完了します。このとき、マスタ側の最終的なデータ内容は以下のようになっています。
今回のサンプルSQLはこちらからダウンロードできます。ぜひ、皆さんもお手元の環境で動作確認を実行してみてください。
今回は、PostgreSQLの利便性を高めるためのrow-level security control(RLS)とpg_rewindについて紹介しました。次回は、PostgreSQLの特徴的な機能でもあるJSONBの機能強化について詳しく解説します。