Utiliser des vues sécurisées paramétrées

Ce document explique comment utiliser des vues sécurisées paramétrées dans Cloud SQL pour PostgreSQL. Elles vous permettent de limiter l'accès aux données en fonction de paramètres nommés spécifiques à l'application, comme 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 la fonctionnalité des vues PostgreSQL. Ces vues atténuent également les risques d'exécution de requêtes non fiables à partir d'applications en appliquant automatiquement un certain nombre de restrictions à toute requête exécutée.

Pour en savoir plus, consultez la présentation des vues sécurisées paramétrées et le tutoriel sur les vues sécurisées paramétrées.

Avant de commencer

Ce document suppose que vous avez créé une instance Cloud SQL pour PostgreSQL.

Avant de pouvoir utiliser des vues sécurisées paramétrées, vous devez effectuer les opérations suivantes :

  1. Activez l'option de base de données cloudsql.enable_parameterized_views pour votre instance Cloud SQL. La modification de cette option nécessite un redémarrage de la base de données. Pour en savoir plus, consultez Configurer des options de base de données.

  2. Utilisez Cloud SQL Studio ou psql pour créer l'extension parameterized_views dans n'importe quelle base de données où une vue paramétrée est créée :

    -- Requires cloudsql.enable_parameterized_views set to on
CREATE EXTENSION parameterized_views;

    Lorsque l'extension est créée, 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 avec l'option security_barrier :

    CREATE VIEW VIEW_NAME WITH (security_barrier) AS
SELECT COLUMN_NAME, COLUMN_NAME_N
FROM TABLE_NAME ALIAS
WHERE CONDITION;

    Remplacez les éléments suivants :

    • VIEW_NAME : nom de la vue sécurisée paramétrée
    • TABLE_NAME : nom de la table à utiliser dans la vue sécurisée paramétrée
    • ALIAS : alias du nom de la table à utiliser dans la vue sécurisée paramétrée
    • COLUMNNAME ou COLUMN_NAMEN : nom de la ou des colonnes de table à utiliser dans la vue sécurisée paramétrée

    • CONDITION : instruction de condition utilisée pour restreindre 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 à l'aide de la syntaxe $@PARAMETER_NAME dans la clause WHERE. Un cas d'utilisation courant consiste à vérifier la valeur d'une colonne à l'aide de WHERE COLUMN = $@PARAMETER_NAME.

      $@PARAMETER_NAME indique un paramètre de vue nommée. Sa valeur est fournie lorsque vous utilisez l'API execute_parameterized_query. Les paramètres de vue nommée doivent respecter les exigences suivantes :

      • Les paramètres de vue nommée doivent commencer par une lettre (a-z) ou un trait de soulignement (_).
      • Les autres caractères peuvent être des lettres, des traits de soulignement ou des chiffres (0-9).
      • Les paramètres des vues nommées sont sensibles à la casse. Par exemple, $@PARAMETER_NAME est interprété différemment de $@parameter_name.

      Voici un exemple de création d'une vue sécurisée paramétrée qui utilise un paramètre de vue nommé :

      CREATE VIEW user_specific_items WITH (security_barrier) AS
SELECT item_id, item_name, description, owner_id
FROM items t
WHERE owner_id = $@app_user_id;

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

  3. Accordez 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 (tutoriel).

Configurer la sécurité pour 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 la vue sécurisée paramétrée en tant qu'utilisateur avec accès administrateur. Il s'agit d'un utilisateur de base de données Cloud SQL qui effectue des opérations administratives pour l'application.

  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 Cloud SQL que l'application utilise pour se connecter et se connecter à la base de données.

    1. Accordez au nouveau rôle des autorisations sur les vues sécurisées, qui incluent généralement les droits SELECT sur les vues et USAGE sur les schémas.
    2. 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 d'accorder l'accès à des schémas et des tables qui ne sont pas publics.

    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.

    Pour en savoir plus, consultez Créer et gérer des utilisateurs.

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 :

  • Basée sur JSON : utilisez cette API pour exécuter la requête en une seule fois et renvoyer des lignes JSON.
  • CURSOR : utilisez cette API lorsque vous avez des requêtes de longue durée ou volumineuses et que vous souhaitez récupérer les résultats par lots.
  • Instruction PREPARE .. AS RESTRICTED : utilisez PREPARE .. AS RESTRICTED pour définir le plan de requête, puis exécutez EXECUTE ... WITH VIEW PARAMETERS (...) pour l'exécuter avec des paramètres spécifiques pour la vue.

API JSON

Exécutez la fonction execute_parameterized_query(), dont la syntaxe est la suivante :

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

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.

L'utilisation de cette API limite la taille de l'ensemble de résultats par taille (en Ko) et par 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

Exécutez la fonction execute_parameterized_query() avec un nom de curseur, ce qui crée et renvoie un CURSEUR à portée de transaction :

-- Must be in a transaction block
BEGIN;

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

FETCH ALL FROM CURSOR_NAME;

END;

Instructions préparées

La méthode des instructions préparées vous permet de préparer un plan de requête une seule fois, puis de l'exécuter plusieurs fois avec différentes valeurs pour les paramètres positionnels de la requête et les paramètres nommés de la vue.

Pour utiliser une instruction préparée, procédez comme suit :

  1. Créez l'instruction préparée.

    -- Prepare the statement
PREPARE PREPARED_STATEMENT_NAME (QUERY_PARAM_TYPE_1, QUERY_PARAM_TYPE_N)
AS RESTRICTED SQL_QUERY;

    Remplacez les éléments suivants :

    • PREPARED_STATEMENT_NAME : nom de l'instruction préparée
    • QUERY_PARAM_TYPE_N : type de données du paramètre de requête, tel que TEXT
    • SQL_QUERY : requête SQL à exécuter dans l'instruction préparée avec la ou les valeurs indiquées

  2. Exécutez l'instruction préparée.

    -- Execute the statement with query parameters and view parameters
EXECUTE PREPARED_STATEMENT_NAME (QUERY_VALUE_1, QUERY_VALUE_N)
WITH VIEW PARAMETERS (PARAMETER_NAME_1 := 'PARAMETER_VALUE_1', PARAMETER_NAME_N := 'PARAMETER_VALUE_N');

    Remplacez les éléments suivants :

    • PREPARED_STATEMENT_NAME : nom de l'instruction préparée.
    • QUERY_VALUE_N : valeur ou valeurs à fournir en tant que paramètre ou paramètres à la requête SQL
    • PARAMETER_NAME_N : nom du ou des paramètres de la vue nommée que vous avez définis lorsque vous avez créé la vue sécurisée paramétrée. Vous créez le paramètre de vue nommée à partir de la colonne de la table.
    • PARAMETER_VALUE_N : valeur ou valeurs du paramètre de vue nommée qui limite la vue sécurisée paramétrée aux lignes associées à la valeur.

  3. Nettoyez l'instruction préparée.

    -- Cleanup
DEALLOCATE PREPARED_STATEMENT_NAME>;

L'exemple suivant utilise une vue sécurisée paramétrée nommée user_specific_items, qui nécessite le paramètre de vue nommé $@app_user_id.

-- Prepare a query with a positional parameter $1 for the item_name pattern
PREPARE get_items_by_name (TEXT) AS RESTRICTED
SELECT item_id, item_name FROM user_specific_items
WHERE item_name LIKE $1;

Une fois l'instruction préparée créée, vous pouvez l'exécuter plusieurs fois et attribuer différentes valeurs aux paramètres de requête et de vue nommée.

Par exemple, la première exécution de la requête :

-- Execute for user 123, looking for items like '%Laptop%'
EXECUTE get_items_by_name ('%Laptop%')
WITH VIEW PARAMETERS (app_user_id := '123');

La deuxième exécution de la requête :

-- Execute for user 456, looking for items like '%Book%'
EXECUTE get_items_by_name ('%Book%')
WITH VIEW PARAMETERS (app_user_id := '456');

La clause WITH VIEW PARAMETERS est l'endroit où les paramètres de vue nommée (123, 456) pour la vue sécurisée paramétrée sont fournis, séparément des paramètres positionnels pour la requête préparée (%Laptop%, %Book%).

Enfin, nettoyez l'instruction préparée.

-- Clean up the get_item_by_name prepared statement
DEALLOCATE get_items_by_name;

Restrictions appliquées aux requêtes

Vous trouverez ci-dessous la liste des opérations restreintes pour les requêtes que vous exécutez à l'aide des options décrites dans Interroger une vue sécurisée paramétrée :

  • Lecture seule : seules les instructions SELECT en lecture seule sont autorisées. Les instructions LMD (INSERT, UPDATE, DELETE) et LDD (CREATE, ALTER) sont interdites.
  • Pas d'imbrication : les appels récursifs à execute_parameterized_query sont interdits.
  • Limites des extensions : certaines extensions qui démarrent de nouvelles sessions en arrière-plan (par exemple, dblink, pg_cron) ne sont pas autorisées.
  • Les instructions EXPLAIN ne sont pas autorisées pour éviter toute fuite d'informations potentielle à l'aide des plans de requête.

Lister toutes les vues paramétrées

Utilisez la vue parameterized_views.all_parameterized_views pour lister toutes les vues paramétrées (celles contenant au moins un paramètre nommé $@...).

SELECT * FROM parameterized_views.all_parameterized_views;

Étapes suivantes