← Connection strings

PostgreSQL connection strings

PostgreSQL has two native forms, a URI and a list of keyword=value pairs, and both are read by libpq, the C library under psql, psycopg, pgx and most drivers. Java and .NET use their own formats on top. Below is each one, built for the same database, the TLS settings that matter, and what breaks when the password has an @ in it.

Build yours (PostgreSQL)

The parts of a PostgreSQL connection string

The shape

postgresql://user:password@host:5432/database?sslmode=verify-full
postgresql://
postgres:// works in libpq too, but SQLAlchemy 1.4 and later reject it.
user:password@
Percent-encoded: an @ in the password is %40, a / is %2F, a % is %25.
host:5432
Several hosts are allowed, comma-separated: h1:5432,h2:5432.
/database
Left out, the database defaults to the user name.
?sslmode=…
disable, prefer (the default), require, verify-ca or verify-full.

One PostgreSQL database, every form

User app_user, password s3cret-Pa55, database shop on db.example.com, tls, verify the certificate.

URI

psql, libpq, psycopg, asyncpg, node-postgres, pgx, Prisma, DATABASE_URL · connected with on a real server

postgresql://app_user:s3cret-Pa55@db.example.com:5432/shop?sslmode=verify-full

Keyword/value (libpq)

psql, psycopg, pgx, any libpq program · connected with on a real server

host=db.example.com port=5432 dbname=shop user=app_user password=s3cret-Pa55 sslmode=verify-full

JDBC

Java (pgJDBC), DBeaver, IntelliJ, Spring

jdbc:postgresql://db.example.com:5432/shop?user=app_user&password=s3cret-Pa55&sslmode=verify-full

SQLAlchemy

Python with SQLAlchemy and psycopg 3

postgresql+psycopg://app_user:s3cret-Pa55@db.example.com:5432/shop?sslmode=verify-full

.NET (Npgsql)

C#, EF Core

Host=db.example.com;Port=5432;Database=shop;Username=app_user;Password=s3cret-Pa55;SSL Mode=VerifyFull

psql command

Shell · connected with on a real server

psql 'postgresql://app_user:s3cret-Pa55@db.example.com:5432/shop?sslmode=verify-full'

Environment variables (libpq)

psql, pg_dump and every libpq program, with no string at all · connected with on a real server

export PGHOST=db.example.com
export PGPORT=5432
export PGDATABASE=shop
export PGUSER=app_user
export PGPASSWORD=s3cret-Pa55
export PGSSLMODE=verify-full

A password in the environment is visible to other processes of the same user; a ~/.pgpass file is the safer place.

A password with special characters

The same database with the password p@ss/w#rd 1. URLs encode it, key=value forms quote it, and each form does it differently:

URI

postgresql://app_user:p%40ss%2Fw%23rd%201@db.example.com:5432/shop?sslmode=verify-full

Keyword/value (libpq)

host=db.example.com port=5432 dbname=shop user=app_user password='p@ss/w#rd 1' sslmode=verify-full

JDBC

jdbc:postgresql://db.example.com:5432/shop?user=app_user&password=p%40ss%2Fw%23rd%201&sslmode=verify-full

SQLAlchemy

postgresql+psycopg://app_user:p%40ss%2Fw%23rd%201@db.example.com:5432/shop?sslmode=verify-full

CharacterIn a URLWhy it breaks
@%40Ends the user name and password: everything after the first unencoded @ is read as the host.
:%3ASeparates user from password, and host from port.
/%2FStarts the database name.
?%3FStarts the options.
#%23Starts a fragment: the rest of the string is thrown away.
%%25Starts an escape itself, so a literal % must be escaped too.
&%26Separates options.
+%2BSome parsers (PyMongo among them) read + as a space.
space%20Ends the string on a command line, and is trimmed by some parsers.

TLS: what each setting looks like

Shown in the URI form; the builder writes the matching setting for every other one.

No TLS

postgresql://app_user:s3cret-Pa55@db.example.com:5432/shop

TLS, don't check the certificate

postgresql://app_user:s3cret-Pa55@db.example.com:5432/shop?sslmode=require

TLS, verify the certificate

postgresql://app_user:s3cret-Pa55@db.example.com:5432/shop?sslmode=verify-full

What catches people out on PostgreSQL

The default sslmode is prefer, which can fall back to plain text

prefer tries TLS and quietly connects without it if the server doesn't offer it. Use require to insist on encryption, or verify-full to also check the certificate and the host name, which is what stops someone in the middle.

require does not check the certificate (usually)

With sslmode=require the connection is encrypted but the server is not authenticated, unless a root certificate file exists at ~/.postgresql/root.crt, in which case libpq verifies against it as verify-ca would.

postgres:// stopped working in SQLAlchemy

SQLAlchemy 1.4 removed the postgres:// alias. Hosting platforms that hand out postgres:// URLs need the scheme changed to postgresql:// (or postgresql+psycopg://) before create_engine.

A Unix socket is a host too

host=/var/run/postgresql in keyword form, or postgresql:///mydb?host=/var/run/postgresql as a URI. Peer authentication on the socket ignores the password entirely.

Failover is built in

postgresql://h1:5432,h2:5432/db?target_session_attrs=read-write tries each host and keeps the one that accepts writes — no proxy needed for a primary/standby pair.

The password belongs in ~/.pgpass, not the URL

libpq reads host:port:database:user:password lines from ~/.pgpass (mode 0600). Leave the password out of the string and it is looked up there, so it never reaches shell history or a process list.

Connected. Now what?

Paste the same string into DBShifts to browse PostgreSQL with sensitive columns masked and every read logged, or to migrate it to another database with each table counted and checksummed afterwards.

Questions

What is the PostgreSQL connection string format?

postgresql://user:password@host:port/database?parameters, or the keyword form host=… port=… dbname=… user=… password=…. Both are understood by libpq and everything built on it; JDBC uses jdbc:postgresql://host:port/database?user=…&password=….

How do I put special characters in a PostgreSQL password in a URL?

Percent-encode them: @ becomes %40, : %3A, / %2F, ? %3F, # %23, % %25 and a space %20. In the keyword/value form, put the value in single quotes instead and escape a ' or \ inside it with a backslash. The builder on this site does both.

What sslmode should I use?

verify-full whenever the server has a real certificate: it encrypts and checks that you reached the right host. require encrypts without checking. disable and the default prefer can send everything, password included, in plain text.

What is the default PostgreSQL port?

5432. Left out of the string, libpq uses 5432 (or PGPORT if it is set).