PostgreSQL remote connections: psql, URIs and SSL modes

A remote connection to PostgreSQL needs five things to line up: a host and a port (5432 unless somebody changed it), a database name, a role with a password, a server that is listening on an address you can reach, and a line in pg_hba.conf that lets your address in. Get all five right and the connection string is one line. Get one wrong and you get an error that names the wrong culprit, which is why this post spends as much time on the failures as on the setup.

A default PostgreSQL install refuses remote connections deliberately. It listens on localhost only and its host-based authentication file is empty of anything but local rules. That is a sensible default for a database on your laptop and useless for a database an application has to reach, so somebody has to change it - either you, on your own machine, or the host, before they hand you the credentials.

What a remote connection actually needs#

Work through these in order when something does not connect. Each one produces a different error, and the order matters because a failure early on makes the later checks meaningless.

1. DNS and routing. The host name resolves, and packets reach the machine. ping proves neither on its own, because ICMP is often blocked while TCP is fine.
2. A listening socket. listen_addresses in postgresql.conf includes the address the packets arrive on. The default is localhost; '*' means every interface. Changing it needs a restart, not a reload.
3. A path through the firewall. Port 5432/tcp inbound, ideally only from the addresses that should be using it. Firewall rules that matter covers the shape of a sane rule set.
4. A matching `pg_hba.conf` line. Host-based authentication is checked per connection against the source address, the database, the role and the type of connection. First match wins, and a non-match is a refusal, not a fallthrough.
5. Credentials the role can present. A password with scram-sha-256, a client certificate with cert, or nothing at all with trust, which you should never see on anything reachable from the internet.

The connection string, piece by piece#

PostgreSQL clients built on libpq accept two formats, and most language drivers accept at least the first.

postgresql://appuser:s3cret@db.example.com:5432/shop?sslmode=requirehost=db.example.com port=5432 dbname=shop user=appuser sslmode=require

The URI form takes postgresql:// or the shorter postgres://; both are the same scheme. Everything after the ? is a libpq parameter, and the useful ones are worth knowing by name:

Two details catch people out. The first is percent-encoding: a password containing @, :, /, ?, # or % breaks the URI unless those characters are escaped (@ becomes %40). Generated passwords love exactly those characters. If a string looks right and the client reports a bizarre host name, an unescaped @ is why. The keyword form has no such problem, which is a reason to prefer it in shell scripts.

The second is that the parts of a connection string all have environment-variable equivalents, and a missing part falls back to them: PGHOST, PGPORT, PGDATABASE, PGUSER, PGPASSWORD, PGSSLMODE, PGAPPNAME. psql with no arguments tries to connect to a database named after your operating-system user on a local socket, which is why a bare psql on a fresh machine fails with a confusing message about a socket in /var/run/postgresql.

Keeping a password in PGPASSWORD puts it in the process environment, where anything on the machine can read it. The better habit is a password file:

db.example.com:5432:shop:appuser:s3cretdb.example.com:5432:*:readonly:other-secret

It must be chmod 0600 or libpq ignores it silently. On Windows the same file lives at %APPDATA%\postgresql\pgpass.conf and permissions are not checked. Wildcards are allowed in the first four fields. For application secrets, the environment is still the usual answer - see environment variables and secrets for how to keep them out of your repository.

psql, and the commands worth knowing#

psql is the reference client and the one every error message assumes you are using. You do not need a PostgreSQL server installed to get it: on Debian and Ubuntu it is postgresql-client, on Fedora postgresql, on macOS brew install libpq (and you add its bin directory to PATH), and on Windows the official installer lets you deselect the server and keep the command-line tools.

A newer psql talks to an older server perfectly well. The reverse works for ordinary queries but not for pg_dump, which refuses to dump from a server newer than itself. Match your client to the newest server you touch.

$ psql "postgresql://appuser@db.example.com:5432/shop?sslmode=require"$ psql -h db.example.com -U appuser -d shop -c "select version();"$ psql -h db.example.com -U appuser -d shop -f schema.sql

Once you are in, the backslash commands do most of the work:

\copy is the one people miss. COPY orders TO '/tmp/orders.csv' runs on the server, writes to the server's disk and needs a privileged role. \copy orders TO 'orders.csv' CSV HEADER runs the same query and writes the file on your machine, which is almost always what you meant.

SSL modes: what each one actually protects#

sslmode is a spectrum from "do not bother" to "prove you are the server I asked for", and the middle of it is weaker than it looks.

prefer is libpq's default, and it is the dangerous one: a connection that should be encrypted will quietly go in clear text if anything goes wrong with TLS on the server. require encrypts but accepts any certificate at all, including one presented by whoever is between you and the database. Only verify-ca and verify-full make interception hard, and only verify-full catches a certificate that is valid but for the wrong machine.

To use them you need a CA file. PostgreSQL 16 and later accept sslrootcert=system, which means the operating system's trust store - the right answer when the database presents a certificate from a public CA. Otherwise point sslrootcert at the file your host gives you, which on a self-signed setup is the server's own certificate.

postgresql://appuser@db.example.com:5432/shop?sslmode=verify-full&sslrootcert=system

Non-libpq drivers have their own defaults and they are not the same. The JDBC driver and several Go and .NET drivers do not negotiate TLS unless you ask. Always set the mode explicitly rather than trusting a default you have not read.

The server side: listening, pg_hba.conf and the firewall#

If you administer the server yourself, two files control who gets in. postgresql.conf decides where the server listens:

listen_addresses = '*'port = 5432password_encryption = scram-sha-256

pg_hba.conf decides who is allowed once they arrive. It is read top to bottom and the first line whose connection type, database, user and source address all match is the one used - even if it rejects you and a later line would have let you in.

# TYPE   DATABASE  USER      ADDRESS            METHODlocal    all       postgres                     peerhostssl  shop      appuser   203.0.113.10/32    scram-sha-256hostssl  shop      readonly  203.0.113.0/24     scram-sha-256host     all       all       0.0.0.0/0          reject

hostssl matches only TLS connections, which is how you make encryption compulsory on the server side rather than hoping the client asked for it. host matches either. hostnossl matches only unencrypted ones and exists mostly to reject them. Changes to this file need a reload, not a restart:

SELECT pg_reload_conf();

listen_addresses and port are different - they are read at startup only, so changing them means a restart. On a managed database you may have none of this to do, because the server is already listening and the rules are already written; what varies between hosts is how much of the configuration you are allowed to touch. Check before you plan a change around it.

On RE:NODE the database lines are PostgreSQL and MongoDB, each plan carries one allocation, and you connect on that host and port with the superuser password the panel generated for that server rather than a default shared by every install of the same image. Because a database is reached by an application on a port and not by a browser, these plans carry no reverse-proxy slot - there is nothing useful to put in front of 5432.

GUI clients and tunnels#

Every desktop client wants the same five fields, plus somewhere to set the SSL mode. pgAdmin 4 is the official one and the heaviest; DBeaver is the usual cross-platform choice; TablePlus and DataGrip are the paid ones people stay with. All of them will happily connect with SSL turned off, so check that setting after you create the connection rather than assuming.

Where a client is useful and a terminal is not: browsing an unfamiliar schema, looking at a wide result set, and editing a value by hand during an incident. Where it is worse: anything you will want to repeat, because a query saved in a .sql file and run with psql -f is reviewable and a click is not.

If the machine gives you shell access, an SSH tunnel keeps the database port off the public network entirely:

$ ssh -N -L 5433:127.0.0.1:5432 you@vds.example.com$ psql -h 127.0.0.1 -p 5433 -U appuser -d shop

The database then only ever listens on localhost, and the only exposed port is SSH. This is the right pattern on a VDS you administer - see SSH keys and hardening - but it needs an SSH account on the machine, which a managed database plan does not necessarily include. On a panel-based host the equivalent protection is restricting the source addresses and using verify-full.

Connecting from application code#

Every driver is a thin wrapper over the same parameters. Keep the whole string in one environment variable, usually DATABASE_URL, and let the driver parse it.

import { Pool } from "pg";const pool = new Pool({  connectionString: process.env.DATABASE_URL,  max: 10,  idleTimeoutMillis: 30_000,  connectionTimeoutMillis: 5_000,});const { rows } = await pool.query("select id, total from orders where id = $1", [id]);

import osimport psycopgwith psycopg.connect(os.environ["DATABASE_URL"], connect_timeout=5) as conn:    with conn.cursor() as cur:        cur.execute("select id, total from orders where id = %s", (order_id,))        row = cur.fetchone()

SQLAlchemy   postgresql+psycopg://appuser:s3cret@db.example.com:5432/shopDjango       DATABASES["default"] = dj_database_url.config()  # reads DATABASE_URLGo (pgx)     pgxpool.New(ctx, os.Getenv("DATABASE_URL"))JDBC         jdbc:postgresql://db.example.com:5432/shop?sslmode=verify-full

Two rules apply whatever the language. Use placeholders ($1, %s, ?) and never string concatenation - that is what makes SQL injection impossible rather than unlikely. And open a pool once at startup instead of a connection per request: each PostgreSQL connection is a backend process with its own memory, and a hundred of them will hurt long before they help. Connection pools and limits has the sizing arithmetic, and it is the same arithmetic that decides max_connections in PostgreSQL tuning for small servers.

Errors you will actually see#

`connection refused` - nothing is listening on that address and port, or a firewall dropped the packet with a reset. Check listen_addresses, check the service is running, check the port. This error never comes from authentication.

`timeout expired` or a hang - the packets are going into a void. A firewall that drops rather than rejects, the wrong host name, or an address the machine has no route to. Different cause from "refused", despite feeling identical.

`no pg_hba.conf entry for host "203.0.113.10", user "appuser", database "shop", no encryption` - you reached the server and it read its rules. The trailing phrase is the useful bit: no encryption means you connected in clear text and only hostssl lines matched. Set sslmode=require and try again before editing anything.

`password authentication failed for user "appuser"` - the role exists and the password is wrong, or the role does not exist at all. PostgreSQL deliberately does not distinguish. Check for a trailing space, and for a password that was percent-decoded differently than you expected.

`database "shop" does not exist` - you are connected to the server. Run psql -l to see what is there; the name is case-sensitive if it was created with quotes.

`FATAL: sorry, too many clients already` - max_connections is full. Almost always an application opening connections and not returning them rather than genuine load.

`server closed the connection unexpectedly` - the backend died or something in the middle gave up. Look at the server log for an out-of-memory stop, and at any load balancer or NAT device for an idle timeout shorter than your pool's.

`SSL error: certificate verify failed` - verify-ca or verify-full with a CA file that does not sign the server's certificate, or a host name that does not match. Check the name you are connecting to against the certificate's subject.

Two habits shorten all of this. Test with psql before blaming the application: it uses the same library and reports the real error, where an ORM may wrap it in three layers. And change one thing at a time, starting from a working local connection outwards.

FAQ#

What port does PostgreSQL use?

5432/tcp by default. It is only a convention set by the port setting, and moving it is mild obscurity rather than security - scanners find a database on any port in minutes. Restricting source addresses is what actually helps.

Do I need PostgreSQL installed locally to use psql?

No. Install the client package only: postgresql-client on Debian and Ubuntu, libpq from Homebrew on macOS, or the official Windows installer with the server component deselected. A newer client connects to an older server without trouble.

Why does my connection work from my laptop but not from my app server?

Because pg_hba.conf matches on source address. Your laptop's address is allowed and the app server's is not, or the app server leaves through a different public address than you think. Check the exact address in the error message - the server prints the one it saw.

Is sslmode=require enough?

It encrypts the traffic, which stops passive capture, but it accepts any certificate, so it does not stop an active attacker who can redirect your traffic. Use verify-full with a root certificate whenever the connection crosses a network you do not control.

How many connections should my application open?

Fewer than you think. A pool of ten to twenty per application process is plenty for most workloads, and the total across every process and background worker has to fit inside max_connections with room to spare for an administrator. On a 1-2 GB database server, that total belongs in the low tens.

Can I connect from a phone or a laptop on mobile data?

You can, but the source address changes constantly, which means either a wide pg_hba.conf rule or a tunnel. Prefer the tunnel, or an application endpoint in front of the database, over opening 5432 to the world.