How do I use dynamic data masking in Aurora PostgreSQL-Compatible?

Resolution

Install the pg_columnmask extension

To activate dynamic data masking, run the following command in each database that you want to mask:

CREATE EXTENSION pg_columnmask;

Note: The extension installs per database. Connections that established before installation don't apply masking, and you must reconnect. Table owners and rds_superuser members are exempt from data masking rules.

Create a masking policy

To create a masking policy, you must be the table owner or a member of rds_superuser. Or, you must be a member of the role specified by the pgcolumnmask.policy_admin_rolname DB cluster parameter group parameter. To update the role to have masking policy permissions, see Configuring masking policy management role.

To create a masking policy, run the following create_masking_policy command:

CALL pgcolumnmask.create_masking_policy(
    'POLICY-NAME',
    'SCHEMA.TABLE',
    '{"COLUMN-NAME": "MASKING-EXPRESSION"}'::JSONB,
    ARRAY['ROLE-NAME'],
    WEIGHT
);

Note: Replace POLICY-NAME with a name for the policy, SCHEMA.TABLE with the target table, and COLUMN-NAME with the column that you want to mask. Also, replace MASKING-EXPRESSION with the masking function call, ROLE-NAME with the target role, and WEIGHT with a numeric priority value.

The weight parameter determines what policy takes effect when you belong to multiple roles. The policy with the highest weight value takes precedence. Each policy on the same table must have a unique weight value. If you don't specify a weight, the default value is 0.

To alter an existing masking policy, run the following alter_masking_policy command:

CALL pgcolumnmask.alter_masking_policy(
    'POLICY-NAME',
    'SCHEMA.TABLE',
    NULL,
    NULL,
    NEW-WEIGHT
);

Note: Replace POLICY-NAME with the existing policy name, SCHEMA.TABLE with the target table, and NEW-WEIGHT with the updated weight value. Pass NULL for any parameter that you don't want to change.

To drop a masking policy, run the following drop_masking_policy command:

CALL pgcolumnmask.drop_masking_policy(
    'POLICY-NAME',
    'SCHEMA.TABLE'
);

Note: Replace POLICY-NAME with the policy name and SCHEMA.TABLE with the target table.

For the full procedure reference, see Procedures for managing data masking policies.

Use built-in masking functions

Use pre-defined data masking functions.

The pgcolumnmask schema provides the three built-in mask_text, mask_email, and mask_timestamp functions. The mask_text function masks text values. The mask_email function masks email addresses and preserves the @ symbol and domain structure. The mask_timestamp function masks date and time components.

Create custom masking functions

Because pgcolumnmask is a managed schema, run the following command to create custom functions in a separate schema:

CREATE SCHEMA IF NOT EXISTS mask_custom;
GRANT USAGE ON SCHEMA mask_custom TO PUBLIC;

Example custom function with SSN masking:

CREATE OR REPLACE FUNCTION mask_custom.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;

Custom masking functions must meet the following requirements:

• Declare the function as IMMUTABLE or STABLE.
• Handle NULL explicitly to prevent data leaks on NULL input.
• Match the return type to the column data type.
• Avoid side effects such as INSERT, external calls, or state changes.
• Use BEGIN ATOMIC or schema-qualify all references to prevent search_path injection attacks.

It's a best practice to declare the function as PARALLEL SAFE to allow parallel query execution. For detailed guidance, see Best practices for secure pg_columnmask implementation.

Configure trigger restrictions

The pgcolumnmask.restrict_dml_triggers_for_masked_users and pgcolumnmask.restrict_iot_triggers_for_masked_users parameters have a default value set to Off. To restrict trigger activation for masked users, set both parameters to On.

To set the pgcolumnmask.restrict_dml_triggers_for_masked_users parameter to On, run the following command:

ALTER DATABASE postgres SET pgcolumnmask.restrict_dml_triggers_for_masked_users = on;

To set the pgcolumnmask.restrict_iot_triggers_for_masked_users parameter to On, run the following command:

ALTER ROLE support_role SET pgcolumnmask.restrict_iot_triggers_for_masked_users = on;

Resolve pg_dump restore failures

If you ran pg_dump as a masked target role, then the dump contains masked data. Masked values can violate UNIQUE, CHECK, or FK constraints during restore. To resolve this issue, verify that the target database has the pg_columnmask extension installed and that all roles referenced by policies exist on the target.

Resolve unexpected masking in materialized views

A materialized view applies only the view owner's masking policy at refresh time. If the view owner belongs to a masked target role, then the materialized view contains masked data regardless of the user that runs REFRESH MATERIALIZED VIEW. To resolve this issue, make the materialized view owner a role that isn't a masking target.

Related information

Aurora PostgreSQL pg_columnmask data movement scenarios