CREATE POLICY — définir un niveau de politique de sécurité pour une table
CREATE POLICYnomONnom_table[ AS { PERMISSIVE | RESTRICTIVE } ] [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ] [ TO {nom_role| PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER } [, ...] ] [ USING (expression_USING) ] [ WITH CHECK (expression_CHECK) ]
La commande CREATE POLICY définit un nouveau niveau
de politique de sécurité pour une table.
Notez que le niveau de politique de sécurité doit être actif pour la
table.
Les politiques de sécurité créées peuvent être appliquées en
utilisant la commande suivante :
ALTER TABLE ... ENABLE ROW LEVEL SECURITY
Une politique (policy dans la version
originale de la documentation) valide l'autorisation de sélectionner
(instruction SELECT), insérer (instruction INSERT), mettre à jour
(instruction UPDATE) ou supprimer (instruction DELETE) des lignes qui
correspondent à l'expression concordante d'une politique particulière.
Une expression spécifiée avec USING
sera vérifiée par rapport aux lignes existantes dans la table,
tandis qu'une expression spécifiée avec WITH CHECK sera
vérifiée sur les nouvelles lignes crées par INSERT ou
UPDATE
Lorsqu'une expression définie dans USING renvoie true
pour une ligne donnée, alors cette ligne est visible pour l'utilisateur.
Dans le cas contraire, cette ligne reste invisible.
Généralement, aucune erreur ne survient quand une ligne est invisible, mais
voir Tableau 297 pour les exceptions.
Lorsqu'une expression définie dans WITH CHECK renvoie
true pour une ligne, alors cette ligne est insérée. Par contre, si elle
renvoie false ou NULL, cela génère une erreur.
Pour les commandes INSERT, UPDATE
et MERGE,
les expressions définies dans WITH CHECK sont
appliquées après l'activation du trigger BEFORE
et avant qu'aucune modification de données n'ait réellement été
effectuée.
Un trigger BEFORE ROW peut éventuellement
modifier les données à insérer, influençant ainsi le résultat de la
politique de sécurité.
Les expressions définies dans WITH CHECK sont forcées avant
toutes les autres contraintes.
Les noms de politique s'entendent par table. De ce fait, un même nom de politique peut être utilisé pour différentes tables et avoir une définition différente, adaptée à la table en question.
Les politiques peuvent être appliquées pour des commandes ou rôles spécifiques. Par défaut, une nouvelle politique créée sera appliquée à toutes les commandes et pour tous les rôles à moins qu'autre chose ne soit spécifié. Plusieurs politiques peuvent s'appliquer à une seule commande ; voir ci-dessous pour plus de détails. Tableau 297 résume la façon dont s'appliquent les différents types de politique aux commandes spécifiques.
Pour les politiques qui ont simultanément les expressions
USING et WITH CHECK
(ALL et UPDATE),
s'il n'y a pas d'expression WITH CHECK définie, alors
l'expression USING sera utilisée pour déterminer les lignes
visibles (cas normal d'utilisation de USING),
et les lignes qui obtiendront l'autorisation d'être ajoutées
(cas WITH CHECK ).
Si un niveau de sécurité est activé pour une table mais qu'aucune politique (policy) n'est applicable, une politique « default deny » est utilisée, plus aucune ligne n'est alors visible ou modifiable.
nomNom de la politique à créer. Chaque nom de politique doit être unique au sein d'une table.
nom_tableLe nom (optionnellement qualifié par le schéma) de la table à laquelle s'applique la politique.
PERMISSIVESpécifie que la politique doit être crée comme une politique permissive. Toutes les politiques permissives qui s'appliquent à une requête donnée seront combinée ensemble en utilisant l'opérateur booléen « OR ». En créant des politiques permissives, les administrateurs peuvent ajouter des enregistrements à l'ensemble qui sera accédé. Les politiques sont permissives par défaut.
RESTRICTIVESpécifie que la politique doit être crée comme une politique restrictive. Toutes les politiques permissives qui s'appliquent à une requête donnée seront combinée ensemble en utilisant l'opérateur booléen « AND ». En créant des politiques restrictives, les administrateurs peuvent retirer des enregistrements de l'ensemble qui sera accédé puisque toutes les politiques restrictives doivent être passées pour chaque enregistrement.
Il est nécessaire d'avoir au moins une politique permissive pour autoriser l'accès aux enregistrements avant que les politiques restrictives ne puissent être utilisées pour réduire cet accès. Si seules des politiques restrictives existent, alors aucun enregistrement ne sera accessible. Quand un mixe de politiques permissives et restrictives est présent, un enregistrement n'est accessible que si au moins une politique permissive passe, en plus de toutes les politiques restrictives.
commande
La commande à laquelle la politique s'applique.
Les options valides sont les suivantes :
ALL, SELECT,
INSERT, UPDATE,
et DELETE.
ALL est la valeur par défaut.
Vous verrez par la suite comment sont appliquées les spécificités
de chaque option.
nom_role
Le ou les role(s) auxquels les politiques sont appliquées.
Par défaut, c'est le pseudo-rôle PUBLIC, qui applique
les politiques à tous les rôles.
expression_USING
Toute expression SQL conditionnelle
(autrement dit, renvoyant une donnée de type boolean).
L'expression conditionnelle ne peut pas contenir de fonction
d'agrégat ou de fenêtrage (window).
Si le niveau de politique de sécurité est activé, cette expression
sera ajoutée aux requêtes exécutées sur la table.
Les lignes pour lesquelles l'expression renvoie true seront
visibles.
Toute ligne pour laquelle l'expression renvoie false
ou NULL sera invisible pour l'utilisateur (avec SELECT)
et ne sera pas modifiable (avec UPDATE ou
DELETE). Généralement,
ces lignes seront supprimées silencieusement ; aucune erreur ne
survient (mais voir
Tableau 297 pour les exceptions).
expression_CHECK
Toute expression SQL conditionnelle
(autrement dit, renvoyant une donnée de type boolean).
L'expression conditionnelle ne peut pas contenir de fonction d'agrégat
ou de fenêtrage (window).
Si le niveau de politique de sécurité est activé, cette expression
sera utilisée dans les requêtes contenant INSERT
et UPDATE.
Seules les lignes pour lesquelles l'expression est évaluée à true
seront autorisée à être modifiées.
Une erreur sera générée si l'évaluation de la condition de la
commande UPDATE ou INSERT renvoie false ou NULL pour n'importe quel
enregistrement parmi l'ensemble des résultats.
Notez que expression_CHECK
est évaluée sur le futur contenu de la ligne, et non pas sur le contenu
d'origine.
ALL #
Utiliser ALL pour une politique signifie qu'elle
s'appliquera pour toutes les commandes, peu importe le type de
commande. Si une politique ALL existe et que des
politiques spécifiques supplémentaires existent, alors leur résultat
sera appliqué. Pour terminer, les politiques ALL
seront appliquées pour la partie extraction et pour la partie
modification de la requête, en utilisant l'expression définie dans
USING pour les deux cas si seule la partie
USING est définie.
Par exemple, si une requête UPDATE est
exécutée, alors la politique ALL sera applicable
sur les lignes à modifier que la commande UPDATE
sera capable de sélectionner (en appliquant l'expression
définie dans USING) mais aussi sur le
résultat des lignes modifiées, pour vérifier s'il est autorisé
de les ajouter à la table (en appliquant l'expression définie
dans WITH CHECK si elle est définie, et
sinon en appliquant l'expression définie dans USING
).
Si une commande INSERT ou UPDATE
essaie d'ajouter des lignes à une table et est bloquée par
l'expression définie dans WITH CHECK de la
politique ALL (ou son expression
USING si elle n'a pas d'expression WITH
CHECK), l'ensemble de la commande est annulé.
SELECT #
Utiliser SELECT dans une politique signifie
que cette politique s'appliquera à toutes les requêtes
SELECT ainsi qu'à toute vérification du
droit SELECT nécessaire sur
la table pour laquelle la politique est définie.
Concernant les requêtes SELECT, le résultat
sera composé uniquement des lignes qui auront passé la politique
SELECT
Pour les requêtes qui demandent des droits, telles que les
commandes d'UPDATE, DELETE et
MERGE, elles verront uniquement
dans le résultat les lignes qui auront été autorisés par la politique
SELECT, sauf comme décrit ci-dessous.
Si une requête de modification de données a une clause
RETURNING, les droits SELECT sont
requis sur la relation, et toute nouvelle ligne insérée ou supprimée pour
cette relation doit satisfaire les politiques SELECT
de la relation pour être disponible dans la clause
RETURNING. Si une ligne nouvellement insérée ou
modifiée ne satisfait pas les politiques SELECT de la
relation, une erreur sera renvoyée (les lignes insérées ou modifiées
à renvoyer ne sont jamais ignorées silencieusement).
Si un INSERT a une clause ON CONFLICT DO
NOTHING/UPDATE, les droits SELECT sont
requis sur la relation et les lignes proposées en insertion sont
vérifiées en utilisant les politiques SELECT de la
relation. Si une ligne proposée en insertion ne satisfait pas les
politiques SELECT de la relation, une erreur est
renvoyée (le INSERT n'est jamais
silencieusement évité). De plus, si le chemin UPDATE
est pris, la ligne à mettre à jour et la nouvelle ligne mise à jour sont
vérifiées avec les politiques SELECT de la relation,
et une erreur est renvoyée si elles ne sont pas satisfaites (un
UPDATE auxiliaire n'est jamais
évité silencieusement.
Une commande MERGE requiert les droits
SELECT sur les relations source et cible, et donc
chaque politique SELECT de la relation est appliqué
avant de les joindre, et les actions du MERGE verront
uniquement les lignes qui sont autorisées par ces politiques. De plus,
si un UPDATE est exécuté, les politiques
SELECT de la relation cible sont appliquées à la ligne
mise à jour, comme pour un UPDATE autonome, sauf
qu'une erreur est renvoyée si elles ne sont pas satisfaite.
Une politique SELECT ne peut pas avoir une
expression définie dans WITH CHECK qui ne
s'applique que dans le cas où des enregistrements sont
récupérés depuis la table.
INSERT #
Utiliser INSERT dans une politique signifie
que cette politique s'appliquera à toutes les requêtes
INSERT
Les lignes à insérer qui ne passent pas la politique renvoient une
erreur de violation de politique, et l'ensemble INSERT
de la commande est annulé.
Une politique INSERT ne peut pas avoir une
expression définie dans USING qui ne
s'applique que dans les cas où des enregistrements sont ajoutés
à la table.
Notez qu'une commande INSERT avec
ON CONFLICT DO UPDATE vérifie les politiques
INSERT avec les expressions définies dans
WITH CHECK pour toutes les lignes proposées en
insertion, qu'elles soient ou non insérées au final.
UPDATE #
Utiliser UPDATE dans une politique signifie
que cette politique s'appliquera à toutes les requêtes
UPDATE, SELECT FOR UPDATE
et SELECT FOR SHARE, ainsi qu'aux clauses
ON CONFLICT DO UPDATE de la commande
INSERT et de la commande MERGE
si elle contient une action UPDATE.
Puisque la commande UPDATE implique de
récupérer un enregistrement existant et le replacer avec un
nouvel enregistrement modifié, la politique UPDATE
accepte les expressions définies dans USING
mais aussi dans WITH CHECK
L'expression définie dans USING déterminera
sur quelle selection d'enregistrements la commande
UPDATE est capable de travailler tandis que
l'expression définie dans WITH CHECK
déterminera les enregistrements qui pourront être modifiés et
réinjectés dans la table.
Si une seule ligne à mettre à jour ne remplit pas les
conditions pour être autorisée par l'expression spécifiée dans
WITH CHECK, une erreur sera générée, et
l'ensemble de la commande est annulé.
S'il n'y a que l'expression spécifiée dans USING
qui a été définie alors c'est cette expression qui
sera utilisée pour vérifier les cas USING et
WITH CHECK
Typiquement, une commande UPDATE a aussi besoin de
lire les données des colonnes de la relation mise à jour (par exemple
dans une clause WHERE ou dans une clause
RETURNING ou dans une expression du côté droit de
la clause SET). Dans ce cas, les droits
SELECT sont aussi requis sur la relation en cours
de mise à jour, et les politiques SELECT ou
ALL seront appliquées en plus des politiques
UPDATE. De ce fait, l'utilisateur doit avoir accès
aux lignes en cours de mise à jour via une politique
SELECT ou ALL en plus d'avoir le
droit de mettre à jour la ligne via une politique
UPDATE ou ALL.
Quand une commande INSERT a une clause
supplémentaire ON CONFLICT DO UPDATE, si le chemin
UPDATE est pris, la ligne à mettre à jour est tout
d'abord vérifiée avec les expressions USING de
toute politique UPDATE, puis la nouvelle ligne mise
à jour est vérifiée avec les expressions WITH
CHECK. Néanmoins, notez que, contrairement à une commande
UPDATE autonome, si la ligne existante ne passe pas
les expressions USING, une erreur sera levée (le
chemin UPDATE ne sera jamais
évité silencieusement). Cela s'applique aussi à une action
UPDATE d'une commande MERGE.
DELETE #
Utiliser DELETE dans une politique signifie
que cette politique s'appliquera à toutes les requêtes
DELETE et aux requêtes MERGE
contenant des commandes DELETE.
Pour une commande DELETE, seules les lignes autorisées
par cette politique seront visibles à une commande
DELETE
Il peut y avoir des lignes visibles retournées par la commande
SELECT qui ne sont pas candidates à la
suppression si elles ne sont pas validées par l'expression
définie dans la clause USING de la politique
DELETE Cependant, notez qu'une action
DELETE dans une commande MERGE
verra des lignes qui sont visibles au travers des politiques
SELECT et, si la politique
DELETE ne passe pas pour une telle ligne, une erreur
sera renvoyée.
Dans la plupart des cas, une commande DELETE doit
également lire les données des colonnes qu'elle supprime dans la relation
(par exemple, dans une clause WHERE ou une clause
RETURNING). Dans ce cas, les droits SELECT
sont également requis sur la relation, et les politiques de
sécurité SELECT ou ALL seront
appliquées en plus des politiques de sécurité DELETE.
Ainsi, l'utilisateur doit avoir accès à la ou aux lignes en cours de
suppression via une politique de sécurité SELECT ou
ALL en plus d'être autorisé à supprimer la ou les
lignes via une politique de sécurité DELETE ou
ALL.
Une politique DELETE ne peut pas avoir
d'expression définie dans WITH CHECK puisque
cette politique ne s'applique qu'à des enregistrements qui vont être
supprimés de la table. Il n'y a donc pas de nouvelles lignes
à vérifier.
Tableau 297 résume comment les différents types de politique s'appliquent aux commandes spécifiques. Dans le tableau, « vérifie » signifie que l'expression de la politique est vérifiée et une erreur est renvoyée si la vérification renvoie false ou null, alors que « filtre » signifie que la ligne est ignorée silencieusement si l'expression de la politique renvoie false ou null.
Tableau 297. Politiques appliquées par type de commande
| Command | Politique SELECT/ALL | Politique INSERT/ALL | Politique UPDATE/ALL | Politique DELETE/ALL | |
|---|---|---|---|---|---|
Expression USING | Expression WITH CHECK | Expression USING | Expression WITH CHECK | Expression USING | |
SELECT / COPY ... TO | Filtre la ligne existante | -- | -- | -- | -- |
SELECT FOR UPDATE/SHARE | Filtre la ligne existante | -- | Filtre la ligne existante | -- | -- |
INSERT | Vérifie la nouvelle ligne [a] | Vérifie la nouvelle ligne | -- | -- | -- |
UPDATE | Filtre la ligne existante [a] & vérifie la nouvelle ligne [a] | -- | Filtre la ligne existante | Vérifie la nouvelle ligne | -- |
DELETE | Filtre la ligne existante [a] | -- | -- | -- | Filtre la ligne existante |
INSERT ... ON CONFLICT | Vérifie la nouvelle ligne [b][c] | Vérifie la nouvelle ligne [c] | -- | -- | -- |
ON CONFLICT DO UPDATE | Vérifie les lignes existantes & nouvelles [d] | -- | Vérifie la ligne existante | Vérifie la nouvelle ligne [d] | -- |
MERGE | Filtre les lignes source & cible | -- | -- | -- | -- |
MERGE ... THEN INSERT | -- | Vérifie la nouvelle ligne | -- | -- | -- |
MERGE ... THEN UPDATE | Vérifie la nouvelle ligne | -- | Vérifie la ligne existante | Vérifie la nouvelle ligne | -- |
MERGE ... THEN DELETE | -- | -- | -- | -- | Vérifie la ligne existante |
[a]
Si l'accès en lecture est requis pour la ligne existante ou la nouvelle
ligne, une clause [b] Si un index ou une contrainte arbitre est indiqué. [c] La ligne proposée en insertion est vérifiée qu'un conflit survienne ou pas. [d]
La nouvelle ligne d'un | |||||
Quand plusieurs politiques de différents types de commande s'appliquent à
la même commande (par exemple, des politiques SELECT et
UPDATE appliquées à une commande
UPDATE), alors l'utilisateur doit avoir les deux types
de droits (par exemple, le droit de sélectionner les lignes de la relation
ainsi que le droit de les mettre à jour). De ce fait, les expressions pour
un type de politique sont combinées pour l'autre type de politique en
utilisant l'opérateur AND.
Quand plusieurs politiques du même type de commande s'appliquent à la même
commande, alors il doit exister au moins une politique
PERMISSIVE donnant accès à la relation, et toutes les
politiques RESTRICTIVE doivent passer. De ce fait,
toutes les expressions de politique PERMISSIVE sont
combinées en utilisant OR, toutes les expressions de
politique RESTRICTIVE sont combinées en utilisant
AND, et les résultats sont combinés en utilisant
AND. S'il n'y a pas de politiques
PERMISSIVE, alors l'accès est refusé.
Notez que, pour combiner les différentes politiques, les politiques
ALL sont traitées comme ayant le même type que tout
autre type de politique appliquée.
Par exemple, dans une commande UPDATE nécessitant des
droits pour SELECT et UPDATE, si
plusieurs politiques de chaque type sont applicables, elles seront
combinées ainsi :
expressionfrom RESTRICTIVE SELECT/ALL politique 1 ANDexpressionfrom RESTRICTIVE SELECT/ALL politique 2 AND ... AND (expressionfrom PERMISSIVE SELECT/ALL politique 1 ORexpressionfrom PERMISSIVE SELECT/ALL politique 2 OR ... ) ANDexpressionfrom RESTRICTIVE UPDATE/ALL politique 1 ANDexpressionfrom RESTRICTIVE UPDATE/ALL politique 2 AND ... AND (expressionfrom PERMISSIVE UPDATE/ALL politique 1 ORexpressionfrom PERMISSIVE UPDATE/ALL politique 2 OR ... )
Vous devez être le propriétaire de la table pour laquelle vous souhaitez creéer ou modifier des politiques.
Tandis que les politiques sont appliquées pour les requêtes accédant explicitement aux tables de la base de données, elles ne sont pas appliquées lorsque le système réalise des vérifications internes d'intégrité sur le référentiel ou pour la validation des contraintes. Ce qui signifie qu'il y a des manières indirectes de déterminer si une valeur donnée existe. Par exemple, si vous essayez d'insérer un doublon dans une colonne clé primaire, ou qui possède une contrainte d'unicité. Si l'insertion échoue alors l'utilisateur peut inférer que la valeur existe déjà. (dans cet exemple, il est entendu que l'utilisateur est soumis à une politique de sécurité lui permettant d'insérer des enregistrements qu'il n'est néanmoins pas autorisé à consulter) Un autre exemple, si un utilisateur est autorisé à insérer dans une table qui en référence une autre, une table cachée. Son existence peut être déterminée par l'utilisateur en insérant une valeur dans la table, la réussite indiquerait que la valeur existe dans la table référencée. Ces problèmes peuvent être résolus en vérifiant minutieusement les politiques de façon à ce que les utilisateurs ne puissent pas insérer, supprimer, ou mettre à jour des enregistrements qui pourraient récupérer des valeurs qu'ils ne devraient pas pouvoir consulter, ou en utilisant un générateur de valeur (par exemple clés substituées) à la place de clés à signification externe.
En général le système va appliquer des conditions filtrantes en
se servant de politiques de sécurité pour prioriser les conditions
apparaissant dans les requêtes utilisateur. Ceci afin d'éviter
d'exposer par inadvertance des données protégées à certaines
fonctions utilisateurs qui pourraient ne pas être dignes de
confiance.
Les fonctions et opérateurs, taggués LEAKPROOF
par le système (ou l'administrateur système) seront évaluées
avant les expressions des politiques et seront considérées comme
digne de confiance.
Comme les expressions de politique s'appliquent directement à la requête d'un
utilisateur, elles seront lancées avec les droits de cet utilisateur pendant
toute la durée de la requête. De ce fait, un utilisateur qui utilise une
politique donnée doit pouvoir accéder à toutes les tables et fonctions
référencées dans l'expression de vérification, sinon il recevra une erreur du
type « permission denied » en essayant d'accéder à une référence
dont le niveau de sécurité est activé. Cependant, ceci ne modifie pas le
fonctionnement des vues. Comme avec les requêtes classiques et leurs vues,
les vérifications des autorisations et politiques des tables référencées par
la vue utilisent les droits du propriétaire de la vue, ainsi que les
politiques qui s'appliquent sur le propriétaire de la la vue, sauf si la vue
est définie en utilisant l'option security_invoker (voir
CREATE VIEW).
Aucune politique séparée n'existe pour MERGE. À la place,
les politiques définies pour SELECT,
INSERT, UPDATE et
DELETE sont appliquées lors de l'exécution de
MERGE, dépendant des actions réalisées.
Des commentaires supplémentaires et des exemples pratiques peuvent être trouvés ici : Section 5.9.
CREATE POLICY est une extension
PostgreSQL.