This page covers security hardening for a PostgreSQL-backed setup where multiple agents share the same database. It only applies to the PostgreSQL backend — SQLite deployments are out of scope.

The following topics are covered:

• TLS — encrypting connections and verifying server/client identity
• Roles — limiting agent privileges to read/write only

Feel free to select and adapt what applies to your environment. In all cases, refer to the official PostgreSQL administration documentation.

TLS

TLS can provide connection encryption as well as server and client authentication.

Enable SSL mode

Enabling SSL is the first step to use TLS features. According to the postgres documentation:

To start in SSL mode, files containing the server certificate and private key must exist. By default, these files are expected to be named server.crt and server.key, respectively, in the server's data directory ¹

On postgresql.conf you can then enable SSL as follows, assuming that you already provide server.crt and server.key at the right location ($PGDATA folder).

ssl = on
# ssl_cert_file is default to '${PGDATA}/server.crt'
# ssl_key_file is default to '${PGDATA}/server.key'

Reject unencrypted connections

Through the pg_hba.conf file, the server can reject any remote connections that do not use SSL.

# Trust local (local connection can be made unconditionally)
# conntype  db      user        auth-method 
local       all     all         trust
# Allow ssl connections
# conntype  db      user        address     auth-method 
hostssl     all     all         0.0.0.0/0   scram-sha-256
hostssl     all     all         ::/0        scram-sha-256
# Explicitly reject non-SSL TCP connections
# conntype  db      user        address     auth-method 
hostnossl   all     all         0.0.0.0/0   reject
hostnossl   all     all         ::/0        reject

On the client side, at minimum sslmode=require must be passed to ensure that nothing will be sent unencrypted. Using sslmode=verify-full is recommended to also verify the server identity (see Client verification below).

Client verification

By default, PostgreSQL will not perform any verification of the server certificate. This means that it is possible to spoof the server identity without the client knowing. ²

To let agents (or other third-party clients) verifying server's identity we must provide to them the certificate authority that signed the server certificate.

Currently the CA certificate cannot be inlined in the DSN, so you must put it somewhere on the system where the client runs and append sslmode=verify-full&sslrootcert=/path/to/ca.crt to the DSN.

Here is an example of the db migration made by the agent.

situation migrate --db="postgres://user:password@db.example.org:5432/situation?sslmode=verify-full&sslrootcert=ca.crt"

situation.exe migrate --db="postgres://user:password@db.example.org:5432/situation?sslmode=verify-full&sslrootcert=ca.crt"

Client certificate

The PostgreSQL server can also authenticate client through SSL certificates (in SSL connections only of course).

You can modify pg_hba.conf as follows to activate this method.

# Allow ssl connections with client cert auth
# conntype  db      user        address     auth-method 
hostssl     all     all         0.0.0.0/0   cert
hostssl     all     all         ::/0        cert

According to the docs:

The CN (Common Name) attribute of the certificate will be compared to the requested database user name ³

So the server will check whether the client cert is well signed by the trusted CA and if its CN matches the user in the DSN. On the postgresql.conf you should append:

# trusted certificate authorities
ssl_ca_file = /path/to/ca.crt

On the client side, client certificate and private key must be passed through the sslcert and sslkey options (see the example below). The password can then be omitted.

situation migrate --db="postgres://user:@db.example.org:5432/situation?sslmode=verify-full&sslrootcert=ca.crt&sslcert=client.crt&sslkey=client.key"

situation.exe migrate --db="postgres://user:@db.example.org:5432/situation?sslmode=verify-full&sslrootcert=ca.crt&sslcert=client.crt&sslkey=client.key"

Roles

We can also add roles to limit privileges. For instance, we can keep the superuser role to run migrations and then create a basic agent role that will only perform read/write and won't be able to modify tables.

In the postgres instance:

-- Agents: read/write data only, cannot touch schema
CREATE ROLE agent LOGIN PASSWORD 'secure-password';
GRANT USAGE ON SCHEMA situation TO agent;

Assuming migrations have been run with the superuser (or another user with enough privileges), you can run the agent with --no-migration so that it does not try to run the migrations.

situation run --no-migration --db="postgres://agent:secure-password@db.example.org:5432/situation?..."

situation.exe run --no-migration --db="postgres://agent:secure-password@db.example.org:5432/situation?..."