PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 10.23 » Référence » Commandes SQL » CREATE POLICY

CREATE POLICY

CREATE POLICY — définir un niveau de politique de sécurité pour une table

Synopsis

CREATE POLICY nom ON nom_table
    [ AS { PERMISSIVE | RESTRICTIVE } ]
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { nom_role | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( expression_USING ) ]
    [ WITH CHECK ( expression_CHECK ) ]
  

Description

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 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 241 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.

Paramètres

nom

Nom de la politique à créer. Chaque nom de politique doit être unique au sein d'une table.

nom_table

Le nom (optionnellement qualifié par le schéma) de la table à laquelle s'applique la politique.

PERMISSIVE

Spé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.

RESTRICTIVE

Spé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.

Politique par commande

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

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 241. Politiques appliquées par type de commande

CommandPolitique SELECT/ALLPolitique INSERT/ALLPolitique UPDATE/ALLPolitique DELETE/ALL
Expression USINGExpression WITH CHECKExpression USINGExpression WITH CHECKExpression USING
SELECTLigne existante -- -- -- --
SELECT FOR UPDATE/SHARELigne existante -- Ligne existante -- --
INSERT -- Nouvelle ligne -- -- --
INSERT ... RETURNING Nouvelle ligne [a] Nouvelle ligne -- -- --
UPDATE Lignes nouvelles et existantes [a] -- Ligne existanteNouvelle ligne --
DELETE Ligne existante [a] -- -- -- Ligne existante
ON CONFLICT DO UPDATELignes nouvelles et existantes -- Ligne existanteNouvelle ligne --

[a] Si l'accès en lecture est requis pour une ligne nouvelle ou existante (par exemple, une clause WHERE ou RETURNING qui fait référence aux colonnes de la relation).


Application de plusieurs politiques

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 :

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

Notes

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.7.

Compatibilité

CREATE POLICY est une extension PostgreSQL.