In the rapidly evolving digital age, data privacy and security have become paramount for organizations across various industries. The increasing need to protect sensitive information has led to the development of robust data protection mechanisms.

The EDB Postgres Advanced Server Data Redaction feature offers a robust method for protecting sensitive data, dynamically altering the presentation of data based on user privileges. This mechanism ensures that sensitive information like Social Security Numbers (SSNs) or salary details are only fully accessible to privileged users, while other users see redacted versions. Data redaction is implemented through the creation of functions tailored to each specific field requiring redaction. These functions dictate how data will be displayed to different users, effectively limiting sensitive data exposure without altering the actual data stored in the database.

This article provides examples created on EPAS 15.

Data redaction policies are streamlined through the CREATE REDACTION POLICY command, which allows administrators to specify the tables, columns, and the specific redaction functions to apply.

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 setting it 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 eliminate it using the DROP REDACTION POLICY command.

The CREATE REDACTION POLICY command involves several parameters that allow for detailed specifications of how the redaction policy should operate:

• name: Specifies the name of the data redaction policy being created. It is crucial that this name is unique and does not clash with the name of any existing data redaction policy for the table in question.

• table_name: The name of the table (optionally schema-qualified) that the data redaction policy applies to. It defines the scope of the policy to a specific table within the database.

• FOR (expression): This optional clause allows for the addition of a redaction policy expression. The data redaction policy will only be applied if this expression evaluates to true. It enables the condition-based application of the policy, allowing for greater flexibility and specificity.

• ADD [COLUMN]: This form, which can be used multiple times within the same command, allows for the addition of one or more columns of the table to the data redaction policy. Each column added can have its specific redaction function defined using the USING clause.

• USING funcname_clause: Part of the ADD COLUMN form, the USING clause specifies the redaction function to be applied to the column data. The function defined here decides how the data for the column will be redacted.

• WITH OPTIONS ( ... ): This optional clause allows for the specification of additional options for the data redaction policy, including scope and exceptions. The SCOPE option defines the query part to apply redaction, while the EXCEPTION option specifies conditions under which redaction should not be applied.

The EXCEPTION value can be none, equal, or leakproof, each serving a distinct purpose in determining when and how data should be redacted:

• None: This is the default setting where no exceptions to the redaction policy are made. Data is redacted according to the defined rules without any exemption.

Example:

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

	postgres=> select * from employees where ssn = '020-78-9345';
 	id | name | ssn | phone | birthday | salary | email 
	----+------+-----+-------+----------+--------+-------
	(0 rows)

	------------------------------------------------------------------------------------------------------
 	Seq Scan on employees  (cost=0.00..57.95 rows=1 width=414) (actual time=0.056..0.062 rows=0 loops=1)
	   Filter: ((redact_ssn(ssn))::text = '020-78-9345'::text)
	   Rows Removed by Filter: 3
	 Planning Time: 0.181 ms
	 Execution Time: 0.107 ms
	(5 rows)

• Equal: When set to equal, the redaction policy exempts data from being redacted if it is used in an equality test. This is particularly useful in scenarios where specific data needs to be visible for comparison or filtering purposes but should remain redacted otherwise.

Example:

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

    postgres=> select * from employees where ssn = '020-78-9345';
     id |     name     |     ssn     |   phone    |      birthday      |   salary   |             email             
    ----+--------------+-------------+------------+--------------------+------------+-------------------------------
      1 | Sally Sample | xxx-xx-9345 | 5081234567 | 02-FEB-61 00:00:00 | $51,234.34 | sally.sample@enterprisedb.com
    (1 row)

    ------------------------------------------------------------------------------------------------------
     Seq Scan on employees  (cost=0.00..12.50 rows=1 width=414) (actual time=0.045..0.056 rows=1 loops=1)
       Filter: ((ssn)::text = '020-78-9345'::text)
       Rows Removed by Filter: 2
     Planning Time: 0.066 ms
     Execution Time: 0.100 ms
    (5 rows)

• Leakproof: If an exception is leakproof, the column isn't redacted when a leakproof function is applied to it. A "leakproof" function guarantees not to leak information about its arguments through its result or by any other side effects. This property helps the query planner optimize queries, especially in the presence of security policies like row-level security or data redaction. When a function is marked as leakproof, it assures the database system that the function's behaviour does not reveal any details about its arguments. This can allow the function to be executed in contexts where sensitive data is protected.

Implementing a robust data redaction policy using EDB Postgres Advanced Server is a critical step towards ensuring data privacy and security. By leveraging the flexibility of the CREATE REDACTION POLICY command and understanding the nuances of the EXCEPTION parameter, organizations can effectively control how sensitive data is accessed and presented, ensuring compliance with data protection regulations and safeguarding their data assets.