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
| Character | In a URL | Why it breaks |
|---|---|---|
| @ | %40 | Ends the user name and password: everything after the first unencoded @ is read as the host. |
| : | %3A | Separates user from password, and host from port. |
| / | %2F | Starts the database name. |
| ? | %3F | Starts the options. |
| # | %23 | Starts a fragment: the rest of the string is thrown away. |
| % | %25 | Starts an escape itself, so a literal % must be escaped too. |
| & | %26 | Separates options. |
| + | %2B | Some parsers (PyMongo among them) read + as a space. |
| space | %20 | Ends 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).
More on PostgreSQL
DataError
invalid input for query argument $23: 'a,b' (a sized iterable container expected (got type 'str'))
28P01
FATAL: password authentication failed for user "app"
Connection refused
connection to server at "db.example.com" (203.0.113.7), port 5432 failed: Connection refused Is the server running on that host and accepting TCP/IP connections?
28000
FATAL: no pg_hba.conf entry for host "203.0.113.7", user "app", database "shop", no encryption
Other databases
MySQL connection strings
mysql://user:password@host:3306/database
MariaDB connection strings
jdbc:mariadb://host:3306/database?user=…&password=…&sslMode=verify-full
SQL Server connection strings
Server=tcp:host,1433;Database=…;User Id=…;Password=…;Encrypt=True;
Oracle connection strings
host:1521/service_name
MongoDB connection strings
mongodb://user:password@host:27017/database?authSource=admin&tls=true
SQLite connection strings
sqlite:////var/data/app.db