Storing User Keys in CipherTrust Manager

The user keys must be stored outside of the PostgreSQL server. This how-to configures TCE to seal and unseal envelope keys with Thales CipherTrust Manager over its HTTP API.

Requirements

The starting point is a PostgreSQL instance with TCE installed, and a CipherTrust Manager deployment that already has:

• A registered client (client id, client certificate, and client private key)
• A key id that the client is allowed to use for encrypt and decrypt

Client registration on CipherTrust Manager is out of scope of this document.

Principles

The TCE extension within Postgres connects to CipherTrust Manager using a credentials file and uses the key assigned to each role. Authentication uses mTLS and a client-credential token; the token is requested on every seal and unseal.

flowchart
    subgraph CipherTrust
    client_id
    key
    end
    subgraph Postgres
    role --> key
    credentials --> client_id
    end

Add a credential file

On the Postgres host, create a configuration file. By default this file is named $XDG_CONFIG_HOME/ciphertrust/credentials.ini, which is typically /var/lib/postgresql/.config/ciphertrust/credentials.ini.

server_url is the CipherTrust origin only (no /api suffix). Optional server_cacert_file points at the CipherTrust CA when it is not already trusted by the system.

server_url=https://ciphertrust.example.com
server_cacert_file=/var/lib/postgresql/.config/ciphertrust/ca.pem

[alice]
client_id=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
cert_file=/var/lib/postgresql/.config/ciphertrust/alice.pem
key_file=/var/lib/postgresql/.config/ciphertrust/alice.key

Each connection requires server_url, client_id, cert_file, and key_file. The key_file is the user's private key used to issue the Certificate Signature Request (CSR) to Ciphertrust. The cert_file and the client_id are returned when registering this key to Ciphertrust's API (see the documentation regarding ths topic).

Values in a profile section override the global (no-section) values.

Optional disable_tls_certificate_check skips verification of the CipherTrust server certificate (the equivalent of curl -k). It defaults to false. Accepted values are true/false, yes/no, on/off, and 1/0.

Configure the TCE extension

Connect to the Postgres instance, and set tce.kms.type to ciphertrust. It is advised to set this setting database wide:

CREATE DATABASE demo;
ALTER DATABASE demo SET session_preload_libraries TO 'tce';
ALTER DATABASE demo SET tce.kms.type TO 'ciphertrust';

Create a Role

Reconnect to the Postgres instance, to apply the changes and define the roles. The key id comes from the security label (HOSTED ON KMS USING <key_id>):

\connect demo

CREATE EXTENSION tce;

CREATE ROLE sue SUPERUSER LOGIN;

SET ROLE sue;

SECURITY LABEL FOR tce_user_key ON ROLE sue
  IS 'HOSTED ON KMS USING sue_key';

CREATE ROLE alice LOGIN;
ALTER DATABASE demo OWNER TO alice;

SECURITY LABEL FOR tce_user_key ON ROLE alice
  IS 'HOSTED ON KMS USING alice_key';

Enjoy

Reconnect to the Postgres instance as Alice, to apply the changes and create the demo objects:

\connect demo alice

CREATE TABLE person (
  id SERIAL,
  firstname VARCHAR(100),
  credit_card TCETEXT
);

INSERT INTO person VALUES
  (1,'Bernard','1234567890'),
  (2,'Bob','1234567890');

SELECT *, raw(credit_card) as raw_value from person;

 id | firstname | credit_card |         raw_value
----+-----------+-------------+---------------------------------------------
  1 | Bernard   | 1234567890  | TCE:229:132:89:15:181:57:201:...
  2 | Bob       | 1234567890  | TCE:53:68:180:197:138:99:200:...

Enhanced security

For better protection, each Postgres role may use its own CipherTrust client certificate to access its own user key. This is controlled using profiles:

flowchart
    subgraph CipherTrust
    client --> key
    end

    subgraph Postgres
    credentials --> profile
    profile --> client
    profile --> role
    key --> role
    end

Create a profile for each role

On the Postgres host, add a section per role in the credentials file. By default the credentials file is named /var/lib/postgresql/.config/ciphertrust/credentials.ini:

server_url=https://ciphertrust.example.com
server_cacert_file=/var/lib/postgresql/.config/ciphertrust/ca.pem

[alice]
client_id=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
cert_file=/var/lib/postgresql/.config/ciphertrust/alice.pem
key_file=/var/lib/postgresql/.config/ciphertrust/alice.key

[sue]
client_id=yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy
cert_file=/var/lib/postgresql/.config/ciphertrust/sue.pem
key_file=/var/lib/postgresql/.config/ciphertrust/sue.key

Associate the profile to the role:

\connect demo sue

ALTER ROLE alice SET tce.kms.ciphertrust.profile TO 'alice';
ALTER ROLE sue SET tce.kms.ciphertrust.profile TO 'sue';

Now Alice gets access to her user key via her dedicated CipherTrust client:

\connect demo alice

SELECT *, raw(credit_card) as raw_value from person;

 id | firstname | credit_card |         raw_value
----+-----------+-------------+---------------------------------------------
  1 | Bernard   | 1234567890  | TCE:229:132:89:15:181:57:201:...
  2 | Bob       | 1234567890  | TCE:53:68:180:197:138:99:200:...

Clean up

Once the demo is done, you can return to the initial state with this procedure:

On the Postgres host:

Delete the credentials:

$ rm -fr /var/lib/postgresql/.config/ciphertrust/

Drop the demo objects:

DROP DATABASE demo;
DROP ROLE sue;
DROP ROLE alice;