安全 pg_columnmask 实施的最佳实践

下一节提供了在 Aurora PostgreSQL 环境中实施 pg_columnmask 的安全最佳实践。请遵循以下建议，以便：

建立基于角色的安全访问控制架构
开发用于防范安全漏洞的掩蔽函数
使用掩蔽数据了解和控制触发器行为

基于角色的安全架构

定义角色层次结构，以便在数据库中实施访问控制。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;

带有 pg_columnmask 的 DML 触发器行为

对于表触发器，将对转换表完全不掩蔽。对于视图触发器（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 的视图上的触发器（Instead of 触发器）

只能在 PostgreSQL 中的视图上创建触发器。它们用于对不可更新的视图运行 DML 语句。传输表始终在 Instead of 触发器（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;

用于控制触发器行为的数据库/用户级别 GuC

两个配置参数控制具有适用掩蔽策略的用户的触发器执行行为。当需要额外的安全限制时，使用这些参数可以防止对掩蔽表或视图执行触发器。默认情况下，这两个参数均处于禁用状态，可让触发器正常执行。

第一个 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 在您的浏览器中被禁用或不可用。

要使用 Amazon Web Services 文档，必须启用 Javascript。请参阅浏览器的帮助页面以了解相关说明。

掩蔽策略管理角色

数据移动场景

安全 pg_columnmask 实施的最佳实践

基于角色的安全架构

例 ：创建组织角色层次结构

安全的掩蔽函数开发

带有 pg_columnmask 的 DML 触发器行为

例：创建组织角色层次结构