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

選取說明文件版本：

您可以在 AlloyDB Omni 中使用參數化安全檢視畫面，根據應用程式專屬的具名參數 (例如應用程式使用者憑證)，限制資料存取權。參數化安全檢視區塊可擴充 PostgreSQL 檢視區塊的功能，進而提升安全性和存取權控管。這些檢視畫面也會自動對執行的任何查詢強制執行限制，降低從應用程式執行不受信任查詢的風險。

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

事前準備

AlloyDB AI 參數化檢視區塊支援功能是透過 parameterized_views 提供，這是 AlloyDB for PostgreSQL 擴充功能。

本頁假設您已安裝 AlloyDB Omni。請參閱「安裝 AlloyDB Omni」(適用於容器、適用於 Kubernetes)。

使用參數化安全檢視區塊前，您必須在每個新的 Postgres 容器中執行下列操作一次。您可以使用 ALTER SYSTEM 套用各項設定，也可以直接編輯 postgresql.conf。

  1. 將 parameterized_views 新增到 shared_preload_libraries 中。
  2. 設定 parameterized_views.enabled=on 即可啟用這項功能。

  3. 重新啟動 PostgreSQL 伺服器，變更即可生效。

    -- See the current shared_preload_libraries
SHOW shared_preload_libraries;
ALTER SYSTEM SET shared_preload_libraries="...,parameterized_views";
ALTER SYSTEM SET parameterized_views.enabled=on;

  4. 使用 psql 在任何要建立參數化檢視區塊的資料庫中，建立 parameterized_views 擴充功能：

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

    建立擴充功能時，系統也會建立名為 parameterized_views 的結構定義，以便將 API 納入該結構定義的命名空間，並避免 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 Omni 資料庫使用者，負責執行應用程式的管理作業，包括資料庫設定和安全管理。
  2. 建立新的資料庫角色，針對參數化安全檢視區塊執行查詢。這是 AlloyDB Omni 資料庫角色，應用程式會使用這個角色連線及登入資料庫，並針對參數化檢視區塊執行查詢。
  3. 將新角色權限授予安全檢視區塊，通常包括檢視區塊的 SELECT 權限和結構定義的 USAGE 權限。
  4. 將這個角色可存取的物件限制在應用程式所需的最低必要公開函式和物件集。避免提供非公開結構定義和資料表的存取權。
  5. 查詢檢視區塊時，應用程式會提供與應用程式使用者身分相關的必要檢視區塊參數值。

查詢參數化安全檢視區塊

如要查詢參數化安全檢視區塊，請使用下列其中一個最符合您用途的選項：

  • 以 JSON 為基礎：使用這個 API 一次執行查詢，並傳回 JSON 資料列。
  • 以 CURSOR 為準：如果查詢時間較長或查詢量較大，且您想分批擷取結果，請使用這個 API。parameterized_views 擴充功能提供的 execute_parameterized_query 函式會接受游標名稱。
  • PREPARE EXECUTE 陳述式：適用於可使用不同參數值多次執行的預先準備陳述式。

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

JSON API

這個 API 宣告指定查詢的游標，因此有相關限制。因此，查詢必須與 PostgreSQL 指標相容。舉例來說，CURSOR API 不支援 DO 或 SHOW 陳述式。

這個 API 也不會依大小或傳回的資料列數限制結果。

執行 execute_parameterized_query() 函式，語法如下：

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

更改下列內容：

  • SQL_QUERY：SQL 查詢，其中的 FROM 子句會參照一或多個參數化安全檢視區塊。
  • PARAMETER_NAMES：要以字串形式傳遞的參數名稱清單。
  • PARAMETER_VALUES：要傳遞的參數值清單。
    • 這個清單的大小必須與 param_names 清單相同，且值的順序必須與名稱的順序相符。
    • 值的確切類型是從查詢和參數化檢視定義推斷而來。系統會在需要時，以及在給定參數值允許的情況下，執行型別轉換。如果類型不符，系統會擲回錯誤。

此函式會傳回 JSON 物件表格。資料表中的每個資料列，都相當於原始查詢結果資料列的 ROW_TO_JSON() 值。

請使用下列範例查詢參數化安全檢視區塊：

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

使用這個 API 時，結果集的大小會受到限制，限制條件包括結果的大小 (以 KB 為單位) 和列數。您可以使用 parameterized_views.json_results_max_size 和 parameterized_views.json_results_max_rows 設定這些限制。

這個 API 宣告指定查詢的游標，因此有相關限制。因此，查詢必須與 PostgreSQL 指標相容。舉例來說，CURSOR API 不支援 DO 或 SHOW 陳述式。

這個 API 也不會依大小或傳回的資料列數限制結果。

執行 execute_parameterized_query() 函式，語法如下：

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

更改下列內容：

  • SQL_QUERY：SQL 查詢，其中的 FROM 子句會參照一或多個參數化安全檢視區塊。
  • PARAMETER_NAMES：要以字串形式傳遞的參數名稱清單。
  • PARAMETER_VALUES：要傳遞的參數值清單。
  • 這個清單的大小必須與 param_names 清單相同，且值的順序必須與名稱的順序相符。
  • 值的確切類型是從查詢和參數化檢視定義推斷而來。系統會在必要時，以及在給定參數值允許的情況下，執行類型轉換。如果類型不符，系統會擲回錯誤。

此函式會傳回 JSON 物件表格。資料表中的每個資料列，都相當於原始查詢結果資料列的 ROW_TO_JSON() 值。

請使用下列範例查詢參數化安全檢視區塊：

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

使用這個 API 時，結果集的大小會受到限制，限制條件包括結果的大小 (以 KB 為單位) 和列數。您可以使用 parameterized_views.json_results_max_size 和 parameterized_views.json_results_max_rows 設定這些限制。

CURSOR API

這個 API 宣告指定查詢的游標，因此有相關限制。因此，查詢必須與 PostgreSQL 指標相容。舉例來說，CURSOR API 不支援 DO 或 SHOW 陳述式。

這個 API 也不會依大小或傳回的資料列數限制結果。

執行 execute_parameterized_query() 函式，語法如下：

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

更改下列內容：

  • SQL_QUERY：SQL 查詢，其中的 FROM 子句會參照一或多個參數化安全檢視區塊。
  • PARAMETER_NAMES：要以字串形式傳遞的參數名稱清單。
  • PARAMETER_VALUES：要傳遞的參數值清單。
    • 這個清單的大小必須與 param_names 清單相同，且值的順序必須與名稱的順序相符。
    • 值的確切類型是從查詢和參數化檢視定義推斷而來。系統會在需要時，以及在給定參數值允許的情況下，執行型別轉換。如果類型不符，系統會擲回錯誤。

此函式會傳回 JSON 物件表格。資料表中的每個資料列，都相當於原始查詢結果資料列的 ROW_TO_JSON() 值。

請使用下列範例查詢參數化安全檢視區塊：

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

使用這個 API 時，結果集的大小會受到限制，限制條件包括結果的大小 (以 KB 為單位) 和列數。您可以使用 parameterized_views.json_results_max_size 和 parameterized_views.json_results_max_rows 設定這些限制。

PREPARE 陳述式

使用 PREPARE .. AS RESTRICTED 指令建立參照參數化檢視區塊的預先準備陳述式。這些預先編譯的陳述式支援位置參數，並在您執行時強制執行各種限制。詳情請參閱「安全機制」。

這項功能會擴充 PREPARE 和 EXECUTE 指令，支援具名檢視區塊參數。使用預先編譯的陳述式，可避免每次執行陳述式時都要剖析、分析及重寫，進而大幅提升效能，尤其是經常執行的查詢或複雜查詢。預先編譯並儲存參數化 SQL 陳述式，以便日後執行，藉此最佳化效能。

這項 API 有限制，因為陳述式必須允許出現在PREPARE陳述式中，也就是說，只支援 SELECT 和 VALUES 陳述式。

這個 API 也不會依大小或傳回的資料列數限制結果。

如要建立參照參數化檢視區塊的預先準備陳述式，請執行 PREPARE .. AS RESTRICTED 指令：

PREPARE pquery (/POSITIONAL_PARAM_TYPES/)
        AS RESTRICTED query % a query that may refer to parameterized views
EXECUTE pquery (/POSITIONAL_PARAM_VALUES/)
      WITH VIEW PARAMETERS (VIEW_PARAM_NAME1 = VIEW_PARAM_VALUE1[, ...]);

更改下列內容：

  • POSITIONAL_PARAM_TYPES：在 RESTRICTED 查詢中使用的一或多個位置參數。
  • POSITIONAL_PARAM_VALUES：實際值，會取代 PREPARE 陳述式中定義的位置參數。
  • VIEW_PARAM_NAME：參數化檢視區塊預期的參數名稱，該檢視區塊參照自 RESTRICTED 查詢。
  • VIEW_PARAM_VALUE：傳遞至參數化檢視區塊對應 viewParamName 參數的實際值。

如要在預先準備好的陳述式中加入參數，請在 PREPARE 陳述式中提供資料類型清單。在準備的陳述式中，您可以使用 $1 和 $2 等方式，依位置參照參數。

使用 EXECUTE .. WITH VIEW PARAMETERS 指令執行先前準備好的陳述式，該陳述式是使用 PREPARE .. AS RESTRICTED 指令建立。如果建立陳述式的 PREPARE 陳述式指定了位置參數，您就必須將相容的參數集傳遞至 EXECUTE 陳述式。您必須在 WITH VIEW PARAMETERS 子句中，傳遞參數化檢視區塊所需的任何具名檢視區塊參數。

請使用下列範例查詢參數化安全檢視區塊：

  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 陳述式。
    • 不允許使用 DDL 陳述式，例如 CREATE TABLE 和 ALTER TABLE。
    • 不允許使用 LOAD, SET, CLUSTER, LOCK, CHECKPOINT 和 EXPLAIN 等其他陳述式類型。

  • 為避免使用查詢計畫發動隱蔽通道攻擊，系統不允許使用 EXPLAIN 陳述式。詳情請參閱「隱蔽通道」。

  • 參數化安全檢視畫面提供相關設定，協助您管理 API 用來查詢參數化檢視畫面的資源，例如 parameterized_views.statement_timeout。詳情請參閱「PostgreSQL 適用的 AlloyDB 旗標」。

列出所有參數化檢視區塊

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

如要列出參數化檢視區塊，請使用下列範例：

postgres=# select * from parameterized_views.all_parameterized_views ;
schemaname |      viewname      | viewowner |                       definition
-----------+--------------------+-----------+---------------------------------------------------------
public     | checked_items_view | 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 中列出參數化檢視區塊，請確認參數化檢視區塊的定義中至少包含一個具名檢視區塊參數。

後續步驟