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 Cloud SQL pour PostgreSQL afin de limiter l'accès des utilisateurs aux vues paramétrées à l'aide de Cloud SQL Studio ou de psql.

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.

Préparer votre environnement

  1. Activez l'indicateur de base de données cloudsql.enable_parameterized_views pour votre instance Cloud SQL. Cette modification nécessite un redémarrage de la base de données.

  2. Connectez-vous à votre base de données en tant qu'utilisateur postgres.

    psql -U postgres

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

    CREATE EXTENSION parameterized_views;

  4. Créez un rôle de base de données pour exécuter des requêtes.

    CREATE ROLE psv_user WITH LOGIN PASSWORD 'PASSWORD';

  5. Créez un schéma et une table pour les données de l'application.

    CREATE SCHEMA app_schema;
CREATE TABLE app_schema.items(item_id INT, item_name TEXT, description TEXT, owner_id INT);

INSERT INTO app_schema.items (item_id, item_name, description, owner_id) VALUES
(1, 'Book', 'A great read', 123),
(2, 'Laptop', 'Work machine', 456),
(3, 'Pencil', 'For writing', 123);

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

  1. Créez une vue sécurisée paramétrée :

    CREATE VIEW app_schema.user_items_view WITH (security_barrier) AS
SELECT item_id, item_name, description
FROM app_schema.items
WHERE owner_id = $@current_user_id;

  2. Accordez l'accès à la vue et au schéma au rôle de l'application.

    GRANT USAGE ON SCHEMA app_schema TO psv_user;
GRANT SELECT ON app_schema.user_items_view TO psv_user;

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

    REVOKE ALL PRIVILEGES ON app_schema.items FROM psv_user;

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

  1. Connectez-vous en tant que psv_user.

    psql -U psv_user -d postgres

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

    SELECT * FROM app_schema.items;
-- ERROR:  permission denied for table 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 app_schema.user_items_view',
  param_names => ARRAY ['current_user_id'],
  param_values => ARRAY ['123']
);

    Le résultat ne doit inclure que les éléments où owner_id est 123.

Étape suivante