TopolearnMost 'can't connect' problems come down to the URI, pg_hba.conf or SSL settings covered here.
Applications connect with a driver: psycopg (Python), pg / postgres (Node.js), pgx (Go), the JDBC driver (Java), Npgsql (.NET). Almost all accept a connection URI:
postgresql://user:password@host:5432/dbname?sslmode=require
Tools built on libpq (psql, pg_dump) also read environment variables such as PGHOST, PGPORT, PGUSER, PGDATABASE and PGPASSWORD, and a ~/.pgpass file for passwords, so secrets don't end up in shell history.
On the server two layers decide whether a connection is accepted:
listen_addressesinpostgresql.confcontrols which network interfaces the server listens on (defaultlocalhostin most packages; the Docker image listens on all).pg_hba.conf("host-based authentication") is an ordered list of rules: connection type, database, user, client address, and method —scram-sha-256for passwords,peerfor local OS users,certfor client certificates. The first matching line wins; reload the server after editing.
Practical rules: use one role per application with only the privileges it needs; require TLS (sslmode=require or verify-full) for anything crossing a network; and keep the number of connections modest — each one is a server process, so use a pool in the application (and possibly PgBouncer) instead of opening a connection per request.
Example
# Python with psycopg 3: pip install "psycopg[binary]"
import psycopg
url = "postgresql://app:secret@localhost:5432/shop"
with psycopg.connect(url) as conn: # commits on clean exit
with conn.cursor() as cur:
cur.execute("CREATE TABLE IF NOT EXISTS note (id serial PRIMARY KEY, body text)")
cur.execute("INSERT INTO note (body) VALUES (%s) RETURNING id", ("hello",))
print(cur.fetchone()) # (1,)
cur.execute("SELECT count(*) FROM note WHERE body = %s", ("hello",))
print(cur.fetchone()[0]) # 1
# pg_hba.conf line: allow app from the app subnet with password auth
# host shop app 10.0.0.0/24 scram-sha-256Remember
- Connection URI: postgresql://user:pass@host:port/db?sslmode=...
- pg_hba.conf rules are matched top to bottom; reload after changes
- Always pass values as parameters, never by string formatting
Watch out for
- Building SQL with f-strings, which opens the door to SQL injection.