(opens in a new tab)

Postgres Connection String: Format and Options

September 21, 2026 by Chat2DB Team

Almost every PostgreSQL problem that looks like a driver bug turns out to be a connection string problem. A password with an @ in it that was never percent-encoded. An sslmode that silently fell back to plaintext. A pooler URL that works in psql but not from the application because the driver parsed one parameter differently. The connection string is a small piece of configuration that controls authentication, encryption, timeouts and failover all at once, and it rewards understanding the format properly rather than copying one from a dashboard.

This guide covers both connection string formats PostgreSQL accepts, the parameters worth knowing, how the same string looks in psql, Python, Node.js, Go and JDBC, and the escaping rules that cause the most wasted afternoons.

The two formats

libpq — the C library nearly every PostgreSQL driver wraps — accepts two syntaxes for the same information.

The URI format is the one you will see most often:

postgresql://user:password@host:port/dbname?param1=value1&param2=value2

The keyword/value format is a space-separated list:

host=localhost port=5432 dbname=mydb user=alice password=secret sslmode=require

Both are equivalent and both accept the same parameter names. The URI form is more portable across languages and fits in a single environment variable, so it has become the default in container and cloud deployments. The keyword/value form is easier to read when you have many parameters and does not require percent-encoding.

A minimal working example of each:

# URI format
psql "postgresql://alice:secret@db.example.com:5432/appdb?sslmode=require"
 
# Keyword/value format
psql "host=db.example.com port=5432 dbname=appdb user=alice password=secret sslmode=require"

Both postgresql:// and the shorter postgres:// scheme are accepted and mean exactly the same thing. There is no functional difference; postgres:// is simply an alias.

Everything is optional

Each component of the URI can be omitted, and libpq falls back to an environment variable, then to a built-in default:

postgresql://                             # all defaults
postgresql://localhost                    # host only
postgresql://localhost:5433               # host and port
postgresql://localhost/mydb               # host and database
postgresql:///mydb                        # database via Unix socket
postgresql://alice@localhost/mydb         # no password (uses .pgpass or trust/peer auth)
postgresql:///mydb?host=/var/run/postgresql  # explicit socket directory

The fallbacks are worth memorising because they explain a lot of surprising behaviour:

The dbname default is the one that catches people out: connecting as user alice with no database specified tries to open a database called alice, and fails with FATAL: database "alice" does not exist even though the server is perfectly healthy.

Escaping special characters

In the URI format, the user name and password are URI components, so any character outside the unreserved set must be percent-encoded. Passwords generated by password managers routinely contain @, /, :, #, ? and +, and every one of them breaks parsing.

The @ is the worst because it does not error — it silently changes which host you connect to. Given the password p@ssw0rd, this string:

postgresql://alice:p@ssw0rd@db.example.com/appdb

is parsed with the last @ as the separator, so the host becomes db.example.com, the password becomes ssw0rd and... actually many parsers split on the first @, making the host ssw0rd@db.example.com. The behaviour varies by driver, which is exactly why you should never rely on it. Percent-encode instead:

So the correct form is:

postgresql://alice:p%40ssw0rd@db.example.com/appdb

You can generate the encoding in any language rather than doing it by hand:

from urllib.parse import quote_plus
print(quote_plus("p@ssw0rd/2026"))   # p%40ssw0rd%2F2026

The keyword/value format has no percent-encoding at all. Instead, values containing spaces or single quotes are wrapped in single quotes with backslash escapes:

host=db.example.com user=alice password='p@ssw0rd with space' dbname=appdb

This is often the simpler choice for awkward passwords. Better still, keep the password out of the string entirely and put it in ~/.pgpass:

# ~/.pgpass — must be chmod 600
db.example.com:5432:appdb:alice:p@ssw0rd

libpq reads this file automatically when no password is supplied, and no escaping rules apply to it.

SSL parameters

sslmode is the single most important security parameter, and its default is weaker than most people assume.

The default prefer means "use TLS if the server offers it, otherwise connect in plaintext anyway". An attacker who can intercept the connection can simply answer that TLS is unavailable and read everything. Even require only guarantees encryption — it does not check who you are encrypting to, so a man-in-the-middle presenting any self-signed certificate is accepted.

For anything crossing a network you do not control, use verify-full:

postgresql://alice@db.example.com/appdb?sslmode=verify-full&sslrootcert=/etc/ssl/certs/rds-ca.pem

The related file parameters are:

sslrootcert=/path/to/ca.pem      # CA bundle used to verify the server
sslcert=/path/to/client.crt      # client certificate, for cert-based auth
sslkey=/path/to/client.key       # matching private key (chmod 600)
sslnegotiation=direct            # PG 17+: skip the plaintext round trip

sslrootcert=system (PostgreSQL 16 and later) uses the operating system trust store, which is what you want for managed services that use publicly-trusted certificates.

Timeouts and failover

Several parameters exist purely to stop an application hanging on a dead database:

connect_timeout=10              # seconds to wait for the TCP connect; 0 = wait forever
keepalives=1                    # enable TCP keepalives (default on)
keepalives_idle=30              # seconds of idle before the first probe
keepalives_interval=10          # seconds between probes
keepalives_count=3              # failed probes before the connection is dropped
tcp_user_timeout=30000          # ms of unacknowledged data before giving up (Linux)

Without connect_timeout, a connection to a host that is dropping packets rather than refusing them will block until the OS TCP timeout — potentially minutes. Setting it to 5–10 seconds is one of the highest-value changes you can make to a production connection string.

You can also list several hosts for automatic failover. libpq tries each in turn:

postgresql://alice@primary.example.com:5432,replica1.example.com:5432,replica2.example.com:5432/appdb?target_session_attrs=read-write

target_session_attrs filters which of those hosts is acceptable:

• any — first host that accepts a connection (default)
• read-write — skip hosts where the session is read-only, i.e. find the primary
• read-only — find a replica
• primary / standby — based on actual recovery state
• prefer-standby — try standbys first, fall back to the primary

This gives you client-side failover without a proxy. Combined with load_balance_hosts=random (PostgreSQL 16+), you can spread read traffic across replicas from the connection string alone.

Other useful parameters

application_name=billing-worker     # shows up in pg_stat_activity — always set this
options=-c statement_timeout=30000  # set any GUC at connection time
options=-c search_path=tenant_42,public
client_encoding=UTF8
gssencmode=disable                  # skip GSSAPI negotiation round trip

application_name deserves special mention. It costs nothing and turns pg_stat_activity from an anonymous list of connections into something you can actually debug:

SELECT application_name,
       state,
       count(*)                                   AS conns,
       max(now() - state_change)                  AS longest_in_state
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY application_name, state
ORDER BY conns DESC;

The options parameter is how you apply per-connection settings without a separate SET round trip. Note that the value needs encoding when it contains spaces — options=-c%20statement_timeout%3D30000 in URI form, or single quotes in keyword/value form.

The same string in every language

psql

psql "postgresql://alice:secret@db.example.com:5432/appdb?sslmode=verify-full"
 
# Test without running a query
psql "postgresql://alice@db.example.com/appdb" -c "SELECT version();"

psql also honours PGHOST, PGUSER, PGPASSWORD and friends, so psql with no arguments often works once those are exported.

Python (psycopg 3)

import psycopg
 
DSN = "postgresql://alice:secret@db.example.com:5432/appdb?sslmode=verify-full&connect_timeout=10"
 
with psycopg.connect(DSN, application_name="report-job") as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT current_database(), current_user, version()")
        print(cur.fetchone())

psycopg accepts both formats and also keyword arguments, which sidesteps escaping entirely:

conn = psycopg.connect(
    host="db.example.com",
    port=5432,
    dbname="appdb",
    user="alice",
    password="p@ssw0rd",     # no encoding needed
    sslmode="verify-full",
)

Node.js (node-postgres)

import { Pool } from "pg";
 
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  connectionTimeoutMillis: 10_000,
  idleTimeoutMillis: 30_000,
});
 
const { rows } = await pool.query("SELECT now() AS ts");
console.log(rows[0].ts);

One well-known gotcha: node-postgres does not implement libpq's sslmode semantics faithfully. ?sslmode=require in a connectionString historically disabled certificate verification. For verified TLS, pass an explicit ssl object:

import fs from "node:fs";
 
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: {
    rejectUnauthorized: true,
    ca: fs.readFileSync("/etc/ssl/certs/rds-ca.pem").toString(),
  },
});

Go (pgx)

cfg, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
if err != nil {
    log.Fatal(err)
}
cfg.MaxConns = 10
cfg.ConnConfig.RuntimeParams["application_name"] = "api-server"
 
pool, err := pgxpool.NewWithConfig(context.Background(), cfg)

JDBC

JDBC uses its own scheme and does not accept a user/password in the authority section the same way:

jdbc:postgresql://db.example.com:5432/appdb?user=alice&password=secret&ssl=true&sslmode=verify-full&ApplicationName=batch-loader

Note jdbc: prefix, user and password as query parameters, and ApplicationName in camel case rather than application_name. Feeding a plain postgresql:// URI to JDBC fails with No suitable driver found, which is a very common first-day error when moving a string from a .env file into a Java service.

Connecting through PgBouncer

When you point an application at PgBouncer instead of PostgreSQL directly, the connection string changes in two ways. The port is usually 6432, and in transaction pooling mode certain features are unavailable:

postgresql://alice:secret@pgbouncer.internal:6432/appdb?sslmode=require&prepared_statements=false

In transaction pooling mode a client does not keep the same server connection between statements, so server-side prepared statements, SET that is expected to persist, advisory locks held across statements, LISTEN/NOTIFY and temporary tables all break. Most drivers need to be told to stop using prepared statements — prepared_statements=false for pgx, prepare_threshold=None for psycopg, ?prepareThreshold=0 for JDBC, ?pgbouncer=true for Prisma.

Debugging a connection string

When a string does not work, resolve it rather than guessing. psql will tell you exactly what it parsed:

# What did libpq actually resolve?
psql "postgresql://alice@db.example.com/appdb" -c "\conninfo"
# You are connected to database "appdb" as user "alice" on host "db.example.com"
# at port "5432". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384)

And from inside a session, confirm the encryption and identity the server sees:

SELECT current_database(),
       current_user,
       inet_server_addr()   AS server_ip,
       inet_server_port()   AS server_port;
 
-- Is this session actually encrypted?
SELECT ssl, version, cipher, client_addr
FROM pg_stat_ssl
JOIN pg_stat_activity USING (pid)
WHERE pid = pg_backend_pid();

If pg_stat_ssl.ssl comes back false on a connection you thought was encrypted, your sslmode fell back to plaintext — almost always because it was left at the prefer default.

Common errors and their usual cause:

When you are juggling several of these strings across development, staging and production, a client that stores and validates them is easier than a scratch file of URIs. Chat2DB (opens in a new tab) keeps each connection as a named profile with its own SSL settings and lets you test the handshake before saving; the web version (opens in a new tab) works the same way without a local install.

A production-ready template

Putting the recommendations together, a connection string for a service talking to a managed PostgreSQL over the public internet should look something like this:

postgresql://app_user@primary.example.com:5432,replica.example.com:5432/appdb
  ?target_session_attrs=read-write
  &sslmode=verify-full
  &sslrootcert=system
  &connect_timeout=10
  &application_name=orders-api
  &options=-c%20statement_timeout%3D30000

(written on one line in practice). The password lives in ~/.pgpass or a secrets manager rather than the string, TLS is verified rather than merely enabled, a dead host fails fast instead of hanging, runaway queries are capped at 30 seconds, and pg_stat_activity will tell you which service opened each connection.

Summary

A PostgreSQL connection string is small but it configures four separate concerns. For routing, remember that omitted components fall back to environment variables and then defaults, and that dbname defaults to the user name. For credentials, percent-encode everything in URI form or use the keyword/value form or .pgpass to avoid encoding altogether. For security, prefer is not secure — use verify-full with a CA bundle for anything leaving your network. For resilience, always set connect_timeout, consider multi-host failover with target_session_attrs=read-write, and always set application_name so you can identify the connection later.

Get those four right and connection strings stop being a source of mysterious production incidents.