Ubiq Security on Postgres/Greenplum

When encrypting and decrypting data in PostgreSQL, the Ubiq Security Python Library can be leveraged to provide convenient interaction with the Ubiq Security Platform API.

Installation

Install plpython3u

The PL/Python Language Extension allows PostgreSQL functions to be written in the Python language. PL/Python Documentation This allows PostgreSQL functions to take advantage of the features and modules written in Python for advanced functionality.

Depending on your PostgreSQL environment, you may need to install/enable it in your database. Greenplum should have it already installed.

CREATE EXTENSION plpython3u;

If this fails with the error ERROR: could not open extension control file ".../plpython3u.control": No such file or directory you will need to install plpython3u on your server outside of Postgres first.

Debian:
apt-get update && apt-get install -y postgresql-plpython3

RHEL/CentOS:
sudo yum install -y postgresql-plpython3

Install Dependencies

You will need the following packages on your system:

python3
build-essential
openssl
libssl-dev
swig

Using pip:

pip install ubiq-security cryptography requests six swig M2Crypto

The Ubiq Security libraries are dependent on M2Crypto which has specific requirements as well which varies depending upon your actual environment. If you encounter problems installing the Ubiq Security libraries, please see M2Crypto for the latest notes and instructions.

Setup

Credentials

The library needs to be configured with your account credentials which is
available in your Ubiq Dashboard credentials.

The functions provided in this readme show two different ways to handle credentials when working with encryption:

Stored in a temp table
Stored on the Server

We recommend only storing credentials on the server that can be used to Encrypt, but not to decrypt. Otherwise anyone running a query on your server can decrypt data without issue. Users can use the secondary _creds functions with their own keys/credentials to access the data as necessary.

If you wish to use a different method, simply modify the methods accordingly. plpython3u uses python, all authentication methods that work for Python work here as well. See the Ubiq-Python Documentation for more information.

Possible changes include:

Using a different profile
Setting the credentials as environment variables in your CI/CD Pipeline
Passing in the credentials/changing profile as additional function parameters

The functions can be modified to suit your needs. Reach out to Support if you encounter difficulties.

Configuration

Like the Python Library, the configuration file can be set to tweak functionality of the library (caching, reporting intervals, etc). The default path is ~/.ubiq/configuration.

If you're running postgres on a linux system, PL/Python expands ~ to /var/lib/postgresql/.

To better handle event reporting on Postgres/Greenplum, please make sure to have event_reporting.unthreaded set to true.

{
  "event_reporting": {
    "wake_interval": 1,
    "minimum_count": 100,
    "flush_interval": 2,
    "trap_exceptions": false,
    "timestamp_granularity" : "MICROS",
	"unthreaded": true
  },
  ...
}

Functions

Note: When creating a credentials object, there is an optional parameter library_label. It is recommended to set this to either ubiq-postgres or ubiq-greenplum in the functions or performing encryption will report by default ubiq-python (since it's running on the python library). For metrics, and better clarity of how libraries are being used, please update this in your usage.

Temporary Credentials

CREATE OR REPLACE PROCEDURE ubiq_begin_session(access_key_id text, secret_signing_key text, secret_crypto_access_key text)
AS $$
	DECLARE
	BEGIN
		CREATE TEMPORARY TABLE IF NOT EXISTS ubiq_creds("access_key_id" varchar, "secret_signing_key" varchar, "secret_crypto_access_key" varchar);
		INSERT INTO ubiq_creds VALUES (access_key_id, secret_signing_key, secret_crypto_access_key);
	END;
$$ 
LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION ubiq_encrypt_creds(dataset text, plaintext text)
RETURNS TEXT
AS $$
	import ubiq_security as ubiq
	import ubiq_security.structured as ubiq_structured
	query_creds = plpy.execute('SELECT access_key_id, secret_signing_key, secret_crypto_access_key FROM ubiq_creds;',1)
	creds = ubiq.credentials(access_key_id = query_creds[0]['access_key_id'], secret_signing_key = query_creds[0]['secret_signing_key'], secret_crypto_access_key = query_creds[0]['secret_crypto_access_key'], library_label = "ubiq-postgres")

	return ubiq_structured.Encrypt(creds, dataset, plaintext)
$$
LANGUAGE 'plpython3u';

CREATE OR REPLACE FUNCTION ubiq_decrypt_creds(dataset text, ciphertext text)
RETURNS TEXT
AS $$
	import ubiq_security as ubiq
	import ubiq_security.structured as ubiq_structured
    query_creds = plpy.execute('SELECT access_key_id, secret_signing_key, secret_crypto_access_key FROM ubiq_creds;',1)
	creds = ubiq.credentials(access_key_id = query_creds[0]['access_key_id'], secret_signing_key = query_creds[0]['secret_signing_key'], secret_crypto_access_key = query_creds[0]['secret_crypto_access_key'], , library_label = "ubiq-postgres")
	return ubiq_structured.Decrypt(credentials, dataset, ciphertext)
$$
LANGUAGE 'plpython3u';

CREATE OR REPLACE FUNCTION ubiq_encrypt_for_search_creds(dataset text, plaintext text)
RETURNS TEXT[]
AS $$
	import ubiq_security as ubiq
	import ubiq_security.structured as ubiq_structured
	query_creds = plpy.execute('SELECT access_key_id, secret_signing_key, secret_crypto_access_key FROM ubiq_creds;',1)
	creds = ubiq.credentials(access_key_id = query_creds[0]['access_key_id'], secret_signing_key = query_creds[0]['secret_signing_key'], secret_crypto_access_key = query_creds[0]['secret_crypto_access_key'], library_label = "ubiq-postgres")

	return ubiq_structured.EncryptForSearch(credentials, dataset, plaintext)
$$
LANGUAGE 'plpython3u';

Usage

CALL ubiq_begin_session({ACCESS_KEY_ID}, {SECRET_API_KEY}, {SECRET_CRYPTO_ACCESS_KEY});

SELECT name, ubiq_decrypt_creds('SSN', ssn) as ssn_decrypted, phone_number FROM customers LIMIT 1000;

WITH args(enc_search) AS (
    VALUES (ubiq_encrypt_for_search_creds('SSN', '999-05-1234'))
)
  SELECT name, email 
	FROM customers 
	CROSS JOIN args 
	WHERE ssn_enc = ANY(enc_search::text[]);;

Credentials on Server

CREATE OR REPLACE FUNCTION ubiq_encrypt(dataset text, plaintext text)
RETURNS TEXT
AS $$
	import ubiq_security as ubiq
	import ubiq_security.structured as ubiq_structured
	credentials = ubiq.configCredentials('/.ubiq/credentials', 'default', library_label = "ubiq-postgres")
	return ubiq_structured.Encrypt(credentials, dataset, plaintext)
$$
LANGUAGE 'plpython3u';

CREATE OR REPLACE FUNCTION ubiq_decrypt(dataset text, ciphertext text)
RETURNS TEXT
AS $$
	import ubiq_security as ubiq
	import ubiq_security.structured as ubiq_structured
    credentials = ubiq.configCredentials('/.ubiq/credentials', 'default', library_label = "ubiq-postgres")
	return ubiq_structured.Decrypt(credentials, dataset, ciphertext)
$$
LANGUAGE 'plpython3u';

CREATE OR REPLACE FUNCTION ubiq_encrypt_for_search(dataset text, plaintext text)
RETURNS TEXT
AS $$
	import ubiq_security as ubiq
	import ubiq_security.structured as ubiq_structured
	credentials = ubiq.configCredentials('/.ubiq/credentials', 'default', library_label = "ubiq-postgres")
	return ubiq_structured.EncryptForSearch(credentials, dataset, plaintext)
$$
LANGUAGE 'plpython3u';

Usage

-- Returns encrypted data, eg. 200-0o-oYyp
SELECT ubiq_encrypt('SSN', '123-45-6789');

-- Returns decrypted data, eg. 123-45-6789
SELECT ubiq_decrypt('SSN', '200-0o-oYyp');

-- Returns text[] of encrypted values for use in a search
-- eg. {000-06-pgk0,100-0W-cOLJ,200-0o-oYyp}
SELECT ubiq_encrypt_for_search('SSN', '123-45-6789');