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 n'est pas visible.
Voir Tableau 281 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 et UPDATE,
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 281 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 sont supprimées silencieusement. Aucune erreur
n'est renvoyée (mais voir
Tableau 281 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 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 de 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 et les DELETE,
elles verront uniquement
dans le résultat les lignes qui auront été autorisés par la politique
SELECT
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, 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 modifiée
dans la relation doit satisfaire les politiques SELECT
de la relation pour être disponible dans la clause
RETURNING clause. Si une nouvelle ligne insérée ou
mise à jour 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 à l'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 évité
silencieusement). De plus, si le chemin UPDATE est pris,
la ligne à mettre à jour et la nouvelle ligne mise à jour sont vérifiées
à partir des 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).
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 que la commande INSERT avec
ON CONFLICT DO NOTHING/UPDATE vérifie la politique
INSERT avec les expressions définies dans
WITH CHECK uniquement pour les lignes proposées en
insertion à la table, qu'elles finissent ou non par être ajoutées.
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.
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).
DELETE
Utiliser DELETE dans une politique signifie
que cette politique s'appliquera à toutes les requêtes
DELETE.
Seules les lignes autorisées par cette politique seront
visibles à une commande DELETE
Il peut y avoir des lignes visibles par une politique
SELECT qui ne sont pas disponibles en suppression
si elles ne sont pas validées par l'expression
définie dans la clause USING de la politique
DELETE
In most cases a DELETE command also needs to read
data from columns in the relation that it is deleting from (e.g.,
in a WHERE clause or a
RETURNING clause). In this case,
SELECT rights are also required on the relation,
and the appropriate SELECT or
ALL policies will be applied in addition to
the DELETE policies. Thus the user must have
access to the row(s) being deleted through a SELECT
or ALL policy in addition to being granted
permission to delete the row(s) via a DELETE or
ALL policy.
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 281 résume comment les différents types de politique s'appliquent aux commandes spécifiques. Dans la table, « check » signifie que l'expression de politique est vérifiée et qu'une erreur est renvoyée si l'expression renvoie false ou null, alors que « filter » signifie que la ligne est silencieusement ignorée si l'expression de la politique renvoie false ou null.
Tableau 281. 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 une ligne existante [a] & vérifie une 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] | -- |
[a]
Si le droit de lecture est requis à la ligne existante ou à la nouvelle
ligne (par exemple, une clause [b] Si un index ou 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 de la commande | |||||
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 les politiques s'appliquent sur le propriétaire de la la vue.
Des commentaires supplémentaires et des exemples pratiques peuvent être trouvés ici : Section 5.8.
CREATE POLICY est une extension
PostgreSQL.