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

---

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

For information about access to this release, see the access request page.

Questo tutorial descrive come utilizzare le viste sicure parametrizzate in AlloyDB per PostgreSQL per limitare l'accesso degli utenti alle viste parametrizzate utilizzando AlloyDB Studio o psql. Sono inclusi esempi per illustrare le funzionalità delle visualizzazioni sicure parametrizzate. Questi esempi sono solo a scopo dimostrativo.

Obiettivi

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

Costi

In questo documento utilizzi i seguenti componenti fatturabili di Google Cloud:

• AlloyDB for PostgreSQL

Per generare una stima dei costi in base all'utilizzo previsto, utilizza il Calcolatore prezzi. I nuovi Google Cloud utenti potrebbero avere diritto a una prova gratuita.

Al termine delle attività descritte in questo documento, puoi evitare la fatturazione continua eliminando le risorse che hai creato. Per ulteriori informazioni, consulta la sezione Pulizia.

Prima di iniziare

Richiedi l'accesso

Prima di poter utilizzare le visualizzazioni sicure parametrizzate, devi richiedere l'accesso alle visualizzazioni sicure parametrizzate di AlloyDB e attendere di ricevere la conferma dell'attivazione prima di iniziare.

Abilita la fatturazione e le API richieste

1. Nella console Google Cloud, seleziona un progetto.

   Vai al selettore dei progetti

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

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

   Attiva l'API

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

   2. Nel passaggio Abilita API, fai clic su Abilita per attivare 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 relativi ruoli, l'estensione parameterized_view e lo schema dell'applicazione.

Configura le visualizzazioni parametrizzate e il database

1. Chiedi al team di AlloyDB di attivare il flag parameterized_views.enabled database, che carica le librerie di estensioni richieste.

2. Crea un database denominato database per i dati dell'applicazione e le viste parametrizzate. Per ulteriori informazioni, vedi Creare un database.

Crea i 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 ulteriori informazioni, consulta 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 in modo che non entrino in conflitto con le API esistenti.

   Nota: devi creare un'estensione in ogni database in cui vengono create le visualizzazioni parametrizzate.

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, consulta CREATE USER.

4. Crea un nuovo ruolo del database per eseguire query su visualizzazioni protette parametrizzate. Si tratta di un ruolo AlloyDB che l'applicazione utilizza per connettersi e accedere al database ed eseguire query con accesso limitato a funzioni o oggetti pubblici, fino all'insieme minimo richiesto.

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

   Per ulteriori informazioni, vedi CREATE USER.

5. Accedi come utente amministrativo.

   SET role TO admin_user;
   

6. Crea lo schema contenente 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 impostare i 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 l'accesso limitato alla visualizzazione, crea una visualizzazione protetta 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 visualizzazioni sicure parametrizzate stiano limitando l'accesso alle visualizzazioni designate, esegui i seguenti comandi come psv_user. Si tratta di un ruolo del database AlloyDB utilizzato dall'applicazione per connettersi e accedere al database per eseguire query.

1. Accedi come utente con visualizzazioni sicure parametrizzate.

   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 tue viste sicure parametrizzate utilizzando il linguaggio naturale dell'IA di AlloyDB.

Esegui la pulizia

Per evitare che al tuo account Google Cloud vengano addebitati costi relativi alle risorse utilizzate in questo tutorial, elimina il progetto che contiene le risorse oppure mantieni il progetto ed elimina le singole risorse.

1. Nella console Google Cloud, vai alla pagina Cluster.

   Vai a Cluster

2. Fai clic sul nome del tuo cluster, my-cluster, nella colonna Nome risorsa.

3. Fai clic su Elimina cluster.

4. In Elimina cluster my-cluster, inserisci my-cluster per confermare che vuoi eliminare il cluster.

5. Fai clic su Elimina.

6. Se hai creato una connessione privata quando hai creato un cluster, vai alla console Google Cloud, alla pagina Networking e fai clic su Elimina rete VPC.

7. Utilizzando AlloyDB Studio o psql, connettiti al database come postgres.

   psql database -U postgres
   

8. Inserisci gli oggetti che hai creato.

   DROP VIEW schema.secure_checked_items;
   DROP TABLE schema.checked_items;
   DROP SCHEMA schema;
   DROP ROLE psv_user;
   DROP ROLE admin_user;
   DROP EXTENSION parameterized_views;
   

Passaggi successivi

• Scopri di più sulle visualizzazioni sicure con parametri.
• Scopri come gestire la sicurezza dei dati delle applicazioni utilizzando viste sicure parametrizzate.