Documentation PostgreSQL 14.22 » Référence » Commandes SQL » CREATE POLICY

Précédent | Niveau supérieur | Suivant
CREATE OPERATOR FAMILY | Sommaire | CREATE PROCEDURE

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

## 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`). 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.

### 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` (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 UPDATE` ou une clause `ON CONFLICT DO NOTHING` avec une spécification d'un index ou d'une contrainte arbitre, 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 `WHERE` ou `RETURNING` qui fait référence aux colonnes de la relation). [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 `UPDATE` auxiliaire, qui peut être différente de la nouvelle ligne de la commande `INSERT` originale.

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

## Compatibilité

`CREATE POLICY` est une extension PostgreSQL.

## Voir aussi

ALTER POLICY, DROP POLICY, ALTER TABLE