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

  • Crea viste sicure con parametri con parametri di visualizzazione denominati.
  • Crea il ruolo del database utilizzato dall'applicazione per connettersi al database e accedere alle viste sicure con parametri.
  • Concedi le autorizzazioni del nuovo ruolo alle viste sicure e revoca l'accesso alle tabelle di base.
  • Connettiti utilizzando il nuovo ruolo e verifica che non sia possibile accedere alle tabelle con limitazioni.
  • Esegui query sulla vista sicura con parametri utilizzando la funzione execute_parameterized_query.

Prepara l'ambiente

  1. Abilita il flag del 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);

Crea viste sicure con parametri e configura 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;

Verifica 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