Zugriff auf Anwendungsdaten mithilfe von parametrisierten sicheren Ansichten sichern und steuern

Wählen Sie eine Dokumentationsversion aus:

In dieser Anleitung wird beschrieben, wie Sie parametrisierte sichere Ansichten in AlloyDB Omni verwenden, um den Nutzerzugriff auf parametrisierte Ansichten mit AlloyDB Omni Studio oder psql einzuschränken.

Die Beispiele veranschaulichen die Möglichkeiten von parametrisierten sicheren Ansichten. Sie dienen nur zu Demonstrationszwecken.

Ziele

  • Sichere parametrisierte Ansichten mit benannten Ansichtsparametern erstellen
  • Datenbankrolle erstellen, die von der Anwendung verwendet wird, um eine Verbindung zur Datenbank herzustellen und auf parametrisierte sichere Ansichten zuzugreifen
  • Der neuen Rolle Berechtigungen für sichere Ansichten gewähren und den Zugriff auf Basistabellen aufheben
  • Verbindung mit der neuen Rolle herstellen und bestätigen, dass auf die eingeschränkten Tabellen nicht zugegriffen werden kann
  • Abfragen für die parametrisierte sichere Ansicht mit der Funktion execute_parameterized_query ausführen

Datenbank installieren und eine Verbindung herstellen

  1. AlloyDB Omni unter Linux installieren.

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.

Bevor Sie parametrisierte sichere Ansichten verwenden können, müssen Sie die folgenden Schritte einmal in Ihrer Linux-Umgebung ausführen. Jede Einstellung kann mit ALTER SYSTEM oder durch direktes Bearbeiten von postgresql.conf angewendet werden:

  1. `parameterized_views` zu shared_preload_libraries hinzufügen
  2. Funktion aktivieren, indem Sie parameterized_views.enabled=on festlegen
  3. Postgres neu starten, damit die Änderungen wirksam werden

Datenbank einrichten

  • Eine Datenbank namens database für die Anwendungsdaten und parametrisierten Ansichten erstellen

Datenbankrollen, Erweiterung und Anwendungsschema erstellen

  1. Über psql als Nutzer postgres oder als Nutzer mit AlloyDB Omni Superuser berechtigungen eine Verbindung zur Datenbank herstellen

    /usr/lib/postgresql/18/bin/psql -h localhost -U postgres

  2. Die Erweiterung parameterized_views in der Datenbank erstellen

    -- 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. So sind die APIs im Namespace dieses Schemas enthalten und geraten nicht mit vorhandenen APIs in Konflikt.

  3. Erstellen Sie die AlloyDB Omni-Administratorrolle, die als Datenbankinhaber dient und die Datenbank 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 Omni-Rolle, die von der Anwendung verwendet wird. Sie dient dazu, eine Verbindung zur Datenbank herzustellen, sich anzumelden und Abfragen mit eingeschränktem Zugriff auf öffentliche Funktionen oder Objekte auszuführen.

    Weitere Informationen finden Sie unter CREATE USER.

  5. Stellen Sie eine Verbindung als Nutzer mit Administratorzugriff her.

    SET role TO admin_user;

  6. Das Schema erstellen, das die Tabellen enthält

    CREATE SCHEMA schema;

  7. Tabellen erstellen und Daten einfügen

    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 die entsprechenden Zugriffsberechtigungen für die Basistabelle und die Ansichten ein:

  1. Über psql als admin_user eine Verbindung zur Datenbank herstellen

    /usr/lib/postgresql/18/bin/psql -h localhost -U admin_user

  2. Eine parametrisierte sichere Ansicht erstellen, um den Zugriff auf die Ansicht einzuschränken

    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. Gewähren Sie Zugriff auf die Ansicht.

    GRANT SELECT ON schema.secure_checked_items TO psv_user;

  4. Um auf die Ansicht zuzugreifen, Zugriff auf das Schema gewähren

    GRANT USAGE ON SCHEMA schema TO psv_user;

  5. Direkten Zugriff auf die Basistabelle aufheben

    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 Omni-Datenbankrolle, die von der Anwendung verwendet wird. Sie dient dazu, eine Verbindung zur Datenbank herzustellen, sich anzumelden und Abfragen auszuführen.

  1. Stellen Sie eine Verbindung als Nutzer mit parametrisierten sicheren Ansichten her.

    /usr/lib/postgresql/18/bin/psql -h localhost -U psv_user

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

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

  3. Mit der Funktion execute_parameterized_query auf die parametrisierte sichere Ansicht zugreifen

    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 Abfragen zu verbessern, die in natürlicher Sprache generiert wurden, parametrisierte sichere Ansichten in natürlicher Sprache einbinden

Bereinigen

Zum Bereinigen können Sie entweder die AlloyDB Omni-Instanz deinstallieren oder die Instanz beibehalten und die einzelnen Objekte löschen.

Nächste Schritte