トリガー関数のマスキング動作について - Amazon Aurora

トリガー関数のマスキング動作について

pg_columnmask ポリシーをテーブルに適用するときは、マスキングがトリガー関数とどのように相互作用するかを理解することが重要です。トリガーは、INSERT、UPDATE、DELETE オペレーションなど、テーブル上の特定のイベントに応答して自動的に実行されるデータベース関数です。

デフォルトでは、DDM はトリガーのタイプに応じて異なるマスキングルールを適用します。

テーブルトリガー

移行テーブルのマスク解除 — テーブルのトリガー関数は、古い行バージョンと新しい行バージョンの両方で、移行テーブルのマスク解除されたデータにアクセスできます。

テーブル所有者はトリガーを作成し、データを所有するため、テーブルを効果的に管理するためのフルアクセスを持ちます

ビュートリガー (トリガーの代わりに)

移行テーブルがマスクされている – ビューのトリガー関数は、現在のユーザーのアクセス許可に従ってマスクされたデータを参照します

ビュー所有者はベーステーブル所有者とは異なる場合があり、基盤となるテーブルのマスキングポリシーを尊重する必要があります

2 つのサーバーレベルの設定パラメータは、マスクされたテーブルでトリガーの動作を制御します。これらは 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;