Gestisci la sicurezza dei dati delle applicazioni utilizzando le viste sicure con parametri di AlloyDB Omni

Select a documentation version:

Puoi utilizzare le viste sicure con parametri in AlloyDB Omni per limitare l'accesso ai dati in base a parametri denominati specifici dell'applicazione, come le credenziali utente dell'applicazione. Le viste sicure con parametri migliorano la sicurezza e il controllo dell'accesso estendendo la funzionalità delle viste PostgreSQL. Queste viste riducono anche i rischi di esecuzione di query non attendibili dalle applicazioni applicando automaticamente le restrizioni a qualsiasi query eseguita.

Per ulteriori informazioni, consulta Panoramica delle viste sicure con parametri e Proteggere e controllare l'accesso ai dati delle applicazioni utilizzando le viste sicure con parametri.

Prima di iniziare

Il supporto delle viste con parametri di AlloyDB AI viene fornito tramite parameterized_views, un'estensione di AlloyDB per PostgreSQL.

Questa pagina presuppone che tu abbia installato AlloyDB Omni. Consulta Install AlloyDB Omni su Linux.

Prima di utilizzare le viste sicure con parametri, devi eseguire i seguenti passaggi una sola volta nel tuo ambiente Linux. 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 il server PostgreSQL per applicare le modifiche.

    -- See the current shared_preload_libraries
SHOW shared_preload_libraries;
ALTER SYSTEM SET shared_preload_libraries="...,parameterized_views";
ALTER SYSTEM SET parameterized_views.enabled=on;

  4. Utilizza psql per creare l'estensione parameterized_views in qualsiasi database in cui vuoi creare una vista con parametri:

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

Creare una vista sicura con parametri

Per creare una vista sicura con parametri:

  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 sicura con parametri consente l'accesso a tre colonne di una tabella denominata checked_items. La vista 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 WHERE clausola utilizzando COLUMN = $@PARAMETER_NAME.
    • $@PARAMETER_NAME indica un parametro di visualizzazione denominato. Il valore viene fornito quando utilizzi l'API execute_parameterized_query. I parametri di visualizzazione denominati hanno i seguenti requisiti:
      • I parametri di visualizzazione denominati 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 alla vista a qualsiasi utente del database autorizzato a eseguire query sulla vista.

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

Per ulteriori informazioni, consulta Proteggere e controllare l'accesso ai dati delle applicazioni utilizzando le viste sicure con parametri.

Configurare la sicurezza per l'applicazione

Per configurare la sicurezza per le applicazioni utilizzando le viste sicure con parametri:

  1. Crea le viste sicure con parametri come utente amministratore. Questo utente è un utente del database AlloyDB Omni 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 sulle viste sicure con parametri. Si tratta di un ruolo del database AlloyDB Omni che l'applicazione utilizza per connettersi e accedere al database ed eseguire query sulle viste con parametri.
  3. Concedi al nuovo ruolo le autorizzazioni per le viste sicure, che in genere includono i privilegi SELECT per le viste e USAGE per gli schemi.
  4. Limita gli oggetti a cui questo ruolo può accedere al set minimo richiesto di funzioni e oggetti pubblici di cui l'applicazione ha bisogno. Evita di fornire l'accesso a schemi e tabelle non pubblici.
  5. Quando esegui query sulle viste, l'applicazione fornisce i valori dei parametri di visualizzazione obbligatori, che sono collegati all'identità dell'utente dell'applicazione.

Eseguire query su una vista sicura con parametri

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

  • Basata su JSON: utilizza questa API per eseguire la query in un'unica operazione e restituire righe JSON.
  • Basata su CURSORE: utilizza questa API quando hai query a esecuzione prolungata o query di grandi dimensioni e vuoi recuperare il risultato in batch. La funzione execute_parameterized_query fornita dall'estensione parameterized_views accetta un nome del cursore.
  • Istruzione PREPARE EXECUTE: utilizza questa istruzione per le istruzioni preparate che possono essere eseguite più volte con valori di parametri diversi.

Per eseguire query sulle viste sicure con parametri, utilizza la funzione execute_parameterized_query() fornita dall'estensione parameterized_views.

API JSON

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.

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

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 con parametri.
  • PARAMETER_NAMES: un elenco di nomi di parametri da passare come stringhe.
  • PARAMETER_VALUES: un elenco di valori di parametri da passare.
    • Questo elenco deve avere le stesse dimensioni dell'elenco param_names, in cui l'ordine dei valori corrisponde all'ordine dei nomi.
    • Il tipo esatto dei valori viene dedotto dalla query e dalla definizione della vista con parametri. Le conversioni di tipo vengono eseguite quando necessario e quando possibile per il valore parametro specificato. In caso di mancata corrispondenza dei tipi, viene generato un errore.

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

Utilizza l'esempio seguente per eseguire 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 parameterized_views.json_results_max_size e parameterized_views.json_results_max_rows.

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.

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

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 con parametri.
  • PARAMETER_NAMES: un elenco di nomi di parametri da passare come stringhe.
  • PARAMETER_VALUES: un elenco di valori di parametri da passare.
  • Questo elenco deve avere le stesse dimensioni dell'elenco param_names, in cui l'ordine dei valori corrisponde all'ordine dei nomi.
  • Il tipo esatto dei valori viene dedotto dalla query e dalla definizione della vista con parametri. Le conversioni di tipo vengono eseguite quando necessario e quando possibile per il valore parametro specificato. In caso di mancata corrispondenza dei tipi, viene generato un errore.

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

Utilizza l'esempio seguente per eseguire 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 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.

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

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 con parametri.
  • PARAMETER_NAMES: un elenco di nomi di parametri da passare come stringhe.
  • PARAMETER_VALUES: un elenco di valori di parametri da passare.
    • Questo elenco deve avere le stesse dimensioni dell'elenco param_names, in cui l'ordine dei valori corrisponde all'ordine dei nomi.
    • Il tipo esatto dei valori viene dedotto dalla query e dalla definizione della vista con parametri. Le conversioni di tipo vengono eseguite quando necessario e quando possibile per il valore parametro specificato. In caso di mancata corrispondenza dei tipi, viene generato un errore.

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

Utilizza l'esempio seguente per eseguire 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 parameterized_views.json_results_max_size e parameterized_views.json_results_max_rows.

Istruzione PREPARE

Utilizza il comando PREPARE .. AS RESTRICTED per creare un'istruzione preparata che fa riferimento alle viste con parametri. Queste istruzioni preparate supportano i parametri posizionali e applicano varie restrizioni quando le esegui. Per ulteriori informazioni, consulta Meccanismo di sicurezza.

Questa funzionalità estende i comandi PREPARE ed EXECUTE per supportare i parametri di visualizzazione denominati. Utilizza le istruzioni preparate per evitare il sovraccarico di analisi, analisi e riscrittura ogni volta che l'istruzione viene eseguita, il che può comportare un aumento significativo delle prestazioni, soprattutto per le query complesse o eseguite di frequente. Un'istruzione preparata è un oggetto lato server che può ottimizzare le prestazioni precompilando e archiviando un'istruzione SQL con parametri 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.

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

Per creare un'istruzione preparata che fa riferimento alle viste con parametri, esegui il comando PREPARE .. AS RESTRICTED:

PREPARE pquery (/POSITIONAL_PARAM_TYPES/)
        AS RESTRICTED query % a query that may refer to parameterized views
EXECUTE pquery (/POSITIONAL_PARAM_VALUES/)
      WITH VIEW PARAMETERS (VIEW_PARAM_NAME1 = VIEW_PARAM_VALUE1[, ...]);

Sostituisci quanto segue:

  • POSITIONAL_PARAM_TYPES: uno o più parametri posizionali utilizzati nella query RESTRICTED.
  • POSITIONAL_PARAM_VALUES: i valori effettivi sostituiti ai parametri posizionali definiti nell'istruzione PREPARE.
  • VIEW_PARAM_NAME: il nome del parametro previsto dalle viste con parametri a cui viene fatto riferimento nella query RESTRICTED.
  • VIEW_PARAM_VALUE: i valori effettivi passati ai parametri viewParamName corrispondenti delle viste con parametri.

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 creata 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 di visualizzazione denominati richiesti dalle viste con parametri nella clausola WITH VIEW PARAMETERS.

Utilizza l'esempio seguente per eseguire 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 imposte alle query

Di seguito è riportato l'insieme di operazioni con restrizioni per le query eseguite utilizzando le opzioni descritte in Eseguire query su una vista sicura con parametri:

  • È vietata qualsiasi chiamata ricorsiva di qualsiasi API (execute_parameterized_query o utilizzando EXECUTE .. WITH VIEW PARAMETERS), in modo che vengano utilizzati solo i valori specificati dall'applicazione. Questa restrizione impedisce anche che la query venga utilizzata per aggirare l'envelope di sicurezza dell'insieme di valori dei parametri specificato.
  • Alcune estensioni che avviano una nuova sessione in background non sono consentite, tra cui le estensioni dblink, pg_cron e pg_background.

  • Di seguito è riportato l'insieme di costrutti di query consentiti con restrizioni:

    • Sono consentite le istruzioni SELECT di sola lettura.
    • Sono consentite le istruzioni SHOW, CALL e DO di sola lettura.
    • 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 evitare la possibilità di attacchi di canali nascosti utilizzando i piani di query. Per ulteriori informazioni, consulta Canale nascosto.

  • Le viste sicure con parametri forniscono impostazioni che ti aiutano a gestire le risorse utilizzate dalle API per eseguire query sulle viste con parametri, ad esempio parameterized_views.statement_timeout. Per ulteriori informazioni, consulta Flag di AlloyDB per PostgreSQL.

Elencare tutte le viste con parametri

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

Per elencare le viste con parametri, utilizza l'esempio seguente:

postgres=# select * from parameterized_views.all_parameterized_views ;
schemaname |      viewname      | viewowner |                       definition
-----------+--------------------+-----------+---------------------------------------------------------
public     | checked_items_view | 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 con parametri in all_parameterized_views, assicurati che la vista con parametri contenga almeno un parametro di visualizzazione denominato nella sua definizione.

Passaggi successivi