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

Sélectionnez une version de la documentation :

Ce tutoriel explique comment utiliser des vues sécurisées paramétrées dans AlloyDB Omni pour limiter l'accès des utilisateurs aux vues paramétrées à l'aide d'AlloyDB Omni Studio ou de psql.

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.

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.

Installer une base de données et s'y connecter

  • Installer AlloyDB Omni (pour Kubernetes, pour les conteneurs).
  • Créer un cluster et son instance principale.
  • Se connecter à votre instance (pour Kubernetes, pour les conteneurs).

    • Préparer votre environnement

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

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

    1. Ajoutez "parameterized_views" à shared_preload_libraries.
    2. Activez la fonctionnalité en définissant parameterized_views.enabled=on.
    3. Redémarrez postgres pour que les modifications prennent effet.

    Configurer la base de données

    • Créez une base de données nommée database pour les données d'application et les vues paramétrées.

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

    1. À l'aide de psql, connectez-vous à la base de données en tant qu'utilisateur postgres ou en tant qu'utilisateur disposant de droits de super-utilisateur AlloyDB Omni.

      psql database -U postgres

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

    3. Créez le rôle d'administrateur AlloyDB Omni, 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 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 Omni 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.

      Pour en savoir plus, consultez CREATE USER.

    5. Connectez-vous en tant qu'utilisateur 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 des 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 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 des droits d'accès appropriés pour la table et les vues de base, procédez comme suit :

    1. À l'aide de psql, connectez-vous à la base de données en tant qu'admin_user.

      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. Accordez 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 Omni 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.

      sql psql database -U psv_user

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

      SELECT * FROM schema.checked_items;
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.

    Effectuer un nettoyage

    Pour effectuer un nettoyage, vous pouvez désinstaller l'instance AlloyDB Omni ou la conserver et supprimer les objets individuels.

    Étape suivante