Parametrisierte sichere Ansichten verwenden

In diesem Dokument wird beschrieben, wie Sie parametrisierte sichere Ansichten in Cloud SQL for PostgreSQL verwenden. Damit können Sie den Datenzugriff basierend auf anwendungsspezifischen benannten Parametern wie Anmeldedaten von Anwendungsnutzern einschränken. Parametrisierte sichere Ansichten verbessern die Sicherheit und Zugriffssteuerung, indem sie die Funktionalität von PostgreSQL-Ansichten erweitern. Diese Ansichten minimieren auch die Risiken, die mit der Ausführung nicht vertrauenswürdiger Abfragen aus Anwendungen verbunden sind, indem sie automatisch eine Reihe von Einschränkungen für jede ausgeführte Abfrage erzwingen.

Weitere Informationen finden Sie unter Parametrisierte sichere Ansichten in Cloud SQL und Zugriff auf Anwendungsdaten mit parametrisierten sicheren Ansichten sichern und steuern.

Hinweis

In diesem Dokument wird davon ausgegangen, dass Sie bereits eine Cloud SQL for PostgreSQL-Instanz erstellt haben.

Bevor Sie parametrisierte sichere Ansichten verwenden können, müssen Sie Folgendes tun:

  1. Aktivieren Sie das Datenbank-Flag cloudsql.enable_parameterized_views für Ihre Cloud SQL-Instanz. Für diese Flag-Änderung ist ein Neustart der Datenbank erforderlich. Weitere Informationen finden Sie unter Datenbank-Flags konfigurieren.

  2. Erstellen Sie mit Cloud SQL Studio oder psql die Erweiterung parameterized_views in einer beliebigen Datenbank, in der eine parametrisierte Ansicht erstellt wird:

    -- Requires cloudsql.enable_parameterized_views set to on
CREATE EXTENSION parameterized_views;

    Wenn die Erweiterung erstellt wird, erstellt das System auch ein Schema mit dem Namen parameterized_views. So sind die APIs im Namespace dieses Schemas enthalten und geraten nicht mit vorhandenen APIs in Konflikt.

Parametrisierte sichere Ansicht erstellen

So erstellen Sie eine parametrisierte sichere Ansicht:

  1. Führen Sie den DDL-Befehl CREATE VIEW mit der Option security_barrier aus:

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

    Ersetzen Sie Folgendes:

    • VIEW_NAME: der Name der parametrisierten sicheren Ansicht
    • TABLE_NAME: der Name der Tabelle, die in der parametrisierten sicheren Ansicht verwendet werden soll
    • ALIAS: der Alias für den Tabellennamen, der in der parametrisierten sicheren Ansicht verwendet werden soll
    • COLUMNNAME oder COLUMN_NAMEN: der Name der Tabellenspalte oder -spalten, die in der parametrisierten sicheren Ansicht verwendet werden sollen

    • CONDITION: die Bedingungsanweisung, mit der Anwendungsnutzer eingeschränkt werden, sodass sie nur die Zeilen sehen können, auf die sie Zugriff haben. Fügen Sie in der WHERE-Klausel mit der Syntax $@PARAMETER_NAME die erforderlichen Parameter hinzu. Ein häufiger Anwendungsfall ist die Prüfung des Werts einer Spalte mit WHERE COLUMN = $@PARAMETER_NAME.

      $@PARAMETER_NAME gibt einen benannten Ansichtsparameter an. Sein Wert wird angegeben, wenn Sie die execute_parameterized_query-API verwenden. Für benannte Ansichtsparameter gelten die folgenden Anforderungen:

      • Benannte Ansichtsparameter müssen mit einem Buchstaben (a–z) oder einem Unterstrich (_) beginnen.
      • Nachfolgende Zeichen können Buchstaben, Unterstriche oder Ziffern (0–9) sein.
      • Bei benannten Ansichtsparametern wird die Groß-/Kleinschreibung beachtet. So wird $@PARAMETER_NAME anders als $@parameter_name interpretiert.

      Im Folgenden sehen Sie ein Beispiel für das Erstellen einer parametrisierten sicheren Ansicht, die einen benannten Ansichtsparameter verwendet:

      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. Gewähren Sie SELECT für die Ansicht für alle Datenbanknutzer, die die Ansicht abfragen dürfen.

  3. Gewähren Sie USAGE für das Schema, das die in der Ansicht definierten Tabellen enthält, für alle Datenbanknutzer, die die Ansicht abfragen dürfen.

Weitere Informationen finden Sie unter Zugriff auf Anwendungsdaten mit parametrisierten sicheren Ansichten sichern und steuern.

Sicherheit für Ihre Anwendung konfigurieren

So konfigurieren Sie die Sicherheit für Ihre Anwendungen mit parametrisierten sicheren Ansichten:

  1. Erstellen Sie die parametrisierte sichere Ansicht als Administrator. Dies ist ein Cloud SQL-Datenbanknutzer, der administrative Vorgänge für die Anwendung ausführt.

  2. Erstellen Sie eine neue Datenbankrolle zum Ausführen von Abfragen für parametrisierte sichere Ansichten. Dies ist eine Cloud SQL-Datenbankrolle, die von der Anwendung verwendet wird, um eine Verbindung zur Datenbank herzustellen und sich anzumelden.

    1. Gewähren Sie der neuen Rolle Berechtigungen für die sicheren Ansichten, einschließlich SELECT-Berechtigungen für die Ansichten und USAGE für die Schemas.
    2. Beschränken Sie die Objekte, auf die diese Rolle zugreifen kann, auf die erforderliche Mindestanzahl öffentlicher Funktionen und Objekte, die die Anwendung benötigt. Vermeiden Sie den Zugriff auf Schemas und Tabellen, die nicht öffentlich sind.

    Wenn Sie die Ansichten abfragen, gibt die Anwendung die Werte der erforderlichen Ansichtsparameter an, die mit der Identität des Anwendungsnutzers verknüpft sind.

    Weitere Informationen finden Sie unter Nutzer erstellen und verwalten.

Parametrisierte sichere Ansicht abfragen

Verwenden Sie eine der folgenden Optionen, um eine parametrisierte sichere Ansicht abzufragen:

  • JSON-basiert: Verwenden Sie diese API, um die Abfrage einmal auszuführen und JSON-Zeilen zurückzugeben.
  • CURSOR-basiert: Verwenden Sie diese API bei längeren Abfragen oder bei großen Abfragen, wenn Sie das Ergebnis in Batches abrufen möchten.
  • PREPARE .. AS RESTRICTED -Anweisung: Verwenden Sie PREPARE .. AS RESTRICTED, um den Abfrageplan zu definieren, und führen Sie dann EXECUTE ... WITH VIEW PARAMETERS (...) aus, um ihn mit bestimmten Parametern für die Ansicht auszuführen.

JSON API

Führen Sie die Funktion execute_parameterized_query() mit der folgenden Syntax aus:

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

Die Funktion gibt eine Tabelle mit JSON-Objekten zurück. Jede Zeile in der Tabelle entspricht dem row_to_json()-Wert der ursprünglichen Abfrageergebniszeile.

Mit dieser API wird die Größe des Ergebnissatzes nach Größe (in KB) und nach Anzahl der Zeilen begrenzt. Sie können diese Grenzwerte mit parameterized_views.json_results_max_size und parameterized_views.json_results_max_rows konfigurieren.

CURSOR API

Führen Sie die Funktion execute_parameterized_query() mit einem Cursornamen aus. Dadurch wird ein transaktionsbezogener CURSOR erstellt und zurückgegeben:

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

Vorbereitete Anweisungen

Mit der Methode der vorbereiteten Anweisungen können Sie einen Abfrageplan einmal vorbereiten und ihn dann mehrmals mit verschiedenen Werten für die Positionsparameter der Abfrage und die benannten Parameter der Ansicht ausführen.

So verwenden Sie eine vorbereitete Anweisung:

  1. Erstellen Sie die vorbereitete Anweisung.

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

    Ersetzen Sie Folgendes:

    • PREPARED_STATEMENT_NAME: der Name der vorbereiteten Anweisung
    • QUERY_PARAM_TYPE_N: der Datentyp des Abfrageparameters, z. B. TEXT
    • SQL_QUERY: die SQL-Abfrage, die als Teil der vorbereiteten Anweisung mit dem angegebenen Wert oder den angegebenen Werten ausgeführt werden soll

  2. Führen Sie die vorbereitete Anweisung aus.

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

    Ersetzen Sie Folgendes:

    • PREPARED_STATEMENT_NAME: der Name der vorbereiteten Anweisung.
    • QUERY_VALUE_N: der Wert oder die Werte, die als Parameter an die SQL-Abfrage übergeben werden sollen
    • PARAMETER_NAME_N: der Name des benannten Ansichtsparameters oder der benannten Ansichtsparameter, die Sie beim Erstellen der parametrisierten sicheren Ansicht definiert haben. Sie erstellen den benannten Ansichtsparameter aus der Spalte der Tabelle.
    • PARAMETER_VALUE_N: der Wert oder die Werte für den benannten Ansichtsparameter, der die parametrisierte sichere Ansicht auf die Zeilen beschränkt, die mit dem Wert verknüpft sind.

  3. Bereinigen Sie die vorbereitete Anweisung.

    -- Cleanup
DEALLOCATE PREPARED_STATEMENT_NAME>;

Im folgenden Beispiel wird eine parametrisierte sichere Ansicht mit dem Namen user_specific_items verwendet, für die der benannte Ansichtsparameter $@app_user_id erforderlich ist.

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

Nachdem Sie die vorbereitete Anweisung erstellt haben, können Sie sie mehrmals mit ausführen und verschiedene Werte für die Abfrage und den benannten Ansichtsparameter zuweisen.

Beispiel für die erste Abfrageausführung:

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

Beispiel für die zweite Abfrageausführung:

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

In der Klausel WITH VIEW PARAMETERS werden die benannten Ansichtsparameter (123, 456) für die parametrisierte sichere Ansicht getrennt von den Positionsparametern für die vorbereitete Abfrage (%Laptop%, %Book%) angegeben.

Bereinigen Sie abschließend die vorbereitete Anweisung.

-- Clean up the get_item_by_name prepared statement
DEALLOCATE get_items_by_name;

Beschränkungen bei Abfragen

Im Folgenden finden Sie eine Liste der eingeschränkten Vorgänge für Abfragen, die Sie mit den unter Parametrisierte sichere Ansicht abfragen beschriebenen Optionen ausführen:

  • Schreibgeschützt: Es sind nur schreibgeschützte SELECT-Anweisungen zulässig. DML (INSERT, UPDATE, DELETE) und DDL (CREATE, ALTER) sind nicht zulässig.
  • Keine Verschachtelung: Rekursive Aufrufe von execute_parameterized_query sind nicht zulässig.
  • Einschränkungen für Erweiterungen: Bestimmte Erweiterungen, die neue Hintergrundsitzungen starten (z.B. dblink, pg_cron), sind nicht zulässig.
  • EXPLAIN-Anweisungen sind nicht zulässig, um potenziellen Informationsverlust durch Abfragepläne zu verhindern.

Alle parametrisierten Ansichten auflisten

Verwenden Sie die Ansicht parameterized_views.all_parameterized_views, um alle parametrisierten Ansichten aufzulisten (die mindestens einen benannten Parameter $@... enthalten).

SELECT * FROM parameterized_views.all_parameterized_views;

Nächste Schritte