All Products
Search

This Product

    AnalyticDB:Dynamic data masking

    Document Center

    AnalyticDB:Dynamic data masking

    Last Updated:Mar 28, 2026

    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:

    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.

    1. Assign a masking function to each sensitive column using CREATE REDACTION POLICY.

    2. Define a WHEN expression to control which users see masked data.

    3. 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.

    4. 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

    ParameterDescription
    policy_nameName of the masking policy.
    table_nameName 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_nameName of the column to mask.
    mask_function_nameMasking function to apply. The return type must match the column type.
    argumentArguments 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

    ParameterDescription
    policy_nameName of the masking policy to modify.
    table_nameName of the table whose policy you want to modify.
    new_when_expressionReplacement expression for the WHEN clause.
    ENABLE | DISABLEEnable or disable the masking policy.
    new_policy_nameNew name for the policy.
    column_nameColumn to add, modify, or remove. When adding a column, specify one not already configured. When modifying or removing, specify an existing masked column.
    function_nameMasking function to apply to the column.
    argumentArguments 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

    ParameterDescription
    IF EXISTSIf specified, returns a NOTICE instead of an error when the policy does not exist.
    policy_nameName of the masking policy to delete.
    table_nameName 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.

    ScopeBehavior
    query (default)All references to masked columns are masked, including in WHERE, JOIN, and ORDER BY clauses.
    query_except_equalColumns 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.

    FieldTypeDescription
    schema_namenameSchema of the masked table.
    table_namenameName of the masked table.
    policy_namenameName of the masking policy.
    enablebooleanWhether the masking policy is enabled.
    expressiontextThe WHEN expression for the policy.

    REDACTION_COLUMNS

    Lists all masked columns in the current database.

    FieldTypeDescription
    table_namenameName of the masked table.
    column_namenameName of the masked column.
    column_typenameData type of the masked column.
    function_infotextMasking 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

    SymptomLikely causeResolution
    UPDATE or DELETE with a WHERE condition on a masked column affects 0 rowsThe WHERE clause operates on masked values by defaultSet redaction.scope = 'query_except_equal' at session or database level. See Configure the masking scope.
    Policy creation fails with a permission errorThe current user is not the table owner and does not have RDS_SUPERUSERSwitch 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 usersThe WHEN clause was omitted or evaluates to falseVerify 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 matchesMasked values in the JOIN condition do not match the plaintext values in the joined tableSet redaction.scope = 'query_except_equal' to exclude equality conditions from masking.
    The masking function return type does not match the column typeIncorrect function selectedEnsure 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.