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

在 AlloyDB for PostgreSQL 中，參數化安全檢視表可讓您根據具名參數，限制應用程式使用者存取特定資料庫資料列。在本教學課程中，您將使用 AlloyDB Studio 或 psql 建立及查詢參數化安全檢視表。

本教學課程中的範例僅用於說明參數化安全檢視表的功能。

目標

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

費用

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

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

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

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

事前準備

啟用計費功能和必要 API

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

    前往專案選取器

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

  3. 啟用必要的 Cloud API，以便建立及連線至 AlloyDB。

    啟用 API

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

    2. 在「啟用 API」步驟中，點選「啟用」來啟用下列項目：

      • AlloyDB API

建立及連線至資料庫

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

準備環境

如要準備對參數化安全檢視表執行查詢，您必須先設定參數化檢視表、資料庫和資料庫角色、parameterized_views 擴充功能和應用程式結構定義。

啟用必要擴充功能

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

設定資料庫

建立名為 database 的資料庫，用於存放應用程式資料和參數化檢視表。如要進一步瞭解如何建立資料庫，請參閱「建立資料庫」。

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

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

    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 '...';

    如要進一步瞭解如何建立 PostgreSQL 使用者，請參閱 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. 如要以 admin_user 身分連線至資料庫，請使用 AlloyDB Studio 或 psql。

    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. 如要以 postgres 身分連線至資料庫，請使用 AlloyDB Studio 或 psql：

    psql database -U postgres

  2. 捨棄您建立的物件：

    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;

如要刪除整個 AlloyDB 叢集和虛擬私有雲網路，請按照下列步驟操作：

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

    前往「Clusters」(叢集) 頁面

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

  3. 按一下 「Delete cluster」(刪除叢集)。

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

  5. 點選「刪除」。

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

後續步驟