CREATE POLICY — définir un niveau de politique de sécurité pour une table
CREATE OPERATOR FAMILY
CREATE POLICY nom ON nom_table
[ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
[ TO { nom_role | PUBLIC | 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 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 238, « Politiques appliquées par type de commande » résume la façon dont s'appliquent les différents types de politique aux commandes spécifiques.
Pour les commandes 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 de USING) et les lignes autorisées à ê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.
Nom de la politique à créer. Chaque nom de politique doit être unique au sein d'une table.
Le nom (optionnellement qualifié par le schéma) de la table à laquelle s'applique la politique.
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.
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.
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.
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'évalutation 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.
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é.
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.
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 .
Utiliser UPDATE dans une politique signifie que cette politique s'appliquera à toutes les requêtes UPDATE, SELECT FOR UPDATE et SELECT FOR SHARE ainsi que les clauses ON CONFLICT DO UPDATE des commandes INSERT. Puisque la commande UPDATE implique de récupérer un enregistrement existant et de le remplacer 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
D'habitude, une commande UPDATE a aussi besoin de lire des données des colonnes de la relation en cours de mise à jour (c'est-à-dire dans une clause WHERE ou dans une clause RETURNING, ou dans une expression sur le 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 la politique SELECT appropriée ou les politiques ALL seront appliqués en plus des politiques de l'UPDATE. De ce fait, l'utilisateur doit avoir accès aux lignes en cours de mise à jour à travers la 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 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 toutes les politiques UPDATE, et alors la nouvelle ligne mise à jour est vérifiée de nouveau avec les expressions WITH CHECK. Néanmoins, notez que, contrairement à une commande UPDATE seule, si la ligne existante ne passe pas les expressions USING, une erreur sera renvoyée (le chemin UPDATE ne sera jamais évité silencieusement).
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 aussi lire les données des colonnes de la relation sur laquelle a lieu la suppression (en raison d'une clause WHERE ou d'une clause RETURNING). Dans ce cas, les droits SELECT sont aussi requis sur la relation, et les politiques SELECT appropriées ou ALL seront appliquées en plus des politiques DELETE. De ce fait, l'utilisateur doit avoir accès à toutes les lignes en cours de suppression au travers d'une politique SELECT ou ALL en plus de disposer du droit de suppression de ligne via les politiques 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 238. 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 | -- | Nouvelle ligne | -- | -- | -- |
| INSERT ... RETURNING | Nouvelle ligne [a] | Nouvelle ligne | -- | -- | -- |
| 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 WHERE ou RETURNING qui fait référence aux colonnes de la relation). |
|||||
Quand plusieurs politiques de différents types de commande s'appliquent à la même commande (par exemple, les 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 des lignes de la relation ainsi que le droit de les mettre à jour). De ce fait, les expressions d'un type de politique sont combinées avec les expressions 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 au moins une des politiques doit donner accès à la relation. De ce fait, les expressions provenant de toutes les politiques de ce type sont combinées en utilisant l'opérateur OR. S'il n'y a pas de politiques applicables, alors l'accès est refusé.
Notez que, dans le cas de combiner plusieurs politiques, les politiques ALL sont traitées comme ayant le même type comme tout autre type de politique en cours d'application.
Par exemple, dans une commande UPDATE nécessitant les droits SELECT et UPDATE, s'il y a plusieurs politiques applicables de chaque type, elles seront combinées ainsi :
(
expression from SELECT/ALL politique 1
OR
expression from SELECT/ALL politique 2
OR
...
)
AND
(
expression from UPDATE/ALL politique 1
OR
expression from 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érfications internes d'integrité sur le référentiel ou pour la validation des contraintes. Ce qui signifie qu'il y a des manières indirectes de determiner 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 determiné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érale 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, « Row Security Policies ».