# Using dynamic masking with Aurora PostgreSQL
Dynamic data masking is a security feature that protects sensitive data in Aurora PostgreSQL databases by controlling how data appears to users at query time. Aurora implements it through the `pg_columnmask` extension. `pg_columnmask` provides column-level data protection that complements PostgreSQL's native row-level security and granular access control mechanisms.
With `pg_columnmask`, you create masking policies that determine data visibility based on user roles. When users query tables with masking policies, Aurora PostgreSQL applies the appropriate masking function at query time based on the user's role and policy weight. The underlying data remains unchanged in storage.
`pg_columnmask` supports the following capabilities:
+ **Built-in and custom masking functions** – Use pre-built functions for common patterns like email and text masking, or create your own custom functions to protects sensitive data (PII) through SQL-based masking policies.
+ **Multiple masking strategies** – Completely hide information, replace partial values with wildcards, or define custom masking approaches.
+ **Policy prioritization** – Define multiple policies for a single column. Use weights to determine which masking policy should be used when multiple policies apply to a column. Aurora PostgreSQL applies policies based on weight and user role membership.
`pg_columnmask` is available on Aurora PostgreSQL version 16.10 and higher, and version 17.6 and higher. It is available is available at no additional cost.
# Getting started with dynamic masking
To dynamically mask data, you install the `pg_columnmask` extension in your database and create masking policies for your tables. The setup process involves prerequisite verification, extension installation, role configuration, policy creation, and validation testing.
## Extension installation and configuration
Connect to your Aurora PostgreSQL cluster using the RDS Console Query Editor or a PostgreSQL client such as psql with rds\$1superuser (master user) credentials.
Execute the extension creation command to enable `pg_columnmask` functionality:
```
CREATE EXTENSION pg_columnmask;
```
This command installs the `pg_columnmask` extension, creates the necessary catalog tables, and registers the built-in masking functions. The extension installation is database-specific, meaning you must install it separately in each database where the functionality is required.
**Note**
Connections made before installing this extension will still show unmasked data. Close and reconnect to fix this.
Verify the extension installation by checking the available masking functions:
```
SELECT proname FROM pg_proc
WHERE pronamespace = 'pgcolumnmask'::regnamespace AND proname LIKE 'mask_%';
proname
--------Output --------
mask_email
mask_text
mask_timestamp
(3 rows)
```
# Procedures for managing data masking policies
You can manage masking policies using procedures provided by the `pg_columnmask` extension. To create, modify, or drop masking policies, you must have one of the following privileges:
+ Owner of the table on which you are creating the `pg_columnmask` policy.
+ Member of `rds_superuser`.
+ Member of `pg_columnmask` policy manager role set by the `pgcolumnmask.policy_admin_rolname` parameter.
The following command creates a table that is used in subsequent sections:
```
CREATE TABLE public.customers (
id SERIAL PRIMARY KEY,
name TEXT,
phone TEXT,
address TEXT,
email TEXT
);
```
## CREATE\$1MASKING\$1POLICY
The following procedure creates a new masking policy for a user table:
**Syntax**
```
create_masking_policy(
policy_name,
table_name,
masking_expressions,
roles,
weight)
```
**Arguments**
| Parameter | Datatype | Description |
| --- | --- | --- |
| policy\$1name | NAME | The name of the masking policy. Must be unique per table. |
| table\$1name | REGCLASS | The qualified/unqualified name or oid of the table to apply masking policy. |
| masking\$1expressions | JSONB | JSON object containing column name and masking function pairs. Each key is a column name and its value is the masking expression to be applied on that column. |
| roles | NAME[] | The roles to which this masking policy applies. Default is PUBLIC. |
| weight | INT | Weight of the masking policy. When multiple policies are applicable to a given user's query, the policy with the highest weight (higher integer number) will be applied to each masked column. Default is 0. No two masking policies on the table can have the same wieght. |
**Return type**
None
**Example of creating a masking policy that masks the email column for the `test_user` role:**
```
CALL pgcolumnmask.create_masking_policy(
'customer_mask',
'public.customers',
JSON_OBJECT('{
"email", "pgcolumnmask.mask_email(email)"
}')::JSONB,
ARRAY['test_user'],
100
);
```
## ALTER\$1MASKING\$1POLICY
This procedure modifies an existing masking policy. `ALTER_MASKING_POLICY` can modify the policy masking expressions, set of roles to which the policy applies and the weight of the masking policy. When one of those parameters is omitted, the corresponding part of the policy is unchanged.
**Syntax**
```
alter_masking_policy(
policy_name,
table_name,
masking_expressions,
roles,
weight)
```
**Arguments**
| Parameter | Datatype | Description |
| --- | --- | --- |
| policy\$1name | NAME | Existing name of the masking policy. |
| table\$1name | REGCLASS | The qualified/unqualified name oid of the table containing the masking policy. |
| masking\$1expressions | JSONB | New JSON object containing column name and masking function pairs or NULL otherwise. |
| roles | NAME[] | The list of new roles to which this masking policy applies or NULL otherwise. |
| weight | INT | New weight for the masking policy or NULL otherwise. |
**Return type**
None
**Example of adding the analyst role to an existing masking policy without changing other policy attributes.**
```
CALL pgcolumnmask.alter_masking_policy(
'customer_mask',
'public.customers',
NULL,
ARRAY['test_user', 'analyst'],
NULL
);
-- Alter the weight of the policy without altering other details
CALL pgcolumnmask.alter_masking_policy(
'customer_mask',
'customers',
NULL,
NULL,
4
);
```
## DROP\$1MASKING\$1POLICY
This procedure removes an existing masking policy.
**Syntax**
```
drop_masking_policy(
policy_name,
table_name)
```
**Arguments**
| Parameter | Datatype | Description |
| --- | --- | --- |
| policy\$1name | NAME | Existing name of the masking policy. |
| table\$1name | REGCLASS | The qualified/unqualified name oid of the table containing the masking policy. |
**Return type**
None
**Example of dropping the masking policy customer\$1mask**
```
-- Drop a masking policy
CALL pgcolumnmask.drop_masking_policy(
'customer_mask',
'public.customers',
);
```
# Escaping identifiers in masking policy DDL procedure
When creating data masking policies with quoted identifiers, proper escaping is required to ensure correct object references and policy application. To use quoted identifiers inside the `pg_columnmask` masking policy management procedures:
+ **Policy name** – Must be enclosed in double quotes.
+ **Table name** – Both schema name and table name must be enclosed in double quotes individually when required.
+ **Masking expressions** – Column and function names in masking expressions must be enclosed in double quotes and the quotes themselves must be escaped using a backslash.
+ **Roles** – The array of role names is automatically quoted. The role name should exactly match the name as seen in `pg_roles` including case sensitivity.
**Example of escaping and quoting syntax**
This example shows the proper escaping and quoting syntax when creating masking policies for tables, columns, functions, and roles that use mixed-case names or require quoted identifiers in Aurora PostgreSQL.
```
-- Create a table and columns with mixed case name
CREATE TABLE public."Employees" (
"Name" TEXT,
"Email" TEXT,
ssn VARCHAR(20)
);
-- Create a role with mixed case name
CREATE ROLE "Masked_user";
-- Create a function with mixed case name
CREATE OR REPLACE FUNCTION public."MaskEmail"(text)
RETURNS character varying
LANGUAGE plpgsql
IMMUTABLE PARALLEL SAFE
AS $$ BEGIN
RETURN 'XXXXXXXX'::text;
END $$;
-- Now use these objects with mixed case names in
-- masking policy management procedures
CALL pgcolumnmask.create_masking_policy(
'"Policy1"', -- policy name should be surrounded with double quotes for quoting
'public."Employees"', -- table and schema name should be individually
-- surrounded with double quotes for quoting
JSON_OBJECT('{
"\"Email\"", "\"MaskEmail\"(\"Email\")"
}')::JSONB, -- masking expression should have double quotes around function names
-- and columns names etc when needed. Also the double quotes itself
-- should be escaped using \ (backslash) since this is a JSON string
ARRAY['Masked_user'], -- Rolename do not need quoting
-- (this behaviour may change in future release)
100
);
SELECT * FROM pgcolumnmask.pg_columnmask_policies
WHERE tablename = 'Employees';
-[ RECORD 1 ]-----+-------------------------------------
schemaname | public
tablename | Employees
policyname | Policy1
roles | {Masked_user}
masked_columns | {Email}
masking_functions | {"(\"MaskEmail\"(\"Email\"))::text"}
weight | 100
```
## Administrative views
You can review all the `pg_columnmask` policy using the publicly accessible `pgcolumnmask.pg_columnmask_policies` administrative view. Following information is available using this view. The view only returns the masking policies owned by current user.
| Column name | Data type | Description |
| --- | --- | --- |
| schemaname | NAME | Schema of the relation to which the policy is attached |
| tablename | NAME | Name of the relation to which the policy is attached |
| policyname | NAME | Name of the masking policy, all masking policies have unique names |
| roles | TEXT[] | Role to which policy applies. |
| masked\$1columns | TEXT[] | Masked columns |
| masking\$1functions | TEXT[] | Masking functions |
| weight | INT | Weight of the attached policy |
# Pre-defined data masking functions
`pg_columnmask` extension provides built-in utility functions written in C language (for faster execution) which can be used as masking expression for `pg_columnmask` policies.
**mask\$1text**
A function to mask text data with configurable visibility options.
**Arguments**
| Parameter | Datatype | Description |
| --- | --- | --- |
| input | TEXT | The original text string to be masked |
| mask\$1char | CHAR(1) | Character used for masking (default: 'X') |
| visible\$1prefix | INT | Number of characters at the beginning of input text that will remain unmasked (default: 0) |
| visible\$1suffix | INT | Number of characters at the end of input text that will remain unmasked (default: 0) |
| use\$1hash\$1mask | BOOLEAN | If TRUE, uses a hash-based masking instead of mask\$1char (default: FALSE) |
**Example of using different masking options**
Mask the entire input string with the default 'X' character
```
postgres=> SELECT pgcolumnmask.mask_text('Hello World');
mask_text
-------------
XXXXXXXXXXX
```
Use the `mask_char` argument to mask text input using a different character
```
postgres=> SELECT pgcolumnmask.mask_text('Hello World', '*');
mask_text
-------------
***********
```
Use `visible_prefix` and `visible_suffix` parameters to control how many characters remain unmasked at the start and end of the text
```
postgres=> SELECT pgcolumnmask.mask_text('Hello World', '*', 5, 1);
mask_text
-------------
Hello*****d
```
When `use_hash_mask` is true the input string is masked using random characters `mask_char` argument is ignored but `visible_prefix` and `visible_suffix` are still honored
```
postgres=> SELECT pgcolumnmask.mask_text('Hello World', '*', 2, 2, true);
mask_text
-------------
Hex36dOHild
```
**mask\$1timestamp**
| Parameter | Datatype | Description |
| --- | --- | --- |
| ts\$1to\$1mask | TIMESTAMP | The original timestamp to be masked |
| mask\$1part | TEXT | Specifies which part of the timestamp to mask (default: 'all') Valid values: 'year', 'month', 'day', 'hour', 'minute', 'second', 'all' |
| mask\$1value | TIMESTAMP | The timestamp value to use for masking (default: '1900-01-01 00:00:00') |
**Example of using `mask_timestamps`**
These examples demonstrate complete timestamp masking to a default value, partial masking of specific timestamp components (year only), and masking with a custom replacement value.
Completely mask input value to the default timestamp
```
postgres=> SELECT pgcolumnmask.mask_timestamp('2023-06-15 14:30:00');
mask_timestamp
---------------------
1900-01-01 00:00:00
```
To mask only one part of the timestamp from example only the year
```
postgres=> SELECT pgcolumnmask.mask_timestamp('2023-06-15 14:30:00', 'year');
mask_timestamp
---------------------
1900-06-15 14:30:00
```
To change the masked value for timestamp use the `mask_value` argument
```
postgres=> SELECT pgcolumnmask.mask_timestamp('2023-06-15 14:30:00', 'all', '2012-12-12 12:12:12');
mask_timestamp
---------------------
2012-12-12 12:12:12
```
**mask\$1timestamp**
A function to mask email addresses while preserving email structure.
| Parameter | Datatype | Description |
| --- | --- | --- |
| input | TEXT | The original email address to be masked |
| mask\$1char | CHAR(1) | Character used for masking (default: 'X') |
| mask\$1local | BOOLEAN | If TRUE, masks the local part of email (before @) (default: TRUE) |
| mask\$1domain | BOOLEAN | If TRUE, masks the domain part of email (after @) (default: TRUE) |
**Example of using `mask_email`**
These examples demonstrate complete email masking, custom mask characters, and selective masking of either the local part or domain part of the email address.
Complete masking
```
postgres=> SELECT pgcolumnmask.mask_email('user@example.com');
mask_email
------------------
XXXX@XXXXXXX.com
```
Use `mask_char` to change the character used for masking
```
postgres=> SELECT pgcolumnmask.mask_email('user@example.com', '*');
mask_email
------------------
****@*******.com
```
Use `mask_local` and `mask_domain` to control masking on local and domain
```
postgres=> SELECT pgcolumnmask.mask_email('user@example.com', '*', true, false);
mask_email
------------------
****@example.com
postgres=> SELECT pgcolumnmask.mask_email('user@example.com', '*', false, true);
mask_email
------------------
user@*******.com
```
# Implementing pg\$1columnmask in an end-to-end workflow
This section demonstrates a complete implementation of `pg_columnmask` using a sample employee table with sensitive data. You'll learn how to create custom masking functions, define multiple masking policies with different weight levels for various roles (intern, support, analyst), and observe how users with single or multiple role memberships see different levels of masked data. The examples also cover masking behavior in DML statements with RETURNING clauses, triggers on tables versus views, and policy management operations including renaming, altering weights, and cleanup.
1. Create a sample table with some sensitive data:
```
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);
```
1. Create custom masking functions:
```
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;
```
1. Create multiple policies with different masking levels based on user roles:
```
-- 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
);
```
1. The following examples demonstrate how different users see data based on their role membership and policy weights.
```
-- 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;
```
As an intern (strictest masking):
```
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
```
As a support user (moderate masking):
```
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
```
As an analyst (lightest masking):
```
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
```
As ethan\$1support\$1intern user which is both intern and support user:
```
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
```
As a john\$1analyst\$1intern which is both intern and analyst:
```
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
```
# Understanding masking behavior in DML operations
`pg_columnmask` applies consistently across all DML operations, including INSERT, UPDATE, DELETE, and MERGE statements. When you execute these operations, Aurora PostgreSQL masks data according to a core principle – any data read from storage is masked according to the current user's applicable policies.
Masking affects some of the following query components like:
+ WHERE clauses
+ JOIN conditions
+ Subqueries
+ RETURNING clauses
All of these components operate on masked values, not the original data. While data is written to storage unmasked, users only see their masked view when reading it back.
Aurora PostgreSQL enforces all database constraints (NOT NULL, UNIQUE, CHECK, FOREIGN KEY) on the actual stored values, not masked values. This can occasionally create apparent inconsistencies if masking functions aren't carefully designed.
Masking works alongside column-level permissions:
+ Users without SELECT privileges cannot read columns
+ Users with SELECT privileges see masked values according to their applicable policies
# Understanding masking behavior in trigger functions
When `pg_columnmask` policies are applied to tables, it's important to understand how masking interacts with trigger functions. Triggers are database functions that execute automatically in response to certain events on a table, such as INSERT, UPDATE, or DELETE operations.
By default, DDM applies different masking rules depending on the type of trigger:
Table triggers
**Transition tables are unmasked** – Trigger functions on tables have access to unmasked data in their transition tables for both old and new row versions
Table owners create triggers and own the data, so they have full access to manage their tables effectively
View Triggers (INSTEAD OF Triggers)
**Transition tables are masked** – Trigger functions on views see masked data according to the current user's permissions
View owners may differ from base table owners and should respect masking policies on underlying tables
Two server-level configuration parameters control trigger behavior with masked tables. These can only be set by `rds_superuser`:
+ **Restrict Triggers on Masked Tables** – Prevents trigger execution when a masked user performs DML operations on tables with applicable masking policies.
+ **Restrict Triggers on Views with Masked Tables:** – Prevents trigger execution on views when the view definition includes tables with masking policies applicable to the current user.
**Example of differences between function application to table and view**
The following example creates a trigger function that prints old and new row values, then demonstrates how the same function behaves differently when attached to a table versus a view.
```
-- 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;
```
We recommend reviewing trigger behavior before implementing triggers on masked tables. Table triggers have access to unmasked data in transition tables, while view triggers see masked data.
**Example of renaming masking policy**
The following example demonstrates how to rename existing policies using the `rename_masking_policy` procedure.
```
-- 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
```
**Example of altering policy weight**
The following example demonstrates how to alter policy weights to change their weight.
```
-- 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
```
**Example of cleaning up**
The following example demonstrates how to drop all policies, tables and users.
```
-- 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;
```
# Configuring masking policy management role
The PostgreSQL column masking extension, `pg_columnmask`, allows you to delegate the management of masking policies to a specific role, rather than requiring `rds_superuser` or table owner privileges. This provides more granular control over who can create, alter, and drop masking policies.
To configure the role that will have masking policy management privileges, follow these steps:
1. Create the policy admin role – As an `rds_superuser`, create a new role responsible for managing masking policies:
```
CREATE ROLE mask_admin NOLOGIN;
```
1. Configure the PostgreSQL parameter – In your custom DB cluster parameter group, set the `pgcolumnmask.policy_admin_rolname` engine configuration parameter to the name of the role you created:
```
pgcolumnmask.policy_admin_rolname = mask_admin
```
This engine configuration parameters can be set in a DB cluster parameter group and it does not require an instance reboot. For details on updating parameter, see [Modifying parameters in a DB cluster parameter groupin Amazon Aurora](USER_WorkingWithParamGroups.ModifyingCluster.md).
1. Grant the role to users As an `rds_superuser`, grant the `mask_admin` role to the users who should be able to manage masking policies:
```
CREATE USER alice LOGIN;
CREATE USER bob LOGIN;
GRANT mask_admin TO alice, bob;
```
Additionally, ensure that the users have USAGE privilege on the schemas where they will be managing masking policies:
```
GRANT USAGE ON SCHEMA hr TO alice, bob;
```
Now, when users `alice` and `bob` connect to the database, they can use the standard `pg_columnmask` extension functions to create, alter, and drop masking policies on all tables in all the schemas where they have `USAGE` privilege on the schema.
# Best practices for secure pg\$1columnmask implementation
The following section provides security best practices for implementing `pg_columnmask` in your Aurora PostgreSQL environment. Follow these recommendations to:
+ Establish a secure role-based access control architecture
+ Develop masking functions that prevent security vulnerabilities
+ Understand and control trigger behavior with masked data
## Role-based security architecture
Define a role hierarchy to implement access controls in your database. Aurora PostgreSQL `pg_columnmask` augments these controls by providing an additional layer for fine-grained data masking within those roles.
Create dedicated roles that align with organizational functions rather than granting permissions to individual users. This approach provides better auditability and simplifies permission management as your organizational structure evolves.
**Example of creating an organzational role heirarchy**
The following example creates an organizational role hierarchy with dedicated roles for different functions, then assigns individual users to the appropriate roles. In this example, organizational roles (analyst\$1role, support\$1role) are created first, then individual users are granted membership in these roles. This structure allows you to manage permissions at the role level rather than for each individual user.
```
-- 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;
```
Implement the principle of least privilege by granting only the minimum permissions necessary for each role. Avoid granting broad permissions that could be exploited if credentials are compromised.
```
-- 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;
```
Policy administrators require `USAGE` privileges on schemas where they manage masking policies. Grant these privileges selectively, following the principle of least privilege. Conduct regular reviews of schema access permissions to ensure only authorized personnel maintain policy management capabilities.
The policy admin role parameter configuration is restricted to database administrators only. This parameter cannot be modified at the database or session level, preventing unprivileged users from overriding policy admin assignments. This restriction ensures that masking policy control remains centralized and secure.
Assign the policy admin role to specific individuals rather than groups. This targeted approach ensures selective access to masking policy management, as policy administrators have the ability to mask all tables within the database.
## Secure masking function development
Develop masking functions using early binding semantics to ensure proper dependency tracking and prevent late binding vulnerabilities such as search path modification during runtime. It is recommended to use `BEGIN ATOMIC` syntax for SQL functions to enable compile-time validation (i.e. early binding) and dependency management.
```
-- 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;
```
Alternatively, create functions that are immune to search path changes by explicitly schema qualifying all object references, ensuring consistent behavior across different user sessions.
```
-- 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;
$$;
```
Implement input validation within masking functions to handle edge cases and prevent unexpected behavior. Always include NULL handling and validate input formats to ensure consistent masking behavior.
```
-- 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;
```
## DML Triggers behavior with pg\$1columnmask
For table triggers, transition tables will be fully unmasked. For view triggers(IOT), transition tables will be masked according to the current user's view permissions.
Table triggers with pg\$1columnmask
Triggers are passed a transition table which contains the old and new version of the rows modified by the firing DML query. Depending upon when the trigger is fired, Aurora PostgreSQL populates the old and new rows. For example, a `BEFORE INSERT` trigger only has new versions of the rows and empty old versions because there is no old version to refer.
`pg_columnmask` does not mask transition tables inside triggers on tables. Triggers can use masked columns inside their body and it sees unmasked data. The trigger creator should make sure how the trigger gets executed for an user. The following example works correctly in this case.
```
-- 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;
```
Trigger creator leaks unmasked data to user if they are not careful about the statements they use in their trigger body. For example using a `RAISE NOTICE ‘%’, masked_column;` prints the column to current user.
```
-- 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
```
Triggers on views with pg\$1columnmask (Instead of triggers)
Triggers can only be created on views in PostgreSQL. They are used for running DML statements on views that are not updatable. Transit tables are always masked inside instead of trigger (IOT), because the view and the base tables used inside the view query could have different owners. In which case, base tables might have some masking policies applicable on the view owner and the view owner must always see masked data from base tables inside its triggers. This is different from triggers on tables because in that case the trigger creator and the data inside the tables are owned by the same user which is not the case here.
```
-- 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;
```
Database/User level GuCs to control triggers behavior
Two configuration parameters control trigger execution behavior for users with applicable masking policies. Use these parameters to prevent triggers from executing on masked tables or views when additional security restrictions are required. Both parameters are disabled by default, allowing triggers to execute normally.
**First GUC: Trigger firing restriction on masked tables**
Specifications:
+ Name: `pgcolumnmask.restrict_dml_triggers_for_masked_users`
+ Type: `boolean`
+ Default: `false` (triggers are allowed to be executed)
Prevents trigger execution on masked tables for masked users when set to TRUE. `pg_columnmask` runs through the error.
**Second GUC: Trigger firing restriction on views with masked tables**
Specifications:
+ Name: `pgcolumnmask.restrict_iot_triggers_for_masked_users`
+ Type: `boolean`
+ Default: `false` (triggers are allowed to be executed)
Prevents trigger execution on views that include masked tables in their definition for masked users when set to TRUE.
These parameters operate independently and are configurable like standard database configuration parameters.
# Aurora PostgreSQL pg\$1columnmask data movement scenarios
`pg_columnmask` behavior varies across different data movement operations depending on whether the operation occurs at the storage, logical, or application layer. Storage-level operations (such as cloning) behave differently from logical operations (such as `pg_dump`) and application-level operations (such as FDW queries). This section describes masking behavior for common scenarios including replication, backups, exports, and migrations, and explains the security implications for each.
**Topics**
+ [
## Aurora Global Database and Read Replicas
](#AuroraPostgreSQL.Security.DynamicMasking.DataMovement.RR)
+ [
## Database clone and snapshot restore
](#AuroraPostgreSQL.Security.DynamicMasking.DataMovement.Clones)
+ [
## Logical replication
](#AuroraPostgreSQL.Security.DynamicMasking.DataMovement.LogRep)
+ [
## Blue/Green deployments
](#AuroraPostgreSQL.Security.DynamicMasking.DataMovement.BlueGreen)
+ [
## Zero-ETL and CDC streams
](#AuroraPostgreSQL.Security.DynamicMasking.DataMovement.ZETL)
+ [
## Amazon Database Migration Service
](#AuroraPostgreSQL.Security.DynamicMasking.DataMovement.DMS)
+ [
## Data exports
](#AuroraPostgreSQL.Security.DynamicMasking.DataMovement.DataExport)
+ [
## Views and materialized views
](#AuroraPostgreSQL.Security.DynamicMasking.DataMovement.Views)
+ [
## Data dump and restore
](#AuroraPostgreSQL.Security.DynamicMasking.DataMovement.DDR)
+ [
## Foreign data wrapper
](#AuroraPostgreSQL.Security.DynamicMasking.DataMovement.FDQ)
## Aurora Global Database and Read Replicas
Aurora `pg_columnmask` policies are stored in database system tables within the cluster volume. All replicas access the same policies and return consistently masked results. For Aurora Global Database deployments, `pg_columnmask` policies replicate to secondary Amazon Web Services Regions along with other database system tables, ensuring consistent data protection across regions. During failover scenarios, all `pg_columnmask` policies remain intact and functional.
## Database clone and snapshot restore
Aurora Fast Clone and snapshot restore operations preserve all `pg_columnmask` policies, roles, and configurations as part of the database system tables. The cloned or restored database inherits all existing policies from the source cluster. After cloning or restoration, each database cluster maintains independent `pg_columnmask` policies.
## Logical replication
During initial synchronization, logical replication uses standard SQL COPY operations, and `pg_columnmask` policies are enforced based on the replication user's permissions. During ongoing CDC (change data capture), masking policies are not applied and unmasked data is replicated through WAL records. Users with `pg_create_subscription` privileges can potentially exfiltrate unmasked data by setting up replication to a system they control.
## Blue/Green deployments
During snapshot restoration, `pg_columnmask` policies are automatically included. The green environment starts with an identical copy of all policies from the blue environment. During replication from blue to green, data is not masked. Subsequent masking policy changes (DDL commands) on the blue cluster do not replicate to the green cluster and invalidate RDS blue/green deployments.
## Zero-ETL and CDC streams
Data replication is not affected by `pg_columnmask` policies. Zero-ETL supports DDL replication but doesn't replicate `pg_columnmask` or RLS policies. No masking policies are applied to replicated data in Zero-ETL.
## Amazon Database Migration Service
Initial data sync is masked or unmasked based on the user selected for the DMS task. CDC data is always unmasked. While `pg_columnmask` related internal RLS policies may be migrated, they won't function on non-pg\$1columnmask-enabled targets.
## Data exports
`pg_columnmask` treats exports like any other query operation—masking is applied based on the executing user's permissions. This applies to SQL commands like COPY, SELECT INTO, CREATE TABLE AS, and Aurora PostgreSQL's S3 export functionality.
**Note**
When masked users export data, the resulting files contain masked values that may violate database constraints when restored.
## Views and materialized views
Keep the following considerations in mind when using views:
+ **Regular views** – Always use `INVOKER` semantics. The current user's masking policies apply when querying the view, regardless of who created the view.
+ **Materialized views** – When refreshed, the masking policies of the materialized view owner apply, not the policies of the user performing the refresh. If the owner has masking policies, the materialized view always contains masked data.
## Data dump and restore
`pg_dump` operates as a regular database user and applies masking policies based on the connecting user's permissions. If a masked user performs a dump, the backup file contains masked data. `pg_columnmask` policies are included in the dump as part of the database schema. Successful restoration requires that all referenced roles exist in the target database and that the target has the `pg_columnmask` extension installed.
**Note**
Starting with PostgreSQL 18, `pg_dump` supports the `—no-policies` option which excludes both Row Level Security (RLS) and `pg_columnmask` masking policies from database dumps. For more information, see [pg\$1dump](https://www.postgresql.org/docs/current/app-pgdump.html).
## Foreign data wrapper
When using foreign data wrappers, masking policies on remote tables are applied based on the mapped user's permissions on the source server, not the local querying user's permissions, and while you can access masked remote data through FDW, you cannot create DDM or RLS policies directly on foreign tables in your local database.