Data redaction v15

Data redaction limits sensitive data exposure by dynamically changing data as it's displayed for certain users.

For example, a social security number (SSN) is stored as 021-23-9567. Privileged users can see the full SSN, while other users see only the last four digits: xxx-xx-9567.

You implement data redaction by defining a function for each field to which to apply redaction. The function returns the value to display to the users subject to the data redaction.

For example, for the SSN field, the redaction function returns xxx-xx-9567 for an input SSN of 021-23-9567.

For a salary field, a redaction function always returns $0.00, regardless of the input salary value.

These functions are then incorporated into a redaction policy by using the CREATE REDACTION POLICY command. In addition to other options, this command specifies:

• The table on which the policy applies
• The table columns affected by the specified redaction functions
• Expressions to determine the affect session users

The edb_data_redaction parameter in the postgresql.conf file then determines whether to apply data redaction.

By default, the parameter is enabled, so the redaction policy is in effect. The following occurs:

• Superusers and the table owner bypass data redaction and see the original data.
• All other users have the redaction policy applied and see the reformatted data.

If the parameter is disabled by having it set to FALSE during the session, then the following occurs:

• Superusers and the table owner bypass data redaction and see the original data.
• All other users get an error.

You can change a redaction policy using the ALTER REDACTION POLICY command. Or, you can eliminate it using the DROP REDACTION POLICY command.

CREATE REDACTION POLICY

CREATE REDACTION POLICY defines a new data redaction policy for a table.

Synopsis

CREATE REDACTION POLICY <name> ON <table_name>
  [ FOR ( <expression> ) ]
  [ ADD [ COLUMN ] <column_name> USING <funcname_clause>
    [ WITH OPTIONS ( [ <redaction_option> ]
      [, <redaction_option> ] )
    ]
  ] [, ...]

Where redaction_option is:

{ SCOPE <scope_value> |
  EXCEPTION <exception_value> }

Description

The CREATE REDACTION POLICY command defines a new column-level security policy for a table by redacting column data using a redaction function. A newly created data redaction policy is enabled by default. You can disable the policy using ALTER REDACTION POLICY ... DISABLE.

FOR ( expression )

This form adds a redaction policy expression.

ADD [ COLUMN ]

This optional form adds a column of the table to the data redaction policy. The USING clause specifies a redaction function expression. You can use multiple ADD [ COLUMN ] forms if you want to add multiple columns of the table to the data redaction policy being created. The optional WITH OPTIONS ( ... ) clause specifies a scope or an exception to the data redaction policy to apply. If you don't specify the scope or exception, the default value for scope is query and for exception is none.

Parameters

name

The name of the data redaction policy to create. This must be distinct from the name of any other existing data redaction policy for the table.

table_name

The optionally schema-qualified name of the table the data redaction policy applies to.

expression

The data redaction policy expression. No redaction is applied if this expression evaluates to false.

column_name

Name of the existing column of the table on which the data redaction policy is being created.

funcname_clause

The data redaction function that decides how to compute the redacted column value. Return type of the redaction function must be the same as the column type on which the data redaction policy is being added.

scope_value

The scope identifies the query part to apply redaction for the column. Scope value can be query, top_tlist, or top_tlist_or_error. If the scope is query, then the redaction is applied on the column regardless of where it appears in the query. If the scope is top_tlist, then the redaction is applied on the column only when it appears in the query’s top target list. If the scope is top_tlist_or_error, the behavior is the same as the top_tlist but throws an errors when the column appears anywhere else in the query.

exception_value

The exception identifies the query part where redaction is exempted. Exception value can be none, equal, or leakproof. If exception is none, then there's no exemption. If exception is equal, then the column isn't redacted when used in an equality test. If exception is leakproof, the column isn't redacted when a leakproof function is applied to it.

Notes

You must be the owner of a table to create or change data redaction policies for it.

The superuser and the table owner are exempt from the data redaction policy.

Examples

This example shows how you can use this feature in production environments.

Create the components for a data redaction policy on the employees table:

CREATE TABLE employees (
 id          integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
 name        varchar(40) NOT NULL,
 ssn         varchar(11) NOT NULL,
 phone       varchar(10),
 birthday    date,
 salary      money,
 email       varchar(100)
);

-- Insert some data
INSERT INTO employees (name, ssn, phone, birthday, salary, email)
VALUES
( 'Sally Sample', '020-78-9345', '5081234567', '1961-02-02', 51234.34,
'sally.sample@enterprisedb.com'),
( 'Jane Doe', '123-33-9345', '6171234567', '1963-02-14', 62500.00,
'jane.doe@gmail.com'),
( 'Bill Foo', '123-89-9345', '9781234567','1963-02-14', 45350,
'william.foe@hotmail.com');

-- Create a user hr who can see all the data in employees
CREATE USER hr;
-- Create a normal user
CREATE USER alice;
GRANT ALL ON employees TO hr, alice;

-- Create redaction function in which actual redaction logic resides
CREATE OR REPLACE FUNCTION redact_ssn (ssn varchar(11)) RETURN varchar(11) IS
BEGIN
   /* replaces 020-12-9876 with xxx-xx-9876 */
   return overlay (ssn placing 'xxx-xx' from 1) ;
END;

CREATE OR REPLACE FUNCTION redact_salary () RETURN money IS BEGIN return
0::money;
END;

Create a data redaction policy on employees to redact column ssn and salary with default scope and exception. Column ssn must be accessible in equality condition. The redaction policy is exempt for the hr user.

CREATE REDACTION POLICY redact_policy_personal_info ON employees FOR (session_user != 'hr')
ADD COLUMN ssn USING redact_ssn(ssn) WITH OPTIONS (SCOPE query, EXCEPTION equal),
ADD COLUMN salary USING redact_salary();

The visible data for the hr user is:

-- hr can view all columns data
edb=# \c edb hr
edb=> SELECT * FROM employees;

id | name         | ssn         | phone      | birthday           |
 salary     | email
----+--------------+-------------+------------+--------------------+---
--+---------------------
  1 | Sally Sample | 020-78-9345 | 5081234567 | 02-FEB-61 00:00:00 |
  $51,234.34 | sally.sample@enterprisedb.com
  2 | Jane Doe     | 123-33-9345 | 6171234567 | 14-FEB-63 00:00:00 |
  $62,500.00 | jane.doe@gmail.com
  3 | Bill Foo     | 123-89-9345 | 9781234567 | 14-FEB-63 00:00:00 |
  $45,350.00 | william.foe@hotmail.com
(3 rows)

The visible data for the normal user alice is:

-- Normal user cannot see salary and ssn number.
edb=> \c edb alice
edb=> SELECT * FROM employees;

id  | name         | ssn         | phone      | birthday           | salary |
email
----+--------------+-------------+------------+--------------------+--------+-
------------------------------
  1 | Sally Sample | xxx-xx-9345 | 5081234567 | 02-FEB-61 00:00:00 |  $0.00 |
  sally.sample@enterprisedb.com
  2 | Jane Doe     | xxx-xx-9345 | 6171234567 | 14-FEB-63 00:00:00 |  $0.00 |
  jane.doe@gmail.com
  3 | Bill Foo     | xxx-xx-9345 | 9781234567 | 14-FEB-63 00:00:00 |  $0.00 |
  william.foe@hotmail.com
(3 rows)

But ssn data is accessible when used for equality check due to the exception_value setting:

-- Get ssn number starting from 123
edb=> SELECT * FROM employees WHERE substring(ssn from 0 for 4) = '123';

id | name     | ssn         | phone      | birthday           | salary |
 email
----+----------+-------------+------------+--------------------+--------+-----
--------------------
  2 | Jane Doe | xxx-xx-9345 | 6171234567 | 14-FEB-63 00:00:00 |  $0.00 |
  jane.doe@gmail.com
  3 | Bill Foo | xxx-xx-9345 | 9781234567 | 14-FEB-63 00:00:00 |  $0.00 |
  william.foe@hotmail.com
(2 rows)

Caveats

• The data redaction policies created on inheritance hierarchies aren't cascaded. For example, if the data redaction policy is created for a parent, it isn't applied to the child table that inherits it, and vice versa. A user with access to these child tables can see the non-redacted data. For information about inheritance hierarchies, see the PostgreSQL core documentation.

• If the superuser or the table owner created any materialized view on the table and provided the access rights GRANT SELECT on the table and the materialized view to any non-superuser, then the non-superuser can access the non-redacted data through the materialized view.

• The objects accessed in the redaction function body must be schema qualified. Otherwise pg_dump might fail.

Compatibility

CREATE REDACTION POLICY is an EDB extension.

See also

ALTER REDACTION POLICY, DROP REDACTION POLICY

ALTER REDACTION POLICY

ALTER REDACTION POLICY changes the definition of data redaction policy for a table.

Synopsis

ALTER REDACTION POLICY <name> ON <table_name> RENAME TO <new_name>

ALTER REDACTION POLICY <name> ON <table_name> FOR ( <expression> )

ALTER REDACTION POLICY <name> ON <table_name> { ENABLE | DISABLE}

ALTER REDACTION POLICY <name> ON <table_name>
  ADD [ COLUMN ] <column_name> USING <funcname_clause>
    [ WITH OPTIONS ( [ <redaction_option> ]
      [, <redaction_option> ] )
    ]

ALTER REDACTION POLICY <name> ON <table_name>
  MODIFY [ COLUMN ] <column_name>
  {
    [ USING <funcname_clause> ]
  |
    [ WITH OPTIONS ( [ <redaction_option> ]
      [, <redaction_option> ] )
    ]
  }

ALTER REDACTION POLICY <name> ON <table_name>
  DROP [ COLUMN ] <column_name>

Where redaction_option is:

{ SCOPE <scope_value> |
  EXCEPTION <exception_value> }

Description

ALTER REDACTION POLICY changes the definition of an existing data redaction policy.

To use ALTER REDACTION POLICY, you must own the table that the data redaction policy applies to.

FOR ( expression )

This form adds or replaces the data redaction policy expression.

ENABLE

Enables the previously disabled data redaction policy for a table.

DISABLE

Disables the data redaction policy for a table.

ADD [ COLUMN ]

This form adds a column of the table to the existing redaction policy. See CREATE REDACTION POLICY for details.

MODIFY [ COLUMN ]

This form modifies the data redaction policy on the column of the table. You can update the redaction function clause or the redaction options for the column. The USING clause specifies the redaction function expression to update. The WITH OPTIONS ( ... ) clause specifies the scope or the exception. For more details on the redaction function clause, the redaction scope, and the redaction exception, see CREATE REDACTION POLICY.

DROP [ COLUMN ]

This form removes the column of the table from the data redaction policy.

Parameters

name

The name of an existing data redaction policy to alter.

table_name

The optionally schema-qualified name of the table that the data redaction policy is on.

new_name

The new name for the data redaction policy. This must be distinct from the name of any other existing data redaction policy for the table.

expression

The data redaction policy expression.

column_name

Name of existing column of the table on which the data redaction policy is being altered or dropped.

funcname_clause

The data redaction function expression for the column. See CREATE REDACTION POLICY for details.

scope_value

The scope identifies the query part to apply redaction for the column. See CREATE REDACTION POLICY for the details.

exception_value

The exception identifies the query part where redaction are exempted. See CREATE REDACTION POLICY for the details.

Examples

Update the data redaction policy called redact_policy_personal_info on the table named employees:

ALTER REDACTION POLICY redact_policy_personal_info ON employees
FOR (session_user != 'hr' AND session_user != 'manager');

To update the data redaction function for the column ssn in the same policy:

ALTER REDACTION POLICY redact_policy_personal_info ON employees
MODIFY COLUMN ssn USING redact_ssn_new(ssn);

Compatibility

ALTER REDACTION POLICY is an EDB extension.

See also

CREATE REDACTION POLICY, DROP REDACTION POLICY

DROP REDACTION POLICY

DROP REDACTION POLICY removes a data redaction policy from a table.

Synopsis

DROP REDACTION POLICY [ IF EXISTS ] <name> ON <table_name>
  [ CASCADE | RESTRICT ]

Description

DROP REDACTION POLICY removes the specified data redaction policy from the table.

To use DROP REDACTION POLICY, you must own the table that the redaction policy applies to.

Parameters

IF EXISTS

Don't throw an error if the data redaction policy doesn't exist. A notice is issued in this case.

name

The name of the data redaction policy to drop.

table_name

The optionally schema-qualified name of the table that the data redaction policy is on.

CASCADE

RESTRICT

These keywords don't have any effect, as there are no dependencies on the data redaction policies.

Examples

To drop the data redaction policy called redact_policy_personal_info on the table named employees:

DROP REDACTION POLICY redact_policy_personal_info ON employees;

Compatibility

DROP REDACTION POLICY is an EDB extension.

See also

CREATE REDACTION POLICY, ALTER REDACTION POLICY

System catalogs

System catalogs store the redaction policy information.

edb_redaction_column

The edb_redaction_column system catalog stores information about the data redaction policy attached to the columns of a table.

edb_redaction_policy

The catalog edb_redaction_policy stores information about the redaction policies for tables.

• On this page
• CREATE REDACTION POLICY
• ALTER REDACTION POLICY
• DROP REDACTION POLICY
• System catalogs