Skip to content

Matches page titles and page text. Forty pages, indexed at build time.

Client examples

Connection examples for psql, Node, Python, Go, Ruby on Rails, Django and JDBC. Every one uses verify-full against the trust store the client already has, and keeps the password out of the URL.

Connection examples for the command line and for seven drivers. Every one verifies the certificate chain and the hostname, and none of them puts a password in a connection string.

There is no file to download. Your endpoint presents a publicly trusted certificate, and each example below points its client at the trust store that client already reads. Those stores are not the same store, which is why the examples differ: libpq reads the operating system's, the JVM reads cacerts, and Node reads either its own list or the operating system's.

To use one: replace db-example.location.example with the endpoint on your service's Connect tab and app_user with your role. Set PGPASSWORD in the environment your process reads. Credentials in these examples are the literal string REDACTED-EXAMPLE-ONLY.

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; use the higher port for the direct one.

Command line and configuration 

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

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.

Drivers 

import { Pool } from 'pg';

export const pool = new Pool({
host: 'db-example.location.example',
port: 25438,
database: 'appdb',
user: 'app_user',
password: process.env.PGPASSWORD,
max: 10,
ssl: {
  // node-postgres defaults to permissive. rejectUnauthorized verifies the
  // chain; servername is what makes it check the hostname.
  rejectUnauthorized: true,
  servername: 'db-example.location.example',
},
});

Two things every example has in common

verify-full, always. Several drivers default to something weaker, and two of them need a second parameter before the mode means what it says. Those parameters are in the examples above for a reason: servername in Node, sslfactory in Java.

An application name. Set one. It turns "an unidentified backend is holding a lock" into "the nightly export is holding a lock":

sql
SELECT application_name FROM pg_stat_activity WHERE pid = pg_backend_pid();

What you will see

A working connection answers \conninfo with your database, your role, the port you chose, and SSL Connection true. The lower of your service's two ports is the pooled endpoint, and anything session-scoped in your application belongs on the higher one instead.

Troubleshooting

certificate verify failed in a driver that works in psql. The driver defaulted to something weaker. Node's pg needs both rejectUnauthorized: true and servername; rejectUnauthorized on its own checks the chain and not the hostname.

A Java stack trace through org.postgresql.ssl.LibPQFactory, ending in FileNotFoundException for ~/.postgresql/root.crt. The JDBC URL is missing sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory. pgjdbc looked for a root certificate file rather than asking the JVM.

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

Rails or Django connects and then a background job misbehaves. The job is probably using LISTEN, a session advisory lock or a session-level SET. Give that process the direct endpoint. See pooled vs direct.

PGPASSWORD is ignored. The process did not inherit it. In a container, pass it through the container's own environment rather than exporting it in the shell that launched the build.