PostgreSQL Setup

Prerequisites
You’re familiar with all the Enterprise Server services.

Currently, the Email Key Manager (EKM) and FlowCrypt External Service (FES) services require integration with PostgreSQL, while for the Web Key Directory (WKD) service, this is optional. The Enterprise Admin Panel service doesn’t need its own database yet, but may need it eventually.

PostgreSQL database configurations

The example described in the next section is for the Email Key Manager, which changes appropriately when creating the database store for other services. For Web Key Directory, db_ekm becomes db_wkd, and so on. This is a recommended naming convention but you’re free to choose your own.

The minimum supported database version is PostgreSQL 15, the latest version is preferable.

Typical configurations

Having a user with all the privileges is the recommended option for most deployment cases. You need to perform these actions with a Postgres superuser:

Create the database with the CREATE DATABASE db_ekm; command.
Create the user when you use a TLS certificate-based client authentication with the CREATE USER user_ekm; command. This is recommended for production deployments. Alternatively, for testing and evaluation, you may use password-based authentication executing the CREATE USER user_ekm PASSWORD '!CHANGE THIS!'; command.

Adding database privileges to this user:

 GRANT CONNECT ON DATABASE db_ekm TO user_ekm;
 CREATE SCHEMA ekm;
 GRANT ALL ON SCHEMA ekm TO user_ekm;
 ALTER USER user_ekm SET search_path to 'ekm';

Advanced Configurations

For deployments that require separate users for read-and-write or read-only operations, the following actions are required to be performed using the Postgres superuser.

1. Create the database:

CREATE DATABASE db_ekm; 

2. Use the following command to create the user, when TLS certificate-based client authentication is required (recommended for production deployments):

CREATE USER ekm_su;
CREATE USER ekm_rw;
CREATE USER ekm_ro;

Use the following commands to create the user, when password-based authentication is required (required for testing and evaluation purposes):

CREATE USER ekm_su PASSWORD '!CHANGE THIS!';
CREATE USER ekm_rw PASSWORD '!CHANGE THIS!';
CREATE USER ekm_ro PASSWORD '!CHANGE THIS!';

3. Configure the database:

GRANT CONNECT ON DATABASE db_ekm TO ekm_su;
GRANT CONNECT ON DATABASE db_ekm TO ekm_rw;
GRANT CONNECT ON DATABASE db_ekm TO ekm_ro;
CREATE SCHEMA ekm;
GRANT ALL ON SCHEMA ekm TO ekm_su;
GRANT USAGE ON SCHEMA ekm TO ekm_ro;
GRANT USAGE ON SCHEMA ekm TO ekm_rw;
ALTER USER ekm_su SET search_path to 'ekm';
ALTER USER ekm_rw SET search_path to 'ekm';
ALTER USER ekm_ro SET search_path to 'ekm';

-- Run as ekm_su!
GRANT SELECT ON ALL TABLES IN SCHEMA ekm TO ekm_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA ekm GRANT SELECT ON TABLES TO ekm_ro;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA ekm TO ekm_rw;
GRANT SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA ekm TO ekm_rw;
ALTER DEFAULT PRIVILEGES IN SCHEMA ekm GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ekm_rw;
ALTER DEFAULT PRIVILEGES IN SCHEMA ekm GRANT SELECT, UPDATE ON SEQUENCES TO ekm_rw;

Additional steps for TLS client certificate-based authentication configuration

For database server keys, follow the PostgreSQL documentation. For client keys, create or export the private key as a PKCS#8 in a not encrypted DER format:

OpenSSL: You can convert a PEM key to PKCS#8 using the following OpenSSL command:

 openssl pkcs8 -topk8 -inform PEM -outform DER -in ekm.key -out ekm.pk8 -nocrypt

Keychain Explorer: Right-Click ⮕ Export private key ⮕ PKCS#8 ⮕ Uncheck encrypt and PEM.

The client.crt and ca.crt should be in the regular PEM format.

EKM Configurations

During the production setup process, you should allow the service to connect to a PostgreSQL (CockroachDB is also supported) database for keys and metadata storage. Sensitive data stored in this database is further encrypted using the Store Encryption Key feature.

Property	Description
`store.type`	Specifies we’re persisting into a database. Example: `DatabaseStore`
`store.database.vendor`	The database vendor. Example: `postgres`
`store.postgres.host`	A comma-separated list of CockroachDB database hostname(s) or IP address(es) with a fail-over mechanism. The connection driver will attempt once to connect to each of hosts in order until the connection succeeds. If none of the connections succeed, a standard connection exception will be thrown. Example: `10.0.0.5` (single host) or `127.0.0.1,10.0.0.5` (multiple hosts, the port number must be the same for all hosts)
`store.postgres.host.readonly` optional	A separate hostname or IP address for read-only requests. Example: `10.0.0.6`
`store.postgres.port`	Postgres database port number. Example: `5432`
`store.postgres.database`	Postgres database name. You can choose any. The recommended names are `db_ekm` or `db_wkd`. Example: `db_ekm`
`store.postgres.schema`	Postgres schema name. You can choose any. The recommended names are `ekm` or `wkd`. Example: `ekm`
`store.postgres.connections` optional	The maximum connection size that the database pool is allowed to reach. We recommend having 3 connections per CPU core. Default value: `10` Example: `12`
`store.postgres.connection.timeout.seconds` optional	The maximum number of seconds that EKM will wait for a connection from the database pool. Default value: `5` Example: `10`
`store.postgres.connection.idletimeout.seconds` optional	The maximum number of seconds that a connection is allowed to be idle in the pool. Default value: `600` (10 minutes) Example: `600`
`store.postgres.connection.maxlifetime.seconds` optional	The maximum number of seconds of a connection in the pool. It should be slightly shorter than any database or infrastructure-imposed connection time limit. Default value: `1740` (29 minutes) Example: `1740`
`store.postgres.update.schema` optional	Defines if the schema should be created or updated when the service starts. Default value: `true`. Example: `true`
`store.postgres.username`	Postgres database username. You can use any. The recommended names are `user_ekm` or `user_wkd`. Example: `user_ekm`
`store.postgres.auth.method`	Postgres database user authentication mechanism, either `password` or `cert` for SSL-based auth. Example: `password`
`store.postgres.auth.password`	(for `store.postgres.auth.method=password`) Postgres database user password. Example: `your_postgres_pwd`
`store.postgres.auth.cert`	(for `store.postgres.auth.method=cert`) Path to the client SSL certificate in PKCS#8 format. Example: `/path/to/client.crt`
`store.postgres.auth.key`	(for `store.postgres.auth.method=cert`) Path to the client SSL key in the PKCS#8 format. Example: `/path/to/client.key`
`store.postgres.auth.ca`	(for `store.postgres.auth.method=cert`) Path to a CA certificate in the PKCS#8 format that will be used to verify the identity of the Postgres database server. Example: `/path/to/ca.crt`

Database store encryption (EKM only)

See the DB Store Encryption guide to enable and configure database store encryption for sensitive information (e.g., private keys).

Test connection

To test whether the service can communicate with the configured database, run the following command:

java -jar flowcrypt-<service-name>.jar --test-store-connection

This command will connect to the data store and issue one search command to test whether the connection between Email Key Manager and the database is configured properly.

The successful output is:

INFO  com.flowcrypt.utils.Reflection - Registering DatabaseStore as Store implementation
INFO  c.f.keymanager.TestStoreConnection - initiating test
INFO  c.f.keymanager.TestStoreConnection - store session started successfully
INFO  c.f.keymanager.TestStoreConnection - testing store search command
INFO  c.f.keymanager.TestStoreConnection - round trip latency: 8ms
INFO  c.f.keymanager.TestStoreConnection - closing session
INFO  c.f.keymanager.TestStoreConnection - success

Scaling

When running more than one node please refer to the High Availability and Scaling guide. All nodes talk to a common PostgreSQL interface.

The rule of thumb when sizing the PostgreSQL cluster versus the service cluster is to use approximately the same amount of overall cores and memory for both the PostgreSQL cluster and the app cluster that uses it.