종단 간 워크플로에서 pg_columnmask 구현 - Amazon Aurora

종단 간 워크플로에서 pg_columnmask 구현

이 섹션에서는 민감한 데이터가 있는 샘플 직원 테이블을 사용하는 pg_columnmask의 전체 구현을 보여 줍니다. 사용자 지정 마스킹 함수를 생성하고, 다양한 역할(인턴, 지원, 분석가)에 대해 서로 다른 가중치 수준으로 여러 마스킹 정책을 정의하고, 단일 또는 여러 역할 멤버십을 가진 사용자가 서로 다른 수준의 마스킹 데이터를 보는 방법을 알아봅니다. 또한 이 예제에서는 RETURNING 절을 사용한 DML 문에서의 마스킹 동작, 테이블과 뷰의 트리거, 이름 변경, 가중치 변경, 정리를 포함한 정책 관리 작업을 다룹니다.

  1. 몇 가지 민감한 데이터가 포함된 샘플 테이블을 생성합니다.

    CREATE SCHEMA hr;

CREATE TABLE hr.employees (
    id INT PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    ssn TEXT,
    salary NUMERIC(10,2)
 );

INSERT INTO hr.employees VALUES
    (1, 'John Doe', 'john.doe@example.com', '123-45-6789', 50000.00),
    (2, 'Jane Smith', 'jane.smith@example.com', '987-65-4321', 60000.00);

  2. 사용자 지정 마스킹 함수 생성:

    CREATE OR REPLACE FUNCTION public.mask_ssn(ssn TEXT)
    RETURNS TEXT AS $$
    BEGIN
        RETURN 'XXX-XX-' || RIGHT(ssn, 4);
    END;
    $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.mask_salary(salary NUMERIC, multiplier NUMERIC DEFAULT 0.0)
    RETURNS NUMERIC AS $$
    BEGIN
        RETURN salary * multiplier;
    END;
    $$ LANGUAGE plpgsql;

  3. 사용자 역할에 따라 마스킹 수준이 다른 여러 정책을 생성합니다.

    -- Create different roles
CREATE ROLE analyst_role;
CREATE ROLE support_role;
CREATE ROLE intern_role;

GRANT USAGE ON SCHEMA hr TO analyst_role, support_role, intern_role;
GRANT SELECT ON hr.employees TO analyst_role, support_role, intern_role;
----------------------------------------------------------------------

-- Low-Weight Policy (Intern)
CALL pgcolumnmask.create_masking_policy(
    'employee_mask_strict',
    'hr.employees',
    JSON_BUILD_OBJECT('name', 'pgcolumnmask.mask_text(name, ''*'')',
                      'email', 'pgcolumnmask.mask_email(email)',
                      'ssn', 'pgcolumnmask.mask_text(ssn, ''*'')',
                      'salary', 'public.mask_salary(salary)')::JSONB,
    ARRAY['intern_role'],
    10  -- Lowest weight
);

----------------------------------------------------------------------
-- Medium-Weight Policy (Support)
CALL pgcolumnmask.create_masking_policy(
    'employee_mask_moderate',
    'hr.employees',
    JSON_BUILD_OBJECT('email', 'pgcolumnmask.mask_email(email, ''#'')',
                      'ssn', 'public.mask_ssn(ssn)',
                      'salary', 'public.mask_salary(salary)')::JSONB,
    ARRAY['support_role'],
    50   -- Medium weight
);

----------------------------------------------------------------------
-- High-Weight Policy (Analyst)
CALL pgcolumnmask.create_masking_policy(
    'employee_mask_light',
    'hr.employees',
    JSON_BUILD_OBJECT('ssn', 'public.mask_ssn(ssn)',
                      'salary', 'public.mask_salary(salary, 0.9)')::JSONB,
    ARRAY['analyst_role'],
    100   -- Highest weight
);

  4. 다음 예제는 다양한 사용자가 역할 멤버십 및 정책 가중치를 기반으로 데이터를 보는 방법을 보여 줍니다.

    -- Create users
CREATE USER sarah_intern;
GRANT intern_role TO sarah_intern;

CREATE USER lisa_support;
GRANT support_role TO lisa_support;

CREATE USER mike_analyst;
GRANT analyst_role TO mike_analyst;

CREATE USER ethan_support_intern;
GRANT support_role, intern_role TO ethan_support_intern;

CREATE USER john_analyst_intern;
GRANT analyst_role, intern_role TO john_analyst_intern;

    인턴으로서(가장 엄격한 마스킹):

    SET ROLE sarah_intern;

SELECT * FROM hr.employees;
 id |    name    |         email          |     ssn     | salary 
----+------------+------------------------+-------------+--------
  1 | ********   | XXXXXXXX@XXXXXXX.com   | *********** |   0.00
  2 | ********** | XXXXXXXXXX@XXXXXXX.com | *********** |   0.00

    지원 사용자로서(중간 수준 마스킹):

    SET ROLE lisa_support;

SELECT * FROM hr.employees;
 id |    name    |         email          |     ssn     | salary 
----+------------+------------------------+-------------+--------
  1 | John Doe   | ########@#######.com   | XXX-XX-6789 |   0.00
  2 | Jane Smith | ##########@#######.com | XXX-XX-4321 |   0.00

    분석가로서(가장 가벼운 마스킹):

    SET ROLE mike_analyst;

SELECT * FROM hr.employees;
 id |    name    |         email          |     ssn     |  salary  
----+------------+------------------------+-------------+----------
  1 | John Doe   | john.doe@example.com   | XXX-XX-6789 | 45000.00
  2 | Jane Smith | jane.smith@example.com | XXX-XX-4321 | 54000.00

    인턴이고 사용자를 지원하는 ethan_support_intern 사용자로서:

    SET ROLE ethan_support_intern;

-- masking policies appliable to this user: employee_mask_strict and employee_mask_moderate
-- id : unmasked because no masking policy appliable on ethan_support_intern
--            masks these columns
-- name : masked because of employee_mask_strict policy
-- email, ssn, salary : both employee_mask_strict and employee_mask_moderate mask these columns
--                      but employee_mask_moderate will be use because of higher weight 

SELECT * FROM hr.employees;
 id |    name    |         email          |     ssn     | salary 
----+------------+------------------------+-------------+--------
  1 | ********   | ########@#######.com   | XXX-XX-6789 |   0.00
  2 | ********** | ##########@#######.com | XXX-XX-4321 |   0.00

    인턴이자 분석가인 john_analyst_intern으로서:

    SET ROLE john_analyst_intern;

-- masking policies appliable to this user: employee_mask_strict and employee_mask_light
-- id : unmasked because no masking policy appliable on john_analyst_intern
--            masks these columns
-- name, email : masked because of employee_mask_strict
-- ssn, salary : both employee_mask_strict and employee_mask_light mask these columns
--               but employee_mask_light will be use because of higher weight 

SELECT * FROM hr.employees;
 id |    name    |         email          |     ssn     |  salary  
----+------------+------------------------+-------------+----------
  1 | ********   | XXXXXXXX@XXXXXXX.com   | XXX-XX-6789 | 45000.00
  2 | ********** | XXXXXXXXXX@XXXXXXX.com | XXX-XX-4321 | 54000.00