Connection strings

A Balta connection string is an ordinary PostgreSQL connection string. sslmode=verify-full is not optional. What goes beside it depends on the client, because the four ecosystems below read three different trust stores.

This page is the reference for each part. Connect is the short path.

The shape #

postgresql://ROLE:PASSWORD@HOST:PORT/DATABASE?sslmode=verify-full&sslrootcert=system

There is no file to download and no path to fill in. Your endpoint presents a publicly trusted certificate, and every client below verifies it against roots it already has.

Verification, per client #

sslrootcert=system needs libpq 16 or newer; on an older libpq give it the path to the operating system's bundle instead: /etc/ssl/certs/ca-certificates.crt on Debian and Ubuntu, /etc/pki/tls/certs/ca-bundle.crt on RHEL and its family, and $(brew --prefix)/etc/openssl@3/cert.pem with Homebrew's libpq on macOS.

Java needs the factory as well as the mode. pgjdbc's default SSL factory imitates libpq: it looks for a root certificate at ~/.postgresql/root.crt and throws a FileNotFoundException naming that path when the file is not there. sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory hands verification to the JVM truststore, which already carries the public roots. sslmode=verify-full on its own is not enough for this driver.

Node checks the hostname only when you ask it to. rejectUnauthorized: true verifies the chain. servername is what makes it check the hostname, and without it the connection is verify-ca wearing verify-full's name. Node's roots depend on where the binary came from: the builds on nodejs.org carry their own list, and the Debian and Ubuntu nodejs packages are compiled to read /etc/ssl/certs. Either way a public root is present. NODE_EXTRA_CA_CERTS is how you add one.

Both endpoints, one hostname #

The pooled and direct endpoints share a hostname and differ by port: the lower of your two ports is pooled, the higher is direct. That is deliberate. It means switching a consumer from one to the other is a port change rather than a DNS change, and it means neither endpoint can drift onto a different name.

The pair is your service's own, not a pair every Balta service shares. Services in a location share their hosts' addresses and are told apart by port, so yours are on the Connect tab and nowhere else. They do not change for the life of the service, including when we move it to another host.

The examples on this page use 25438 and 25439. That pair belongs to one service and to no other. Yours are on the Connect tab. Every example here is the pooled endpoint; change the port to the higher one and the same string reaches the direct endpoint.

Keep the password out of the URL #

A password in a connection string ends up in process listings, shell history, container inspect output and log lines. Supply it separately.

export PGPASSWORD=REDACTED-EXAMPLE-ONLY
psql "postgresql://[email protected]:25438/appdb?sslmode=verify-full&sslrootcert=system"

Every credential in this documentation is the literal string REDACTED-EXAMPLE-ONLY. If you find something in our docs, dashboard or fixtures that looks like a real password, it is a defect and we want to hear about it.

Escaping #

Some characters are special in a URL: @, :, /, ?, #, %. If a role name or password contains one, percent-encode it, or use the key-value form instead:

host=db-example.location.example port=25438 dbname=appdb user=app_user sslmode=verify-full sslrootcert=system

The key-value form has no escaping rules to get wrong, which is a good reason to prefer it in configuration files.

Two parameters worth adding #

• connect_timeout — without it a client can hang for the operating system's TCP timeout, which is much longer than you want.
• application_name — it appears in pg_stat_activity, and it is the difference between "some backend" and "the reporting job".

What you will see #

\conninfo in psql reports the database, the user, the port and whether TLS was negotiated. It is the quickest way to confirm which endpoint you reached. On PostgreSQL 18 it prints a table:

        Connection Information
      Parameter       |     Value
----------------------+----------------
 Database             | appdb
 Client User          | app_user
 Host                 | db-example.location.example
 Server Port          | 25438
 SSL Connection       | true
 Superuser            | off

Server Port tells you which endpoint you are on, and SSL Connection must read true. On PostgreSQL 16 and 17 the same command prints a sentence instead of a table.

Troubleshooting #

invalid connection option. A parameter name is misspelled, or a URL query parameter was used where the key-value form was intended. The two forms take the same names and different separators.

invalid value for parameter "sslrootcert": "system". The libpq your client is linked against is older than 16. Give it the path to the operating system's bundle instead, as above.

A password with a @ in it fails to parse. Percent-encode it, or move the password out of the URL entirely.

The connection reaches the wrong endpoint. Check the port. The hostname is the same for both.

Related #

• Pooled versus direct
• TLS and certificate verification
• Client examples