Enabling database-native masking in PostgreSQL databases

Edit online

To enable database-native data masking in PostgreSQL, install the IBM® Optim Data Privacy Java™ User-Defined Function (UDF) into your PostgreSQL database.

Before you begin

Before you begin, ensure that you have the following prerequisites:

A PostgreSQL database with PL/Java extension installed
Access to the IBM Optim runtime container (optim-runtime)
A PostgreSQL database connection with appropriate privileges to install JAR files and create functions
The masking-udf-postgresql-1.0.0-SNAPSHOT.jar file from the optim-runtime container
Root or sudo access to modify PostgreSQL configuration files

Installing the PL/Java extension

The PL/Java extension for PostgreSQL must be installed before you can use Java UDFs. Pre-built packages are available for Debian and Ubuntu distributions and can be installed using a package manager. For other operating systems, you must build PL/Java from source code. For more information, see the PL/Java wiki.

The following example shows how to install PL/Java for PostgreSQL 14 on Ubuntu 22:

sudo wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt update
sudo apt install postgresql-server-dev-14
sudo apt install postgresql-14-pljava
sudo apt list --installed

sudo -u postgres psql
CREATE EXTENSION pljava;
\q
exit

About this task

The installation process involves configuring permissions for the masking UDF, copying the JAR file from the container, installing it into PostgreSQL, and creating the UDF function.

Installing the IBM Optim Data Privacy UDF

To install the IBM Optim Data Privacy udfBasicMask UDF function into your PostgreSQL database:

Close any open psql sessions.
Add the required permissions to the pljava.policy file.
```
sudo vi /etc/postgresql-common/pljava.policy
```

Add the following permissions to the file:

permission java.util.PropertyPermission "*", "read,write";
permission java.io.FilePermission "/var/lib/postgresql/-", "read";
permission java.lang.reflect.ReflectPermission "suppressAccessChecks";
permission java.lang.RuntimePermission "getClassLoader";
permission java.lang.RuntimePermission "accessDeclaredMembers";
permission java.lang.RuntimePermission "fileSystemProvider";
permission java.lang.RuntimePermission "createClassLoader";
permission java.lang.RuntimePermission "getenv.*";

For more information about PL/Java security policies, see PL/Java policy documentation.

Restart PostgreSQL to apply the policy changes.
```
sudo service postgresql restart
```
Copy the masking UDF JAR file from the optim-runtime container to your local file system.
```
podman cp optim-runtime:/masking-udf/masking-udf-postgresql-1.0.0-SNAPSHOT.jar /tmp
```
Connect to PostgreSQL as a user that has permission to install UDFs. In this example, the user is named postgres.
```
sudo -u postgres psql
```

Install the JAR file into PostgreSQL.

SELECT sqlj.install_jar('file:///tmp/masking-udf-postgresql-1.0.0-SNAPSHOT.jar', 'OptimMaskingUDF', true);

Set the classpath for the schema where you want to use the UDF.
```
SELECT sqlj.set_classpath('public', 'OptimMaskingUDF');
```
Replace public with your schema name if you are using a different schema.

Create the UDF function.

CREATE OR REPLACE FUNCTION udfBasicMask(varchar, varchar, varchar, varchar DEFAULT NULL, varchar DEFAULT NULL, varchar DEFAULT NULL, varchar DEFAULT NULL) RETURNS varchar AS 'java.lang.String=com.ibm.optim.tdm.runtime.udf.postgresql.MaskingFunctions.udfBasicMask(java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String)' LANGUAGE java;

The udfBasicMask function is now available in your PostgreSQL database. You can use it in SQL queries to mask sensitive data.

Using the `udfBasicMask` function

The following example shows how to use the udfBasicMask function to mask names in a customers table:

select udfBasicMask(name, 'USPersonName', 'RandomFormatFabricationProcessor') from customers;

This query masks the name column using the US person name format and the random format fabrication processor.

Note: The first time you invoke the UDF takes longer than subsequent invocations because the UDF must be initialized and the masking formats must be loaded.

Uninstalling the IBM Optim Data Privacy UDF

To uninstall the udfBasicMask UDF:

Drop the function:
```
DROP FUNCTION udfBasicMask;
```

Remove the JAR file:

SELECT sqlj.remove_jar('OptimMaskingUDF', true);