Proteggi e controlla l'accesso ai dati dell'applicazione utilizzando viste sicure parametrizzate

Questo tutorial descrive come utilizzare le viste sicure con parametri in Cloud SQL per PostgreSQL per limitare l'accesso degli utenti alle viste con parametri utilizzando Cloud SQL Studio o psql.

Obiettivi

  • Creare viste sicure con parametri con parametri di visualizzazione denominati.
  • Creare il ruolo del database utilizzato dall'applicazione per connettersi al database e accedere alle viste sicure con parametri.
  • Concedere le autorizzazioni del nuovo ruolo alle viste sicure e revocare l'accesso alle tabelle di base.
  • Connettersi utilizzando il nuovo ruolo e verificare che non sia possibile accedere alle tabelle con limitazioni.
  • Eseguire query sulla vista sicura con parametri utilizzando la funzione execute_parameterized_query.

prepara l'ambiente

  1. Abilita il flag di database cloudsql.enable_parameterized_views per l'istanza Cloud SQL. Questa modifica del flag richiede il riavvio del database.

  2. Connettiti al database come utente postgres.

    psql -U postgres

  3. Crea l'estensione parameterized_views nel database.

    CREATE EXTENSION parameterized_views;

  4. Crea un nuovo ruolo del database per l'esecuzione delle query.

    CREATE ROLE psv_user WITH LOGIN PASSWORD 'PASSWORD';

  5. Crea uno schema e una tabella per i dati dell'applicazione.

    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);

Creare viste sicure con parametri e configurare i privilegi di accesso

  1. Crea una vista sicura con parametri:

    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. Concedi l'accesso alla vista e allo schema al ruolo dell'applicazione.

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

  3. Revoca l'accesso diretto alla tabella di base.

    REVOKE ALL PRIVILEGES ON app_schema.items FROM psv_user;

Verificare la sicurezza dei dati

  1. Connettiti come psv_user.

    psql -U psv_user -d postgres

  2. Verifica che non sia possibile accedere alla tabella di base.

    SELECT * FROM app_schema.items;
-- ERROR:  permission denied for table items

  3. Accedi alla vista sicura con parametri utilizzando la funzione 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']
);

    Il risultato deve includere solo gli elementi in cui owner_id è 123.

Passaggi successivi