了解觸發函數中的遮罩行為 - Amazon Aurora

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

了解觸發函數中的遮罩行為

將pg_columnmask政策套用至資料表時，請務必了解遮罩如何與觸發函數互動。觸發條件是自動執行的資料庫函數，以回應資料表上的某些事件，例如 INSERT、UPDATE 或 DELETE 操作。

根據預設，DDM 會根據觸發類型套用不同的遮罩規則：

資料表觸發條件

未遮罩轉換資料表 – 資料表上的觸發函數可以存取其轉換資料表中舊資料列和新資料列版本的未遮罩資料

資料表擁有者會建立觸發條件並擁有資料，因此具有有效管理資料表的完整存取權

檢視觸發 (INSTEAD OF Triggers)

轉換資料表已遮罩 – 檢視上的觸發函數會根據目前使用者的許可查看遮罩的資料

檢視擁有者可能與基礎資料表擁有者不同，因此應遵守基礎資料表上的遮罩政策

兩個伺服器層級組態參數會使用遮罩資料表控制觸發行為。這些只能由 設定rds_superuser：

  • 限制遮罩資料表的觸發 – 當遮罩使用者對具有適用遮罩政策的資料表執行 DML 操作時，防止觸發執行。

  • 使用遮罩資料表限制檢視的觸發： – 當檢視定義包含適用於目前使用者的遮罩政策的資料表時，防止在檢視上觸發執行。

範例函數應用程式與資料表和檢視之間的差異

下列範例會建立觸發函數來列印舊資料列值和新資料列值，然後示範相同的函數在連接至資料表時與檢視時的行為有何不同。

-- Create trigger function
CREATE OR REPLACE FUNCTION print_changes()
    RETURNS TRIGGER AS
    $$
        BEGIN
        RAISE NOTICE 'Old row: name=%, email=%, ssn=%, salary=%',
            OLD.name, OLD.email, OLD.ssn, OLD.salary;
        
        RAISE NOTICE 'New row: name=%, email=%, ssn=%, salary=%',
            NEW.name, NEW.email, NEW.ssn, NEW.salary;
        
        RETURN NEW;
        END;
    $$ LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER print_changes_trigger
    BEFORE UPDATE ON hr.employees
    FOR EACH ROW
    EXECUTE FUNCTION print_changes();

-- Grant update to analyst role
GRANT UPDATE ON hr.employees TO analyst_role;

-- Unmasked data must be seen inside trigger even for masked user for the OLD and NEW
-- row passed to trigger function
BEGIN;
SET ROLE mike_analyst;
UPDATE hr.employees SET id = id + 10 RETURNING *;
NOTICE:  Old row: name=John Doe, email=john.doe@example.com, ssn=123-45-6789, salary=50000.00
NOTICE:  New row: name=John Doe, email=john.doe@example.com, ssn=123-45-6789, salary=50000.00
NOTICE:  Old row: name=Jane Smith, email=jane.smith@example.com, ssn=987-65-4321, salary=60000.00
NOTICE:  New row: name=Jane Smith, email=jane.smith@example.com, ssn=987-65-4321, salary=60000.00
 id |    name    |         email          |     ssn     |  salary  
----+------------+------------------------+-------------+----------
 11 | John Doe   | john.doe@example.com   | XXX-XX-6789 | 45000.00
 12 | Jane Smith | jane.smith@example.com | XXX-XX-4321 | 54000.00
(2 rows)

ROLLBACK;


-- Triggers on views (which are supposed to see masked data for new/old row)
CREATE VIEW hr.view_over_employees AS SELECT * FROM hr.employees;
GRANT UPDATE, SELECT ON hr.view_over_employees TO analyst_role;

-- Create trigger for this view
CREATE TRIGGER print_changes_trigger
    INSTEAD OF UPDATE ON hr.view_over_employees
    FOR EACH ROW
    EXECUTE FUNCTION print_changes();

-- Masked new and old rows should be passed to trigger if trigger is on view
BEGIN;
SET ROLE mike_analyst;
UPDATE hr.view_over_employees SET id = id + 10 RETURNING *;
NOTICE:  Old row: name=John Doe, email=john.doe@example.com, ssn=XXX-XX-6789, salary=45000.00
NOTICE:  New row: name=John Doe, email=john.doe@example.com, ssn=XXX-XX-6789, salary=45000.00
NOTICE:  Old row: name=Jane Smith, email=jane.smith@example.com, ssn=XXX-XX-4321, salary=54000.00
NOTICE:  New row: name=Jane Smith, email=jane.smith@example.com, ssn=XXX-XX-4321, salary=54000.00
 id |    name    |         email          |     ssn     |  salary  
----+------------+------------------------+-------------+----------
 11 | John Doe   | john.doe@example.com   | XXX-XX-6789 | 45000.00
 12 | Jane Smith | jane.smith@example.com | XXX-XX-4321 | 54000.00
(2 rows)
ROLLBACK;

建議您先檢閱觸發行為，再對遮罩的資料表實作觸發。資料表觸發程序可存取轉換資料表中的未遮罩資料，而檢視觸發程序則會看到遮罩的資料。

範例重新命名遮罩政策

下列範例示範如何使用 rename_masking_policy 程序重新命名現有政策。

-- Rename the strict policy
CALL pgcolumnmask.rename_masking_policy(
    'employee_mask_strict',
    'hr.employees',
    'intern_protection_policy'
);

-- Verify the rename
SELECT policyname, roles, weight
    FROM pgcolumnmask.pg_columnmask_policies
    WHERE tablename = 'employees'
    ORDER BY weight DESC;

        policyname        |     roles      | weight 
--------------------------+----------------+--------
 employee_mask_light      | {analyst_role} |    100
 employee_mask_moderate   | {support_role} |     50
 intern_protection_policy | {intern_role}  |     10
範例更改政策權重

下列範例示範如何變更政策權重以變更其權重。

-- Change weight of moderate policy
CALL pgcolumnmask.alter_masking_policy(
    'employee_mask_moderate'::NAME,
    'hr.employees'::REGCLASS,
    NULL,    -- Keep existing masking expressions
    NULL,    -- Keep existing roles
    75       -- New weight
);

-- Verify the changes
SELECT policyname, roles, weight
    FROM pgcolumnmask.pg_columnmask_policies
    WHERE tablename = 'employees'
    ORDER BY weight DESC;
        policyname        |     roles      | weight 
--------------------------+----------------+--------
 employee_mask_light      | {analyst_role} |    100
 employee_mask_moderate   | {support_role} |     75
 intern_protection_policy | {intern_role}  |     10
範例清除

下列範例示範如何捨棄所有政策、資料表和使用者。

-- Drop policies
CALL pgcolumnmask.drop_masking_policy(
    'intern_protection_policy',
    'hr.employees'
);

CALL pgcolumnmask.drop_masking_policy(
    'employee_mask_moderate',
    'hr.employees'
);

CALL pgcolumnmask.drop_masking_policy(
    'employee_mask_light',
    'hr.employees'
);

-- Drop table and functions
DROP VIEW IF EXISTS hr.view_over_employees;
DROP TABLE IF EXISTS hr.employees;
DROP SCHEMA IF EXISTS hr;
DROP FUNCTION IF EXISTS public.mask_ssn(text);
DROP FUNCTION IF EXISTS public.mask_salary(numeric, numeric);

-- Drop users
DROP USER sarah_intern, lisa_support, mike_analyst,
    ethan_support_intern, john_analyst_intern;
DROP ROLE intern_role, support_role, analyst_role;