Using Testcontainers with PostgreSQL over SSL/TLS (with client certificates)
This guide walks you through running PostgreSQL in Testcontainers with SSL enabled and client-certificate authentication (verify-full). It explains:
- How to generate a CA, server, and client certificates.
- How to prepare certificate/key files and the right formats/permissions.
- How to configure PostgreSQL (postgresql.conf and pg_hba.conf) to require client certificates.
- How to wire everything up in Testcontainers.
- How to connect from Java (JDBC) and optionally from the psql CLI.
Everything here is based on the code in this repository, particularly:
- CryptoUtil.java: https://github.com/ozkanpakdil/java-examlpes/blob/master/postgresql-ssl-testcontainers/src/test/java/com/example/ssl/CryptoUtil.java
- PostgresWithClientCertTest.java: https://github.com/ozkanpakdil/java-examlpes/blob/master/postgresql-ssl-testcontainers/src/test/java/com/example/ssl/PostgresWithClientCertTest.java
TL;DR
- Generate a self-signed CA.
- Issue a server cert for hostname localhost (SAN=DNS:localhost) and a client cert for the DB user (CN=testuser).
- Copy certs/keys into the container during init; set ssl=on and point PostgreSQL to the files.
- Set pg_hba.conf rules to require client certs with verify-full.
- Connect from Java using JSSE keystore/truststore or from psql using PEM files.
Prerequisites
- Docker installed and accessible by Testcontainers.
- Java 17+ and Maven 3.9+.
- Optionally, psql installed if you want to try the CLI connection.
Certificate model and requirements We’ll use a minimal but correct PKI model:
- A self-signed Certificate Authority (CA) issues the server and client certificates.
- The server certificate must include the DNS name clients will use to connect; here we use localhost and set SAN=DNS:localhost.
- The client certificate’s CN should match a PostgreSQL role; we’ll create role testuser and issue a client cert with CN=testuser.
Key points and formats:
- PostgreSQL accepts server key/cert in PEM files referenced by postgresql.conf.
- The client key for psql must be readable only by the user (0600) or psql will refuse to use it.
- PgJDBC (when using libpq factory) expects the client private key in PKCS#8 (BEGIN PRIVATE KEY). We handle that in this project.
Generating the CA, server, and client certificates This project generates all materials at runtime for you (see CryptoUtil.java). The essentials:
- Create a self‑signed CA:
- Is a CA cert (basicConstraints CA:TRUE)
- Has keyCertSign and cRLSign key usages
- Issue a server certificate (Extended Key Usage: serverAuth) with SAN=DNS:localhost
- Issue a client certificate (Extended Key Usage: clientAuth) with CN=testuser
Snippet (simplified from CryptoUtil):
var ca = generateSelfSignedCA("Demo-CA");
var server = issueCertificate(ca, "localhost", true, List.of("localhost"));
var client = issueCertificate(ca, "testuser", false, List.of());
// Write to PEM files
writePemCertificate(caCertPem, ca.certificate());
writePemCertificate(serverCertPem, server.certificate());
writePemPrivateKey(serverKeyPem, server.keyPair().getPrivate()); // server: PKCS#1 (RSA PRIVATE KEY) ok
writePemCertificate(clientCertPem, client.certificate());
writePemPrivateKeyPkcs8(clientKeyPem, client.keyPair().getPrivate()); // client: PKCS#8 for PgJDBC
About formats:
- Server key: the code writes PKCS#1 (BEGIN RSA PRIVATE KEY), which PostgreSQL accepts.
- Client key: written as PKCS#8 (BEGIN PRIVATE KEY) so PgJDBC can load it if using libpq factory.
Permissions for the client key (psql requires 0600):
// Best-effort on POSIX; otherwise fallback to owner-only readable/writable
Set<PosixFilePermission> perms = EnumSet.of(PosixFilePermission.OWNER_READ, PosixFilePermission.OWNER_WRITE);
Files.setPosixFilePermissions(clientKeyPem, perms);
Java keystore/truststore (JSSE) for JDBC:
// Client key + chain in PKCS#12 keystore (client.p12)
createPkcs12KeyStore(clientKeystore, "client", client.keyPair().getPrivate(),
new Certificate[]{client.certificate(), ca.certificate()}, KEYSTORE_PASSWORD);
// Truststore containing the CA
createPkcs12TrustStore(truststore, "ca", ca.certificate(), KEYSTORE_PASSWORD);
Configuring PostgreSQL for SSL + client cert auth We need to:
- Enable SSL in postgresql.conf and point to server cert/key and CA.
- Require client certificates in pg_hba.conf with verify-full.
A minimal pg_hba.conf used here:
# TYPE DATABASE USER ADDRESS METHOD
# Allow local socket connections for init scripts and superuser tasks
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
# Require client certs for all TCP connections
hostssl all all 0.0.0.0/0 cert clientcert=verify-full
hostssl all all ::0/0 cert clientcert=verify-full
The postgresql.conf changes we apply during container init:
ssl=on
ssl_cert_file='server.crt'
ssl_key_file='server.key'
ssl_ca_file='root.crt'
listen_addresses='*'
We write an init shell script (00-ssl.sh) that the official postgres image runs once on first startup:
#!/bin/bash
set -euo pipefail
echo '[ssl-init] Configuring Postgres SSL (copying files into PGDATA)'
cp /docker-entrypoint-initdb.d/server.crt "$PGDATA"/server.crt
cp /docker-entrypoint-initdb.d/server.key "$PGDATA"/server.key
cp /docker-entrypoint-initdb.d/root.crt "$PGDATA"/root.crt
chmod 600 "$PGDATA"/server.key || true
chmod 644 "$PGDATA"/server.crt "$PGDATA"/root.crt || true
echo "ssl=on" >> "$PGDATA"/postgresql.conf
echo "ssl_cert_file='server.crt'" >> "$PGDATA"/postgresql.conf
echo "ssl_key_file='server.key'" >> "$PGDATA"/postgresql.conf
echo "ssl_ca_file='root.crt'" >> "$PGDATA"/postgresql.conf
echo "listen_addresses='*'" >> "$PGDATA"/postgresql.conf
# Replace pg_hba.conf with our rules
cp /docker-entrypoint-initdb.d/pg_hba.conf "$PGDATA"/pg_hba.conf
Finally, create the database role that matches the client cert’s CN (testuser):
CREATE ROLE "testuser" LOGIN;
Wiring it up with Testcontainers The PostgreSQLContainer is started with SSL assets and init scripts copied into /docker-entrypoint-initdb.d:
pg = new PostgreSQLContainer<>("postgres:16")
.withDatabaseName("postgres")
.withUsername("postgres")
.withPassword("postgres")
.withStartupTimeout(Duration.ofSeconds(30))
.withLogConsumer(new Slf4jLogConsumer(LOG).withSeparateOutputStreams())
// Init SQL and HBA rules
.withCopyFileToContainer(MountableFile.forHostPath(initSql), "/docker-entrypoint-initdb.d/01-init.sql")
.withCopyFileToContainer(MountableFile.forHostPath(pgHba), "/docker-entrypoint-initdb.d/pg_hba.conf")
// SSL assets; the 00-ssl.sh script moves them into $PGDATA
.withCopyFileToContainer(MountableFile.forHostPath(serverCertPem, 0644), "/docker-entrypoint-initdb.d/server.crt")
.withCopyFileToContainer(MountableFile.forHostPath(serverKeyPem, 0644), "/docker-entrypoint-initdb.d/server.key")
.withCopyFileToContainer(MountableFile.forHostPath(caCertPem, 0644), "/docker-entrypoint-initdb.d/root.crt")
.withCopyFileToContainer(MountableFile.forHostPath(sslInit, 0755), "/docker-entrypoint-initdb.d/00-ssl.sh");
pg.start();
Notes:
- We initially set relaxed permissions (0644) on server.key while it lives in /docker-entrypoint-initdb.d so the postgres user inside the container can read it during init. The script then tightens perms when moving it into $PGDATA.
- Make sure your server certificate’s SAN includes the hostname you’ll use from the client (localhost here). verify-full enforces this.
Connecting from Java (JDBC) with verify-full Two approaches are common:
- JSSE keystore/truststore (used here): put client key+cert chain into a PKCS#12 keystore and CA into a PKCS#12 truststore; use DefaultJavaSSLFactory.
- Libpq (PEM) style files via PgJDBC’s LibPQFactory (not shown here, but compatible with the PEM files you generated).
In this project we use JSSE:
String url = "jdbc:postgresql://localhost:" + pg.getFirstMappedPort() + "/postgres";
// JSSE system properties
System.setProperty("javax.net.ssl.keyStore", clientKeystore.toAbsolutePath().toString());
System.setProperty("javax.net.ssl.keyStorePassword", new String(KEYSTORE_PASSWORD));
System.setProperty("javax.net.ssl.keyStoreType", "PKCS12");
System.setProperty("javax.net.ssl.trustStore", truststore.toAbsolutePath().toString());
System.setProperty("javax.net.ssl.trustStorePassword", new String(KEYSTORE_PASSWORD));
System.setProperty("javax.net.ssl.trustStoreType", "PKCS12");
Properties props = new Properties();
props.setProperty("user", "testuser");
props.setProperty("ssl", "true");
props.setProperty("sslmode", "verify-full");
props.setProperty("sslfactory", "org.postgresql.ssl.DefaultJavaSSLFactory");
try (Connection conn = DriverManager.getConnection(url, props)) {
// ... use the connection
}
Verification query used in the test:
select current_user,
(select ssl from pg_stat_ssl where pid = pg_backend_pid()) as ssl_used;
Connecting with psql (optional) If you have psql on your PATH, you can connect with the generated PEM files and verify-full:
- Set the environment variables:
- PGHOST=localhost
- PGPORT=
- PGDATABASE=postgres
- PGUSER=testuser
- PGSSLMODE=verify-full
- PGSSLROOTCERT=/path/to/root.crt
- PGSSLCERT=/path/to/client.crt
- PGSSLKEY=/path/to/client.key (must be 0600)
Example invocation (from the test):
psql --set ON_ERROR_STOP=on -c "select 1;"
The test builds a Process with these environment variables and checks the exit code to be 0.
Common pitfalls and troubleshooting
- SAN and hostname mismatch: verify-full checks that the hostname you connect to matches a SAN on the server certificate. Include DNS:localhost in SAN if you connect to localhost.
- Wrong key format for client key in PgJDBC: use PKCS#8 (BEGIN PRIVATE KEY) for compatibility with libpq factories, or use JSSE keystore as shown.
- Client key permissions: psql requires 0600 on the private key; otherwise it refuses to use it.
- CA trust: ensure your client trusts the issuing CA. With JSSE, put the CA into a truststore; with psql, pass sslrootcert.
- Server key permissions: PostgreSQL demands restrictive permissions on server.key (the init script sets 600 in $PGDATA).
- Docker not available: tests in this project self-skip if Docker is not reachable; ensure Docker is running.
- Container startup hangs: set reasonable timeouts; this project uses a 30s startup timeout and a class-level @Timeout(60).
Security notes
- Use a real CA and long(er) lifetimes for non-test environments.
- Manage keys securely; don’t commit private keys to VCS. This project generates them at test time only.
- Prefer verify-full over verify-ca; verify-full protects you from hostname spoofing.
- Rotate keys/certs periodically; keep revocation in mind for production.
Full flow summary
- Generate runtime CA, server (SAN=localhost) and client (CN=testuser) certificates.
- Create keystore/truststore for Java and write PEM files for PostgreSQL and psql.
- Copy certs/keys and pg_hba.conf into the postgres container; run an init script to enable SSL and tighten permissions.
- Create role testuser.
- Connect via JDBC using verify-full (JSSE) or via psql with PEM files.
That’s it—you now have a fully working Postgres over SSL with client certificates inside Testcontainers, including both JDBC and CLI examples.