Data redaction v14

Data redaction limits sensitive data exposure by dynamically changing data as it is 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 only see the last four digits xxx-xx-9567.

Data redaction is implemented by defining a function for each field to which redaction is to be applied. The function returns the value that should be displayed to the users subject to the data redaction.

So for example, for the SSN field, the redaction function would return xxx-xx-9567 for an input SSN of 021-23-9567.

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

These functions are then incorporated into a redaction policy by using the CREATE REDACTION POLICY command. This command specifies the table on which the policy applies, the table columns to be affected by the specified redaction functions, expressions to determine which session users are to be affected, and other options.

The edb_data_redaction parameter in the postgresql.conf file then determines whether or not data redaction is to be applied.

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

• Superusers and the table owner bypass data redaction and see the original data.
• All other users get 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.

A redaction policy can be changed by using the ALTER REDACTION POLICY command, or it can be eliminated using the DROP REDACTION POLICY command.

The redaction policy commands are described in more detail in the subsequent sections.

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 redaction function. A newly created data redaction policy is enabled by default. The policy can be disabled 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 specifies a redaction function expression. Multiple ADD [ COLUMN ] form can be used, 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 and/or an exception to the data redaction policy to be applied. If the scope and/or exception are not specified, the default values for scope and exception are query and none respectively.

Parameters

name

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

table_name

The name (optionally schema-qualified) 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 being created.

funcname_clause

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

scope_value

The scope identified the query part where redaction to be applied for the column. Scope value could be query, top_tlist or top_tlist_or_error. If the scope is query then, the redaction applied on the column irrespective of where it appears in the query. If the scope is top_tlist then, the redaction 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 same as the top_tlist but throws an errors when the column appears anywhere else in the query.

exception_value

The exception identified the query part where redaction to be exempted. Exception value could be none, equal or leakproof. If exception is none then there is no exemption. If exception is equal, then the column is not 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

Below is an example of how this feature can be used 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;

Now create a data redaction policy on employees to redact column ssn which should be accessible in equality condition and salary with default scope and exception. 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 it used for equality check due to 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

1. The data redaction policy created on inheritance hierarchies aren't cascaded. For example, if the data redaction policy is created for a parent, it isn't be applied to the child table, which inherits it and vice versa. Someone who has access to these child tables can see the non-redacted data. For information about inheritance hierarchies, see Inheritance in the PostgreSQL Core Documentation available at:

   https://www.postgresql.org/docs/current/static/ddl-inherit.html

2. If the superuser or the table owner has created any materialized view on the table and has 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.

3. The objects accessed in the redaction function body should 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 the details.

MODIFY [ COLUMN ]

This form modifies the data redaction policy on the column of the table. You can update the redaction function clause and/or the redaction options for the column. The USING clause specifies the redaction function expression to be updated and the WITH OPTIONS ( ... ) clause specifies the scope and/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 name (optionally schema-qualified) 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 being altered or dropped.

funcname_clause

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

scope_value

The scope identified the query part where redaction to be applied for the column. See CREATE REDACTION POLICY for the details.

exception_value

The exception identified the query part where redaction to be exempted. See CREATE REDACTION POLICY for the details.

Examples

Update 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');

And to update 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 does not exist. A notice is issued in this case.

name

The name of the data redaction policy to drop.

table_name

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

CASCADE

RESTRICT

These keywords don't have any effect, since 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

This section describes the system catalogs that 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