安全 pg_columnmask 實作的最佳實務

下一節提供pg_columnmask在 Aurora PostgreSQL 環境中實作的安全最佳實務。請遵循這些建議：

建立安全的角色型存取控制架構
開發防止安全漏洞的遮罩函數
使用遮罩資料了解和控制觸發行為

角色型安全架構

定義角色階層以在資料庫中實作存取控制。Aurora PostgreSQL 透過為這些角色中的精細資料遮罩提供額外的圖層來pg_columnmask增強這些控制項。

建立符合組織函數的專用角色，而不是將許可授予個別使用者。此方法可提供更好的可稽核性，並簡化組織結構演進時的許可管理。

範例建立組織角色階層

下列範例會建立具有不同函數專用角色的組織角色階層，然後將個別使用者指派給適當的角色。在此範例中，會先建立組織角色 (analyst_role、support_role)，然後授與個別使用者這些角色的成員資格。此結構可讓您在角色層級管理許可，而不是針對每個個別使用者。


-- Create organizational role hierarchy
CREATE ROLE data_admin_role;
CREATE ROLE security_admin_role;
CREATE ROLE analyst_role;
CREATE ROLE support_role;
CREATE ROLE developer_role;

-- Specify security_admin_role as masking policy manager in the DB cluster parameter
-- group pgcolumnmask.policy_admin_rolname = 'security_admin_role'

-- Create specific users and assign to appropriate roles
CREATE USER security_manager;
CREATE USER data_analyst1, data_analyst2;
CREATE USER support_agent1, support_agent2;

GRANT security_admin_role TO security_manager;
GRANT analyst_role TO data_analyst1, data_analyst2;
GRANT support_role TO support_agent1, support_agent2;

僅授予每個角色所需的最低許可，以實作最低權限原則。避免授予在登入資料洩露時可能遭到利用的廣泛許可。


-- Grant specific table permissions rather than schema-wide access
GRANT SELECT ON sensitive_data.customers TO analyst_role;
GRANT SELECT ON sensitive_data.transactions TO analyst_role;
-- Do not grant: GRANT ALL ON SCHEMA sensitive_data TO analyst_role;

政策管理員在管理遮罩政策的結構描述上需要USAGE權限。遵循最低權限原則，選擇性地授予這些權限。定期審查結構描述存取許可，以確保只有獲得授權的人員才能維護政策管理功能。

政策管理員角色參數組態僅限於資料庫管理員。此參數無法在資料庫或工作階段層級修改，防止無權限使用者覆寫政策管理員指派。此限制可確保遮罩政策控制保持集中且安全。

將政策管理員角色指派給特定個人，而非群組。此目標方法可確保選擇性存取遮罩政策管理，因為政策管理員能夠遮罩資料庫中的所有資料表。

安全遮罩函數開發

使用早期繫結語義來開發遮罩函數，以確保適當的相依性追蹤，並防止延遲繫結漏洞，例如在執行期間修改搜尋路徑。建議使用 SQL BEGIN ATOMIC 函數的語法來啟用編譯時間驗證（即早期繫結）和相依性管理。


-- Example - Secure masking function with early binding
CREATE OR REPLACE FUNCTION secure_mask_ssn(input_ssn TEXT)
    RETURNS TEXT
    LANGUAGE SQL
    IMMUTABLE PARALLEL SAFE STRICT
    BEGIN ATOMIC
        SELECT CASE
            WHEN input_ssn IS NULL THEN NULL
            WHEN length(input_ssn) < 4 THEN repeat('X', length(input_ssn))
            ELSE repeat('X', length(input_ssn) - 4) || right(input_ssn, 4)
        END;
    END;

或者，透過明確建構符合所有物件參考的結構描述，建立可避免搜尋路徑變更的函數，確保不同使用者工作階段的行為一致。


-- Function immune to search path changes
CREATE OR REPLACE FUNCTION data_masking.secure_phone_mask(phone_number TEXT)
    RETURNS TEXT
    LANGUAGE SQL
    IMMUTABLE PARALLEL SAFE STRICT
    AS $$
    SELECT CASE
        WHEN phone_number IS NULL THEN NULL
        WHEN public.length(public.regexp_replace(phone_number, '[^0-9]', '', 'g')) < 10 THEN 'XXX-XXX-XXXX'
        ELSE public.regexp_replace(
            phone_number,
            '([0-9]{3})[0-9]{3}([0-9]{4})',
            public.concat('\1-XXX-\2')
        )
    END;
    $$;

在遮罩函數中實作輸入驗證，以處理邊緣案例並防止意外行為。一律包含 NULL 處理和驗證輸入格式，以確保一致的遮罩行為。


-- Robust masking function with comprehensive input validation
CREATE OR REPLACE FUNCTION secure_mask_phone(phone_number TEXT)
    RETURNS TEXT
    LANGUAGE SQL
    IMMUTABLE PARALLEL SAFE STRICT
    BEGIN ATOMIC
        SELECT CASE
            WHEN phone_number IS NULL THEN NULL
            WHEN length(trim(phone_number)) = 0 THEN phone_number
            WHEN length(regexp_replace(phone_number, '[^0-9]', '', 'g')) < 10 THEN 'XXX-XXX-XXXX'
            ELSE regexp_replace(phone_number, '([0-9]{3})[0-9]{3}([0-9]{4})', '\1-XXX-\2')
        END;
    END;

DML 使用 pg_columnmask 觸發行為

對於資料表觸發，轉換資料表將完全取消遮罩。對於檢視觸發 (IOT)，將根據目前使用者的檢視許可遮罩轉換資料表。

使用 pg_columnmask 的資料表觸發

觸發程序會傳遞轉換表，其中包含由觸發 DML 查詢修改的舊和新資料列版本。根據觸發時間，Aurora PostgreSQL 會填入新舊資料列。例如，BEFORE INSERT觸發條件只有新版本的資料列和空白舊版本，因為沒有要參考的舊版本。

pg_columnmask 不會遮罩資料表上觸發條件內的轉換資料表。觸發程序可以在其內文中使用遮罩的資料欄，並查看未遮罩的資料。觸發器建立者應確保如何為使用者執行觸發器。在此情況下，下列範例可正常運作。


-- Example for table trigger uses masked column in its definition
-- Create a table and insert some rows
CREATE TABLE public.credit_card_table (
    name TEXT,
    credit_card_no VARCHAR(16),
    is_fraud BOOL
);

INSERT INTO public.credit_card_table (name, credit_card_no, is_fraud)
    VALUES
    ('John Doe', '4532015112830366', false),
    ('Jane Smith', '5410000000000000', true),
    ('Brad Smith', '1234567891234567', true);

-- Create a role which will see masked data and grant it privileges
CREATE ROLE intern_user;
GRANT SELECT, DELETE ON public.credit_card_table TO intern_user;

-- Trigger which will silenty skip delete of non fraudelent credit cards
CREATE OR REPLACE FUNCTION prevent_non_fraud_delete()
    RETURNS TRIGGER AS
    $$
    BEGIN
        IF OLD.is_fraud = false THEN
            RETURN NULL;
        END IF;
        RETURN OLD;
    END;
    $$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_non_fraud_delete
    BEFORE DELETE ON credit_card_table
    FOR EACH ROW
    EXECUTE FUNCTION prevent_non_fraud_delete();

CREATE OR REPLACE FUNCTION public.return_false()
    RETURNS BOOLEAN
    LANGUAGE SQL
    IMMUTABLE PARALLEL SAFE STRICT
    BEGIN ATOMIC
      SELECT false;
    END;

-- A masking policy that masks both credit card number and is_fraud column.
-- If we apply masking inside trigger then prevent_non_fraud_delete trigger will
-- allow deleting more rows to masked user (even non fraud ones).
CALL pgcolumnmask.create_masking_policy(
    'mask_credit_card_no_&_is_fraud'::NAME,
    'public.credit_card_table'::REGCLASS,
    JSON_BUILD_OBJECT('credit_card_no', 'pgcolumnmask.mask_text(credit_card_no)',
                      'is_fraud', 'public.return_false()')::JSONB,
    ARRAY['intern_user']::NAME[],
    10::INT
);

-- Test trigger behaviour using intern_user
BEGIN;
SET ROLE intern_user;
-- credit card number & is_fraud is completely masked from intern_user
SELECT * FROM public.credit_card_table;
    name    |  credit_card_no  | is_fraud 
------------+------------------+----------
 John Doe   | XXXXXXXXXXXXXXXX | f
 Jane Smith | XXXXXXXXXXXXXXXX | f
 Brad Smith | XXXXXXXXXXXXXXXX | f
(3 rows)

-- The delete trigger lets the intern user delete rows for Jane and Brad even though
-- intern_user sees their is_fraud = false, but the table trigger works with original
-- unmasked value
DELETE FROM public.credit_card_table RETURNING *;
    name    |  credit_card_no  | is_fraud 
------------+------------------+----------
 Jane Smith | XXXXXXXXXXXXXXXX | f
 Brad Smith | XXXXXXXXXXXXXXXX | f
(2 rows)

COMMIT;

如果使用者對其在觸發內文中使用的陳述式不小心，觸發建立者會將未遮罩的資料洩漏給使用者。例如，使用會將資料欄RAISE NOTICE ‘%’, masked_column;列印到目前使用者。


-- Example showing table trigger leaking column value to current user
CREATE OR REPLACE FUNCTION leaky_trigger_func()
    RETURNS TRIGGER AS
    $$
    BEGIN
        RAISE NOTICE 'Old credit card number was: %', OLD.credit_card_no;
        RAISE NOTICE 'New credit card number is %', NEW.credit_card_no;
        RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;

CREATE TRIGGER leaky_trigger
    AFTER UPDATE ON public.credit_card_table
    FOR EACH ROW
    EXECUTE FUNCTION leaky_trigger_func();

-- Grant update on column is_fraud to auditor role
-- auditor will NOT HAVE PERMISSION TO READ DATA
CREATE ROLE auditor;
GRANT UPDATE (is_fraud) ON public.credit_card_table TO auditor;

-- Also add auditor role to existing masking policy on credit card table
CALL pgcolumnmask.alter_masking_policy(
    'mask_credit_card_no_&_is_fraud'::NAME,
    'public.credit_card_table'::REGCLASS,
    NULL::JSONB,
    ARRAY['intern_user', 'auditor']::NAME[],
    NULL::INT
);

-- Log in as auditor
-- [auditor]
-- Update will fail if trying to read data from the table
UPDATE public.credit_card_table
    SET is_fraud = true
    WHERE credit_card_no = '4532015112830366';
ERROR:  permission denied for table cc_table

-- [auditor]
-- But leaky update trigger will still print the entire row even though
-- current user does not have permission to select from public.credit_card_table
UPDATE public.credit_card_table SET is_fraud = true;
NOTICE:  Old credit_card_no was: 4532015112830366
NOTICE:  New credit_card_no is 4532015112830366

使用 pg_columnmask 的檢視觸發（而非觸發）

觸發條件只能在 PostgreSQL 的檢視上建立。它們用於在無法更新的檢視上執行 DML 陳述式。傳輸資料表一律會在內部遮罩，而非觸發 (IOT)，因為檢視和檢視查詢中使用的基底資料表可能會有不同的擁有者。在這種情況下，基礎資料表可能會有一些適用於檢視擁有者的遮罩政策，而且檢視擁有者必須在其觸發條件內查看基礎資料表的遮罩資料。這與資料表上的觸發不同，因為在這種情況下，觸發建立者和資料表內的資料是由相同使用者所擁有，但此處並非如此。


-- Create a view over credit card table
CREATE OR REPLACE VIEW public.credit_card_view
    AS
    SELECT * FROM public.credit_card_table;

-- Truncate credit card table and insert fresh data
TRUNCATE TABLE public.credit_card_table;
INSERT INTO public.credit_card_table (name, credit_card_no, is_fraud)
    VALUES
    ('John Doe', '4532015112830366', false),
    ('Jane Smith', '5410000000000000', true),
    ('Brad Smith', '1234567891234567', true);

CREATE OR REPLACE FUNCTION public.print_changes()
    RETURNS TRIGGER AS
    $$
    BEGIN
        RAISE NOTICE 'Old row: name=%, credit card number=%, is fraud=%',
            OLD.name, OLD.credit_card_no, OLD.is_fraud;
    
        RAISE NOTICE 'New row: name=%, credit card number=%, is fraud=%',
            NEW.name, NEW.credit_card_no, NEW.is_fraud;
    
    RETURN NEW;
   END;
   $$ LANGUAGE plpgsql;

CREATE TRIGGER print_changes_trigger
    INSTEAD OF UPDATE ON public.credit_card_view
    FOR EACH ROW
    EXECUTE FUNCTION public.print_changes();

GRANT SELECT, UPDATE ON public.credit_card_view TO auditor;

-- [auditor]
-- Login as auditor role
BEGIN;

-- Any data coming out from the table will be masked in instead of triggers
-- according to masking policies applicable to current user
UPDATE public.credit_card_view
    SET name = CONCAT(name, '_new_name')
    RETURNING *;
NOTICE:  Old row: name=John Doe, credit card number=XXXXXXXXXXXXXXXX, is fraud=f
NOTICE:  New row: name=John Doe_new_name, credit card number=XXXXXXXXXXXXXXXX, is fraud=f
NOTICE:  Old row: name=Jane Smith, credit card number=XXXXXXXXXXXXXXXX, is fraud=f
NOTICE:  New row: name=Jane Smith_new_name, credit card number=XXXXXXXXXXXXXXXX, is fraud=f
NOTICE:  Old row: name=Brad Smith, credit card number=XXXXXXXXXXXXXXXX, is fraud=f
NOTICE:  New row: name=Brad Smith_new_name, credit card number=XXXXXXXXXXXXXXXX, is fraud=f
        name         |  credit_card_no  | is_fraud 
---------------------+------------------+----------
 John Doe_new_name   | XXXXXXXXXXXXXXXX | f
 Jane Smith_new_name | XXXXXXXXXXXXXXXX | f
 Brad Smith_new_name | XXXXXXXXXXXXXXXX | f
 
 -- Any new data going into the table using INSERT or UPDATE command will be unmasked
 UPDATE public.credit_card_view
    SET credit_card_no = '9876987698769876'
    RETURNING *;
NOTICE:  Old row: name=John Doe, credit card number=XXXXXXXXXXXXXXXX, is fraud=f
NOTICE:  New row: name=John Doe, credit card number=9876987698769876, is fraud=f
NOTICE:  Old row: name=Jane Smith, credit card number=XXXXXXXXXXXXXXXX, is fraud=f
NOTICE:  New row: name=Jane Smith, credit card number=9876987698769876, is fraud=f
NOTICE:  Old row: name=Brad Smith, credit card number=XXXXXXXXXXXXXXXX, is fraud=f
NOTICE:  New row: name=Brad Smith, credit card number=9876987698769876, is fraud=f
    name    |  credit_card_no  | is_fraud 
------------+------------------+----------
 John Doe   | 9876987698769876 | f
 Jane Smith | 9876987698769876 | f
 Brad Smith | 9876987698769876 | f
 
 COMMIT;

控制觸發行為的資料庫/使用者層級 GuCs

兩個組態參數控制具有適用遮罩政策之使用者的觸發執行行為。當需要額外的安全限制時，請使用這些參數來防止觸發程序在遮罩的資料表或檢視上執行。預設會停用這兩個參數，允許觸發程序正常執行。

第一個 GUC：遮罩資料表的觸發條件限制

規格：

名稱：pgcolumnmask.restrict_dml_triggers_for_masked_users
類型：boolean
預設： false （允許執行觸發）

設為 TRUE 時，防止遮罩使用者在遮罩資料表上觸發執行。 pg_columnmask會執行錯誤。

第二個 GUC：對具有遮罩資料表的檢視觸發觸發限制

規格：

名稱：pgcolumnmask.restrict_iot_triggers_for_masked_users
類型：boolean
預設： false （允許執行觸發）

將設定為 TRUE 時，防止在其定義中包含遮罩資料表的檢視上觸發執行。

這些參數可獨立運作，並可像標準資料庫組態參數一樣進行設定。

您的瀏覽器已停用或無法使用 Javascript。

您必須啟用 Javascript，才能使用 AWS 文件。請參閱您的瀏覽器說明頁以取得說明。

文件慣用形式

遮罩政策管理角色

資料移動案例