CREATE POLICY — テーブルに新しい行単位のセキュリティポリシーを定義する
CREATE POLICYname
ONtable_name
[ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ] [ TO {role_name
| PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ] [ USING (using_expression
) ] [ WITH CHECK (check_expression
) ]
CREATE POLICY
はテーブルに新しい行単位のセキュリティポリシーを定義します。
作成したポリシーを適用するには、(ALTER TABLE ... ENABLE ROW LEVEL SECURITY
を使って)テーブルの行単位セキュリティを有効にしなければならないことに注意して下さい。
ポリシーは、それを定義する式にマッチした行をselect/insert/update/deleteする権限を与えます。
テーブルの既存の行はUSING
で指定した式によって検査されます。
INSERT
またはUPDATE
によって作成される新しい行はWITH CHECK
で指定した式によって検査されます。
ある行についてUSING
式がtrueを返した場合、その行はユーザに可視となりますが、falseまたはnullを返した場合は不可視となります。
行に対してWITH CHECK
式がtrueを返した場合、その行は挿入または更新されますが、falseまたはnullを返した場合はエラーが発生します。
INSERT
文およびUPDATE
文では、BEFORE
トリガーが実行された後で、かつ、実際のデータ更新が行われるより前にWITH CHECK
式が実行されます。
従って、BEFORE ROW
トリガーは挿入されるデータを変更する場合があり、これはセキュリティポリシーの検査の結果に影響を与えます。
WITH CHECK
式は他のいかなる制約よりも前に実行されます。
ポリシー名はテーブル毎につけられます。 従って、1つのポリシー名を多くの異なるテーブルに使うことができます。 また、その定義は各テーブル毎に異なった、適切な内容にできます。
ポリシーは特定のコマンドまたは特定のロールに対して適用することができます。
新しく作成するポリシーのデフォルトは、特に指定しなければ、すべてのコマンドとロールに適用、となっています。
ある文に複数のポリシーを適用する場合、それらはORを使って結合されます
(ただし、ON CONFLICT DO UPDATE
およびINSERT
のポリシーはこのようには結合されず、ON CONFLICT
の実行の各ステージに記された通りに実行されます)。
USING
とWITH CHECK
の両方のポリシーを持ち得るコマンド(ALL
とUPDATE
)について、WITH CHECK
ポリシーが定義されていない場合、どの行が可視か(通常のUSING
と同じ)とどの行が追加可能か(WITH CHECK
に相当)の両方でUSING
ポリシーが使用されます。
テーブルの行単位セキュリティが有効で、適用可能なポリシーが存在しない場合、「デフォルト拒否」のポリシーが適用され、すべての行が不可視で更新不能になります。
name
作成するポリシーの名前です。 同じテーブルの他のポリシーとは異なる名前でなければなりません。
table_name
ポリシーが適用されるテーブルの名前(スキーマ修飾可)です。
command
ポリシーが適用されるコマンドです。
有効なオプションはALL
、SELECT
、INSERT
、UPDATE
、DELETE
です。
デフォルトはALL
です。
これらがどのように適用されるかの詳細は以下を参照して下さい。
role_name
ポリシーが適用されるロールです。
デフォルトはPUBLIC
で、すべてのロールに対してポリシーが適用されます。
using_expression
任意のSQL条件式(戻り値はboolean
)です。
条件式に集約関数やウィンドウ関数を含めることはできません。
行単位セキュリティが有効なときは、テーブルへの問い合わせにこの式が追加されます。
この式がtrueを返す行が可視となります。
この式がfalseまたはnullを返す行は、ユーザには(SELECT
において)不可視となり、また(UPDATE
あるいはDELETE
では)更新の対象ではなくなります。
そのような行は静かに無視され、エラーは報告されません。
check_expression
任意のSQL条件式(戻り値はboolean
)です。
条件式に集約関数やウィンドウ関数を含めることはできません。
この式は、そのテーブルに対するINSERT
およびUPDATE
の問い合わせで使用され、この式の評価がtrueになる行のみが許されます。
挿入されるレコード、あるいは更新の結果のレコードでこの式の評価がfalseまたはnullになるものについては、エラーが発生します。
check_expression
は元の内容ではなく、予定される更新の後の新しい内容に対して評価されることに注意してください。
ALL
ポリシーにALL
を使うのは、そのポリシーはコマンドの種類に関係なく、すべてのコマンドに適用されるという意味になります。
ALL
のポリシーと特定のコマンドに対するポリシーの両方が存在する場合、重なっているポリシーの常として、それらがすべてORで結合されます。
さらにALL
のポリシーは、問い合わせの選択側と更新側の両方で適用されます。
このとき、USING
式だけが定義されていたら、両方の場合についてUSING
式を使用します。
例えばUPDATE
が実行されるとき、ALL
のポリシーは、UPDATE
が更新対象の行として選択できる行(USING
式が適用されます)と、UPDATE
文の結果としてできる行がテーブルに追加できるかどうかの検証(WITH CHECK
が定義されていれば、それが適用され、なければUSING
式が適用されます)の両方で適用可能です。
INSERT
またはUPDATE
コマンドがALL
のWITH CHECK
式に反する行をテーブルに追加しようとした場合、コマンド全体が中止されます。
SELECT
ポリシーにSELECT
を使うのは、そのポリシーはSELECT
の問い合わせの他に、そのポリシーが定義されているリレーションに対してSELECT
権限が必要な時は常に適用されるという意味になります。
その結果、SELECT
問い合わせでは、SELECT
ポリシーに適うレコードだけが返されます。
また、UPDATE
などSELECT
権限が必要な問い合わせでも、SELECT
ポリシーによって許可されるレコードだけが見えます。
SELECT
ポリシーはリレーションからレコードを取り出す場合にしか適用されないので、WITH CHECK
式を持つことはできません。
INSERT
ポリシーにINSERT
を使うのは、そのポリシーはINSERT
コマンドに適用されるという意味になります。
このポリシーに反する行を挿入しようとすると、ポリシー違反エラーを起こし、INSERT
コマンド全体が中止されます。
INSERT
ポリシーはリレーションにレコードを追加する場合にしか適用されないため、USING
式を持つことはできません。
ON CONFLICT DO UPDATE
のあるINSERT
では、INSERT
ポリシーのWITH CHECK
式について、INSERT
の部分でリレーションに追加されるすべての行についてのみ確認することに注意してください。
UPDATE
ポリシーにUPDATE
を使うのは、そのポリシーはUPDATE
コマンド(あるいは補助的にINSERT
コマンドのON CONFLICT DO UPDATE
句)で適用されるという意味になります。
UPDATE
は既存のレコードを取り出すことと、その後でレコードの一部(全部ではないかもしれない)を変更することが含まれるので、UPDATE
ポリシーはUSING
式とWITH CHECK
式の両方を受け付けます。
USING
式はUPDATE
コマンドが操作の対象としてどのレコードを見ることができるかを決めるのに使われます。
一方でWITH CHECK
はどの修正した行をリレーションに戻すことができるかを定義します。
WHERE
句またはRETURNING
句のあるUPDATE
コマンドでは、更新されるリレーションのSELECT
権限も必要です。
また、適切なSELECT
ポリシーとALL
が結合され(SELECT
関連のポリシーが複数ある場合はORで結合されます)、UPDATE
ポリシーのUSING
句とANDで結合されます。
従って、ある行をUPDATE
するには、それらの行に対してSELECT
またはALL
のポリシーによるアクセス権があり、かつ、それらの行はUPDATE
ポリシーのUSING
式の条件に適っていなければなりません。
更新後の値がWITH CHECK
式に反する行があればエラーを起こし、コマンド全体が中止されます。
USING
句だけが指定されていた場合は、それがUSING
とWITH CHECK
の両方に対して使用されます。
ただし、ON CONFLICT DO UPDATE
のあるINSERT
では、UPDATE
ポリシーのUSING
式はいつでもWITH CHECK
として使用されることに注意して下さい。
このUPDATE
のポリシーは、UPDATE
の部分が実行される時は必ず満たされなければなりません。
UPDATE
部分の実行を必要とする既存の行はすべて、(UPDATE
またはALL
がORで結合された)USING
の制約を満たさねばならず、それがこの文の実行において常にWITH CHECK
オプションとして適用されます。
(UPDATE
部分が何も起こさずに回避されることは決してなく、その場合はエラーが発生します。)
なお、リレーションに最後に追加される行は、通常のUPDATE
が満たさなければならないすべてのWITH CHECK
オプションを満たさなければなりません。
DELETE
ポリシーにDELETE
を使うのは、そのポリシーはDELETE
コマンドに適用されるという意味になります。
ポリシーを満たす行だけがDELETE
コマンドから見えます。
SELECT
では見えるけれど、削除の対象ではない、という行もあり得ます。
それらの行がDELETE
ポリシーのUSING
式を満たさない場合です。
DELETE
コマンドがWHERE
句またはRETURNING
句を含む場合、更新対象のリレーションのSELECT
権限も必要です。
また、適切なSELECT
ポリシーとALL
ポリシーが結合され(SELECT
関連のポリシーが複数ある場合はORで結合されます)、DELETE
ポリシーのUSING
句とANDで結合されます。
従って、ある行をDELETE
するには、それらの行に対してSELECT
またはALL
のポリシーによるアクセス権があり、かつ、DELETE
ポリシーのUSING
式の条件に適っていなければなりません。
DELETE
ポリシーはリレーションからレコードが削除される場合にしか適用されず、確認すべき新しい行はないので、WITH CHECK
式を持つことはできません。
ポリシーを作成あるいは変更するには、テーブルの所有者でなければなりません。
ポリシーは、データベース内のテーブルに対する明示的な問い合わせには適用されますが、システムが内部的な参照整合性のチェックや制約の検証をしている時には適用されません。 この意味するところは、ある値が存在するかどうかを判定する間接的な方法がある、ということです。 その例の1つは、主キーあるいは一意制約のある列に重複する値を挿入しようとすることです。 挿入に失敗すれば、その値が既に存在すると推定することができます。 (この例では、ユーザが見ることができないレコードを挿入することがポリシーにより許されていると仮定しています。) 別の例は、あるテーブルへの挿入は許されているが、そのテーブルが別の隠されているテーブルを参照している、という場合です。 参照元のテーブルに値を挿入することで、値の存在が判断できます。 この場合、挿入の成功はその値が参照先のテーブルに存在することを示唆します。 これらの問題は、見ることができない値を示唆するかもしれないようなレコードの挿入、削除、更新が全くできないように注意深くポリシーを設計するか、あるいは外部的な意味を持つキーの代わりに生成された値(例:代理キー)を使うことで解決できます。
一般に、システムは問い合わせに記述される制限より前に、セキュリティポリシーを使ったフィルター条件を実行します。
これは守られるべきデータが信頼できないかもしれないユーザ定義関数に偶然に意図せずに渡されることを防ぐためです。
しかし、システム(またはシステム管理者)によってLEAKPROOF
であるとされた関数や演算子については、信頼できるとみなして良いので、ポリシー式より先に評価される場合があります。
ポリシーの式はユーザの問い合わせに直接追加されるため、式は問い合わせ全体を実行しているユーザの権限によって実行されます。 そのため、あるポリシーを使用するユーザは、その式が参照しているすべてのテーブルおよび関数にアクセスできる必要があります。 そうでなければ、行単位セキュリティが有効になっているテーブルに問い合わせをしようとしたときに、単に権限なしのエラーを受け取ります。 しかし、これによってビューの動作が変わることはありません。 通常の問い合わせおよびビューと同じく、ビューによって参照されるテーブルに対する権限の確認とポリシーは、ビューの所有者の権限およびビューの所有者に適用されるポリシーを利用します。
更なる詳細と実践的な例については5.7. 行セキュリティポリシーに記述されています。
CREATE POLICY
はPostgreSQLの拡張です。