使用參數化安全檢視畫面，安全地控管應用程式資料存取權

本教學課程說明如何在 AlloyDB for PostgreSQL 中使用參數化安全檢視區塊，透過 AlloyDB Studio 或 psql 限制使用者對參數化檢視區塊的存取權。

我們提供範例，說明參數化安全檢視區塊的功能。這些範例僅供示範。

必要條件

啟用計費功能和必要 API

  1. 在 Google Cloud 控制台中選取專案。

    前往專案選取器

  2. 請確認您已為 Google Cloud 專案啟用計費功能。

  3. 啟用建立及連線至 PostgreSQL 適用的 AlloyDB 時所需的 Cloud API。

    啟用 API

    1. 在「確認專案」步驟中，按一下「下一步」，確認要變更的專案名稱。

    2. 在「啟用 API」步驟中，按一下「啟用」，啟用下列項目：

      • AlloyDB API

建立及連線至資料庫

  1. 建立叢集及其主要執行個體。
  2. 連線至執行個體並建立資料庫。

準備環境

如要準備對參數化安全檢視區塊執行查詢，請先設定參數化檢視區塊、資料庫和資料庫角色、parameterized_view 擴充功能，以及應用程式結構定義。

啟用必要擴充功能

啟用 parameterized_views.enabled 資料庫旗標，載入必要的擴充功能程式庫。詳情請參閱「設定執行個體的資料庫旗標」。

設定資料庫

  • 建立名為 database 的資料庫，用於儲存應用程式資料和參數化檢視區塊。詳情請參閱「建立資料庫」。

建立資料庫角色、擴充功能和應用程式結構定義

  1. 使用 AlloyDB Studio 或 psql，以 postgres 使用者或具備 AlloyDB 超級使用者權限的使用者身分連線至資料庫。

    psql database -U postgres

    詳情請參閱「關於 AlloyDB 中的資料庫使用者管理」。

  2. 在資料庫中建立 parameterized_views 擴充功能。

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

    建立擴充功能時，系統也會建立名為 parameterized_views 的結構定義，確保 API 位於該結構定義的命名空間中，且不會與現有 API 發生衝突。

  3. 建立 AlloyDB 管理員角色，負責擁有及管理資料庫。

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

    詳情請參閱 CREATE USER。

  4. 建立新的資料庫角色，針對參數化安全檢視區塊執行查詢。這是 AlloyDB 角色，應用程式會使用這個角色連線及登入資料庫，執行查詢時僅能存取最少量的必要公開函式或物件。

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

    詳情請參閱 CREATE USER。

  5. 以管理使用者身分連線。

    SET role TO admin_user;

  6. 建立包含資料表的結構定義。

    CREATE SCHEMA schema;

  7. 建立資料表並插入資料。

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

建立安全且含參數的檢視畫面，並設定存取權

如要建立安全參數化檢視區塊，並為基本資料表和檢視區塊設定適當的存取權，請按照下列步驟操作：

  1. 使用 AlloyDB Studio 或 psql 以 admin_user 身分連線至資料庫。

    psql database -U admin_user

  2. 如要提供檢視表的限制存取權，請建立參數化安全檢視表：

    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. 授予檢視畫面存取權。

    GRANT SELECT ON schema.secure_checked_items TO psv_user;

  4. 如要存取檢視畫面，請授予結構定義的存取權。

    GRANT USAGE ON SCHEMA schema TO psv_user;

  5. 撤銷對基礎資料表的直接存取權。

    REVOKE ALL PRIVILEGES ON schema.checked_items FROM psv_user;

驗證資料安全性

如要確認參數化安全檢視區塊是否限制對指定檢視區塊的存取權，請以 psv_user 身分執行下列指令。這是 AlloyDB 資料庫角色，應用程式會使用這個角色連線及登入資料庫，以執行查詢。

  1. 以參數化安全檢視使用者身分連線。

    psql database -U psv_user

  2. 確認無法存取基礎資料表。

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

  3. 使用 execute_parameterized_query 函式存取參數化安全檢視畫面：

    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. 如要提升自然語言生成查詢的安全性，請使用 AlloyDB AI 自然語言整合參數化安全檢視區塊。