Utilizzare viste sicure con parametri

Questo documento descrive come utilizzare le viste sicure parametrizzate in Cloud SQL per PostgreSQL, che consentono di 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 econtrollo dell'accessoo estendendo la funzionalità delle viste PostgreSQL. Queste visualizzazioni riducono anche i rischi di esecuzione di query non attendibili dalle applicazioni applicando automaticamente una serie di restrizioni a qualsiasi query eseguita.

Per saperne di più, consulta Viste sicure con parametri in Cloud SQL e Proteggere e controllare l'accesso ai dati delle applicazioni utilizzando viste sicure con parametri.

Prima di iniziare

Questo documento presuppone che tu abbia già creato un'istanza Cloud SQL per PostgreSQL.

Prima di poter utilizzare le viste sicure parametrizzate, devi:

  1. Attiva il flag di database cloudsql.enable_parameterized_views per l'istanza Cloud SQL. Questa modifica del flag richiede il riavvio del database. Per saperne di più, consulta Configura i flag di database.

  2. Utilizza Cloud SQL Studio o psql per creare l'estensione parameterized_views in qualsiasi database in cui viene creata una vista parametrizzata:

    -- Requires cloudsql.enable_parameterized_views set to on
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.

Creare una vista sicura con parametri

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

  1. Esegui il comando DDL CREATE VIEW utilizzando l'opzione security_barrier:

    CREATE VIEW VIEW_NAME WITH (security_barrier) AS
SELECT COLUMN_NAME, COLUMN_NAME_N
FROM TABLE_NAME ALIAS
WHERE CONDITION;

    Sostituisci quanto segue:

    • VIEW_NAME: il nome della vista sicura con parametri
    • TABLE_NAME: il nome della tabella da utilizzare nella visualizzazione sicura con parametri
    • ALIAS: l'alias del nome della tabella da utilizzare nella vista sicura con parametri
    • COLUMNNAME o COLUMN_NAMEN: il nome della colonna o delle colonne della tabella da utilizzare nella vista sicura parametrizzata

    • CONDITION: l'istruzione di condizione utilizzata per limitare gli utenti dell'applicazione in modo che possano visualizzare solo le righe a cui è consentito l'accesso. Aggiungi i parametri obbligatori utilizzando la sintassi $@PARAMETER_NAME nella clausola WHERE. Un caso d'uso comune è controllare il valore di una colonna utilizzando WHERE COLUMN = $@PARAMETER_NAME.

      $@PARAMETER_NAME indica un parametro della visualizzazione denominata. Il suo valore viene fornito quando utilizzi l'API execute_parameterized_query. I parametri della visualizzazione denominata devono soddisfare i seguenti requisiti:

      • I parametri della visualizzazione denominata devono iniziare con una lettera (a-z) o un trattino basso (_).
      • I caratteri successivi possono essere lettere, trattini bassi o cifre (0-9).
      • I parametri della visualizzazione denominata sono sensibili alle maiuscole. Ad esempio, $@PARAMETER_NAME viene interpretato in modo diverso da $@parameter_name.

      Di seguito è riportato un esempio di creazione di una vista sicura con parametri che utilizza un parametro di vista denominato:

      CREATE VIEW user_specific_items WITH (security_barrier) AS
SELECT item_id, item_name, description, owner_id
FROM items t
WHERE owner_id = $@app_user_id;

  2. Concedi SELECT alla vista a qualsiasi utente del database autorizzato a interrogare la 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 saperne di più, vedi Proteggere e controllare l'accesso ai dati delle applicazioni utilizzando viste sicure parametrizzate.

Configura la sicurezza per la tua applicazione

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

  1. Crea la vista sicura con parametri come utente amministrativo. Si tratta di un utente del database Cloud SQL che esegue operazioni amministrative per l'applicazione.

  2. Crea un nuovo ruolo del database per l'esecuzione di query su viste sicure parametrizzate. Si tratta di un ruolo del database Cloud SQL che l'applicazione utilizza per connettersi e accedere al database.

    1. Concedi le nuove autorizzazioni del ruolo alle visualizzazioni protette, che in genere includono i privilegi SELECT per le visualizzazioni 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.

    Quando esegui una query sulle visualizzazioni, l'applicazione fornisce i valori dei parametri di visualizzazione obbligatori, che sono collegati all'identità dell'utente dell'applicazione.

    Per saperne di più, consulta Creare e gestire utenti.

Esegui query su una vista sicura con parametri

Per eseguire query su una vista sicura con parametri, utilizza una delle seguenti opzioni:

  • Basata su JSON: utilizza questa API per eseguire la query in un'unica operazione e restituire righe JSON.
  • Basata sul CURSORE: utilizza questa API quando hai query di esecuzione più lunga o quando hai query di grandi dimensioni e vuoi recuperare il risultato in batch.
  • Istruzione PREPARE .. AS RESTRICTED: utilizza PREPARE .. AS RESTRICTED per definire il piano di query, quindi esegui EXECUTE ... WITH VIEW PARAMETERS (...) per eseguirlo con parametri specifici per la visualizzazione.

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

La funzione restituisce una tabella di oggetti JSON. Ogni riga della tabella equivale al valore row_to_json() della riga dei risultati della query originale.

L'utilizzo di questa API limita le dimensioni del set di risultati in base alle dimensioni (in KB) 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

Esegui la funzione execute_parameterized_query() con un nome di cursore, che crea e restituisce un cursore con ambito di transazione:

-- Must be in a transaction block
BEGIN;

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

FETCH ALL FROM CURSOR_NAME;

END;

Istruzioni preparate

Il metodo delle istruzioni preparate consente di preparare un piano di query una sola volta e poi di eseguirlo più volte con valori diversi sia per i parametri posizionali della query sia per i parametri denominati della vista.

Per utilizzare un'istruzione preparata:

  1. Crea l'istruzione preparata.

    -- Prepare the statement
PREPARE PREPARED_STATEMENT_NAME (QUERY_PARAM_TYPE_1, QUERY_PARAM_TYPE_N)
AS RESTRICTED SQL_QUERY;

    Sostituisci quanto segue:

    • PREPARED_STATEMENT_NAME: il nome dell'istruzione preparata
    • QUERY_PARAM_TYPE_N: il tipo di dati del parametro di query, ad esempio TEXT
    • SQL_QUERY: la query SQL da eseguire nell'ambito dell'istruzione preparata con il valore o i valori specificati

  2. Esegui l'istruzione preparata.

    -- Execute the statement with query parameters and view parameters
EXECUTE PREPARED_STATEMENT_NAME (QUERY_VALUE_1, QUERY_VALUE_N)
WITH VIEW PARAMETERS (PARAMETER_NAME_1 := 'PARAMETER_VALUE_1', PARAMETER_NAME_N := 'PARAMETER_VALUE_N');

    Sostituisci quanto segue:

    • PREPARED_STATEMENT_NAME: il nome dell'istruzione preparata.
    • QUERY_VALUE_N: il valore o i valori da fornire come parametro o parametri alla query SQL
    • PARAMETER_NAME_N: il nome del parametro o dei parametri della vista denominata che hai definito quando hai creato la vista sicura con parametri. Crei il parametro della visualizzazione denominata dalla colonna della tabella.
    • PARAMETER_VALUE_N: il valore o i valori del parametro della vista denominata che limita la vista sicura parametrizzata alle righe associate al valore.

  3. Pulisci l'istruzione preparata.

    -- Cleanup
DEALLOCATE PREPARED_STATEMENT_NAME>;

Il seguente esempio utilizza una vista sicura con parametri denominata user_specific_items che richiede il parametro della vista denominato $@app_user_id.

-- Prepare a query with a positional parameter $1 for the item_name pattern
PREPARE get_items_by_name (TEXT) AS RESTRICTED
SELECT item_id, item_name FROM user_specific_items
WHERE item_name LIKE $1;

Dopo aver creato l'istruzione preparata, puoi eseguirla più volte e assegnare valori diversi sia per la query sia per il parametro della vista denominata.

Ad esempio, la prima esecuzione della query:

-- Execute for user 123, looking for items like '%Laptop%'
EXECUTE get_items_by_name ('%Laptop%')
WITH VIEW PARAMETERS (app_user_id := '123');

La seconda esecuzione della query:

-- Execute for user 456, looking for items like '%Book%'
EXECUTE get_items_by_name ('%Book%')
WITH VIEW PARAMETERS (app_user_id := '456');

La clausola WITH VIEW PARAMETERS è il punto in cui vengono forniti i parametri della vista denominata (123, 456) per la vista sicura con parametri, separati dai parametri posizionali per la query preparata (%Laptop%, %Book%).

Infine, pulisci l'istruzione preparata.

-- Clean up the get_item_by_name prepared statement
DEALLOCATE get_items_by_name;

Restrizioni per le query

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

  • Sola lettura: sono consentite solo istruzioni SELECT di sola lettura. DML (INSERT, UPDATE, DELETE) e DDL (CREATE, ALTER) sono vietati.
  • Nessuna nidificazione: sono vietate le chiamate ricorsive a execute_parameterized_query.
  • Limiti delle estensioni: non sono consentite alcune estensioni che avviano nuove sessioni in background (ad es. dblink, pg_cron).
  • Le istruzioni EXPLAIN non sono consentite per impedire la potenziale perdita di informazioni utilizzando i piani di query.

Elenco di tutte le viste parametrizzate

Utilizza la visualizzazione parameterized_views.all_parameterized_views per elencare tutte le visualizzazioni con parametri (quelle che contengono almeno un parametro denominato $@...).

SELECT * FROM parameterized_views.all_parameterized_views;

Passaggi successivi