Gestisci la sicurezza dei dati delle applicazioni utilizzando le visualizzazioni sicure parametrizzate di AlloyDB

Le viste sicure parametrizzate in AlloyDB per PostgreSQL consentono di limitare l'accesso alle righe in base a parametri denominati specifici dell'applicazione, come le credenziali utente dell'applicazione. Queste viste estendono le viste PostgreSQL per proteggere il database durante l'esecuzione di query non attendibili applicando automaticamente limitazioni a ogni query.

Per saperne di più, consulta Panoramica delle viste sicure parametrizzate e Proteggi e controlla l'accesso ai dati delle applicazioni utilizzando viste sicure parametrizzate.

Prima di iniziare

Questa pagina presuppone che tu abbia creato un cluster e un'istanza AlloyDB. Per saperne di più, consulta Crea un database.

Prima di utilizzare le viste sicure parametrizzate, devi:

  1. Attiva il flag di database parameterized_views.enabled, che carica le librerie di estensioni richieste. Devi attivare questo flag, anche se è stato attivato in precedenza dal team di AlloyDB. Per saperne di più sull'abilitazione del flag di database, consulta Configura i flag di database di un'istanza.

  2. Utilizza AlloyDB Studio o psql per creare l'estensione parameterized_views in qualsiasi database in cui vuoi creare una vista parametrizzata:

    -- 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 entrino in conflitto con le API esistenti.

Crea una vista sicura parametrizzata

Per creare una vista sicura con parametri, segui questi passaggi:

  1. Esegui il comando DDL CREATE VIEW, come mostrato nell'esempio seguente:

    CREATE VIEW secure_checked_items WITH (security_barrier) AS
SELECT bag_id, timestamp, location
FROM checked_items t
WHERE customer_id = $@app_end_userid;

    Nell'esempio precedente, la vista protetta con parametri consente l'accesso a tre colonne di una tabella denominata checked_items. La visualizzazione limita i risultati alle righe in cui checked_items.customer_id corrisponde a un parametro obbligatorio.

    Utilizza i seguenti attributi:

    • Crea la vista utilizzando l'opzione security_barrier.
    • Per limitare gli utenti dell'applicazione in modo che possano visualizzare solo le righe a cui sono autorizzati ad accedere, aggiungi i parametri obbligatori nella definizione della vista utilizzando la sintassi $@PARAMETER_NAME. Un caso d'uso comune è il controllo del valore di una colonna nella clausola WHERE utilizzando COLUMN = $@PARAMETER_NAME.
    • $@PARAMETER_NAME indica un parametro di visualizzazione denominato. Il suo valore viene fornito quando utilizzi l'API execute_parameterized_query. I parametri di visualizzazione denominata hanno i seguenti requisiti:
      • I parametri della vista con nome devono iniziare con una lettera (a-z).
      • Puoi utilizzare lettere con segni diacritici e lettere non latine, nonché un trattino basso (_).
      • I caratteri successivi possono essere lettere, trattini bassi o cifre (0-9).
      • I parametri di visualizzazione denominati non possono contenere $.
      • I parametri di visualizzazione denominati sono sensibili alle maiuscole. Ad esempio, $@PARAMETER_NAME viene interpretato in modo diverso da $@parameter_name.

  2. Concedi SELECT sulla vista a qualsiasi utente del database autorizzato a eseguire query sulla vista.

  3. Concedi USAGE sullo schema che contiene le tabelle definite nella vista a qualsiasi utente del database autorizzato a eseguire query sulla vista.

Per saperne di più, consulta Proteggi e controlla l'accesso ai dati delle applicazioni utilizzando viste sicure parametrizzate.

Configura la sicurezza per la tua applicazione

Per configurare la sicurezza delle tue applicazioni utilizzando le viste sicure parametrizzate, segui questi passaggi:

  1. Crea le viste parametrizzate sicure come utente amministrativo. Questo utente è un utente del database AlloyDB che esegue operazioni amministrative per l'applicazione, tra cui la configurazione del database e l'amministrazione della sicurezza.

  2. Crea un nuovo ruolo del database per l'esecuzione di query su viste protette parametrizzate. Si tratta di un ruolo del database AlloyDB utilizzato dall'applicazione per connettersi e accedere al database ed eseguire query su viste parametrizzate.

    1. Concedi le autorizzazioni del nuovo ruolo alle viste sicure, che in genere includono i privilegi SELECT per le viste e USAGE per gli schemi.

    2. Limita gli oggetti a cui questo ruolo può accedere al set minimo richiesto di funzioni e oggetti pubblici necessari all'applicazione. Evita di fornire l'accesso a schemi e tabelle non pubblici.

    3. Per ridurre i rischi per la sicurezza, revoca l'accesso da questo ruolo a tutti gli schemi o oggetti sensibili non strettamente richiesti dal ruolo.

    Quando esegui query sulle viste, l'applicazione fornisce i valori dei parametri di visualizzazione richiesti, che sono legati all'identità dell'utente dell'applicazione.

    Per saperne di più sulla creazione di utenti del database, consulta Crea un utente del database.

Esegui query su una vista sicura con parametri

Per eseguire query su una vista protetta con parametri, utilizza una delle seguenti opzioni che meglio supporta il tuo caso d'uso:

  • Basata su JSON: utilizza questa API per eseguire la query in un'unica soluzione e restituire righe JSON.

  • Basata su CURSOR: utilizza questa API quando hai query con tempi di esecuzione più lunghi o quando hai query di grandi dimensioni e vuoi recuperare il risultato in batch. La funzione execute_parameterized_query fornita dall'estensione parameterized_views accetta un nome di cursore.

  • Istruzione PREPARE EXECUTE: utilizzala per le istruzioni preparate che possono essere eseguite più volte con valori parametro diversi.

Per eseguire query su viste sicure parametrizzate, utilizza la funzione execute_parameterized_query() fornita dall'estensione parameterized_views.

API JSON

Esegui la funzione execute_parameterized_query(), che ha la seguente sintassi:

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
)

Sostituisci quanto segue:

  • SQL_QUERY: una query SQL la cui clausola FROM fa riferimento a una o più viste sicure parametrizzate.

  • PARAMETER_NAMES: un elenco di nomi di parametri da passare come stringhe.

  • PARAMETER_VALUES: un elenco di valori parametro da passare.

    • Questo elenco deve avere le stesse dimensioni dell'elenco param_names, dove l'ordine dei valori corrisponde all'ordine dei nomi.

    • Il tipo esatto dei valori viene dedotto dalla query e dalla definizione della vista parametrizzata. Le conversioni di tipo vengono eseguite quando necessario e quando possibile per il valore parametro specificato. In caso di mancata corrispondenza del tipo, viene generato un errore.

La funzione restituisce una tabella di oggetti JSON. Ogni riga della tabella equivale al valore ROW_TO_JSON() della riga del risultato della query originale.

Utilizza il seguente esempio per eseguire una query su una vista sicura con parametri:

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => 'SELECT * FROM secure_checked_items',
    param_names => ARRAY ['app_end_userid'],
    param_values => ARRAY ['40']
)

L'utilizzo di questa API limita le dimensioni del set di risultati in base alle dimensioni espresse in kilobyte (kB) dei risultati e al numero di righe. Puoi configurare questi limiti utilizzando i flag parameterized_views.json_results_max_size e parameterized_views.json_results_max_rows.

API CURSOR

Questa API presenta limitazioni perché dichiara un cursore per la query specificata. Di conseguenza, la query deve essere compatibile con i cursori PostgreSQL. Ad esempio, l'API CURSOR non supporta le istruzioni DO o SHOW.

Inoltre, questa API non limita i risultati in base alle dimensioni o al numero di righe restituite.

Esegui la funzione execute_parameterized_query(), che crea e restituisce un CURSOR con ambito di transazione che utilizzi per recuperare i risultati della query:

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    cursor_name => CURSOR_NAME,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
)

Sostituisci quanto segue:

  • SQL_QUERY: una query SQL la cui clausola FROM fa riferimento a una o più viste sicure con parametri.

  • CURSOR_NAME: il nome del cursore da dichiarare.

  • PARAMETER_NAMES: un elenco di nomi di parametri da passare come stringhe.

  • PARAMETER_VALUES: un elenco di valori parametro da passare. Questo elenco deve avere le stesse dimensioni dell'elenco param_names, dove l'ordine dei valori corrisponde all'ordine dei nomi. Il tipo esatto dei valori viene dedotto dalla query e dalla definizione della vista parametrizzata. Le conversioni di tipo vengono eseguite quando necessario e quando possibile per il valore parametro specificato. In caso di mancata corrispondenza del tipo, viene generato un errore.

Utilizza il seguente esempio per eseguire una query su una vista sicura con parametri:

  -- Start a transaction, which is the default lifetime of a CURSOR
  BEGIN;
  -- Create a cursor called 'mycursor'
  SELECT * FROM parameterized_views.execute_parameterized_query(
   query => 'SELECT * FROM secure_checked_items',
   cursor_name => 'mycursor',
   param_names => ARRAY ['app_end_userid'],
   param_values => ARRAY ['40']
  );

  -- Fetch the results
  FETCH ALL FROM mycursor;
  -- End the transaction, which cleans up the cursor
  END;

Il cursore restituito è un cursore NO SCROLL WITHOUT HOLD. Non puoi utilizzare il cursore per recuperare le righe in modo non sequenziale, ad esempio in ordine inverso. Non puoi utilizzare il cursore al di fuori della transazione che lo ha creato.

Istruzione PREPARE

Utilizza il comando PREPARE .. AS RESTRICTED per creare un'istruzione preparata che fa riferimento a viste parametrizzate. Queste istruzioni preparate supportano i parametri posizionali e applicano varie limitazioni quando le esegui. Per saperne di più su come le viste sicure parametrizzate applicano la sicurezza, consulta Meccanismo di sicurezza.

Questa funzionalità estende i comandi PREPARE e EXECUTE per supportare i parametri delle viste denominate. Utilizza istruzioni preparate per evitare l'overhead di analisi, analisi e riscrittura ogni volta che l'istruzione viene eseguita, il che può comportare un notevole miglioramento delle prestazioni, soprattutto per query eseguite di frequente o complesse. Un'istruzione preparata è un oggetto lato server che può ottimizzare le prestazioni precompilando e archiviando un'istruzione SQL parametrizzata per l'esecuzione successiva.

Questa API presenta limitazioni perché l'istruzione deve essere consentita in un'istruzione PREPARE, il che significa che sono supportate solo le istruzioni SELECT e VALUES.

Inoltre, questa API non limita i risultati in base alle dimensioni o al numero di righe restituite.

Per creare un'istruzione preparata che fa riferimento a viste parametrizzate, esegui il comando PREPARE .. AS RESTRICTED:

PREPARE pquery (POSITIONAL_PARAM_TYPES)
        AS RESTRICTED SQL_QUERY;
EXECUTE pquery (POSITIONAL_PARAM_VALUES)
      WITH VIEW PARAMETERS (VIEW_PARAM_NAME1 = VIEW_PARAM_VALUE1[, ...]);

Sostituisci quanto segue:

  • POSITIONAL_PARAM_TYPES: uno o più tipi di dati dei parametri posizionali utilizzati nella query RESTRICTED.

  • SQL_QUERY: una query SQL che può fare riferimento a viste parametrizzate.

  • POSITIONAL_PARAM_VALUES: i valori effettivi che vengono sostituiti ai parametri posizionali definiti nell'istruzione PREPARE.

  • VIEW_PARAM_NAME: il nome del parametro previsto dalle viste parametrizzate a cui fa riferimento la query RESTRICTED.

  • VIEW_PARAM_VALUE: i valori effettivi passati ai parametri VIEW_PARAM_NAME corrispondenti delle viste parametrizzate.

Per includere i parametri in un'istruzione preparata, fornisci un elenco di tipi di dati nell'istruzione PREPARE. Nell'istruzione che prepari, fai riferimento ai parametri in base alla posizione utilizzando, ad esempio, $1 e $2.

Utilizza il comando EXECUTE .. WITH VIEW PARAMETERS per eseguire un'istruzione preparata in precedenza che hai creato utilizzando il comando PREPARE .. AS RESTRICTED. Se l'istruzione PREPARE che ha creato l'istruzione ha specificato parametri posizionali, devi passare un insieme di parametri compatibili all'istruzione EXECUTE. Devi passare tutti i parametri della vista con nome richiesti dalle viste parametrizzate nella clausola WITH VIEW PARAMETERS.

Utilizza il seguente esempio per eseguire una query su una vista sicura con parametri:

PREPARE pquery (timestamp) AS RESTRICTED SELECT * FROM secure_checked_items WHERE timestamp > $1;

EXECUTE pquery (current_date - 1) WITH VIEW PARAMETERS (app_end_userid = 40);
EXECUTE pquery (current_date - 30) WITH VIEW PARAMETERS (app_end_userid = 40);

Restrizioni applicate alle query

Di seguito è riportato l'insieme di operazioni limitate per le query che esegui utilizzando le opzioni descritte in Esegui query su una vista protetta con parametri:

  • È vietata qualsiasi chiamata ricorsiva di qualsiasi API, execute_parameterized_query o tramite l'utilizzo di EXECUTE .. WITH VIEW PARAMETERS, in modo che vengano utilizzati solo i valori specificati dall'applicazione. Questa limitazione impedisce inoltre l'utilizzo della query per aggirare l'envelope di sicurezza dell'insieme di valori parametro specificato.

  • Alcune estensioni che avviano una nuova sessione in background non sono consentite, tra cui le estensioni dblink, pg_cron e pg_background.

  • I seguenti costrutti di query sono consentiti o limitati:

    • Sono consentite istruzioni SELECT di sola lettura.

    • Sono consentite istruzioni SHOW di sola lettura, istruzioni CALL e istruzioni DO.

    • Le istruzioni DML come INSERT, UPDATE e DELETE non sono consentite.

    • Le istruzioni DDL come CREATE TABLE e ALTER TABLE non sono consentite.

    • Altri tipi di istruzioni come LOAD, SET, CLUSTER, LOCK, CHECKPOINT e EXPLAIN non sono consentiti.

  • Le istruzioni EXPLAIN non sono consentite per impostazione predefinita per evitare la possibilità di attacchi di canali nascosti utilizzando i piani di query. Per saperne di più, consulta Canale nascosto. Tuttavia, per eseguire istruzioni EXPLAIN, qualsiasi utente del database con privilegi di super utente AlloyDB può impostare il GUC a livello di sessione parameterized_views.enable_explain su on.

  • Le viste protette parametrizzate forniscono impostazioni per aiutarti a gestire le risorse utilizzate dalle API per eseguire query sulle viste parametrizzate, ad esempio parameterized_views.statement_timeout. Per ulteriori informazioni, consulta Flag di database supportati.

Elenca tutte le viste parametrizzate

Utilizza l'estensione parameterized_views per elencare tutte le viste parametrizzate nel database utilizzando la vista all_parameterized_views. L'output di questa vista è lo stesso della vista del catalogo pg_views, ma all_parameterized_views elenca solo le viste con parametri di vista denominati.

Per elencare le viste parametrizzate, esegui questa query:

SELECT * FROM parameterized_views.all_parameterized_views;

L'output è simile al seguente:

schemaname |       viewname       | viewowner |                       definition
-----------+----------------------+-----------+---------------------------------------------------------
public     | secure_checked_items | postgres  |  SELECT checked_items.bag_id,                          +
           |                      |           |     checked_items."timestamp",                         +
           |                      |           |     checked_items.location                             +
           |                      |           |    FROM checked_items                                  +
           |                      |           |   WHERE (checked_items.customer_id = $@app_end_userid);

Per elencare una vista parametrizzata in all_parameterized_views, assicurati che la vista parametrizzata contenga almeno un parametro di vista denominato nella sua definizione.

Passaggi successivi