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.
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).
Ces lignes seront supprimées sans qu'aucune erreur ou notification
ne soit rapportée.
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, 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, 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.
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 UPDATE vérifie la politique
INSERT avec l'expression définie dans
WITH CHECK uniquement pour les lignes ajoutées
à la table par la commande INSERT .
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 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
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. 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 | Ligne existante | -- | -- | -- | -- |
SELECT FOR UPDATE/SHARE | Ligne existante | -- | Ligne existante | -- | -- |
INSERT / MERGE ... THEN INSERT | -- | Nouvelle ligne | -- | -- | -- |
INSERT ... RETURNING | Nouvelle ligne [a] | Nouvelle ligne | -- | -- | -- |
UPDATE / MERGE ... THEN UPDATE | Lignes nouvelles et existantes [a] | -- | Ligne existante | Nouvelle ligne | -- |
DELETE | Ligne existante [a] | -- | -- | -- | Ligne existante |
ON CONFLICT DO UPDATE | Lignes nouvelles et existantes | -- | Ligne existante | Nouvelle ligne | -- |
[a]
Si l'accès en lecture est requis pour une ligne nouvelle ou
existante (par exemple, une clause | |||||
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.