Gérer la sécurité des données des applications à l'aide des vues sécurisées paramétrées AlloyDB Omni

Sélectionnez une version de la documentation :

Vous pouvez utiliser des vues sécurisées paramétrées dans AlloyDB Omni pour limiter l'accès aux données en fonction de paramètres nommés spécifiques à l'application, tels que les identifiants des utilisateurs de l'application. Les vues sécurisées paramétrées améliorent la sécurité et le contrôle des accès en étendant les fonctionnalités des vues PostgreSQL. Ces vues réduisent également les risques d'exécution de requêtes non fiables à partir d'applications en appliquant automatiquement des restrictions à toute requête exécutée.

Pour en savoir plus, consultez Présentation des vues sécurisées paramétrées et Sécuriser et contrôler l'accès aux données d'application à l'aide de vues sécurisées paramétrées.

Avant de commencer

La compatibilité avec les vues paramétrées d'AlloyDB AI est fournie par parameterized_views, une extension d'AlloyDB pour PostgreSQL.

Cette page suppose que vous avez installé AlloyDB Omni. Consultez Installer AlloyDB Omni (pour les conteneurs, pour Kubernetes).

Avant d'utiliser des vues sécurisées paramétrées, vous devez effectuer les opérations suivantes une fois dans chaque nouveau conteneur Postgres. Chaque paramètre peut être appliqué à l'aide de ALTER SYSTEM ou en modifiant directement le postgresql.conf.

  1. Ajoutez parameterized_views au fichier shared_preload_libraries.
  2. Activez la fonctionnalité en définissant parameterized_views.enabled=on.

  3. Redémarrez le serveur PostgreSQL pour que les modifications prennent effet.

    -- See the current shared_preload_libraries
SHOW shared_preload_libraries;
ALTER SYSTEM SET shared_preload_libraries="...,parameterized_views";
ALTER SYSTEM SET parameterized_views.enabled=on;

  4. Utilisez psql pour créer l'extension parameterized_views dans n'importe quelle base de données où vous souhaitez créer une vue paramétrée :

    -- Requires parameterized_views.enabled set to true
CREATE EXTENSION parameterized_views;

    Lors de la création de l'extension, un schéma nommé parameterized_views est également créé par le système afin que les API soient contenues dans l'espace de noms de ce schéma et qu'elles n'entrent pas en conflit avec les API existantes.

Créer une vue sécurisée paramétrée

Pour créer une vue sécurisée paramétrée, procédez comme suit :

  1. Exécutez la commande LDD CREATE VIEW, comme illustré dans l'exemple suivant :

    CREATE VIEW secure_checked_items WITH (security_barrier) AS
SELECT bag_id, timestamp, location
FROM checked_items t
WHERE customer_id = $@app_end_userid;

    Dans l'exemple précédent, la vue sécurisée paramétrée permet d'accéder à trois colonnes d'une table nommée checked_items. La vue limite les résultats aux lignes où checked_items.customer_id correspond à un paramètre obligatoire. Utilisez les attributs suivants :

    • Créez la vue à l'aide de l'option security_barrier.
    • Pour limiter les utilisateurs de l'application afin qu'ils ne puissent afficher que les lignes auxquelles ils sont autorisés à accéder, ajoutez les paramètres requis dans la définition de la vue à l'aide de la syntaxe $@PARAMETER_NAME. Un cas d'utilisation courant consiste à vérifier la valeur d'une colonne dans la clause WHERE à l'aide de COLUMN = $@PARAMETER_NAME.
    • $@PARAMETER_NAME indique un paramètre de vue nommé. Sa valeur est fournie lorsque vous utilisez l'API execute_parameterized_query. Les paramètres de vue nommés doivent respecter les exigences suivantes :
      • Les paramètres de vue nommée doivent commencer par une lettre (a-z).
      • Vous pouvez utiliser des lettres avec des signes diacritiques et des lettres non latines, ainsi qu'un trait de soulignement (_).
      • Les caractères suivants peuvent être des lettres, des traits de soulignement ou des chiffres (0-9).
      • Les paramètres de vue nommés ne peuvent pas contenir $.
      • Les paramètres de vue nommée sont sensibles à la casse. Par exemple, $@PARAMETER_NAME est interprété différemment de $@parameter_name.

  2. Accordez l'autorisation SELECT sur la vue à tout utilisateur de base de données autorisé à interroger la vue.

  3. Accordez l'autorisation USAGE sur le schéma contenant les tables définies dans la vue à tout utilisateur de base de données autorisé à interroger la vue.

Pour en savoir plus, consultez Sécuriser et contrôler l'accès aux données d'application à l'aide de vues sécurisées paramétrées.

Configurer la sécurité de votre application

Pour configurer la sécurité de vos applications à l'aide de vues sécurisées paramétrées, procédez comme suit :

  1. Créez les vues paramétrées sécurisées en tant qu'utilisateur avec accès administrateur. Cet utilisateur est un utilisateur de la base de données AlloyDB Omni qui effectue des opérations d'administration pour l'application, y compris la configuration de la base de données et l'administration de la sécurité.
  2. Créez un rôle de base de données pour exécuter des requêtes sur des vues sécurisées paramétrées. Il s'agit d'un rôle de base de données AlloyDB Omni que l'application utilise pour se connecter à la base de données et s'y connecter, ainsi que pour exécuter des requêtes sur des vues paramétrées.
  3. Accordez les autorisations du nouveau rôle aux vues sécurisées, ce qui inclut généralement les droits SELECT sur les vues et USAGE sur les schémas.
  4. Limitez les objets auxquels ce rôle peut accéder à l'ensemble minimal requis de fonctions et d'objets publics dont l'application a besoin. Évitez de fournir l'accès à des schémas et des tables qui ne sont pas publics.
  5. Lorsque vous interrogez les vues, l'application fournit les valeurs des paramètres de vue requis, qui sont liés à l'identité de l'utilisateur de l'application.

Interroger une vue sécurisée paramétrée

Pour interroger une vue sécurisée paramétrée, utilisez l'une des options suivantes qui correspond le mieux à votre cas d'utilisation :

  • Basée sur JSON : utilisez cette API pour exécuter la requête en une seule fois et renvoyer des lignes JSON.
  • Basée sur CURSOR : utilisez cette API lorsque vous avez des requêtes plus longues ou lorsque vous avez des requêtes volumineuses et que vous souhaitez récupérer le résultat par lots. La fonction execute_parameterized_query fournie par l'extension parameterized_views accepte un nom de curseur.
  • Instruction PREPARE EXECUTE : utilisez-la pour les instructions préparées qui peuvent être exécutées plusieurs fois avec différentes valeurs de paramètres.

Pour interroger des vues sécurisées paramétrées, vous utilisez la fonction execute_parameterized_query() fournie par l'extension parameterized_views.

API JSON

Cette API présente des limites, car elle déclare un curseur pour la requête donnée. Par conséquent, la requête doit être compatible avec les curseurs PostgreSQL. Par exemple, l'API CURSOR n'est pas compatible avec les instructions DO ou SHOW.

Cette API ne limite pas non plus les résultats en fonction de la taille ou du nombre de lignes renvoyées.

Exécutez la fonction execute_parameterized_query(), qui utilise la syntaxe suivante :

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
)

Remplacez les éléments suivants :

  • SQL_QUERY : requête SQL dont la clause FROM fait référence à une ou plusieurs vues sécurisées paramétrées.
  • PARAMETER_NAMES : liste de noms de paramètres à transmettre sous forme de chaînes.
  • PARAMETER_VALUES : liste des valeurs de paramètres à transmettre.
    • Cette liste doit avoir la même taille que la liste param_names, où l'ordre des valeurs correspond à l'ordre des noms.
    • Le type exact des valeurs est déduit de la requête et de la définition de la vue paramétrée. Les conversions de type sont effectuées si nécessaire et lorsque cela est possible pour la valeur de paramètre donnée. En cas de non-concordance de type, une erreur est générée.

La fonction renvoie une table d'objets JSON. Chaque ligne de la table équivaut à la valeur ROW_TO_JSON() de la ligne de résultat de la requête d'origine.

Utilisez l'exemple suivant pour interroger une vue sécurisée paramétrée :

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => 'SELECT * FROM secure_checked_items',
    param_names => ARRAY ['app_end_userid'],
    param_values => ARRAY ['40']
)

L'utilisation de cette API limite la taille de l'ensemble de résultats en fonction de la taille exprimée en kilo-octets (ko) des résultats et du nombre de lignes. Vous pouvez configurer ces limites à l'aide de parameterized_views.json_results_max_size et parameterized_views.json_results_max_rows.

Cette API présente des limites, car elle déclare un curseur pour la requête donnée. Par conséquent, la requête doit être compatible avec les curseurs PostgreSQL. Par exemple, l'API CURSOR n'est pas compatible avec les instructions DO ou SHOW.

Cette API ne limite pas non plus les résultats en fonction de la taille ou du nombre de lignes renvoyées.

Exécutez la fonction execute_parameterized_query(), qui utilise la syntaxe suivante :

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
)

Remplacez les éléments suivants :

  • SQL_QUERY : requête SQL dont la clause FROM fait référence à une ou plusieurs vues sécurisées paramétrées.
  • PARAMETER_NAMES : liste de noms de paramètres à transmettre sous forme de chaînes.
  • PARAMETER_VALUES : liste des valeurs de paramètres à transmettre.
  • Cette liste doit avoir la même taille que la liste param_names, où l'ordre des valeurs correspond à l'ordre des noms.
  • Le type exact des valeurs est déduit de la requête et de la définition de la vue paramétrée. Les conversions de type sont effectuées si nécessaire et lorsque cela est possible pour la valeur de paramètre donnée. En cas de non-concordance de type, une erreur est générée.

La fonction renvoie une table d'objets JSON. Chaque ligne de la table équivaut à la valeur ROW_TO_JSON() de la ligne de résultat de la requête d'origine.

Utilisez l'exemple suivant pour interroger une vue sécurisée paramétrée :

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => 'SELECT * FROM secure_checked_items',
    param_names => ARRAY ['app_end_userid'],
    param_values => ARRAY ['40']
)

L'utilisation de cette API limite la taille de l'ensemble de résultats en fonction de la taille exprimée en kilo-octets (ko) des résultats et du nombre de lignes. Vous pouvez configurer ces limites à l'aide de parameterized_views.json_results_max_size et parameterized_views.json_results_max_rows.

API CURSOR

Cette API présente des limites, car elle déclare un curseur pour la requête donnée. Par conséquent, la requête doit être compatible avec les curseurs PostgreSQL. Par exemple, l'API CURSOR n'est pas compatible avec les instructions DO ou SHOW.

Cette API ne limite pas non plus les résultats en fonction de la taille ou du nombre de lignes renvoyées.

Exécutez la fonction execute_parameterized_query(), qui utilise la syntaxe suivante :

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
)

Remplacez les éléments suivants :

  • SQL_QUERY : requête SQL dont la clause FROM fait référence à une ou plusieurs vues sécurisées paramétrées.
  • PARAMETER_NAMES : liste de noms de paramètres à transmettre sous forme de chaînes.
  • PARAMETER_VALUES : liste des valeurs de paramètres à transmettre.
    • Cette liste doit avoir la même taille que la liste param_names, où l'ordre des valeurs correspond à l'ordre des noms.
    • Le type exact des valeurs est déduit de la requête et de la définition de la vue paramétrée. Les conversions de type sont effectuées si nécessaire et lorsque cela est possible pour la valeur de paramètre donnée. En cas de non-concordance de type, une erreur est générée.

La fonction renvoie une table d'objets JSON. Chaque ligne de la table équivaut à la valeur ROW_TO_JSON() de la ligne de résultat de la requête d'origine.

Utilisez l'exemple suivant pour interroger une vue sécurisée paramétrée :

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => 'SELECT * FROM secure_checked_items',
    param_names => ARRAY ['app_end_userid'],
    param_values => ARRAY ['40']
)

L'utilisation de cette API limite la taille de l'ensemble de résultats en fonction de la taille exprimée en kilo-octets (ko) des résultats et du nombre de lignes. Vous pouvez configurer ces limites à l'aide de parameterized_views.json_results_max_size et parameterized_views.json_results_max_rows.

Instruction PREPARE

Utilisez la commande PREPARE .. AS RESTRICTED pour créer une instruction préparée qui référence des vues paramétrées. Ces instructions préparées acceptent les paramètres positionnels et appliquent diverses restrictions lorsque vous les exécutez. Pour en savoir plus, consultez Mécanisme de sécurité.

Cette fonctionnalité étend les commandes PREPARE et EXECUTE pour accepter les paramètres de vue nommés. Utilisez des instructions préparées pour éviter la surcharge liée à l'analyse, à l'analyse et à la réécriture à chaque exécution de l'instruction, ce qui peut entraîner des gains de performances significatifs, en particulier pour les requêtes fréquemment exécutées ou complexes. Une instruction préparée est un objet côté serveur qui peut optimiser les performances en précompilant et en stockant une instruction SQL paramétrée pour une exécution ultérieure.

Cette API présente des limites, car l'instruction doit être autorisée dans une instruction PREPARE, ce qui signifie que seules les instructions SELECT et VALUES sont acceptées.

Cette API ne limite pas non plus les résultats en fonction de la taille ou du nombre de lignes renvoyées.

Pour créer une instruction préparée qui référence des vues paramétrées, exécutez la commande PREPARE .. AS RESTRICTED :

PREPARE pquery (/POSITIONAL_PARAM_TYPES/)
        AS RESTRICTED query % a query that may refer to parameterized views
EXECUTE pquery (/POSITIONAL_PARAM_VALUES/)
      WITH VIEW PARAMETERS (VIEW_PARAM_NAME1 = VIEW_PARAM_VALUE1[, ...]);

Remplacez les éléments suivants :

  • POSITIONAL_PARAM_TYPES : un ou plusieurs paramètres positionnels utilisés dans la requête RESTRICTED.
  • POSITIONAL_PARAM_VALUES : valeurs réelles qui remplacent les paramètres positionnels définis dans l'instruction PREPARE.
  • VIEW_PARAM_NAME : nom du paramètre attendu par les vues paramétrées référencées dans la requête RESTRICTED.
  • VIEW_PARAM_VALUE : valeurs réelles transmises aux paramètres viewParamName correspondants des vues paramétrées.

Pour inclure des paramètres dans une instruction préparée, vous devez fournir une liste de types de données dans l'instruction PREPARE. Dans l'instruction que vous préparez, vous faites référence aux paramètres par position en utilisant, par exemple, $1 et $2.

Utilisez la commande EXECUTE .. WITH VIEW PARAMETERS pour exécuter une instruction préparée précédemment que vous avez créée à l'aide de la commande PREPARE .. AS RESTRICTED. Si l'instruction PREPARE qui a créé l'instruction a spécifié des paramètres positionnels, vous devez transmettre un ensemble de paramètres compatible à l'instruction EXECUTE. Vous devez transmettre tous les paramètres de vue nommés requis par les vues paramétrées dans la clause WITH VIEW PARAMETERS.

Utilisez l'exemple suivant pour interroger une vue sécurisée paramétrée :

  PREPARE pquery (timestamp) AS RESTRICTED SELECT * FROM secure_checked_items WHERE timestamp > $1;

  EXECUTE pquery (current_date - 1) WITH VIEW PARAMETERS (app_end_userid = 40);
  EXECUTE pquery (current_date - 30) WITH VIEW PARAMETERS (app_end_userid = 40);
  ```

Restrictions appliquées aux requêtes

Voici la liste des opérations limitées pour les requêtes que vous exécutez à l'aide des options décrites dans Interroger une vue sécurisée paramétrée :

  • Toute invocation récursive d'API (execute_parameterized_query ou à l'aide de EXECUTE .. WITH VIEW PARAMETERS) est interdite, de sorte que seules les valeurs spécifiées par l'application sont utilisées. Cette restriction empêche également l'utilisation de la requête pour contourner l'enveloppe de sécurité de l'ensemble de valeurs de paramètres donné.
  • Certaines extensions qui démarrent une nouvelle session en arrière-plan ne sont pas autorisées, y compris les extensions dblink, pg_cron et pg_background.

  • Voici la liste des constructions de requête autorisées qui sont limitées :

    • Les instructions SELECT en lecture seule sont autorisées.
    • Les instructions SHOW, CALL et DO en lecture seule sont autorisées.
    • Les instructions LMD telles que INSERT, UPDATE et DELETE ne sont pas autorisées.
    • Les instructions LDD telles que CREATE TABLE et ALTER TABLE ne sont pas autorisées.
    • Les autres types d'instructions, tels que LOAD, SET, CLUSTER, LOCK, CHECKPOINT et EXPLAIN, ne sont pas autorisés.

  • Les instructions EXPLAIN ne sont pas autorisées pour éviter les attaques par canal caché à l'aide de plans de requête. Pour en savoir plus, consultez Canal caché.

  • Les vues sécurisées paramétrées fournissent des paramètres pour vous aider à gérer les ressources utilisées par les API pour interroger les vues paramétrées, telles que parameterized_views.statement_timeout. Pour en savoir plus, consultez Options AlloyDB pour PostgreSQL.

Répertorier toutes les vues paramétrées

Utilisez l'extension parameterized_views pour lister toutes les vues paramétrées de la base de données à l'aide de la vue all_parameterized_views. Le résultat de cette vue est identique à celui de la vue de catalogue pg_views, mais all_parameterized_views ne liste que les vues avec des paramètres de vue nommés.

Pour lister les vues paramétrées, utilisez l'exemple suivant :

postgres=# select * from parameterized_views.all_parameterized_views ;
schemaname |      viewname      | viewowner |                       definition
-----------+--------------------+-----------+---------------------------------------------------------
public     | checked_items_view | postgres  |  SELECT checked_items.bag_id,                          +
           |                    |           |     checked_items."timestamp",                         +
           |                    |           |     checked_items.location                             +
           |                    |           |    FROM checked_items                                  +
           |                    |           |   WHERE (checked_items.customer_id = $@app_end_userid);

Pour lister une vue paramétrée dans all_parameterized_views, assurez-vous qu'elle contient au moins un paramètre de vue nommé dans sa définition.

Étapes suivantes