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

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

Sono inclusi esempi per illustrare le funzionalità delle viste sicure con parametri. Questi esempi sono forniti solo a scopo dimostrativo.

Prerequisiti

Abilita la fatturazione e le API richieste

  1. Nella console Google Cloud , seleziona un progetto.

    Vai al selettore dei progetti

  2. Verifica che la fatturazione sia attivata per il tuo progetto Google Cloud .

  3. Abilita le API Cloud necessarie per creare e connetterti ad AlloyDB per PostgreSQL.

    Abilita l'API

    1. Nel passaggio Conferma progetto, fai clic su Avanti per confermare il nome del progetto a cui apporterai le modifiche.

    2. Nel passaggio Abilita API, fai clic su Abilita per abilitare quanto segue:

      • API AlloyDB

Creare e connettersi a un database

  1. Crea un cluster e la relativa istanza principale.
  2. Connettiti all'istanza e crea un database.

prepara l'ambiente

Per prepararti a eseguire query su una vista sicura con parametri, devi prima configurare le viste con parametri, il database e i ruoli del database, l'estensione parameterized_view e lo schema dell'applicazione.

Attiva l'estensione richiesta

Attiva il flag di database parameterized_views.enabled, che carica le librerie di estensioni richieste. Per saperne di più, consulta Configurare i flag di database di un'istanza.

Configurare il database

  • Crea un database denominato database per i dati dell'applicazione e le viste parametrizzate. Per saperne di più, vedi Creare un database.

Crea ruoli di database, l'estensione e lo schema dell'applicazione

  1. Utilizzando AlloyDB Studio o psql, connettiti al database come utente postgres o come utente con privilegi di superutente AlloyDB.

    psql database -U postgres

    Per saperne di più, consulta la sezione Informazioni sulla gestione degli utenti del database in AlloyDB.

  2. Crea l'estensione parameterized_views nel database.

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

    Quando viene creata l'estensione, il sistema crea anche uno schema denominato parameterized_views in modo che le API siano contenute nello spazio dei nomi di questo schema e non entrino in conflitto con le API esistenti.

  3. Crea il ruolo amministrativo AlloyDB, che possiede e gestisce il database.

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

    Per ulteriori informazioni, vedi CREATE USER.

  4. Crea un nuovo ruolo del database per l'esecuzione di query su viste sicure con parametri. Si tratta di un ruolo AlloyDB che l'applicazione utilizza per connettersi e accedere al database per eseguire query con accesso limitato a funzioni o oggetti pubblici al set minimo richiesto.

    CREATE ROLE psv_user WITH LOGIN PASSWORD '...';

    Per ulteriori informazioni, vedi CREATE USER.

  5. Connettiti come utente amministrativo.

    SET role TO admin_user;

  6. Crea lo schema che contiene le tabelle.

    CREATE SCHEMA schema;

  7. Crea le tabelle e inserisci i dati.

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

Crea viste parametrizzate sicure e configura i privilegi di accesso

Per creare viste parametrizzate sicure e configurare privilegi di accesso appropriati per la tabella di base e le viste:

  1. Utilizzando AlloyDB Studio o psql, connettiti al database come admin_user.

    psql database -U admin_user

  2. Per fornire un accesso limitato alla vista, crea una vista sicura con parametri:

    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. Concedi l'accesso alla vista.

    GRANT SELECT ON schema.secure_checked_items TO psv_user;

  4. Per accedere alla visualizzazione, concedi l'accesso allo schema.

    GRANT USAGE ON SCHEMA schema TO psv_user;

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

    REVOKE ALL PRIVILEGES ON schema.checked_items FROM psv_user;

Verificare la sicurezza dei dati

Per verificare che le viste sicure con parametri limitino l'accesso alle viste designate, esegui i seguenti comandi come psv_user. Si tratta di un ruolo del database AlloyDB che l'applicazione utilizza per connettersi e accedere al database per eseguire query.

  1. Connettiti come utente di viste sicure con parametri.

    psql database -U psv_user

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

    SELECT * FROM schema.checked_items;
ERROR:  permission denied for table checked_items

  3. Accedi alla visualizzazione sicura parametrizzata utilizzando la funzione 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. Per migliorare la sicurezza delle query generate in linguaggio naturale, integra le viste sicure con parametri utilizzando il linguaggio naturale di AlloyDB AI.