Zugriff auf Anwendungsdaten mithilfe von parametrisierten sicheren Ansichten sichern und steuern

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

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

Umgebung vorbereiten

  1. Aktivieren Sie das Datenbankflag cloudsql.enable_parameterized_views für Ihre Cloud SQL-Instanz. Für diese Flagänderung ist ein Neustart der Datenbank erforderlich.

  2. Stellen Sie als Nutzer postgres eine Verbindung zu Ihrer Datenbank her.

    psql -U postgres

  3. Erstellen Sie die Erweiterung parameterized_views in der Datenbank.

    CREATE EXTENSION parameterized_views;

  4. Erstellen Sie eine neue Datenbankrolle zum Ausführen von Abfragen.

    CREATE ROLE psv_user WITH LOGIN PASSWORD 'PASSWORD';

  5. Erstellen Sie ein Schema und eine Tabelle für die Anwendungsdaten.

    CREATE SCHEMA app_schema;
CREATE TABLE app_schema.items(item_id INT, item_name TEXT, description TEXT, owner_id INT);

INSERT INTO app_schema.items (item_id, item_name, description, owner_id) VALUES
(1, 'Book', 'A great read', 123),
(2, 'Laptop', 'Work machine', 456),
(3, 'Pencil', 'For writing', 123);

Sichere parametrisierte Ansichten erstellen und Zugriffsberechtigungen einrichten

  1. Erstellen Sie eine parametrisierte sichere Ansicht:

    CREATE VIEW app_schema.user_items_view WITH (security_barrier) AS
SELECT item_id, item_name, description
FROM app_schema.items
WHERE owner_id = $@current_user_id;

  2. Gewähren Sie der Anwendungsrolle Zugriff auf die Ansicht und das Schema.

    GRANT USAGE ON SCHEMA app_schema TO psv_user;
GRANT SELECT ON app_schema.user_items_view TO psv_user;

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

    REVOKE ALL PRIVILEGES ON app_schema.items FROM psv_user;

Datensicherheit überprüfen

  1. Stellen Sie eine Verbindung als psv_user her.

    psql -U psv_user -d postgres

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

    SELECT * FROM app_schema.items;
-- ERROR:  permission denied for table 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 app_schema.user_items_view',
  param_names => ARRAY ['current_user_id'],
  param_values => ARRAY ['123']
);

    Das Ergebnis sollte nur Elemente enthalten, bei denen owner_id gleich 123 ist.

Nächste Schritte