PostgreSQL Connection String: libpq URI, Keyword/Value, and Code Examples

Q: What's the default port for PostgreSQL?

5432. The team that uses custom ports for security-through-obscurity knows they're slightly safer but mostly relies on firewall rules anyway.

Q: What's the difference between postgresql:// and postgres://?

Both work. The official scheme is `postgresql://`; `postgres://` is a common shorthand. The team that uses `postgresql://` is technically correct.

Q: Can I use a Unix socket in the connection string?

Yes - `host=/var/run/postgresql`. The team that uses peer authentication on localhost uses Unix sockets instead of TCP.

Q: What's the default sslmode?

`prefer` - tries SSL, falls back to non-SSL. The team that uses production should set `sslmode=verify-full` explicitly.

Q: Can I include the database name in the URI?

Yes - `postgresql://user:pass@host/dbname`. Optional in some clients but standard practice to include.

PostgreSQL connection strings come in two formats per libpq docs: URI (postgresql://user:pass@host:port/db) and keyword/value (host=... port=... user=... password=... dbname=...). Both work with libpq, psql, and most drivers. The URI is more readable; keyword/value is more explicit. The team that uses URIs in env vars and code has the same string across tools.

The URI format
The keyword/value format
SSL mode options
psql from CLI
Application drivers
Special characters in passwords
Security: don’t hardcode
FAQ

The URI format

postgresql://user:password@host:port/database?options

Example:

postgresql://app_user:[email protected]:5432/appdb?sslmode=require

Components:

user:role name
password:the password
host:hostname or IP
port:defaults to 5432
database:database name
?options:query string for SSL mode, application_name, etc.

The keyword/value format

host=db.example.com port=5432 user=app_user password=secret dbname=appdb sslmode=require

Same info, different syntax. The team that uses keyword/value has more readable configs in long strings.

SSL mode options

sslmode= is important for production:

disable: no SSL.
allow: try SSL, fall back to non-SSL.
prefer: try SSL, fall back to non-SSL (default).
require: SSL required, don’t verify cert.
verify-ca: SSL required, verify CA.
verify-full: SSL required, verify hostname.

The team that uses verify-full in production has verified TLS. The team that uses disable or allow has unencrypted connections.

psql from CLI

psql "postgresql://app_user:[email protected]/appdb"

# Or keyword/value
psql "host=db.example.com user=app_user dbname=appdb"

The team that uses psql with a connection string has scriptable connections.

Application drivers

Most drivers accept the URI:

# Python psycopg2
conn = psycopg2.connect("postgresql://app_user:[email protected]/appdb")

# Node.js pg
const client = new Client({
  connectionString: "postgresql://app_user:[email protected]/appdb"
});

The team that uses URIs has one string format across Python, Node, Go, etc.

Special characters in passwords

URL-encode special chars in passwords:

@ -> %40
: -> %3A
/ -> %2F
# -> %23

Example: password p@ss/word#1 becomes p%40ss%2Fword%231.

The team that uses URL-encoding has working connections with complex passwords.

Security: don’t hardcode

Connection strings with passwords shouldn’t be in source code. Use env vars:

DATABASE_URL=postgresql://app_user:[email protected]/appdb

The team that uses env vars has secrets in the environment, not in code. The team that uses AWS Secrets Manager / Vault has dynamic secret rotation.

FAQ

What’s the default port for PostgreSQL?

The team that uses custom ports for security-through-obscurity knows they’re slightly safer but mostly relies on firewall rules anyway.

What’s the difference between postgresql:// and postgres://?

Both work. The official scheme is postgresql://; postgres:// is a common shorthand. The team that uses postgresql:// is technically correct.

Can I use a Unix socket in the connection string?

Yes - host=/var/run/postgresql. The team that uses peer authentication on localhost uses Unix sockets instead of TCP.

What’s the default sslmode?

prefer - tries SSL, falls back to non-SSL. The team that uses production should set sslmode=verify-full explicitly.

Can I include the database name in the URI?

Yes - postgresql://user:pass@host/dbname. Optional in some clients but standard practice to include.

If you are sizing the infrastructure for the kind of project this post covers, the RunxBuild hosting calculator is the right place to model the line items. The compute, the memory, the storage, the bandwidth, the database - each one is a separate number, and the team’s mental model for the platform is the sum of those numbers. The RunxBuild dashboard is where the team sees the actual usage in one place.

Useful related references: