CREATE POLICY — définir un niveau de politique de sécurité pour une table
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 ) ]
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é. Si plusieurs politiques sont applicables à une requête donnée, elles seront combinées en utilisant « OR » (bien que les politiques ON CONFLICT DO UPDATE et INSERT ne soient pas combinées de cette manière mais sont plutôt appliquées comme demandé à chaque étape de l'exécution ON CONFLICT).
Pour les commandes qui ont simultanément les politiques USING et WITH CHECK (ALL et UPDATE), s'il n'y a pas de politique WITH CHECK définie, alors la politique USING sera utilisée pour les lignes visibles (cas normal d'utilisation de USING), et pour 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.
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.
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.
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 $etre passée pour chaque enregistrement.
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 combinée avec une opération booléenne OR, et c'est le résultat de cette opération qui sera appliqué. Le comportement est identique lorsque les politiques se chevauchent. 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 (ou accessoirement aux clauses ON CONFLICT DO UPDATE de la commande INSERT). Puisque la commande UPDATE implique de récupérer un enregistrement existant puis d'en modifier une partie (voire ne rien modifier), 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.
Lorsqu'une commande UPDATE est utilisée avec une clause WHERE ou RETURNING, les droits de SELECT sont nécessaires sur la table à mettre à jour. De ce fait, le résultat des politiques sur SELECT et ALL seront combinées avec une opération booléenne OR si les politiques SELECT se chevauchent et sinon avec une opération booléenne AND sur la clause USING appliquée par la politique définie pour UPDATE. Donc, pour qu'un utilisateur soit capable de mettre à jour des lignes spécifiques, cet utilisateur doit avoir accès aux lignes protégées par les politiques SELECT ou ALL et les lignes doivent être autorisées à être mises à jour par la politique UPDATE utilisant l'expression définie dans USING
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
Notez toutefois que, dans le cas d'une commande INSERT avec ON CONFLICT DO UPDATE, la politique UPDATE avec une expression définie dans USING sera toujours appliquée comme une expression définie dans WITH CHECK. Cette policy doit systématiquement être appliquée lorsque la branche UPDATE est empruntée. Toutes les lignes existantes doivent nécessairement être autorisées par les policy (SELECT ou ALL)en utilisant la ou les expressions définies dans USING (combinées avec OR) qui sont toujours appliquées comme une ou des expression(s) définie(s) dans WITH CHECK dans ce contexte. (La branche UPDATE ne sera jamais ignorée sans que cela génère une erreur.) Pour terminer, la dernière ligne insérée dans la table doit passer toutes les contraintes définies dansWITH CHECK qu'un simple UPDATE devrait de toutes façons satisfaire.
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
Lorsqu'une commande DELETE est utilisée avec une clause WHERE ou une clause RETURNING, les droits sur SELECT sont nécessaires sur la table à mettre à jour, De ce fait, les résultats des politiques sur SELECT et ALL seront combinées avec une opération booléenne AND (avec un OR si les politiques SELECTsse chevauchent) sur la clause USING appliquée par la policy définie pour DELETE. Pour qu'un utilisateur soit capable de mettre à jour des lignes spécifiques, cet utilisateur doit avoir accès aux lignes protégées par les politiques SELECT ou ALL et les lignes doivent être autorisées à être mise à jour par la politique DELETE utilisant l'expression définie dans USING
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.
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.
Veuillez noter qu'il doit y avoir au moins une politique permissive pour autoriser l'accès aux enregistrements avant que les politiques restrictives puissent être efficacement utilisées pour réduire cet accès. Si seules des politiques restrictives existent, alors aucun enregistrement ne sera accessible. Quand un mélange de politiques permissives et restrictives est présent, un enregistrement est seulement accessible si au moins une des politiques permissives est passée, en plus de toutes les politiques restrictives.
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, « Row Security Policies ».
CREATE OPERATOR FAMILY
