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 parametrizzate migliorano la sicurezza e controllo dell'accesso estendendo la funzionalità delle viste PostgreSQL. Queste viste riducono inoltre i rischi di esecuzione di query non attendibili dalle applicazioni applicando automaticamente una serie di limitazioni a qualsiasi query eseguita.
Per saperne di più, consulta le viste sicure parametrizzate in Cloud SQL e Proteggi e controlla l'accesso ai dati delle applicazioni utilizzando viste sicure parametrizzate.
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:
Attiva il flag di database
cloudsql.enable_parameterized_viewsper l'istanza Cloud SQL. La modifica di questo flag richiede il riavvio del database. Per saperne di più, consulta Configura i flag di database.Utilizza Cloud SQL Studio o psql per creare l'estensione
parameterized_viewsin 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_viewsin 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:
Esegui il comando DDL
CREATE VIEWutilizzando l'opzionesecurity_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 parametrizzataTABLE_NAME: il nome della tabella da utilizzare nella vista protetta parametrizzataALIAS: l'alias del nome della tabella da utilizzare nella vista protetta parametrizzataCOLUMNNAMEoCOLUMN_NAMEN: il nome della colonna o delle colonne della tabella da utilizzare nella vista sicura parametrizzataCONDITION: l'istruzione della condizione utilizzata per limitare gli utenti dell'applicazione in modo che possano visualizzare solo le righe a cui sono autorizzati ad accedere. Aggiungi i parametri obbligatori utilizzando la sintassi$@PARAMETER_NAMEnella clausolaWHERE. Un caso d'uso comune è il controllo del valore di una colonna utilizzandoWHERE COLUMN = $@PARAMETER_NAME.$@PARAMETER_NAMEindica un parametro di visualizzazione denominato. Il suo valore viene fornito quando utilizzi l'APIexecute_parameterized_query. I parametri di visualizzazione denominati hanno i seguenti requisiti:- I parametri di visualizzazione con nome 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 vista denominata sono sensibili alle maiuscole. Ad esempio,
$@PARAMETER_NAMEviene interpretato in modo diverso da$@parameter_name.
Di seguito è riportato un esempio di creazione di una vista protetta parametrizzata che utilizza un parametro della 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;- I parametri di visualizzazione con nome devono iniziare con una lettera (a-z) o un trattino basso (
Concedi
SELECTsulla vista a qualsiasi utente del database autorizzato a eseguire query sulla vista.Concedi
USAGEsullo 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 viste sicure parametrizzate, segui questi passaggi:
- 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.
Crea un nuovo ruolo del database per l'esecuzione di query su viste protette parametrizzate. Si tratta di un ruolo del database Cloud SQL utilizzato dall'applicazione per connettersi e accedere al database.
- Concedi le autorizzazioni del nuovo ruolo alle viste sicure, che in genere includono i privilegi
SELECTper le viste eUSAGEper gli schemi. - 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.
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ù, consulta Crea e gestisci gli utenti.
- Concedi le autorizzazioni del nuovo ruolo alle viste sicure, che in genere includono i privilegi
Esegui una 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 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.
- Istruzione
PREPARE .. AS RESTRICTED: utilizzaPREPARE .. AS RESTRICTEDper definire il piano di query, quindi eseguiEXECUTE ... WITH VIEW PARAMETERS (...)per eseguirlo con parametri specifici per la vista.
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 del risultato 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 cursore, che crea e restituisce un CURSOR 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 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:
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 preparataQUERY_PARAM_TYPE_N: il tipo di dati del parametro di query, ad esempioTEXTSQL_QUERY: la query SQL da eseguire nell'ambito dell'istruzione preparata con il valore o i valori specificati
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 SQLPARAMETER_NAME_N: il nome del parametro o dei parametri della vista denominata che hai definito durante la creazione della vista protetta parametrizzata. Puoi creare il parametro della vista 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.
Esegui la pulizia dell'istruzione preparata.
-- Cleanup DEALLOCATE PREPARED_STATEMENT_NAME>;
L'esempio seguente utilizza una vista protetta con parametri denominata
user_specific_items che richiede il parametro della vista denominata $@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 protetta parametrizzata, separati dai parametri posizionali per la query preparata (%Laptop%, %Book%).
Infine, esegui la pulizia dell'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 limitate per le query eseguite utilizzando le opzioni descritte in Esegui query su una vista protetta con parametri:
- Sola lettura: sono consentite solo istruzioni
SELECTdi sola lettura. DML (INSERT,UPDATE,DELETE) e DDL (CREATE,ALTER) non sono consentiti. - Nessun nesting: le chiamate ricorsive a
execute_parameterized_querysono vietate. - Limiti delle estensioni: alcune estensioni che avviano nuove sessioni in background (ad es.
dblink,pg_cron) non sono consentite. - Le istruzioni
EXPLAINnon sono consentite per evitare potenziali fughe di informazioni utilizzando i piani di query.
Elenca tutte le viste parametrizzate
Utilizza la vista parameterized_views.all_parameterized_views per elencare tutte le viste parametrizzate (quelle contenenti almeno un parametro denominato $@...).
SELECT * FROM parameterized_views.all_parameterized_views;