CREATE POLICY

PostgreSQL 11.5文書
		SQLコマンド
前へ	上へ	CREATE POLICY	次へ

CREATE POLICY — テーブルに新しい行単位のセキュリティポリシーを定義する

概要

CREATE POLICY name ON table_name
    [ AS { PERMISSIVE | RESTRICTIVE } ]
    [ 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つのポリシー名を多くの異なるテーブルに使うことができます。また、その定義は各テーブル毎に異なった、適切な内容にできます。

ポリシーは特定のコマンドまたは特定のロールに対して適用することができます。新しく作成するポリシーのデフォルトは、特に指定しなければ、すべてのコマンドとロールに適用、となっています。複数のポリシーを単一のコマンドに適用できます。更なる詳細は以下を参照ください。表 240に、どのようにして、特定のコマンドに異なるタイプのポリシーが適用されるかがまとめられています。

USING式とWITH CHECK式の両方を持つことができるポリシー（ALLとUPDATE）についてWITH CHECK式が定義されていない場合、どの行が可視であるかの決定（通常のUSINGの対象）と新しい行のどれが追加可能であるかの決定（WITH CHECKの対象）の両方でUSING式が使用されます。

テーブルの行単位セキュリティが有効で、適用可能なポリシーが存在しない場合、「デフォルト拒否」のポリシーが適用され、すべての行が不可視で更新不能になります。

パラメータ

name

作成するポリシーの名前です。同じテーブルの他のポリシーとは異なる名前でなければなりません。

table_name

ポリシーが適用されるテーブルの名前（スキーマ修飾可）です。

PERMISSIVE

作成するポリシーが許容(permissive)ポリシーであることを指定します。問い合わせに適用可能なすべての許容ポリシーは論理演算子「OR」を使って結合されます。許容ポリシーを作成することで、管理者はアクセス可能なレコード集合を追加することができます。デフォルトではポリシーは許容ポリシーです。

RESTRICTIVE

作成するポリシーが制限(restrictive)ポリシーであることを指定します。問い合わせに適用可能なすべての制限ポリシーは論理演算子「AND」と使って結合されます。各行についてすべての制限ポリシーを満たさなければならなくなるため、制限ポリシーを作成することで、管理者はアクセス可能なレコード集合を減らすことができます。

制限ポリシーを有効に使ってアクセスを制限できるようにする前に、レコードへのアクセスを許可する許容ポリシーが少なくとも1つ必要であることに注意してください。制限ポリシーだけしか存在しない場合、レコードにアクセスすることはできません。許容ポリシーと制限ポリシーが混在している場合、少なくとも1つの許容ポリシーを満たし、さらに、すべての制限ポリシーを満たしている場合のみレコードにアクセスできます。

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のポリシーと特定のコマンドに対するポリシーの両方が存在する場合、ALLのポリシーと特定のコマンドに対するポリシーの両方が適用されます。さらに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コマンド、SELECT FOR UPDATEコマンド、SELECT FOR SHAREコマンド、および補助的にINSERTコマンドのON CONFLICT DO UPDATE句で適用されるという意味になります。 UPDATEは既存のレコードを取り出すことと、レコードを新しい修正されたレコードで置換することが含まれるので、UPDATEポリシーはUSING式とWITH CHECK式の両方を受け付けます。 USING式はUPDATEコマンドが操作の対象としてどのレコードを見ることができるかを決めるのに使われます。一方でWITH CHECKはどの修正した行をリレーションに戻すことができるかを定義します。

更新後の値がWITH CHECK式に反する行があればエラーを起こし、コマンド全体が中止されます。 USING句だけが指定されていた場合は、それがUSINGとWITH CHECKの両方に対して使用されます。

通常は、UPDATEコマンドは更新対象のリレーションの列からデータを読む必要もあります（例えば、WHERE句の中、RETURNING句、あるいはSET句の右辺の式の中）。この場合、更新対象のリレーションのSELECT権限も必要となり、UPDATEポリシーに加えて、適切なSELECTまたはALLポリシーも適用されます。従って、ユーザはUPDATEまたはALLポリシーによって、行を更新する権限を付与されているのに加えて、SELECTまたはALLポリシーによって、更新対象の行にアクセスできなければなりません。

INSERTコマンドに補助的なON CONFLICT DO UPDATE句があり、UPDATEの部分が使われるとき、更新対象の行についてまず、すべてのUPDATEポリシーのUSING式が検査され、次いで、更新された新しい行がWITH CHECK式が検査されます。しかし、単独のUPDATEコマンドとは異なり、既存の行がUSING式を満たさないときは、エラーが発生します（UPDATEの部分が警告なしに回避されることは決してありません）。

DELETE

ポリシーにDELETEを使うのは、そのポリシーはDELETEコマンドに適用されるという意味になります。ポリシーを満たす行だけがDELETEコマンドから見えます。 SELECTでは見えるけれど、削除の対象ではない、という行もあり得ます。それらの行がDELETEポリシーのUSING式を満たさない場合です。

ほとんどの場合、DELETEコマンドは削除対象のリレーションの列からデータを読む必要もあります（例えば、WHERE句の中やRETURNING句）。この場合、リレーション上のSELECT権限も必要となり、DELETEポリシーに加えて、適切なSELECTポリシーまたはALLポリシーも適用されます。従って、ユーザはDELETEまたはALLポリシーによって、行を削除する権限を付与されているのに加えて、SELECTまたはALLポリシーによって、削除対象の行にアクセスできなければなりません。

DELETEポリシーはリレーションからレコードが削除される場合にしか適用されず、確認すべき新しい行はないので、WITH CHECK式を持つことはできません。

表240 コマンドタイプにより適用されるポリシー

コマンド	`SELECT/ALLポリシー`	`INSERT/ALLポリシー`	`UPDATE/ALLポリシー`		`DELETE/ALLポリシー`
コマンド	`USING式`	`WITH CHECK式`	`USING式`	`WITH CHECK式`	`USING式`
`SELECT`	既存の行	—	—	—	—
`SELECT FOR UPDATE/SHARE`	既存の行	—	既存の行	—	—
`INSERT`	—	新しい行	—	—	—
`INSERT ... RETURNING`	新しい行 ^[a]	新しい行	—	—	—
`UPDATE`	既存の行と新しい行 ^[a]	—	既存の行	新しい行	—
`DELETE`	既存の行 ^[a]	—	—	—	既存の行
`ON CONFLICT DO UPDATE`	既存の行と新しい行	—	既存の行	新しい行	—
^[a] 読み出しアクセスが既存の、あるいは新しい行（たとえば、リレーションのカラムを参照する`WHERE`あるいは`RETURNING`句）に要求された場合

複数のポリシーの適用

同じコマンドに対して、異なるコマンド種別の複数のポリシーを適用する場合（例えば、UPDATEコマンドに対してはSELECTとUPDATEのポリシーが適用されます）、ユーザは両方の種別の権限（例えば、リレーションから行を検索する権限と、それを更新する権限）を持っている必要があります。従って、ある種別のポリシーの式は、別の種別のポリシーの式とAND演算子を使って結合されます。

同じコマンドに対して同じコマンド種別の複数のポリシーが適用される場合、リレーションのアクセスを許可する少なくとも1つのPERMISSIVEポリシーがなければならず、さらにすべてのRESTRICTIVEポリシーを満たす必要があります。従って、すべてのPERMISSIVEポリシー式がORを使って結合され、すべてのRESTRICTIVEポリシー式がANDを使って結合され、その結果がANDを使って結合されます。 PERMISSIVEポリシーがなければアクセスは拒絶されます。

複数のポリシーを結合するという目的において、ALLのポリシーは適用対象となっている他のすべてのポリシーと同じ種別であるとして扱われることに注意してください。

例えば、SELECTとUPDATEの両方の権限を必要とするUPDATEコマンドにおいて、それぞれの種別の適用可能な複数のポリシーがある場合、以下のように結合されます。

expression from RESTRICTIVE SELECT/ALL policy 1
AND
expression from RESTRICTIVE SELECT/ALL policy 2
AND
...
AND
(
  expression from PERMISSIVE SELECT/ALL policy 1
  OR
  expression from PERMISSIVE SELECT/ALL policy 2
  OR
  ...
)
AND
expression from RESTRICTIVE UPDATE/ALL policy 1
AND
expression from RESTRICTIVE UPDATE/ALL policy 2
AND
...
AND
(
  expression from PERMISSIVE UPDATE/ALL policy 1
  OR
  expression from PERMISSIVE UPDATE/ALL policy 2
  OR
  ...
)

注釈

ポリシーを作成あるいは変更するには、テーブルの所有者でなければなりません。

ポリシーは、データベース内のテーブルに対する明示的な問い合わせには適用されますが、システムが内部的な参照整合性のチェックや制約の検証をしている時には適用されません。この意味するところは、ある値が存在するかどうかを判定する間接的な方法がある、ということです。その例の1つは、主キーあるいは一意制約のある列に重複する値を挿入しようとすることです。挿入に失敗すれば、その値が既に存在すると推定することができます。（この例では、ユーザが見ることができないレコードを挿入することがポリシーにより許されていると仮定しています。）別の例は、あるテーブルへの挿入は許されているが、そのテーブルが別の隠されているテーブルを参照している、という場合です。参照元のテーブルに値を挿入することで、値の存在が判断できます。この場合、挿入の成功はその値が参照先のテーブルに存在することを示唆します。これらの問題は、見ることができない値を示唆するかもしれないようなレコードの挿入、削除、更新が全くできないように注意深くポリシーを設計するか、あるいは外部的な意味を持つキーの代わりに生成された値（例：代理キー）を使うことで解決できます。

一般に、システムは問い合わせに記述される制限より前に、セキュリティポリシーを使ったフィルター条件を実行します。これは守られるべきデータが信頼できないかもしれないユーザ定義関数に偶然に意図せずに渡されることを防ぐためです。しかし、システム（またはシステム管理者）によってLEAKPROOFであるとされた関数や演算子については、信頼できるとみなして良いので、ポリシー式より先に評価される場合があります。

ポリシーの式はユーザの問い合わせに直接追加されるため、式は問い合わせ全体を実行しているユーザの権限によって実行されます。そのため、あるポリシーを使用するユーザは、その式が参照しているすべてのテーブルおよび関数にアクセスできる必要があります。そうでなければ、行単位セキュリティが有効になっているテーブルに問い合わせをしようとしたときに、単に権限なしのエラーを受け取ります。しかし、これによってビューの動作が変わることはありません。通常の問い合わせおよびビューと同じく、ビューによって参照されるテーブルに対する権限の確認とポリシーは、ビューの所有者の権限およびビューの所有者に適用されるポリシーを利用します。

更なる詳細と実践的な例については5.7に記述されています。

互換性

CREATE POLICYはPostgreSQLの拡張です。

CREATE POLICY

概要

説明

パラメータ

コマンド毎のポリシー

複数のポリシーの適用

注釈

互換性

関連項目