Skip to content

Storing User Keys in OpenBAO

The user keys must be stored outside of the PostgreSQL server. The recommended way is to use the OpenBao KMS.

Warning

This document is intended for demos and training. DO NOT USE THIS IN PRODUCTION.

OpenBao or Hashicorp Vault ?

Info

Hashicorp Vault is now proprietary software. OpenBao is an open source fork of Hashicorp Vault maintained by the Cloud Native Computing Foundation.

We strongly recommend using OpenBao over Hashicorp Vault.

That being said the documentation below should work nicely with Hashicorp Vault. All you have to do is replace the bao command line by vault

or if you are lazy:

alias bao=vault

Requirements

The starting point of this how-to is to have a OpenBao installed on the same server as Postgres.

  • A PostgreSQL instance up and running
  • The Cryptic Elephant is installed
  • OpenBao is installed (not running)

For simplicity, we will run OpenBao and Postgres on the same host. In practice, they should be located on 2 different hosts.

You can check the requirements are met with:

$ psql [...] -c `\dx` |grep tce
tce     | 0.9.0-dev | 0.9.0-dev          | public     | The Cryptic Elephant (TCE)

$ bao -version
OpenBao v2.5.5

Please refer to the OpenBao Install Guide if you need assistance.

Start OpenBao

On the OpenBao host, launch the service

$ mkdir -p /tmp/kms-tls
$ bao server \
    -dev \
    -dev-tls \
    -dev-tls-cert-dir=/tmp/kms-tls

The output will contain a root token such as b.blIKqmKNXr5T4HGevc6gvTRH

Let the server run and open a new terminal.

You now access the web interface at

https://127.0.0.1:8200

For this demo, we will use the CLI commands but every bao command below can be achieved via the web interface too.

Configure the environment variables to use the CLI

Info

The variable names differ from OpenBao and Hashicorp Vault

export BAO_ADDR='https://127.0.0.1:8200'
export BAO_CACERT='/tmp/kms-tls/vault-ca.pem'

export VAULT_SKIP_VERIFY=1
export VAULT_ADDR='https://127.0.0.1:8200'
export VAULT_CACERT='/tmp/kms-tls/vault-ca.pem'

You can check the server status at anytime with:

$ bao status
Key             Value
---             -----
Seal Type       shamir
Initialized     true
Sealed          false
Total Shares    1
Threshold       1
Version         2.5.5
Build Date      2026-06-17T11:18:48Z
Storage Type    inmem
Cluster Name    vault-cluster-5d589040
Cluster ID      0f4e54e2-5b73-7ffb-405d-ab736a8e4fe9
HA Enabled      false

Info

The OpenBao server runs in dev mode which means its storage is not persistent. In other words, if you stop the server, you will have to replay the entire how-to from here.

Principles

The TCE extension within Postgres will connect to OpenBao using a credentials file and will use the key assigned to each role.

flowchart
    subgraph OpenBao
    token
    key

    end
    subgraph Postgres
    role --> key
    credentials --> token
    end

Storing keys in OpenBAO

On the OpenBao host, the transit secret engine must be enabled to encrypt/decrypt using keys:

$ bao secrets enable transit
Success! Enabled the transit secrets engine at: transit/

Now, authenticated as an OpenBAO role that will be used by Postgres, create the user keys in the vault:

$ bao write -f transit/keys/sue_key
[...]
$ bao write -f transit/keys/alice_key
[...]

The user keys are now available.

Add a Credential file

On the Postgres host:

First transfer the Openbao certificate located at /tmp/kms-tls/vault-ca.pem on the OpenBao to the PostgreSQL instance in the /var/lib/postgresql/.config/openbao folder

export TCE_CONFIG_DIR=/var/lib/postgresql/.config/openbao/
mkdir -p $TCE_CONFIG_DIR

# Adapt this line as needed
rsync /tmp/kms-tls/vault-ca.pem $TCE_CONFIG_DIR

Then to configure the openbao server's address and the token to use to interact with OpenBAO, a configuration file must be created. By default this file is named /var/lib/postgresql/.config/openbao/credentials.ini:

server_url=https://127.0.0.1:8200
server_cert_file=/var/lib/postgresql/.config/openbao/vault-ca.pem
# the root token that was provided by the `bao server` command earlier
token=b.blIKqmKNXr5T4HGevc6gvTRH

Configure the TCE extension

Connect to the Postgres instance, and set the tce.kms.type to openbao alongside the location of the credentials file. 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 'openbao';

Create a Role

Reconnect to the Postgres instance, to apply the changes and defines the roles:

\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 OpenBao token and policy to access its own user key, this can be controlled using profiles:

flowchart
    subgraph OpenBao
    policy --> key
    policy --> token
    end

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

Create a Policy for each role

Define a strict policy to access only the encrypt/decrypt capabilities:

On the OpenBao host:

Create a policy file named /tmp/kms-tls/postgres-tce-transit.hcl

# Policy For Alice
path "transit/encrypt/alice_key" {
   capabilities = [ "update" ]
}
path "transit/decrypt/alice_key" {
   capabilities = [ "update" ]
}

# Policy For Sue
path "transit/encrypt/sue_key" {
   capabilities = [ "update" ]
}
path "transit/decrypt/sue_key" {
   capabilities = [ "update" ]
}

Activate the policy:

bao policy write postgres-tce-transit /tmp/kms-tls/postgres-tce-transit.hcl

Create a personal token for this policy

Each role may use its own OpenBao token access to access their own keys, this can be controlled using profile

On the OpenBao host:

$ bao token create \
    -policy="postgres-tce-transit" \
    -ttl="30d" \
    -display-name="alice-token"

$ bao token create \
    -policy="postgres-tce-transit" \
    -ttl="30d" \
    -display-name="sue-token"

The output will contain a token such as s.hhiGEwpn5XIFMAof74f87LpX

The -ttl option defines how long the token will live. In this case, the demo will stop working after 30 days.

Create a profile with each personal token

On the Postgres host:

Add the tokens for Alice and Sue in the credentials file where sections are role profiles used mainly to associate tokens to database roles.

By default the credentials file is named /var/lib/postgresql/.config/openbao/credentials.ini:

server_url=https://127.0.0.1:8200
server_cert_file=/var/lib/postgresql/.config/openbao/vault-ca.pem

[alice_profile]
token=s.hhiGEwpn5XIFMAof74f87LpX

[sue_profile]
token=s.koplhspskfemsf5f3c1s95dd

Create a token for this policy

And finally associate the profile to the role

\connect demo sue

ALTER ROLE alice SET tce.kms.openbao.profile TO 'alice_profile';
ALTER ROLE sue SET tce.kms.openbao.profile TO 'sue_profile';

Now Alice gets access to her user key via a dedicated policy

\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:...

Rotating keys

It is advised to change keys on a regular basis. Since the vault transit keys are stored in a keyring, old keys remain active even though new keys exist. This means that when a new key is created in OpenBAO key ring, updating the security label will automatically unlock the DEK using the old key and create a new envelope using the latest key.

This is the same thing when rotating envelope key, updating the security label will create a new envelope key.

Clean up

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

On the Postgres host:

Delete the credentials:

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

Drop the demo objects:

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

On the OpenBao host:

Just stop the OpenBao server, all the object were create in memory and the config files will be deleted.