Zugriff auf Anwendungsdaten mithilfe von parametrisierten sicheren Ansichten sichern und steuern

In dieser Anleitung wird beschrieben, wie Sie parametrisierte sichere Ansichten in AlloyDB for PostgreSQL verwenden, um den Nutzerzugriff auf parametrisierte Ansichten mit AlloyDB Studio oder psql einzuschränken. Es sind Beispiele enthalten, um die Funktionen von parametrisierten sicheren Ansichten zu veranschaulichen. Diese Beispiele dienen nur zu Demonstrationszwecken.

Lernziele

  • Sichere parametrisierte Ansichten mit benannten Ansichtsparametern erstellen
  • Erstellen Sie die Datenbankrolle, die von der Anwendung verwendet wird, um eine Verbindung zur Datenbank herzustellen und auf parametrisierte sichere Ansichten zuzugreifen.
  • Gewähren Sie der neuen Rolle Berechtigungen für die sicheren Ansichten und heben Sie den Zugriff auf die Basistabellen auf.
  • Stellen Sie eine Verbindung mit der neuen Rolle her und prüfen Sie, ob auf die eingeschränkten Tabellen nicht zugegriffen werden kann.
  • Führen Sie Abfragen für die parametrisierte sichere Ansicht mit der Funktion execute_parameterized_query aus.

Kosten

In diesem Dokument verwenden Sie die folgenden kostenpflichtigen Komponenten von Google Cloud:

Mit dem Preisrechner können Sie eine Kostenschätzung für Ihre voraussichtliche Nutzung vornehmen.

Neuen Google Cloud Nutzern steht möglicherweise eine kostenlose Testversion zur Verfügung.

Nach Abschluss der in diesem Dokument beschriebenen Aufgaben können Sie weitere Kosten vermeiden, indem Sie die erstellten Ressourcen löschen. Weitere Informationen finden Sie unter Bereinigen.

Hinweise

Zugriff anfordern

Bevor Sie parametrisierte sichere Ansichten verwenden können, müssen Sie Zugriff auf parametrisierte sichere Ansichten in AlloyDB anfordern und warten, bis Sie die Bestätigung der Aktivierung erhalten.

Abrechnung und erforderliche APIs aktivieren

  1. Wählen Sie in der Google Cloud -Console ein Projekt aus.

    Zur Projektauswahl

  2. Die Abrechnung für Ihr Google Cloud -Projekt muss aktiviert sein.

  3. Aktivieren Sie die Cloud APIs, die zum Erstellen einer Verbindung zu AlloyDB for PostgreSQL erforderlich sind.

    API aktivieren

    1. Klicken Sie im Schritt Projekt bestätigen auf Weiter, um den Namen des Projekts zu bestätigen, an dem Sie Änderungen vornehmen möchten.

    2. Klicken Sie im Schritt APIs aktivieren auf Aktivieren, um Folgendes zu aktivieren:

      • AlloyDB API

Erstellen und Verbindung zu einer Datenbank herstellen

  1. Cluster und primäre Instanz erstellen
  2. Verbindung zur Instanz herstellen und Datenbank erstellen

Umgebung vorbereiten

Bevor Sie Abfragen für eine parametrisierte sichere Ansicht ausführen können, müssen Sie zuerst parametrisierte Ansichten, die Datenbank und Datenbankrollen, die Erweiterung parameterized_view und das Anwendungsschema einrichten.

Parametrisierte Ansichten und die Datenbank einrichten

  1. Bitten Sie das AlloyDB-Team, das Flag parameterized_views.enabled database zu aktivieren, um die erforderlichen Erweiterungsbibliotheken zu laden.

  2. Erstellen Sie eine Datenbank namens database für die Anwendungsdaten und parametrisierten Ansichten. Weitere Informationen finden Sie unter Datenbank erstellen.

Datenbankrollen, die Erweiterung und das Anwendungsschema erstellen

  1. Stellen Sie über AlloyDB Studio oder psql eine Verbindung zur Datenbank als postgres-Nutzer oder als Nutzer mit AlloyDB-Superuser-Berechtigungen her.

    psql database -U postgres

    Weitere Informationen finden Sie unter Datenbanknutzerverwaltung in AlloyDB.

  2. Erstellen Sie die Erweiterung parameterized_views in der Datenbank.

    -- Requires parameterized_views.enabled set to true
CREATE EXTENSION parameterized_views;

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

  3. Erstellen Sie die AlloyDB-Administratorrolle, die die Datenbank besitzt und verwaltet.

    CREATE ROLE admin_user WITH LOGIN PASSWORD '...';
 GRANT ALL PRIVILEGES ON DATABASE database TO admin_user;

    Weitere Informationen finden Sie unter CREATE USER.

  4. Erstellen Sie eine neue Datenbankrolle zum Ausführen von Abfragen für parametrisierte sichere Ansichten. Dies ist eine AlloyDB-Rolle, die von der Anwendung verwendet wird, um eine Verbindung zur Datenbank herzustellen und sich in der Datenbank anzumelden, um Abfragen mit eingeschränktem Zugriff auf öffentliche Funktionen oder Objekte auszuführen. Der Zugriff ist auf das erforderliche Minimum beschränkt.

    CREATE ROLE psv_user WITH LOGIN PASSWORD '...';

    Weitere Informationen finden Sie unter CREATE USER.

  5. Stellen Sie eine Verbindung als Administratornutzer her.

    SET role TO admin_user;

  6. Erstellen Sie das Schema, das die Tabellen enthält.

    CREATE SCHEMA schema;

  7. Erstellen Sie die Tabellen und fügen Sie Daten ein.

    CREATE TABLE schema.checked_items(bag_id INT,timestamp TIMESTAMP, loc_code CHAR(3), scan_type CHAR(1), location TEXT, customer_id INT);

INSERT INTO schema.checked_items (bag_id, timestamp, loc_code, scan_type, location, customer_id) VALUES
(101, '2023-10-26 10:00:00', 'ABC', 'I', 'Warehouse A', 123),
(102, '2023-10-26 10:15:30', 'DEF', 'O', 'Loading Dock B', 456),
(103, '2023-10-26 10:30:45', 'GHI', 'I', 'Conveyor Belt 1', 789),
(104, '2023-10-26 11:00:00', 'JKL', 'O', 'Shipping Area C', 101),
(105, '2023-10-26 11:45:15', 'MNO', 'I', 'Sorting Station D', 202),
(106, '2023-10-26 12:00:00', 'PQR', 'O', 'Truck Bay E', 303);

Sichere parametrisierte Ansichten erstellen und Zugriffsberechtigungen einrichten

So erstellen Sie sichere parametrisierte Ansichten und richten entsprechende Zugriffsberechtigungen für die Basistabelle und Ansichten ein:

  1. Stellen Sie mit AlloyDB Studio oder psql als admin_user eine Verbindung zur Datenbank her.

    psql database -U admin_user

  2. Wenn Sie eingeschränkten Zugriff auf die Ansicht gewähren möchten, erstellen Sie eine parametrisierte sichere Ansicht:

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

  3. Zugriff auf die Ansicht gewähren

    GRANT SELECT ON schema.secure_checked_items TO psv_user;

  4. Um auf die Ansicht zuzugreifen, müssen Sie Zugriff auf das Schema gewähren.

    GRANT USAGE ON SCHEMA schema TO psv_user;

  5. Heben Sie den direkten Zugriff auf die Basistabelle auf.

    REVOKE ALL PRIVILEGES ON schema.checked_items FROM psv_user;

Datensicherheit prüfen

Führen Sie die folgenden Befehle als psv_user aus, um zu prüfen, ob der Zugriff auf die angegebenen Ansichten durch die parametrisierten sicheren Ansichten eingeschränkt wird. Dies ist eine AlloyDB-Datenbankrolle, die von der Anwendung verwendet wird, um eine Verbindung zur Datenbank herzustellen und sich in der Datenbank anzumelden, um Abfragen auszuführen.

  1. Verbindung als Nutzer mit parametrisierten sicheren Ansichten herstellen

    psql database -U psv_user

  2. Prüfen Sie, ob auf die Basistabelle zugegriffen werden kann.

    SELECT * FROM schema.checked_items;
ERROR:  permission denied for table checked_items

  3. Greifen Sie mit der Funktion execute_parameterized_query auf die parametrisierte sichere Ansicht zu:

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

  4. Um die Sicherheit von in natürlicher Sprache generierten Abfragen zu verbessern, können Sie Ihre parametrisierten sicheren Ansichten mit AlloyDB AI Natural Language einbinden.

Bereinigen

Damit Ihrem Google Cloud-Konto die in dieser Anleitung verwendeten Ressourcen nicht in Rechnung gestellt werden, löschen Sie entweder das Projekt, das die Ressourcen enthält, oder Sie behalten das Projekt und löschen die einzelnen Ressourcen.

  1. Rufen Sie in der Google Cloud Console die Seite Cluster auf.

    Zu den Clustern

  2. Klicken Sie in der Spalte Ressourcenname auf den Namen Ihres Clusters, my-cluster.

  3. Klicken Sie auf  Cluster löschen.

  4. Geben Sie in Cluster „my-cluster“ löschen my-cluster ein, um zu bestätigen, dass Sie den Cluster löschen möchten.

  5. Klicken Sie auf Löschen.

  6. Wenn Sie beim Erstellen eines Clusters eine private Verbindung erstellt haben, rufen Sie in der Google Cloud -Konsole die Seite „Netzwerk“ auf und klicken Sie auf VPC-Netzwerk löschen.

  7. Stellen Sie mit AlloyDB Studio oder psql als postgres eine Verbindung zur Datenbank her.

    psql database -U postgres

  8. Löschen Sie die von Ihnen erstellten Objekte.

    DROP VIEW schema.secure_checked_items;
DROP TABLE schema.checked_items;
DROP SCHEMA schema;
DROP ROLE psv_user;
DROP ROLE admin_user;
DROP EXTENSION parameterized_views;

Nächste Schritte