使用參數化安全檢視區塊

本文說明如何在 PostgreSQL 適用的 Cloud SQL 中使用參數化安全檢視，根據應用程式專屬的具名參數 (例如應用程式使用者憑證) 限制資料存取權。參數化安全檢視會擴充 PostgreSQL 檢視的功能，進而提升安全性及存取權控管。此外，這類檢視也會對執行的任何查詢自動強制執行多項限制，降低從應用程式執行不受信任查詢的風險。

詳情請參閱參數化安全檢視區塊總覽和參數化安全檢視區塊教學課程。

事前準備

本文假設您已建立 PostgreSQL 適用的 Cloud SQL 執行個體。

如要使用參數化安全檢視區塊，請先完成下列步驟：

  1. 為 Cloud SQL 執行個體啟用 cloudsql.enable_parameterized_views 資料庫旗標。這項旗標變更需要重新啟動資料庫。詳情請參閱「設定資料庫旗標」。

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

    -- Requires cloudsql.enable_parameterized_views set to on
CREATE EXTENSION parameterized_views;

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

建立參數化安全檢視區塊

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

  1. 使用 security_barrier 選項執行 CREATE VIEW DDL 指令：

    CREATE VIEW VIEW_NAME WITH (security_barrier) AS
SELECT COLUMN_NAME, COLUMN_NAME_N
FROM TABLE_NAME ALIAS
WHERE CONDITION;

    更改下列內容：

    • VIEW_NAME：參數化安全檢視區塊的名稱
    • TABLE_NAME：要在參數化安全檢視區塊中使用的資料表名稱
    • ALIAS：要在參數化安全檢視區塊中使用的資料表名稱別名
    • COLUMNNAME 或 COLUMN_NAMEN：要在參數化安全檢視區塊中使用的資料表欄名稱

    • CONDITION：用於限制應用程式使用者的條件陳述式，讓他們只能查看自己有權存取的資料列。在 WHERE 子句中，使用 $@PARAMETER_NAME 語法新增必要參數。常見用途是使用 WHERE COLUMN = $@PARAMETER_NAME 檢查資料欄的值。

      $@PARAMETER_NAME 表示具名檢視區塊參數。使用 execute_parameterized_query API 時，系統會提供其值。具名檢視區塊參數須符合下列規定：

      • 具名檢視區塊參數開頭必須為英文字母 (a-z) 或底線 (_)。
      • 後續字元可以是字母、底線或數字 (0-9)。
      • 具名檢視區塊參數會區分大小寫。舉例來說，$@PARAMETER_NAME 的解讀方式與 $@parameter_name 不同。

      以下範例說明如何建立使用具名檢視區塊參數的參數化安全檢視區塊：

      CREATE VIEW user_specific_items WITH (security_barrier) AS
SELECT item_id, item_name, description, owner_id
FROM items t
WHERE owner_id = $@app_user_id;

  2. 將 view 的 SELECT 授予任何可查詢 view 的資料庫使用者。

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

詳情請參閱「使用參數化安全檢視畫面，安全地控管應用程式資料存取權 (教學課程)」。

設定應用程式的安全性

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

  1. 以管理員使用者身分建立參數化安全檢視區塊。這是 Cloud SQL 資料庫使用者，負責執行應用程式的管理作業。

  2. 建立新的資料庫角色，針對參數化安全檢視區塊執行查詢。這是應用程式用來連線及登入資料庫的 Cloud SQL 資料庫角色。

    1. 將新角色權限授予安全檢視區塊，通常包括檢視區塊的 SELECT 權限和結構定義的 USAGE 權限。
    2. 將這個角色可存取的物件，限制在應用程式所需的最低必要公開函式和物件集。避免提供非公開結構定義和資料表的存取權。

    查詢檢視區塊時，應用程式會提供必要檢視區塊參數的值，這些參數與應用程式使用者身分識別資訊相關聯。

    詳情請參閱「建立及管理使用者」。

查詢參數化安全檢視區塊

如要查詢參數化安全檢視區塊，請使用下列其中一種方法：

  • 以 JSON 為基礎：使用這個 API 一次執行查詢，並傳回 JSON 資料列。
  • CURSOR 型：如果查詢執行時間較長，或是查詢量大且想分批擷取結果，請使用這個 API。
  • PREPARE .. AS RESTRICTED 陳述式：使用 PREPARE .. AS RESTRICTED 定義查詢計畫，然後執行 EXECUTE ... WITH VIEW PARAMETERS (...)，以檢視的特定參數執行查詢。

JSON API

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

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

這個函式會傳回 JSON 物件的資料表。資料表中的每個資料列都等同於原始查詢結果資料列的 row_to_json() 值。

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

CURSOR API

使用游標名稱執行 execute_parameterized_query() 函式，建立並傳回交易範圍的 CURSOR：

-- Must be in a transaction block
BEGIN;

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

FETCH ALL FROM CURSOR_NAME;

END;

預先編譯的陳述式

預先準備好的陳述式方法可讓您準備一次查詢計畫，然後使用查詢的位置參數和檢視區塊的具名參數的不同值，多次執行該計畫。

如要使用預先準備好的陳述式，請執行下列操作：

  1. 建立預先編譯的陳述式。

    -- Prepare the statement
PREPARE PREPARED_STATEMENT_NAME (QUERY_PARAM_TYPE_1, QUERY_PARAM_TYPE_N)
AS RESTRICTED SQL_QUERY;

    更改下列內容：

    • PREPARED_STATEMENT_NAME：預先準備好的陳述式名稱
    • QUERY_PARAM_TYPE_N：查詢參數的資料類型，例如 TEXT
    • SQL_QUERY：要執行的 SQL 查詢，做為預先準備好的陳述式的一部分，並使用指定值

  2. 執行預先編譯的陳述式。

    -- Execute the statement with query parameters and view parameters
EXECUTE PREPARED_STATEMENT_NAME (QUERY_VALUE_1, QUERY_VALUE_N)
WITH VIEW PARAMETERS (PARAMETER_NAME_1 := 'PARAMETER_VALUE_1', PARAMETER_NAME_N := 'PARAMETER_VALUE_N');

    更改下列內容：

    • PREPARED_STATEMENT_NAME：預先準備好的陳述式名稱。
    • QUERY_VALUE_N：要提供給 SQL 查詢的一或多個參數值
    • PARAMETER_NAME_N：具名檢視畫面參數的名稱，或您建立參數化安全檢視畫面時定義的參數。您可從資料表的欄建立具名檢視畫面參數。
    • PARAMETER_VALUE_N：具名檢視區塊參數的值，可將參數化安全檢視區塊限制為與該值相關聯的資料列。

  3. 清理預先編譯的陳述式。

    -- Cleanup
DEALLOCATE PREPARED_STATEMENT_NAME>;

以下範例使用名為 user_specific_items 的參數化安全檢視區塊，該檢視區塊需要具名檢視區塊參數 $@app_user_id。

-- Prepare a query with a positional parameter $1 for the item_name pattern
PREPARE get_items_by_name (TEXT) AS RESTRICTED
SELECT item_id, item_name FROM user_specific_items
WHERE item_name LIKE $1;

建立預先編譯的陳述式後，您可以多次執行預先編譯的陳述式，並為查詢和具名檢視參數指派不同的值。

舉例來說，第一次執行查詢時：

-- Execute for user 123, looking for items like '%Laptop%'
EXECUTE get_items_by_name ('%Laptop%')
WITH VIEW PARAMETERS (app_user_id := '123');

第二次查詢執行：

-- Execute for user 456, looking for items like '%Book%'
EXECUTE get_items_by_name ('%Book%')
WITH VIEW PARAMETERS (app_user_id := '456');

WITH VIEW PARAMETERS 子句會提供參數化安全檢視區塊的具名檢視區塊參數 (123、456)，與預先準備查詢的位置參數 (%Laptop%、%Book%) 分開。

最後，請清理預先編譯的陳述式。

-- Clean up the get_item_by_name prepared statement
DEALLOCATE get_items_by_name;

查詢的強制限制

以下列出使用「查詢參數化安全檢視區塊」一節所述選項執行的查詢，其受限作業如下：

  • 唯讀：僅允許唯讀 SELECT 陳述式，禁止使用 DML (INSERT、UPDATE、DELETE) 和 DDL (CREATE、ALTER)。
  • 不得巢狀呼叫：禁止對 execute_parameterized_query 進行遞迴呼叫。
  • 擴充功能限制：系統不允許啟動新背景工作階段的特定擴充功能 (例如 dblink、pg_cron)。
  • 為避免使用查詢計畫時可能發生資訊洩漏，系統不允許使用 EXPLAIN 陳述式。

列出所有參數化檢視區塊

使用 parameterized_views.all_parameterized_views 檢視畫面列出所有參數化檢視區塊 (包含至少一個具名參數 $@... 的檢視區塊)。

SELECT * FROM parameterized_views.all_parameterized_views;

後續步驟