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

Aperçu — Vues sécurisées paramétrées

Cette fonctionnalité est soumise aux "Conditions des offres de pré-DG" de la section "Conditions générales du service" des Conditions spécifiques du service. Les fonctionnalités pré-DG sont disponibles "en l'état" et peuvent avoir une prise en charge limitée. Pour en savoir plus, consultez les descriptions des étapes de lancement.

Les vues sécurisées paramétrées dans AlloyDB pour PostgreSQL vous permettent de limiter l'accès des utilisateurs de l'application à des lignes de base de données spécifiques en fonction de paramètres nommés. Dans ce tutoriel, vous allez créer et interroger une vue sécurisée paramétrée à l'aide d'AlloyDB Studio ou de psql.

Les exemples de ce tutoriel illustrent les fonctionnalités des vues sécurisées paramétrées à des fins de démonstration uniquement.

Objectifs

• Créez des vues paramétrées sécurisées avec des paramètres de vue nommés.
• Créez 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.
• Accordez les autorisations du nouveau rôle aux vues sécurisées et révoquez l'accès aux tables de base.
• Connectez-vous à l'aide du nouveau rôle et vérifiez que les tables restreintes ne sont pas accessibles.
• Exécutez des requêtes sur la vue sécurisée paramétrée à l'aide de la fonction execute_parameterized_query.

Coûts

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

• AlloyDB for 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 Google Cloud peuvent bénéficier d'un essai sans frais.

Une fois que vous avez terminé les tâches décrites dans ce document, supprimez les ressources que vous avez créées pour éviter que des frais vous soient facturés. Pour en savoir plus, consultez la section Effectuer un nettoyage.

Avant de commencer

Activer la facturation et les API requises

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

   Accéder au sélecteur de projet

2. Assurez-vous que la facturation est activée pour votre projet Google Cloud .

3. Activez les APIs Cloud nécessaires pour créer une instance AlloyDB 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

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

1. Créez un cluster et son instance principale.
2. Connectez-vous à votre instance et créez une base de données.

Préparer votre environnement

Pour vous préparer à exécuter des requêtes sur une vue sécurisée paramétrée, vous devez d'abord configurer les vues paramétrées, la base de données et les rôles de base de données, l'extension parameterized_views et le schéma d'application.

Activer l'extension requise

Activez l'indicateur de base de données parameterized_views.enabled, qui charge les bibliothèques d'extensions requises. Pour en savoir plus, consultez Configurer les indicateurs de base de données d'une instance.

Remarque : Après avoir activé l'indicateur de base de données parameterized_views.enabled, votre base de données redémarre pour que ces modifications prennent effet.

Configurer la base de données

Créez une base de données appelée database pour les données d'application et les vues paramétrées. Pour en savoir plus sur la création d'une base de données, consultez 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

1. Pour vous connecter à la base de données en tant qu'utilisateur postgres ou en tant qu'utilisateur disposant des droits de super-utilisateur AlloyDB, utilisez AlloyDB Studio ou psql.

   psql database -U postgres

   Pour en savoir plus, consultez À propos de la gestion des utilisateurs de base de données dans AlloyDB.

2. Créez l'extension parameterized_views dans la base de données.

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

   Lors de la création de l'extension, 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.

3. Créez le rôle d'administrateur AlloyDB, qui possède et gère la base de données.

   CREATE ROLE admin_user WITH LOGIN PASSWORD '...';
   GRANT ALL PRIVILEGES ON DATABASE database TO admin_user;
   

   Pour en savoir plus, consultez les sections sur CREATE USER

4. 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 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 sur la création d'utilisateurs PostgreSQL, consultez CREATE USER.

5. Connectez-vous en tant qu'utilisateur avec accès administrateur.

   SET role TO admin_user;
   

6. Créez le schéma contenant les tables.

   CREATE SCHEMA schema;
   

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

   CREATE TABLE schema.checked_items(bag_id INT,timestamp TIMESTAMP, loc_code CHAR(3), scan_type CHAR(1), location TEXT, customer_id INT);
   
   INSERT INTO schema.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 paramétrées sécurisées et configurer des droits d'accès

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

1. Pour vous connecter à la base de données en tant que admin_user, utilisez AlloyDB Studio ou psql.

   psql database -U admin_user

2. Pour fournir un accès limité à la vue, créez une vue sécurisée paramétrée :

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

3. Accorder l'accès à la vue.

   GRANT SELECT ON schema.secure_checked_items TO psv_user;
   

4. Pour accéder à la vue, accordez l'accès au schéma.

   GRANT USAGE ON SCHEMA schema TO psv_user;
   

5. Révoquez l'accès direct à la table de base.

   REVOKE ALL PRIVILEGES ON schema.checked_items FROM psv_user;
   

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, exécutez les commandes suivantes en tant que psv_user. Il s'agit d'un rôle de base de données AlloyDB que l'application utilise pour se connecter à la base de données et s'y connecter afin d'exécuter des requêtes.

1. Connectez-vous en tant qu'utilisateur de vues sécurisées paramétrées.

   psql database -U psv_user

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

   SELECT * FROM schema.checked_items;
   

   Le résultat ressemble à ce qui suit :

   ERROR:  permission denied for table checked_items
   

3. 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 schema.secure_checked_items',
     param_names => ARRAY ['app_end_userid'],
     param_values => ARRAY ['303']
   );
   

4. Pour améliorer la sécurité des requêtes générées en langage naturel, intégrez vos vues sécurisées paramétrées à l'aide du langage naturel AlloyDB AI.

Effectuer un nettoyage

Pour éviter que les ressources utilisées lors de ce tutoriel soient facturées sur votre compte Google Cloud, supprimez le projet contenant les ressources, ou conservez le projet et supprimez les ressources individuelles.

Si vous souhaitez conserver votre cluster et ne supprimer que les objets de base de données que vous avez créés dans ce tutoriel, exécutez les commandes suivantes :

1. Pour vous connecter à la base de données en tant que postgres, utilisez AlloyDB Studio ou psql :

   psql database -U postgres

2. Supprimez les objets que vous avez créés :

   DROP VIEW schema.secure_checked_items;
   DROP TABLE schema.checked_items;
   DROP SCHEMA schema;
   DROP ROLE psv_user;
   DROP ROLE admin_user;
   DROP EXTENSION parameterized_views;
   

Pour supprimer l'intégralité du cluster AlloyDB et du réseau VPC, procédez comme suit :

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

   Accéder à la page Clusters

2. Dans la colonne Nom de la ressource, cliquez sur le nom de votre cluster, my-cluster.

3. Cliquez sur Supprimer le cluster.

4. Dans Supprimer le cluster my-cluster, saisissez my-cluster pour confirmer que vous souhaitez supprimer votre cluster.

5. Cliquez sur Supprimer.

6. Si vous avez créé une connexion privée lorsque vous avez créé un cluster, accédez à la page Mise en réseau de la console Google Cloud , puis cliquez sur Supprimer le réseau VPC.

Étapes suivantes

• 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.