psql Host: Connecting to a Remote Postgres With -h, Connection URLs, and the Server Side That Has to Agree

To connect psql to a remote host, pass -h with the hostname or use a connection URL: psql -h db.example.com -U app -d appdb, or psql postgresql://[email protected]:5432/appdb. If that hangs or is refused, the client is fine and the server is not listening for you: listen_addresses in postgresql.conf has to include the interface, pg_hba.conf has to have a host line for your address, and the firewall has to allow 5432. Add sslmode=require so the password does not cross the network in the clear.

Every answer to this question shows the pg_hba.conf line and stops. The line is correct and it is one of four things that have to agree before the connection works. Here are all four, from the client side inward, then the errors you get when each one is wrong, then the pattern that avoids exposing the database to the internet at all.

Table of contents

• Three ways to tell psql where the host is
• What the server has to allow
• SSL: sslmode and why require is the floor
• The errors and what each means
• The safer pattern: do not expose 5432 at all
• How this fits the rest of the stack
• FAQ

Three ways to tell psql where the host is

psql reads the host from, in order of precedence, the command line, the environment, and a service file. Pick one and be consistent; mixing them is how people connect to the wrong database.

The -h flag. The plain form. -p for the port if it is not 5432, -U for the user, -d for the database.

psql -h db.example.com -p 5432 -U app -d appdb

A connection URL. One string, which is what most application frameworks want anyway, so it is worth using the same form in the shell.

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

Putting the password in the URL puts it in your shell history. Leave it out and psql prompts, or set it in ~/.pgpass with 0600 permissions.

Environment variables. PGHOST, PGPORT, PGUSER, PGDATABASE and PGSSLMODE are read by psql and every libpq client. Useful in a deploy environment; confusing on a laptop where you forget they are set.

export PGHOST=db.example.com PGUSER=app PGDATABASE=appdb PGSSLMODE=require
psql

There is also a service file, ~/.pg_service.conf, which names a set of parameters so that psql service=prod works. Worth it if you connect to more than two databases.

One subtlety: if you omit -h entirely, psql connects over a Unix socket, not TCP to localhost. That is why a local connection can work while -h localhost fails; they hit different pg_hba.conf rules.

What the server has to allow

The client can be perfect and the connection still fails, because Postgres ships in a state where it accepts connections from the local machine only. Three settings on the server side open it up.

listen_addresses in postgresql.conf. Defaults to localhost, meaning the server does not bind to the network interface at all. Set it to the private IP, or to a wildcard if a firewall is doing the real filtering.

listen_addresses = '*'

This needs a restart, not a reload.

pg_hba.conf. The host-based authentication file. Each line says which connection type, database, user and client address is allowed, and how it must authenticate. A remote client needs a host line, or hostssl to require TLS.

# TYPE  DATABASE  USER  ADDRESS         METHOD
hostssl appdb     app   10.0.0.0/8      scram-sha-256

Use the narrowest address range you can. The line everyone copies from forum answers, 0.0.0.0/0, means the whole internet, and it is the reason Postgres servers appear in breach reports. Use scram-sha-256, not md5, on any version from 14 up. This needs a reload.

The firewall. Port 5432 has to be open from the client’s address. On a cloud VM this is a security group rule; on a VPS it is ufw allow from 10.0.0.5 to any port 5432.

SSL: sslmode and why require is the floor

By default psql tries SSL and falls back to plain if the server does not offer it, which is sslmode=prefer. That means a misconfigured server silently gets your password in cleartext. Set the floor explicitly.

• require. Encrypts the connection. Does not verify the server certificate, so a man in the middle could still present their own. The minimum for anything over a network.
• verify-ca. Encrypts and checks the server certificate was signed by a CA in your sslrootcert file.
• verify-full. verify-ca plus a hostname match. The right setting when the database host has a proper certificate, which managed databases do.

On the server, ssl = on in postgresql.conf and a certificate and key file. Then use hostssl in pg_hba.conf so plain connections are refused rather than merely discouraged.

The errors and what each means

The error text tells you which of the four layers said no. Read it before changing anything.

• Connection timed out. Packets are not arriving. Wrong host, wrong port, or the firewall is dropping them. Check with nc -zv db.example.com 5432.
• Connection refused. The host is reachable and nothing is listening on that port. listen_addresses is still localhost, or Postgres is not running, or it is on a different port.
• no pg_hba.conf entry for host. The server accepted the TCP connection and rejected you by policy. Add a host or hostssl line for your address, database and user, and reload.
• password authentication failed. The pg_hba line matched. The password is wrong, or the user does not exist, or the method in pg_hba (md5 vs scram) does not match how the password was stored.
• server does not support SSL, but SSL was required. You set sslmode=require and the server has ssl = off. Fix the server; do not lower the client.
• FATAL: database appdb does not exist. Everything worked; you asked for the wrong database. psql -l lists them.

The safer pattern: do not expose 5432 at all

The pg_hba line with a public address range is the wrong answer to most remote connection problems. The better patterns keep the database off the public internet entirely.

Private network. The application and the database share a private network; the database listens only on its private IP; pg_hba allows only that range. Nothing on the internet can reach the port. This is the default on any decent managed database.

SSH tunnel for humans. When you need psql from a laptop, tunnel through a host that is on the private network rather than opening the port.

ssh -L 5433:10.0.0.9:5432 [email protected]
# in another terminal
psql -h localhost -p 5433 -U app -d appdb

The database sees a connection from the bastion’s private IP, which pg_hba already allows. Your laptop never needed a rule.

On RunxBuild, a managed Postgres runs on a private network with the services that use it, with connection limits, backups and user management in the dashboard; see database network security on RunxBuild for what is exposed and what is not. The connection string the dashboard gives you is the -h value, and the SSL is already on.

How this fits the rest of the stack

The four things that have to agree, the address psql is given, listen_addresses, pg_hba.conf and the firewall, are the whole story of remote Postgres access, and the errors map to them one to one. If the database is going to live next to an application, model the pair together before choosing plans: the RunxBuild hosting calculator shows the service and the managed database as separate line items, and the connection limit on the database plan is the number that matters once the app has a pool. Databases on RunxBuild covers the managed instance, and the dashboard is where the connection string lives.

Useful related references:

• Connect to a Database Remotely Without Leaving It Open to the Internet
• PostgreSQL Port: 5432 (Default), How to Change, and Multi-Version Setup
• The psql Tool: What It Is For, the Flags and Meta-Commands That Matter, and When a GUI Is the Wrong Answer
• Database network security on RunxBuild

FAQ

How do I connect psql to a remote host?

Use psql -h HOSTNAME -p 5432 -U USER -d DATABASE, or a URL: psql postgresql://USER@HOSTNAME:5432/DATABASE?sslmode=require. The server must have listen_addresses set to include its network interface, a matching host line in pg_hba.conf, and port 5432 open in the firewall.

Why does psql work locally but not with -h?

Without -h, psql connects over a Unix socket, which matches the local pg_hba.conf rule. With -h it uses TCP, which needs listen_addresses to include the interface and a host line in pg_hba.conf for the client address. Different path, different rules.

What does no pg_hba.conf entry for host mean?

Postgres accepted the network connection and found no line in pg_hba.conf allowing that combination of client address, database and user. Add a host or hostssl line for the address range, then reload Postgres.

Is it safe to set listen_addresses to a wildcard?

Only when a firewall or private network restricts who can reach the port. The wildcard binds every interface; pg_hba.conf and the firewall decide who gets in. Never combine it with a 0.0.0.0/0 line in pg_hba.conf.

Should I use sslmode=require or verify-full?

require is the floor: it encrypts but does not check the certificate. verify-full also checks the certificate matches the hostname, which stops a man in the middle. Use verify-full when the server has a proper certificate, which managed databases provide.