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

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

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

目標

  • 使用具名檢視區塊參數建立安全參數化檢視區塊。
  • 建立應用程式用來連線至資料庫及存取參數化安全檢視區塊的資料庫角色。
  • 授予新角色安全檢視畫面權限，並撤銷對基礎資料表的存取權。
  • 使用新角色連線，並確認無法存取受限資料表。
  • 使用 execute_parameterized_query 函式，對參數化安全檢視執行查詢。

費用

在本文件中，您會使用下列 Google Cloud的計費元件：

您可以使用 Pricing Calculator，根據預測用量估算費用。

初次使用 Google Cloud 的使用者可能符合免費試用期資格。

完成本文所述工作後，您可以刪除建立的資源，避免繼續計費，詳情請參閱「清除所用資源」。

事前準備

啟用計費功能和必要 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 自然語言整合參數化安全檢視區塊。

清除所用資源

為避免因為本教學課程所用資源，導致系統向 Google Cloud 收取費用，請刪除含有相關資源的專案，或者保留專案但刪除個別資源。

  1. 前往 Google Cloud 控制台的「Clusters」(叢集) 頁面。

    前往「Clusters」(叢集) 頁面

  2. 在「資源名稱」欄中，按一下叢集名稱 my-cluster。

  3. 按一下 「刪除叢集」。

  4. 在「Delete cluster my-cluster」(刪除叢集 my-cluster) 中輸入 my-cluster，確認要刪除叢集。

  5. 按一下「Delete」(刪除)。

  6. 如果您在建立叢集時建立了私人連線，請前往 Google Cloud 控制台的「網路」頁面，然後按一下「刪除虛擬私有雲網路」。

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

    psql database -U postgres

  8. 捨棄您建立的物件。

    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;

後續步驟