PostgreSQL connection string (URI and key/value) explained

The two forms

PostgreSQL’s client library, libpq, accepts a connection string in two forms. These are the same connection:

postgresql://app:<password>@db.example.com:5432/shop?sslmode=verify-full&application_name=orders-api

host=db.example.com port=5432 dbname=shop user=app password=<password> sslmode=verify-full application_name=orders-api

psql, pg_dump, pg_restore and drivers built on libpq (such as psycopg and Ruby’s pg) accept either. Many other drivers, such as Go’s pgx and Node’s pg, parse the URI themselves and support a subset of the same parameters. Pass the string wherever a database name is expected:

psql 'postgresql://app@db.example.com:5432/shop?sslmode=verify-full'
pg_dump -d 'host=db.example.com dbname=shop user=app' -f shop.sql

Put the URI in single quotes in a shell: & and ? mean something to the shell.

The URI, part by part

postgresql://[user[:password]@][host][:port][,host2[:port2]...][/dbname][?param=value[&...]]

Rules we checked with psql 18.6:

• A parameter in the query string beats the same thing in the path. postgresql://…/inlet?dbname=postgres connected to postgres.
• Unknown parameters are an error, not ignored:

  psql: error: invalid URI query parameter: "unknown_param"
  

• Spaces and = inside a value must be encoded (%20, %3D):

  psql: error: extra key/value separator "=" in URI query parameter: "options"
  

• + is a plus sign, not a space: ?application_name=a+b set the name to a+b.

A working example against PostgreSQL 18.6:

psql 'postgresql://inlet:<password>@localhost:54318/inlet?application_name=orders-api&connect_timeout=5' \
  -c "select current_user, current_database(), current_setting('application_name')"

 current_user | current_database | current_setting
--------------+------------------+-----------------
 inlet        | inlet            | orders-api
(1 row)

Parameters you’ll use

sslmode

Whether to use TLS, and how much to check. The default is prefer.

require, verify-ca and verify-full fail if the server doesn’t do TLS:

psql: error: connection to server at "localhost" (::1), port 54318 failed: server does not support SSL, but SSL was required

verify-ca and verify-full need to know which CA to trust. By default libpq looks for ~/.postgresql/root.crt:

psql: error: connection to server at "localhost" (::1), port 54328 failed: root certificate file "/Users/<you>/.postgresql/root.crt" does not exist
Either provide the file, use the system's trusted roots with sslrootcert=system, or change sslmode to disable server certificate verification.

With the server’s CA given in sslrootcert, verify-full connected as localhost and refused a name the certificate doesn’t list:

psql: error: connection to server at "127.0.0.1", port 54328 failed: server certificate for "localhost" (and 2 other names) does not match host name "db.example.com"

Only verify-full protects against someone in the middle presenting their own valid certificate. Use it for anything across a network you don’t control. Two details:

• require verifies the chain if a root certificate is present. If ~/.postgresql/root.crt exists (or sslrootcert is set), require behaves like verify-ca.
• sslmode is ignored for Unix-socket connections.

sslrootcert, sslcert, sslkey

• sslrootcert=<path>: the CA certificate(s) to trust for verify-ca and verify-full. Hosted providers publish theirs (Amazon RDS, Azure, Google Cloud SQL and others).
• sslrootcert=system (libpq 16 and later): trust the operating system’s CA store. It turns on verify-full, and anything weaker is refused:

  psql: error: weak sslmode "require" may not be used with sslrootcert=system (use "verify-full")
  

  It suits providers whose certificates come from a public CA.
• sslcert=<path> and sslkey=<path>: a client certificate and its key, when the server authenticates you by certificate. The key file must not be readable by others (chmod 600).

application_name

A label for your connection. It shows up in pg_stat_activity, in server logs (with %a in log_line_prefix) and in tools that list sessions, so you can tell your API’s connections from a migration script’s. Set it on every service.

connect_timeout

Seconds to wait for each connection attempt. The default is to wait indefinitely (in practice, until the operating system gives up). It applies per host and address: with two hosts and connect_timeout=5, the whole attempt can take 10 seconds.

options

Settings sent to the server at connection start, as command-line style -c name=value flags. Encode the spaces and = in a URI:

psql 'postgresql://inlet:<password>@localhost:54318/inlet?options=-c%20search_path%3Dpg_catalog%20-c%20statement_timeout%3D5s' \
  -Atc 'show search_path' -c 'show statement_timeout'

pg_catalog
5s

In key/value form: options='-c statement_timeout=5s'. Some connection poolers reject or ignore options; there, set those defaults with ALTER ROLE … SET instead.

Multiple hosts and target_session_attrs

List several hosts, each with its own port. libpq tries them in order until one accepts:

psql 'postgresql://inlet:<password>@localhost:54399,localhost:54318/inlet?target_session_attrs=read-write&connect_timeout=3' \
  -c '\echo :HOST :PORT'

localhost 54318

Nothing listened on 54399, so it moved on. target_session_attrs says which kind of server to accept:

On a primary, target_session_attrs=standby fails with server is not in hot standby mode. The last four values need libpq 14 or later. load_balance_hosts=random (libpq 16 and later) tries the hosts in random order instead, for spreading connections across replicas.

Other parameters worth knowing

• sslnegotiation=direct (libpq and server 17 and later): start TLS straight away, saving a round trip. Only allowed with sslmode=require or stricter.
• require_auth=scram-sha-256 (libpq 16 and later): refuse to send a password unless the server uses this method. Against our MD5 server it failed with:

  authentication method requirement "scram-sha-256" failed: server requested a hashed password
  

• channel_binding=require: insist on SCRAM channel binding, which ties authentication to the TLS connection. Needs TLS.
• passfile, service: see .pgpass and pg_service.conf below.

The key/value form

Space-separated keyword=value pairs, using the same parameter names. Put values with spaces in single quotes; escape a single quote or backslash inside a value with a backslash:

PGPASSWORD=<password> psql "host=localhost port=54318 dbname=inlet user=inlet application_name='nightly report'" \
  -Atc 'show application_name'

nightly report

There’s no percent-encoding in this form, which makes it the easier place for a password with special characters: password='p@ss:w/rd#100%' works as written.

Environment variables

Every parameter has a PG… environment variable, used when the connection string doesn’t set it:

PGHOST=localhost PGPORT=54318 PGUSER=inlet PGDATABASE=inlet PGAPPNAME=from-env psql \
  -Atc "select current_user, current_database(), current_setting('application_name')"

inlet|inlet|from-env

Order of precedence, highest first: what the connection string says, then the service file, then environment variables, then defaults. We checked: with PGAPPNAME=from-env set, service=local18 connected with the service file’s application_name=from-service, and an explicit application_name=override beat both. An environment variable can also surprise you the other way: PGSSLMODE=require left in a shell profile makes every connection without an sslmode demand TLS.

.pgpass

~/.pgpass keeps passwords out of connection strings and shell history. One line per server:

hostname:port:database:username:password
db.example.com:5432:*:app:<password>
localhost:54318:*:inlet:<password>

• * matches anything in the first four fields. The first matching line wins, so put specific lines above general ones.
• Escape : and \ in a field with a backslash.
• The file must not be readable by group or others: chmod 600 ~/.pgpass. Otherwise libpq ignores it:

  WARNING: password file "/Users/<you>/.pgpass" has group or world access; permissions should be u=rw (0600) or less
  

• The host is compared as text. A line for localhost didn’t match a connection to 127.0.0.1, and psql asked for a password. For Unix-socket connections to the default socket directory, the line for localhost is used.
• PGPASSFILE or the passfile parameter points libpq at another file.

pg_service.conf

A service file gives a name to a set of parameters, so you can write service=reporting instead of the whole string:

# ~/.pg_service.conf
[local18]
host=localhost
port=54318
dbname=inlet
user=inlet
application_name=from-service

Use it in any of these ways; all three connected in our test:

psql 'service=local18'
psql 'postgresql:///?service=local18'
PGSERVICE=local18 psql

libpq reads ~/.pg_service.conf (or the file named by PGSERVICEFILE), then the system-wide pg_service.conf in the directory pg_config --sysconfdir prints (or PGSYSCONFDIR). The per-user file wins when both define the same service. Passwords can go in the file, but .pgpass is the better place; it applies to service connections too. An unknown name fails straight away:

psql: error: definition of service "nope" not found

Unix sockets

On the same machine you can skip TCP and connect through a Unix-domain socket. Give the socket’s directory as the host:

host=/tmp dbname=shop
postgresql:///shop?host=/tmp
postgresql://%2Ftmp/shop

With no host at all, libpq uses its built-in default directory. For the psql we tested on macOS that’s /tmp:

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: fe_sendauth: no password supplied

Homebrew and Postgres.app servers put their socket in /tmp; Debian and Ubuntu packages use /var/run/postgresql. Inside a Debian-based PostgreSQL 18 container, all of these connected:

psql 'postgresql://inlet@%2Fvar%2Frun%2Fpostgresql/inlet'
psql 'host=/var/run/postgresql dbname=inlet user=inlet'
psql 'postgresql:///inlet?host=/var/run/postgresql&user=inlet'

Socket connections use the local lines of pg_hba.conf, which often allow peer authentication (your operating-system user name) instead of a password. If you get no pg_hba.conf entry, the rule for your connection type is missing.

In Inlet

Paste a postgres:// or postgresql:// URL into a new connection and Inlet fills in the form. TLS supports every sslmode; verify-full checks the server against the certificates macOS trusts, including ones you’ve added to the Keychain, and you can give a CA file, client certificate and key. Inlet can also import connections from ~/.pgpass, pg_service.conf and a .env file’s DATABASE_URL, and keeps passwords in the Keychain. For provider-specific strings, see Supabase and Neon.