Connect to a PostgreSQL Database: psql, pg_hba.conf, and the Right Connection String

Q: What is the default port for PostgreSQL?

5432. The right answer is to use the default unless you have a specific reason to change it. The wrong answer is to assume a non-standard port.

Q: What is the default user?

`postgres`. The right answer for the first connection is to connect as the postgres superuser, then create your application user. The wrong answer is to do all work as the postgres superuser.

Q: What is the right auth method?

`scram-sha-256` for new installs. The right answer is to upgrade from `md5` to `scram-sha-256` on existing installs with `ALTER USER ... PASSWORD ...`.

Q: Can I connect over SSL?

Yes. The right answer is to set `ssl = on` in `postgresql.conf` and configure the SSL certificates. The right answer is to also set `sslmode=require` or `sslmode=verify-full` on the client.

Q: How do I see active connections?

`SELECT * FROM pg_stat_activity;`. The right answer is to filter by `state = 'active'` to see only active queries.

Connect to a PostgreSQL database with psql -h host -p port -U user -d database. The right connection string format is postgresql://user:password@host:port/database. The right auth method is configured in pg_hba.conf. The team that has the right connection string, the right auth method, and the right firewall rules has a working connection in under five minutes.

The psql command line
The connection string format
The .pgpass file
pg_hba.conf authentication
listen_addresses for remote connections
Common errors and fixes
FAQ

The psql command line

The right command to connect to a local PostgreSQL:

psql -U postgres -d postgres

The right command to connect to a remote PostgreSQL:

psql -h db.example.com -p 5432 -U alice -d mydb

The right answer is to use the full set of flags for a remote connection. The wrong answer is to assume psql will prompt for the missing parameters — psql uses defaults, and the defaults may not match your server.

The connection string format

The right connection string format is the URI scheme:

postgresql://alice:[email protected]:5432/mydb

The right answer is to use this format for tools that accept it (ORMs, migration tools, application config). The wrong answer is to special-case the URI per tool — the URI is the standard.

The .pgpass file

The right way to avoid typing the password every time is .pgpass in the home directory:

# hostname:port:database:username:password
db.example.com:5432:mydb:alice:secret

The file mode must be chmod 600 ~/.pgpass — psql refuses to read it otherwise.

pg_hba.conf authentication

The right way to control which users can connect from which hosts is pg_hba.conf. The file has lines of the form:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    all             all             10.0.0.0/8              scram-sha-256

The right answer for production is scram-sha-256 (the modern, secure method). The wrong answer is trust (no auth) or md5 (deprecated). After editing, reload PostgreSQL:

sudo systemctl reload postgresql

listen_addresses for remote connections

By default, PostgreSQL listens only on localhost. The right way to enable remote connections is to set listen_addresses in postgresql.conf:

listen_addresses = '*'

Or to a specific IP:

listen_addresses = '10.0.0.5'

The right answer is to also open the firewall on port 5432. The wrong answer is to leave listen_addresses at the default and try to connect remotely — the connection will be refused.

Common errors and fixes

The most common error is connection to server ... timeout. The right answer is to check the firewall, the listen_addresses setting, and the security group on a cloud provider.

The second most common error is password authentication failed for user. The right answer is to check the password, the pg_hba.conf method, and the user exists.

The third most common error is database does not exist. The right answer is to check the database name and the user’s permission to access it.

FAQ

What is the default port for PostgreSQL?

The right answer is to use the default unless you have a specific reason to change it. The wrong answer is to assume a non-standard port.

What is the default user?

postgres. The right answer for the first connection is to connect as the postgres superuser, then create your application user. The wrong answer is to do all work as the postgres superuser.

What is the right auth method?

scram-sha-256 for new installs. The right answer is to upgrade from md5 to scram-sha-256 on existing installs with ALTER USER ... PASSWORD ....

Can I connect over SSL?

Yes. The right answer is to set ssl = on in postgresql.conf and configure the SSL certificates. The right answer is to also set sslmode=require or sslmode=verify-full on the client.

What is the connection limit?

The right answer is 100 by default. The right answer for a high-traffic server is to set max_connections higher. The right answer is also to use a connection pooler (pgBouncer) to keep the connection count manageable.

How do I see active connections?

SELECT * FROM pg_stat_activity;. The right answer is to filter by state = 'active' to see only active queries.

What is the difference between psql and pgAdmin?

psql is the command-line client. pgAdmin is the GUI. The right answer is to use psql for scripts and automation, pgAdmin for visual exploration.

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: