使用 AlloyDB 參數化安全檢視區塊管理應用程式資料安全

AlloyDB for PostgreSQL 的參數化安全檢視表可讓您根據應用程式專屬的命名參數 (例如應用程式使用者憑證)，限制資料列存取權。這些檢視表會擴充 PostgreSQL 檢視表，在執行不受信任的查詢時，自動對每項查詢強制執行限制，藉此保護資料庫。

詳情請參閱「參數化安全檢視總覽」和「使用參數化安全檢視保護及控管應用程式資料存取權」。

事前準備

本頁面假設您已建立 AlloyDB 叢集和執行個體。詳情請參閱「建立資料庫」。

使用參數化安全檢視前，請務必完成下列事項：

1. 啟用 parameterized_views.enabled 資料庫旗標，載入必要的擴充功能程式庫。即使 AlloyDB 團隊先前已啟用這個旗標，您仍須啟用。如要進一步瞭解如何啟用資料庫旗標，請參閱「設定執行個體的資料庫旗標」。

2. 使用 AlloyDB Studio 或 psql，在要建立參數化檢視表的任何資料庫中，建立 parameterized_views 擴充功能：

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

   建立擴充功能時，系統也會建立名為 parameterized_views 的結構定義，以便將 API 納入該結構定義的命名空間，避免與現有 API 衝突。

建立參數化安全檢視

如要建立參數化安全檢視，請按照下列步驟操作：

1. 執行 CREATE VIEW DDL 指令，如下列範例所示：

   CREATE VIEW secure_checked_items WITH (security_barrier) AS
   SELECT bag_id, timestamp, location
   FROM checked_items t
   WHERE customer_id = $@app_end_userid;
   

   在上述範例中，參數化安全檢視允許存取名為 checked_items 的資料表中的三個資料欄。檢視表會將結果限制在 checked_items.customer_id 與必要參數相符的資料列。

   請使用下列屬性：

   • 使用 security_barrier 選項建立檢視表。
   • 如要限制應用程式使用者，讓他們只能查看有權存取的資料列，請使用 $@PARAMETER_NAME 語法在檢視表定義中新增必要參數。常見的用途是使用 COLUMN = $@PARAMETER_NAME 檢查 WHERE 子句中的資料欄值。
   • $@PARAMETER_NAME 表示具名檢視畫面參數。使用 execute_parameterized_query API 時，系統會提供這個值。具名檢視畫面參數須符合下列規定：
     • 命名檢視表參數開頭必須是英文字母 (a-z)。
     • 您可以使用帶有變音符號的字母和非拉丁字母，也可以使用底線 (_)。
     • 後續字元可以是英文字母、底線或數字 (0-9)。
     • 具名檢視表參數不得包含 $。
     • 具名檢視表參數區分大小寫。舉例來說，$@PARAMETER_NAME 的解讀方式與 $@parameter_name 不同。

2. 將檢視表的 SELECT 授予任何有權查詢檢視表的資料庫使用者。

3. 針對包含檢視表中所定義資料表的結構定義，將 USAGE 授予任何可查詢檢視表的資料庫使用者。

詳情請參閱「使用參數化安全檢視表保護及控管應用程式資料存取權」。

設定應用程式的安全性

如要使用參數化安全檢視表設定應用程式的安全性，請按照下列步驟操作：

1. 以管理員使用者身分建立安全參數化檢視表。這位使用者是 AlloyDB 資料庫使用者，負責執行應用程式的管理作業，包括資料庫設定和安全性管理。

2. 建立新的資料庫角色，對參數化安全檢視表執行查詢。這是 AlloyDB 資料庫角色，應用程式會使用這個角色連線及登入資料庫，並對參數化檢視表執行查詢。

   1. 將新角色權限授予安全檢視表，通常包括檢視表的 SELECT 權限和結構定義的 USAGE。

   2. 將這個角色可存取的物件，限制為應用程式所需的最低必要公開函式和物件組合。請勿提供非公開結構定義和資料表的存取權。

   3. 為降低安全風險，請撤銷這個角色對任何非必要機密結構定義或物件的存取權。

   查詢檢視表時，應用程式會提供必要檢視表參數的值，這些值會與應用程式使用者身分綁定。

   如要進一步瞭解如何建立資料庫使用者，請參閱「建立資料庫使用者」。

查詢參數化安全檢視

如要查詢參數化安全檢視，請根據用途選擇下列其中一種最合適的做法：

• JSON：使用這個 API 一次執行查詢，並傳回 JSON 資料列。

• CURSOR 型：如果查詢執行時間較長，或查詢規模較大，且您想分批擷取結果，請使用這個 API。parameterized_views 擴充功能提供的 execute_parameterized_query 函式可接受游標名稱。

• PREPARE EXECUTE 陳述式：用於預先準備的陳述式，可使用不同的參數值多次執行。

如要查詢參數化安全檢視，請使用 parameterized_views 擴充功能提供的 execute_parameterized_query() 函式。

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
)

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => 'SELECT * FROM secure_checked_items',
    param_names => ARRAY ['app_end_userid'],
    param_values => ARRAY ['40']
)

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    cursor_name => CURSOR_NAME,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
)

  -- Start a transaction, which is the default lifetime of a CURSOR
  BEGIN;
  -- Create a cursor called 'mycursor'
  SELECT * FROM parameterized_views.execute_parameterized_query(
   query => 'SELECT * FROM secure_checked_items',
   cursor_name => 'mycursor',
   param_names => ARRAY ['app_end_userid'],
   param_values => ARRAY ['40']
  );

  -- Fetch the results
  FETCH ALL FROM mycursor;
  -- End the transaction, which cleans up the cursor
  END;

PREPARE pquery (POSITIONAL_PARAM_TYPES)
        AS RESTRICTED SQL_QUERY;
EXECUTE pquery (POSITIONAL_PARAM_VALUES)
      WITH VIEW PARAMETERS (VIEW_PARAM_NAME1 = VIEW_PARAM_VALUE1[, ...]);

PREPARE pquery (timestamp) AS RESTRICTED SELECT * FROM secure_checked_items WHERE timestamp > $1;

EXECUTE pquery (current_date - 1) WITH VIEW PARAMETERS (app_end_userid = 40);
EXECUTE pquery (current_date - 30) WITH VIEW PARAMETERS (app_end_userid = 40);

強制執行的查詢限制

下列清單列出查詢的受限作業，這些查詢是使用「查詢參數化安全檢視表」一節所述的選項執行：

• 禁止遞迴叫用任何 API (execute_parameterized_query 或使用 EXECUTE .. WITH VIEW PARAMETERS)，因此只會使用應用程式指定的值。這項限制也能防止查詢用於規避特定參數值組合的安全範圍。

• 系統不允許某些會啟動新背景工作階段的擴充功能，包括 dblink、pg_cron 和 pg_background 擴充功能。

• 以下查詢建構項目為允許或受限：

  • 可使用唯讀 SELECT 陳述式。

  • 允許唯讀 SHOW 陳述式、CALL 陳述式和 DO 陳述式。

  • 不允許使用 INSERT、UPDATE 和 DELETE 等 DML 陳述式。

  • 不允許使用 CREATE TABLE 和 ALTER TABLE 等 DDL 陳述式。

  • 不允許使用其他陳述式類型，例如 LOAD、SET、CLUSTER、LOCK、CHECKPOINT 和 EXPLAIN。

• 根據預設，系統不允許 EXPLAIN 陳述式，以免發生使用查詢計畫的隱蔽通道攻擊。詳情請參閱「隱蔽通道」。不過，如要執行 EXPLAIN 陳述式，任何具備 AlloyDB 超級使用者權限的資料庫使用者，都可以將工作階段層級 GUC parameterized_views.enable_explain 設為 on。

• 參數化安全檢視提供設定，可協助您管理 API 用來查詢參數化檢視的資源，例如 parameterized_views.statement_timeout。詳情請參閱「支援的資料庫旗標」。

列出所有參數化檢視表

使用 parameterized_views 擴充功能，透過 all_parameterized_views 檢視表列出資料庫中所有參數化檢視表。這個檢視畫面的輸出內容與 pg_views 目錄檢視畫面相同，但 all_parameterized_views 只會列出含有具名檢視畫面參數的檢視畫面。

如要列出參數化檢視表，請執行下列查詢：

SELECT * FROM parameterized_views.all_parameterized_views;

輸出結果會與下列內容相似：

schemaname |       viewname       | viewowner |                       definition
-----------+----------------------+-----------+---------------------------------------------------------
public     | secure_checked_items | postgres  |  SELECT checked_items.bag_id,                          +
           |                      |           |     checked_items."timestamp",                         +
           |                      |           |     checked_items.location                             +
           |                      |           |    FROM checked_items                                  +
           |                      |           |   WHERE (checked_items.customer_id = $@app_end_userid);

如要在 all_parameterized_views 中列出參數化檢視表，請確認參數化檢視表的定義中至少包含一個具名檢視表參數。

後續步驟

• 瞭解參數化安全檢視表。
• 瞭解如何使用參數化安全檢視表，保護及控管應用程式資料的存取權。