Configure dynamic data masking policies to protect sensitive data - AnalyticDB

Dynamic data masking (DDM) lets you protect sensitive columns in AnalyticDB for PostgreSQL without changing the underlying data. Define a masking policy once, and AnalyticDB for PostgreSQL automatically rewrites queries at runtime — showing masked values to restricted users while privileged users continue to see plaintext.

Prerequisites

Before you begin, ensure that you have:

An AnalyticDB for PostgreSQL V6.0 instance at V6.6 or later, or a V7.0 instance at V7.0.3 or later
(Optional) Links to check or update your minor version: Query the minor engine version and Update the minor engine version

Note

AnalyticDB for PostgreSQL instances in Serverless mode do not support dynamic data masking.

How it works

Dynamic data masking intercepts query results at runtime and applies masking functions to sensitive columns. The original data is never modified.

Assign a masking function to each sensitive column using CREATE REDACTION POLICY.
Define a WHEN expression to control which users see masked data.
When a restricted user queries the table, AnalyticDB for PostgreSQL rewrites the query automatically — for example, SELECT phone FROM employees becomes SELECT mask_partial(phone, ...) FROM employees.
Privileged users (table owners and accounts with the RDS_SUPERUSER permission) always see plaintext, regardless of any masking policy.

Limitations

Only table owners and accounts with RDS_SUPERUSER can create, modify, or delete masking policies.
Table owners and RDS_SUPERUSER accounts always see plaintext data, even when a masking policy is active.
Dynamic data masking policies apply to regular tables only. System tables, external tables, temporary tables, unlogged tables, views, and materialized views are not supported.
Each table has one masking policy, and each column can have at most one masking function.
If you omit the WHEN clause when creating a policy, the policy does not take effect. Always specify the WHEN clause.
COPY TO and INSERT INTO export masked values, not plaintext. Calculations on masked data produce incorrect results because masking is irreversible. Mask only the columns that require protection.

Create a masking policy

Use CREATE REDACTION POLICY to assign masking functions to sensitive columns and define when the policy applies.

Syntax

CREATE REDACTION POLICY <policy_name> ON <table_name>
    [ WHEN (<when_expression>) ]
    [ ADD COLUMN <column_name> WITH <mask_function_name> ( [ argument [, ...] ] )] [, ... ];

Parameters

Parameter	Description
`policy_name`	Name of the masking policy.
`table_name`	Name of the table to protect.
`WHEN (<when_expression>)`	Expression that determines which users see masked data. The expression must return a Boolean value. Use `AND` or `OR` to combine sub-expressions, and `IN` or `NOT IN` to target specific users. To apply the policy to all users, use `WHEN (1=1)`. If you omit this clause, the policy does not take effect.
`column_name`	Name of the column to mask.
`mask_function_name`	Masking function to apply. The return type must match the column type.
`argument`	Arguments passed to the masking function. Use built-in masking functions or define custom ones in SQL or PL/pgSQL.

Examples

Custom masking function, applied to a specific user

-- Create a table
CREATE TABLE test_mask(id INT, name TEXT);

-- Define a custom masking function
CREATE OR REPLACE FUNCTION mask_text(input_text text) RETURNS text AS $$
BEGIN
  RETURN REPEAT('*', CHAR_LENGTH(input_text));
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Create a policy that masks the name column for bob only
CREATE REDACTION POLICY test_mask_policy ON test_mask
  WHEN (CURRENT_USER = 'bob')
  ADD COLUMN name WITH mask_text(name);

Built-in masking function, applied to all users

-- WHEN (true) applies the policy to all users
CREATE REDACTION POLICY test_mask_policy ON test_mask
  WHEN (true)
  ADD COLUMN name WITH mask_full_str(name);

For all available built-in masking functions, see Masking functions.

Modify a masking policy

Use ALTER REDACTION POLICY to update an existing masking policy.

Syntax

-- Change the WHEN expression
ALTER REDACTION POLICY <policy_name> ON <table_name> WHEN (<new_when_expression>);

-- Enable or disable the policy
ALTER REDACTION POLICY <policy_name> ON <table_name> ENABLE | DISABLE;

-- Rename the policy
ALTER REDACTION POLICY <policy_name> ON <table_name> RENAME TO <new_policy_name>;

-- Add, modify, or remove a masked column
ALTER REDACTION POLICY policy_name ON table_name
    ADD COLUMN <column_name> WITH <function_name> ( arguments )
  | ALTER COLUMN <column_name> WITH <function_name> ( arguments )
  | DROP COLUMN <column_name>;

Parameters

Parameter	Description
`policy_name`	Name of the masking policy to modify.
`table_name`	Name of the table whose policy you want to modify.
`new_when_expression`	Replacement expression for the `WHEN` clause.
`ENABLE \| DISABLE`	Enable or disable the masking policy.
`new_policy_name`	New name for the policy.
`column_name`	Column to add, modify, or remove. When adding a column, specify one not already configured. When modifying or removing, specify an existing masked column.
`function_name`	Masking function to apply to the column.
`argument`	Arguments for the masking function.

Examples

-- Apply the policy to specific users
ALTER REDACTION POLICY mask_emp ON employees WHEN(current_user in ('alice', 'bob'));

-- Apply the policy to all users (table owner still sees plaintext)
ALTER REDACTION POLICY mask_emp ON employees WHEN(1=1);

-- Disable and re-enable a policy
ALTER REDACTION POLICY mask_emp ON employees DISABLE;
ALTER REDACTION POLICY mask_emp ON employees ENABLE;

-- Rename a policy
ALTER REDACTION POLICY mask_emp ON employees RENAME TO new_mask_emp;

-- Add a masked column
ALTER REDACTION POLICY mask_emp_new ON emp ADD COLUMN name WITH mask_none(name);

-- Change the masking function for a column
ALTER REDACTION POLICY mask_emp_new ON emp ALTER COLUMN name WITH mask_none(name);

-- Remove a masked column
ALTER REDACTION POLICY mask_emp_new ON emp DROP COLUMN name;

Delete a masking policy

Use DROP REDACTION POLICY to remove a masking policy from a table.

Syntax

DROP REDACTION POLICY [ IF EXISTS ] <policy_name> ON <table_name>;

Parameters

Parameter	Description
`IF EXISTS`	If specified, returns a `NOTICE` instead of an error when the policy does not exist.
`policy_name`	Name of the masking policy to delete.
`table_name`	Name of the table whose policy you want to delete.

Query behavior for masked users

Grant masked users only the SELECT permission. INSERT, UPDATE, and DELETE operations on masked columns produce unreliable results because those operations work on masked values, not the original data.

SELECT

Dynamic data masking is transparent. After you configure a policy, AnalyticDB for PostgreSQL automatically rewrites SELECT queries — no changes to application code are needed.

For example, if the phone column in employees is masked, the query:

SELECT phone FROM employees;

is rewritten internally to something like:

SELECT mask_func(phone) FROM employees;

Calculations involving masked columns — such as JOIN and equality conditions — operate on masked values, which can produce unexpected results. To handle these cases, use the redaction.scope parameter described in Configure the masking scope.

INSERT

When a masked user inserts plaintext data, subsequent queries return masked results:

-- The phone column is masked for the current user
INSERT INTO employees (id, name, ssn, phone, birthday, salary, email)
  VALUES (1, 'Sally Sample', '020-78-9345', '13900001111', '1961-02-02', 51234.34,
          'sally.sample@alibaba.com');

-- Query results show masked phone values
SELECT * FROM employees;
 id |     name     |     ssn     |    phone    |  birthday  |   salary   |          email
----+--------------+-------------+-------------+------------+------------+--------------------------
  1 | Sally Sample | 020-78-9345 | 139****1111 | 1961-02-02 | $51,234.34 | sally.sample@alibaba.com
(1 row)

UPDATE

WHERE clause conditions on masked columns use masked values, so row matching may fail:

-- The phone column is masked; this UPDATE matches 0 rows
UPDATE employees SET id = 2 WHERE phone = '13900001111';
UPDATE 0

-- Use query_except_equal to exclude equality conditions from masking
SET redaction.scope = 'query_except_equal';
UPDATE employees SET id = 2 WHERE phone = '13900001111';
UPDATE 1

DELETE

Similarly, DELETE with a WHERE condition on a masked column may match 0 rows:

-- Without scope adjustment, DELETE matches 0 rows
DELETE FROM employees WHERE phone = '13900001111';
DELETE 0

SET redaction.scope = 'query_except_equal';
DELETE FROM employees WHERE phone = '13900001111';
DELETE 1

Configure the masking scope

The redaction.scope parameter controls which parts of a query are subject to masking.

Scope	Behavior
`query` (default)	All references to masked columns are masked, including in `WHERE`, `JOIN`, and `ORDER BY` clauses.
`query_except_equal`	Columns in equality conditions (`=`) are not masked, enabling correct row matching for `UPDATE`, `DELETE`, and `JOIN` operations.

Set at session level:

SET redaction.scope = 'query_except_equal';

Set at database level:

ALTER DATABASE <db_name> SET redaction.scope = 'query_except_equal';

Example: With the default query scope, this query:

SELECT name, email, phone FROM employees WHERE email = 'sally.***@alibaba.com';

is rewritten to:

SELECT name, masked(email), masked(phone) FROM employees WHERE masked(email) = 'sally.***@alibaba.com';

After setting query_except_equal, the email column in the WHERE clause is not masked:

SELECT name, masked(email), masked(phone) FROM employees WHERE email = 'sally.***@alibaba.com';

View masking policies

Use the REDACTION_POLICIES and REDACTION_COLUMNS system views to inspect masking configuration in the current database.

REDACTION_POLICIES

Lists all masking policies in the current database.

Field	Type	Description
`schema_name`	name	Schema of the masked table.
`table_name`	name	Name of the masked table.
`policy_name`	name	Name of the masking policy.
`enable`	boolean	Whether the masking policy is enabled.
`expression`	text	The `WHEN` expression for the policy.

REDACTION_COLUMNS

Lists all masked columns in the current database.

Field	Type	Description
`table_name`	name	Name of the masked table.
`column_name`	name	Name of the masked column.
`column_type`	name	Data type of the masked column.
`function_info`	text	Masking function applied to the column.

End-to-end example

The following example walks through the full lifecycle of a masking policy on an employees table. The table owner is alice; bob and eve are restricted users.

Set up roles and the table

-- Create roles
CREATE ROLE alice PASSWORD 'password';
CREATE ROLE bob PASSWORD 'password';
CREATE ROLE eve PASSWORD 'password';

-- Grant schema access
GRANT ALL ON SCHEMA PUBLIC TO alice, bob, eve;

-- Switch to alice and create the table
SET role alice;

CREATE TABLE employees (
  id       SERIAL PRIMARY KEY,
  name     varchar(40)  NOT NULL,
  ssn      varchar(11)  NOT NULL,
  phone    varchar(11),
  birthday date,
  salary   money,
  email    varchar(100)
);

INSERT INTO employees (name, ssn, phone, birthday, salary, email)
VALUES
  ('Sally Sample', '020-78-9345', '13900001111', '1961-02-02', 51234.34, 'sally.***@alibaba.com'),
  ('Jane Doe',     '123-33-9345', '13900002222', '1963-02-14', 62500.00, 'jane.***@gmail.com'),
  ('Bill Foo',     '123-89-9345', '13900003333', '1963-02-14', 45350.00, 'william.***@163.com');

-- Grant read access to bob and eve
GRANT SELECT ON employees TO bob, eve;

Create the masking policy

Mask the phone, salary, and email columns for bob and eve. alice always sees plaintext as the table owner.

CREATE REDACTION POLICY mask_emp ON employees
  WHEN (current_user IN ('bob', 'eve'))
  ADD COLUMN phone  WITH mask_partial(phone, '****', 3, 4),
  ADD COLUMN salary WITH mask_full_num(salary),
  ADD COLUMN email  WITH mask_email(email);

Masking functions used:

`mask_partial(phone, '', 3, 4)` — keeps the first 3 and last 4 characters; replaces the middle with ****
`mask_full_num(salary)` — masks the numeric value as 0
`mask_email(email)` — replaces the local part (before @) with x characters

Verify masking results

SET ROLE bob;
SELECT * FROM employees;
 id |     name     |     ssn     |    phone    |  birthday  | salary |          email
----+--------------+-------------+-------------+------------+--------+--------------------------
  2 | Jane Doe     | 123-33-9345 | 139****1111 | 1963-02-14 |  $0.00 | xxxxxxxx@gmail.com
  3 | Bill Foo     | 123-89-9345 | 139****2222 | 1963-02-14 |  $0.00 | xxxxxxxxxxx@163.com
  1 | Sally Sample | 020-78-9345 | 139****3333 | 1961-02-02 |  $0.00 | xxxxxxxxxxxx@alibaba.com
(3 rows)

SET ROLE eve;
SELECT * FROM employees;
 id |     name     |     ssn     |    phone    |  birthday  | salary |          email
----+--------------+-------------+-------------+------------+--------+--------------------------
  1 | Sally Sample | 020-78-9345 | 139****1111 | 1961-02-02 |  $0.00 | xxxxxxxxxxxx@alibaba.com
  2 | Jane Doe     | 123-33-9345 | 139****2222 | 1963-02-14 |  $0.00 | xxxxxxxx@gmail.com
  3 | Bill Foo     | 123-89-9345 | 139****3333 | 1963-02-14 |  $0.00 | xxxxxxxxxxx@163.com
(3 rows)

Modify the policy

Grant bob access to plaintext data by updating the WHEN expression to exclude him:

SET ROLE alice;
ALTER REDACTION POLICY mask_emp ON employees WHEN(current_user = 'eve');

Bob now sees plaintext; eve still sees masked data:

SET ROLE bob;
SELECT * FROM employees;
 id |     name     |     ssn     |    phone    |  birthday  |   salary   |          email
----+--------------+-------------+-------------+------------+------------+--------------------------
  2 | Jane Doe     | 123-33-9345 | 13900001111 | 1963-02-14 | $62,500.00 | jane.***@gmail.com
  3 | Bill Foo     | 123-89-9345 | 13900002222 | 1963-02-14 | $45,350.00 | william.***@163.com
  1 | Sally Sample | 020-78-9345 | 13900003333 | 1961-02-02 | $51,234.34 | sally.***@alibaba.com
(3 rows)

SET ROLE eve;
SELECT * FROM employees;
 id |     name     |     ssn     |    phone    |  birthday  | salary |          email
----+--------------+-------------+-------------+------------+--------+--------------------------
  1 | Sally Sample | 020-78-9345 | 139****1111 | 1961-02-02 |  $0.00 | xxxxxxxxxxxx@alibaba.com
  2 | Jane Doe     | 123-33-9345 | 139****2222 | 1963-02-14 |  $0.00 | xxxxxxxx@gmail.com
  3 | Bill Foo     | 123-89-9345 | 139****3333 | 1963-02-14 |  $0.00 | xxxxxxxxxxx@163.com
(3 rows)

Inspect policy state

SELECT * FROM redaction_policies;
 schema_name | table_name | policy_name | enable |            expression
-------------+------------+-------------+--------+----------------------------------
 public      | employees  | mask_emp    | t      | ("current_user"() = 'eve'::name)
(1 row)

SELECT * FROM redaction_columns;
 table_name | column_name | column_type |              function_info
------------+-------------+-------------+-----------------------------------------
 employees  | phone       | varchar     | mask_partial(phone, '****'::text, 3, 4)
 employees  | salary      | money       | mask_full_num(salary)
 employees  | email       | varchar     | mask_email(email)
(4 rows)

Add a custom masking function

Mask the ssn column as ###-##-#### using a SQL-defined custom function:

SET ROLE alice;

CREATE FUNCTION mask_ssn() RETURNS varchar AS
$$
  SELECT '###-##-####'::varchar;
$$
LANGUAGE SQL;

ALTER REDACTION POLICY mask_emp ON employees ADD COLUMN ssn WITH mask_ssn();

SET ROLE eve;
SELECT * FROM employees;
 id |     name     |     ssn     |    phone    |  birthday  | salary |          email
----+--------------+-------------+-------------+------------+--------+--------------------------
  1 | Sally Sample | ###-##-#### | 139****1111 | 1961-02-02 |  $0.00 | xxxxxxxxxxxx@alibaba.com
  2 | Jane Doe     | ###-##-#### | 139****2222 | 1963-02-14 |  $0.00 | xxxxxxxx@gmail.com
  3 | Bill Foo     | ###-##-#### | 139****3333 | 1963-02-14 |  $0.00 | xxxxxxxxxxx@163.com
(3 rows)

Delete the policy

SET ROLE alice;
DROP REDACTION POLICY mask_emp ON employees;

Masking functions

AnalyticDB for PostgreSQL provides the following built-in masking functions. All functions return the same type as the input column unless noted otherwise.

mask_none(column anyelement)

Returns the column value unchanged. Used for internal testing only.

Parameter: column — any data type
Return type: same as input

mask_tonull(column anyelement)

Masks all values as NULL.

Parameter: column — any data type
Return type: NULL

mask_full_num(column anyelement)

Masks all numeric values as 0.

Parameter: column — numeric type (INT, BIGINT, FLOAT, or NUMERIC)
Return type: same as input

mask_full_str(column anyelement)

Masks all string values as [redact].

Parameter: column — character type (TEXT, VARCHAR, or CHAR)
Return type: same as input

mask_full_time(column anyelement)

Masks all time values as 00:00:00.

Parameter: column — TIME or TIME WITH TIME ZONE
Return type: same as input

mask_full_timestamp(column anyelement)

Masks all timestamp and date values as 2001.01.01 00:00.

Parameter: column — DATE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE
Return type: same as input

mask_full_bytea()

Masks all BYTEA values to a fixed BYTEA value.

Parameter: none
Return type: BYTEA

mask_email(column anyelement, letter char default 'x')

Masks the local part of an email address (before @) with the specified replacement character.

Parameters:
- column — character type (TEXT, VARCHAR, or CHAR)
- letter — replacement character; default is 'x'
Return type: same as input

mask_shuffle(column anyelement)

Returns the characters of the value in a random order. The output length equals the input length.

Parameter: column — character type (TEXT, VARCHAR, or CHAR)
Return type: same as input

mask_random(column anyelement)

Returns a random character string of the same length as the input.

Parameter: column — character type (TEXT, VARCHAR, or CHAR)
Return type: same as input

mask_regexp(column anyelement, reg text, replace_text text, pos INTEGER default 0, reg_len INTEGER default -1)

Replaces parts of a string matched by a regular expression.

Parameters:
- column — character type (TEXT, VARCHAR, or CHAR)
- reg — regular expression (TEXT)
- replace_text — replacement string (TEXT)
- pos — starting position for matching; default 0 starts from the beginning
- reg_len — length of the match window; default -1 extends to the end of the string
Return type: same as input

Example: Replace all digits with #:

SELECT mask_regexp('$27412.45earned'::text, '[\d+]', '#');
     mask_regexp
----------------------
 $#####.##earned
(1 row)

mask_partial(column anyelement, padding text, prefix INTEGER, suffix INTEGER)

Keeps a specified number of leading and trailing characters and replaces the rest with the padding string.

Parameters:
- column — character type (TEXT, VARCHAR, or CHAR)
- padding — replacement string for the masked portion (TEXT)
- prefix — number of leading characters to keep (INT)
- suffix — number of trailing characters to keep (INT)
Return type: same as input

Example: Keep 5 leading and 1 trailing character:

SELECT mask_partial('$27412.45earned'::text, '###.##', 5, 1);
    mask_partial
--------------------
 $###.##earned
(1 row)

Troubleshooting

Symptom	Likely cause	Resolution
`UPDATE` or `DELETE` with a `WHERE` condition on a masked column affects 0 rows	The `WHERE` clause operates on masked values by default	Set `redaction.scope = 'query_except_equal'` at session or database level. See Configure the masking scope.
Policy creation fails with a permission error	The current user is not the table owner and does not have `RDS_SUPERUSER`	Switch to a privileged account or request `RDS_SUPERUSER`. Only table owners and `RDS_SUPERUSER` accounts can create masking policies.
Policy is created but masked data is visible to all users	The `WHEN` clause was omitted or evaluates to false	Verify the policy has a `WHEN` clause. Without it, the policy does not take effect. Use `SELECT * FROM redaction_policies;` to inspect the stored expression.
A `JOIN` on a masked column returns no matches	Masked values in the `JOIN` condition do not match the plaintext values in the joined table	Set `redaction.scope = 'query_except_equal'` to exclude equality conditions from masking.
The masking function return type does not match the column type	Incorrect function selected	Ensure the masking function returns the same type as the column. For example, use `mask_full_num` for numeric columns and `mask_full_str` for character columns.