Sécurisez et contrôlez l'accès aux données de l'application à l'aide de vues sécurisées paramétrées.

Ce tutoriel explique comment utiliser des vues sécurisées paramétrées dans AlloyDB pour PostgreSQL afin de limiter l'accès des utilisateurs aux tables de base de données. Dans ce tutoriel, vous allez configurer des vues sécurisées paramétrées, configurer des rôles et des droits d'accès à la base de données pour limiter l'accès aux tables de base, et vérifier la sécurité des données. Les vues sécurisées paramétrées permettent de sécuriser les applications qui utilisent des requêtes en langage naturel générées à l'aide de l'API QueryData.

Des exemples sont inclus pour illustrer les fonctionnalités des vues sécurisées paramétrées. Ces exemples sont fournis à des fins de démonstration uniquement.

Que sont les vues sécurisées paramétrées ?

En règle générale, votre application doit s'exécuter à l'aide d'un compte de service disposant du niveau d'accès minimal requis à votre base de données. Par exemple, si votre application ne doit pas écrire dans votre base de données, elle doit utiliser un rôle avec un accès en lecture seule. Vous devez configurer ces contrôles d'accès au niveau de la base de données.

Toutefois, il arrive que votre application ait besoin de contrôles de sécurité plus précis que les autorisations d'accès standard au niveau de la base de données. Les vues sécurisées paramétrées assurent la sécurité des données de l'application et le contrôle des accès aux lignes à l'aide de vues SQL. Elles permettent de s'assurer que les utilisateurs de l'application ne peuvent afficher que les données auxquelles ils sont autorisés à accéder.

Les vues sécurisées paramétrées atténuent les risques de sécurité lors du traitement de requêtes en langage naturel ad hoc ou générées par LLM en paramétrant les vues avec des identifiants d'utilisateur final. Ainsi, les utilisateurs n'accèdent qu'aux données autorisées, quelle que soit la formulation de la requête. Cette approche simplifie également la gestion des utilisateurs en permettant à un seul rôle de base de données de servir tous les utilisateurs de l'application de manière sécurisée. Il n'est donc pas nécessaire de créer des rôles de base de données distincts pour chaque personne afin d'appliquer la sécurité au niveau des lignes.

Objectifs

Créer des vues sécurisées paramétrées avec des paramètres de vue nommés.
Créer le rôle de base de données utilisé par l'application pour se connecter à la base de données et accéder aux vues sécurisées paramétrées.
Accorder au nouveau rôle des autorisations sur les vues sécurisées et révoquer l'accès aux tables de base.
Se connecter à l'aide du nouveau rôle et vérifier que les tables restreintes ne sont pas accessibles.
Exécuter des requêtes sur la vue sécurisée paramétrée à l'aide de la fonction execute_parameterized_query ou de l'API QueryData.

Coûts

Dans ce document, vous utilisez les composants facturables suivants de Google Cloud:

AlloyDB pour PostgreSQL

Obtenez une estimation des coûts en fonction de votre utilisation prévue à l'aide du simulateur de coût.

Les nouveaux utilisateurs de peuvent bénéficier d'un essai sans frais. Google Cloud

Pour éviter de continuer à payer des frais, supprimez les ressources que vous avez créées une fois les tâches décrites dans ce document terminées. Pour en savoir plus, consultez la section Effectuer un nettoyage.

Avant de commencer

Avant de créer un ensemble de contexte, remplissez les conditions préalables suivantes.

Activer la facturation et les API requises

Dans la console Google Cloud , sélectionnez un projet.

Accéder au sélecteur de projet
Assurez-vous que la facturation est activée pour votre projet Google Cloud .
Activez les API Cloud nécessaires pour créer une instance AlloyDB pour PostgreSQL et vous y connecter.
- Activer l'API
1. À l'étape Confirmer le projet, cliquez sur Suivant pour confirmer le nom du projet que vous allez modifier.
2. À l'étape Activer les API, cliquez sur Activer pour activer les éléments suivants :
  - API AlloyDB pour PostgreSQL
  - API Dataplex Universal Catalog

Créer une base de données et s'y connecter

Préparer votre environnement

Pour préparer l'exécution de requêtes sur une vue sécurisée paramétrée, configurez la base de données, les rôles de base de données, l'extension parameterized_view et le schéma d'application.

Activer l'extension requise

Activez l'option de base de données parameterized_views.enabled, qui charge les bibliothèques d'extension requises. Pour en savoir plus, consultez la page Configurer les options de base de données d'une instance.

Configurer la base de données

Créez une base de données nommée database pour les données de l'application et les vues paramétrées. Pour en savoir plus, consultez la page Créer une base de données.

Créer des rôles de base de données, l'extension et le schéma d'application

Dans la Google Cloud console, accédez à la page AlloyDB.

Accéder à AlloyDB
Sélectionnez un cluster dans la liste.
Dans le menu de navigation, cliquez sur AlloyDB Studio.
Connectez-vous à Studio à l'aide de l'authentification postgres.
Cliquez sur Authentifier. Le volet Explorateur affiche la liste des objets de votre base de données.
Cliquez sur Nouvel onglet de l'éditeur SQL ou Nouvel onglet pour ouvrir un nouvel onglet.
Créez l'extension parameterized_views dans la base de données.
```
-- Requires parameterized_views.enabled set to true
CREATE EXTENSION parameterized_views;
```
Lorsque l'extension est créée, le système crée également un schéma nommé parameterized_views 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.

Remarque : Vous devez créer une extension dans chaque base de données dans laquelle des vues paramétrées sont créées.
Connectez-vous en tant qu'utilisateur disposant de droits de superutilisateur, tel que l'utilisateur postgres intégré. Si vous souhaitez créer un superutilisateur, par exemple admin_user, exécutez les commandes suivantes :
```
CREATE ROLE admin_user WITH LOGIN PASSWORD '...';
GRANT ALL PRIVILEGES ON DATABASE database TO admin_user;
```
Pour en savoir plus, consultez CREATE USER.
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 AlloyDB que l'application utilise pour se connecter à la base de données et s'y connecter afin d'exécuter des requêtes avec un accès limité aux fonctions ou objets publics au minimum requis.
```
CREATE ROLE psv_user WITH LOGIN PASSWORD '...';
```
Pour en savoir plus, consultez CREATE USER.
Connectez-vous en tant qu'utilisateur administratif.
```
SET role TO admin_user;
```
Créez le schéma contenant les tables.
```
CREATE SCHEMA schema;
```

Créez les tables et insérez des données.

CREATE TABLE store.checked_items(bag_id INT,timestamp TIMESTAMP, loc_code CHAR(3), scan_type CHAR(1), location TEXT, customer_id INT);

INSERT INTO store.checked_items (bag_id, timestamp, loc_code, scan_type, location, customer_id) VALUES
(101, '2023-10-26 10:00:00', 'ABC', 'I', 'Warehouse A', 123),
(102, '2023-10-26 10:15:30', 'DEF', 'O', 'Loading Dock B', 456),
(103, '2023-10-26 10:30:45', 'GHI', 'I', 'Conveyor Belt 1', 789),
(104, '2023-10-26 11:00:00', 'JKL', 'O', 'Shipping Area C', 101),
(105, '2023-10-26 11:45:15', 'MNO', 'I', 'Sorting Station D', 202),
(106, '2023-10-26 12:00:00', 'PQR', 'O', 'Truck Bay E', 303);

Créer des vues sécurisées paramétrées et configurer des droits d'accès

Pour créer des vues sécurisées paramétrées et configurer les droits d'accès appropriés pour la table de base et les vues, procédez comme suit :

Dans la Google Cloud console, accédez à la page AlloyDB.

Accéder à AlloyDB
Sélectionnez un cluster dans la liste.
Dans le menu de navigation, cliquez sur AlloyDB Studio.
Connectez-vous à Studio et connectez-vous à la database en tant que admin_user.
Cliquez sur Authentifier. Le volet Explorateur affiche la liste des objets de votre base de données.
Cliquez sur Nouvel onglet de l'éditeur SQL ou Nouvel onglet pour ouvrir un nouvel onglet.
Pour fournir un accès limité à la vue, créez une vue sécurisée paramétrée :
```
CREATE VIEW store.secure_checked_items WITH (security_barrier) AS
SELECT bag_id, timestamp, location
FROM store.checked_items t
WHERE customer_id = $@app_end_userid;
```
Remarque : Veillez à utiliser des noms de paramètres uniques, tels que $@app_end_userid, lorsque vous créez les vues paramétrées.

Accorder l'accès à la vue.

GRANT SELECT ON store.secure_checked_items TO psv_user;

Pour accéder à la vue, accordez l'accès au schéma.
```
GRANT USAGE ON SCHEMA store TO psv_user;
```
Révoquez l'accès direct à la table de base.
```
REVOKE ALL PRIVILEGES ON store.checked_items FROM psv_user;
```
Remarque : Le propriétaire de la vue paramétrée doit disposer des droits SELECT sur les tables de base. De plus, l'utilisateur de la vue paramétrée doit disposer des droits USAGE sur le schéma de la vue et SELECT sur la vue. Par exemple, le admin_use r est propriétaire de la vue paramétrée. Il dispose donc des droits SELECT sur les tables de base, tandis que le psv_user doit disposer des droits USAGE sur le schéma store et SELECT sur la vue.
Connectez-vous en tant qu'administrateur admin_user et accordez le rôle psv_user à un utilisateur authentifié par IAM.
```
GRANT psv_user TO "IAM_USER_EMAIL";
```
Remplacez IAM_USER_EMAIL par l'adresse e-mail de votre utilisateur IAM.

Vérifier la sécurité des données

Pour vérifier que les vues sécurisées paramétrées limitent l'accès aux vues désignées, connectez-vous à la base de données en tant qu'utilisateur authentifié par IAM. Dans AlloyDB, les utilisateurs IAM héritent des autorisations des rôles de base de données qui leur sont attribués.

Connectez-vous à la base de données en tant qu'utilisateur authentifié par IAM.

Vérifiez que la table de base n'est pas accessible.

SELECT * FROM store.checked_items;
ERROR:  permission denied for table checked_items

Accédez à la vue sécurisée paramétrée à l'aide de la fonction execute_parameterized_query :

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

Interrogez la vue sécurisée paramétrée à l'aide de la syntaxe SQL et de la requête QueryData avec des paramètres PSV.

    curl -X POST \
      "https://geminidataanalytics.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION:queryData" \
      -H "Authorization: Bearer $(gcloud auth print-access-token)" \
      -H "Content-Type: application/json; charset=utf-8" \
      -d '{
        "prompt": "Show me the checked items.",
        "context": {
          "datasource_references": {
            "alloydb": {
              "database_reference": {
                "project_id": "PROJECT_ID",
                "region": "REGION",
                "cluster_id": "CLUSTER_ID",
                "instance_id": "INSTANCE_ID",
                "database_id": "DATABASE_ID"
              }
            }
          },
          "parameterized_secure_view_parameters": {
            "parameters": {
              "app_end_userid": "303"
            }
          }
        },
        "generation_options": {
          "generate_query_result": true,
          "generate_natural_language_answer": true,
          "generate_explanation": true
        }
      }'

Remplacez les valeurs suivantes :

PROJECT_ID: ID de votre Google Cloud projet.
REGION : région dans laquelle se trouve votre instance AlloyDB pour PostgreSQL.
CLUSTER_ID : ID de votre cluster AlloyDB pour PostgreSQL.
INSTANCE_ID : ID de votre instance AlloyDB pour PostgreSQL.
DATABASE_ID : ID de votre base de données AlloyDB pour PostgreSQL.

Effectuer un nettoyage

Supprimer le cluster

Lorsque vous supprimez le cluster que vous avez créé dans la section Avant de commencer, vous supprimez également tous les objets que vous avez créés.

Dans la Google Cloud console, accédez à la page AlloyDB.

Accéder à AlloyDB
Sélectionnez un cluster dans la liste.
Cliquez sur Supprimer le cluster.
Dans Supprimer le cluster, saisissez le nom du cluster pour confirmer que vous souhaitez le supprimer.

Étape suivante

En savoir plus sur les vues sécurisées paramétrées.
Découvrez comment gérer la sécurité des données d'application à l'aide de vues sécurisées paramétrées.