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

Seleziona una versione della documentazione:

Questo tutorial descrive come utilizzare le visualizzazioni sicure parametrizzate in AlloyDB Omni per limitare l'accesso degli utenti alle visualizzazioni parametrizzate utilizzando AlloyDB Omni Studio o psql.

Sono inclusi esempi per illustrare le funzionalità delle visualizzazioni sicure parametrizzate. Questi esempi sono destinati esclusivamente a scopi dimostrativi.

Obiettivi

  • Crea visualizzazioni sicure parametrizzate con parametri di visualizzazione denominati.
  • Crea il ruolo del database utilizzato dall'applicazione per connettersi al database e accedere alle visualizzazioni sicure parametrizzate.
  • Concedi al nuovo ruolo le autorizzazioni per le visualizzazioni 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 visualizzazione sicura parametrizzata utilizzando la funzione execute_parameterized_query.

Installare e connettersi a un database

  1. Installa AlloyDB Omni(per Kubernetes, per container).
  2. Crea un cluster e la relativa istanza principale.
  3. Connettiti all'istanza (per Kubernetes, per container)

Prepara l'ambiente

Per prepararti a eseguire query su una visualizzazione sicura parametrizzata, devi prima configurare le visualizzazioni parametrizzate, il database e i ruoli del database, l'estensione parameterized_view e lo schema dell'applicazione.

Prima di utilizzare le visualizzazioni sicure parametrizzate, devi eseguire le seguenti operazioni una sola volta in ogni nuovo container postgres. Ogni impostazione può essere applicata utilizzando ALTER SYSTEM o modificando direttamente postgresql.conf:

  1. Aggiungi "parameterized_views" a shared_preload_libraries.
  2. Attiva la funzionalità impostando parameterized_views.enabled=on.
  3. Riavvia postgres per applicare le modifiche.

Configura il database

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

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

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

    psql database -U postgres

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

  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 dello schema e non siano in conflitto con le API esistenti.

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

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

    Per saperne di più, consulta CREATE USER.

  4. Crea un nuovo ruolo del database per l'esecuzione di query sulle visualizzazioni sicure parametrizzate. Si tratta di un ruolo AlloyDB Omni che l'applicazione utilizza per connettersi e accedere al database per eseguire query con accesso limitato alle funzioni o agli oggetti pubblici al set minimo richiesto.

    Per saperne di più, consulta CREATE USER.

  5. Connettiti come utente amministratore.

    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 visualizzazioni sicure parametrizzate e configura i privilegi di accesso

Per creare visualizzazioni sicure parametrizzate e configurare i privilegi di accesso appropriati per la tabella di base e le visualizzazioni, segui questi passaggi:

  1. Utilizzando psql, connettiti al database come admin_user.

    psql database -U admin_user

  2. Per fornire un accesso limitato alla visualizzazione, crea una visualizzazione sicura parametrizzata.

    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 visualizzazione.

    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;

Verifica la sicurezza dei dati

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

  1. Connettiti come utente delle 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 visualizzazioni sicure parametrizzate utilizzando il linguaggio naturale.

Esegui la pulizia

Per eseguire la pulizia, puoi disinstallare l'istanza AlloyDB Omni o conservarla ed eliminare i singoli oggetti.

Passaggi successivi