PostgreSQL

Configuring Hyperscience for PostgreSQL

For a list of supported versions, see Infrastructure Requirements (Production).

Add the following variables to your ".env" file:

FORMS_DB_TYPE=postgres
FORMS_DB_HOST=<ip>
FORMS_DB_PORT=<port>
FORMS_DB_NAME=<DB>
FORMS_DB_USER=<username>
FORMS_DB_PASS=<password>

PostgreSQL SSL connections

To enable SSL connections from the Hyperscience application to the PostgreSQL database, place all certificate files in the ${HS_PATH}/certs folder and set the following environment variables.

HS_POSTGRES_CLIENT_TLS_MODE=<client TLS mode>

The possible client TLS modes are:

• allow - First attempt a non-SSL connection. If this fails, try a TLS(SSL) connection.

• disable - Only try a non-TLS(SSL) connection.

• prefer - This is the default option. First try an SSL connection. If that fails, try a non-TLS(SSL) connection.

• require - Only try a TLS(SSL) connection. If a root CA file is present, verify the certificate in the same way as if verify-ca was specified.

• verify-ca - Only try a TLS(SSL) connection, and verify that the server certificate is issued by a trusted certificate authority (CA).

• verify-full - Only try a TLS(SSL) connection, verify that the server certificate is issued by a trusted CA and that the server host name matches that in the certificate.

HS_POSTGRES_CLIENT_TLS_ROOT_CERT=<filename of the certs bundle>

HS_POSTGRES_CLIENT_TLS_ROOT_CERT specifies the root and leaf certs bundle that can be used to verify the server certificate. The variable should include only the filename and the file should be placed in the ${HS_PATH}/certs folder.

HS_POSTGRES_CLIENT_TLS_CRL=<filename of certification revocation list>

HS_POSTGRES_CLIENT_TLS_CRL specifies the certification revocation list to check when connecting to the server. The variables should include only the filename, and the file should be put in the ${HS_PATH}/certs folder.

HS_POSTGRES_CLIENT_TLS_CERT=<filename of client certificate>
HS_POSTGRES_CLIENT_TLS_KEY=<filename of client certificate key>

Note that if the clientcert server option is not set or set to no-verify(0), the server will still try to verify client certificates against the root certificate if those are configured. The server will not insist on this verification if the client does not have the certificates configured. If setting these keys, make sure they are signed by the server’s root CA. The variables should include only the filename and the file should be put in the ${HS_PATH}/certs folder.

At-rest encryption

To secure your PII data, you can enable at-rest encryption for a PostgreSQL database. Data at rest can be encrypted at the partition layer by encrypting the whole partition and configuring the PostgreSQL server to use this partition. 

PgBouncer

PgBouncer is a lightweight connection pooler for PostgreSQL that is designed to run between the database server and the Hyperscience application. The goal of running PgBouncer between the database server and Hyperscience is to minimize the load on the database server by limiting the number of the actual connections to it. This load reduction allows Hyperscience to quickly scale resources like blocks and threads, which increases overall performance. 

When PgBouncer is enabled in Hyperscience, a PgBouncer container is started on every application machine. Only the PgBouncer containers establish connections to the database server, while all application containers establish connections to the PgBouncer containers. In this way, the PgBouncer containers limit the number of outgoing connections to the database server and serve as load balancers for the database.

PgBouncer is included in the Hyperscience installation bundles for v31.0.9+ and v32.0.1+. No additional installations are required to use PgBouncer; you only need to enable it after installing Hyperscience. 

You do not need to run PgBouncer on the same machine as your PostgreSQL database.

When to enable PgBouncer

You should enable PgBouncer when:

• you are running v31.0.9+ or v32.0.1+,

• you have a PostgreSQL database, and

• the total number of CPU cores in all application machines in the instance is greater than 100.

How to enable PgBouncer

1. Add the HS_PGBOUNCER_ENABLED variable to your “.env” file.

Add the following to your application’s “.env” file:

HS_PGBOUNCER_ENABLED=yes

2. Add the HS_PGB_MAX_DB_CONNECTIONS variable to your “.env” file.

This setting defines the maximum number of connections that each application machine can make:

HS_PGB_MAX_DB_CONNECTIONS=<number>

The recommendation is that the total number of connections to the database server should not be greater than 500, so the value for this configuration option can be calculated as 500 divided by the number of application machines in the instance.

For example, if you have 5 application machines, you would enter:

HS_PGB_MAX_DB_CONNECTIONS=100