Creating a data redaction policy v17

The CREATE REDACTION POLICY command 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;

Output

 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;

Output

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

Output

 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.

Creating a data redaction policy v17

Synopsis

Description

Parameters

Notes

Examples

Caveats

Compatibility

See also

← Prev

↑ Up

Next →