Working example of a data redaction policy v18

Suggest edits

This example walks you through how to create and implement a data redaction policy on a table.

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 to the employees table:

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

Create a user hr who can see all the data in the employees table:

CREATE USER hr;

Create a normal user and grant all privileges to hr and alice:

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 name) RETURNS varchar AS
$$
BEGIN
    return overlay (ssn placing 'xxx-xx' from 1);
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION redact_salary(salary money) RETURNS money AS
$$
BEGIN
    return 0::money;
END;
$$ LANGUAGE plpgsql;

These functions are then incorporated into a redaction policy by using the CREATE_REDACTION_POLICY function.

To use the data redaction feature, first add the data_redaction libraries by adding the following lines to the postgresql.conf file and restarting the database server:

# add data redaction libraries in the postgresql.conf file:
shared_preload_libraries = 'data_redaction'

Restart the database server and create the extension:

Create an extension and then create a data redaction policy on employees table to redact ssn and salary columns with the default scope and exception. Column ssn must be accessible in equality condition. The redaction policy is exempted for the hr user.

CREATE EXTENSION data_redaction;
SELECT data_redaction.CREATE_REDACTION_POLICY('redact_personal_info_ssn', 'employees', 'public',
                                              'session_user != ''hr''', 'ssn', 'redact_ssn(ssn)',
                                              'query', 'equal');
SELECT data_redaction.CREATE_REDACTION_POLICY('redact_personal_info_salary', 'employees', 'public',
                                              'session_user != ''hr''', 'salary', 'redact_salary(salary)');

The hr user can view all columns data:

\c hr
SELECT * FROM employees;
Output
 id |     name     |     ssn     |   phone    |  birthday  |   salary   |             email
----+--------------+-------------+------------+------------+------------+-------------------------------
  1 | Sally Sample | 020-78-9345 | 5081234567 | 1961-02-02 | $51,234.34 | sally.sample@enterprisedb.com
  2 | Jane Doe     | 123-33-9345 | 6171234567 | 1963-02-14 | $62,500.00 | jane.doe@gmail.com
(2 rows)

The alice user can't see the actual data in ssn and salary columns:

\c edb alice
SELECT * FROM employees;
Output
 id |     name     |     ssn     |   phone    |  birthday  | salary |             email
----+--------------+-------------+------------+------------+--------+-------------------------------
  1 | Sally Sample | xxx-xx-9345 | 5081234567 | 1961-02-02 |  $0.00 | sally.sample@enterprisedb.com
  2 | Jane Doe     | xxx-xx-9345 | 6171234567 | 1963-02-14 |  $0.00 | jane.doe@gmail.com
(2 rows)
← Prev

Data redaction system catalogs

↑ Up

Redacting data

Next →

Replication