How to fix Server rejected establishment of SQL connection in PostgreSQL

Check if PostgreSQL is listening on the expected port:

lsof -i :5432
# or
ss -tlnp | grep 5432

If nothing listens on port 5432, start the PostgreSQL service:

sudo systemctl start postgresql
# or on macOS
brew services start postgresql

Ensure the server is accepting TCP/IP connections by checking postgresql.conf:

Test the connection with known-good credentials. If you are unsure about the password, reset it:

sudo -u postgres psql
# Inside psql:
ALTER USER postgres WITH PASSWORD 'newpassword';
\q

Then try connecting with the new password:

psql -U postgres -h localhost -d postgres -W
# Enter password when prompted

If the user does not exist, create it:

psql -U postgres -c "CREATE USER myuser WITH PASSWORD 'mypassword';"

Locate pg_hba.conf (usually in /etc/postgresql/VERSION/main/ on Linux or /usr/local/var/postgres on macOS):

sudo -u postgres psql -c "SHOW hba_file;"

Open the file and verify it contains an entry for your client IP. For local connections, you typically need:

local   all             all                                     trust
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

For remote connections, add a line like:

host    mydatabase      myuser          192.168.1.100/32        md5

After editing, reload the configuration:

sudo systemctl reload postgresql

The authentication method in pg_hba.conf must match how you are connecting.

For password authentication (most common), use md5 or scram-sha-256:

host    mydb            myuser          192.168.1.0/24          scram-sha-256

For local connections using Unix sockets, use peer (requires matching OS user):

local   all             myuser                                  peer

For SSL/TLS encrypted connections, ensure your pg_hba.conf entry starts with hostssl instead of host:

hostssl    all             all             0.0.0.0/0              scram-sha-256

After changes, reload:

sudo systemctl reload postgresql

Verify that your connection string uses the correct host, port, and database name:

psql -U myuser -h myhost.com -p 5432 -d mydatabase

For remote connections, ensure the firewall allows traffic on port 5432:

# On the PostgreSQL server
sudo ufw allow 5432/tcp
# or
sudo firewall-cmd --add-port=5432/tcp --permanent
sudo firewall-cmd --reload

Test connectivity from the client:

telnet myhost.com 5432
# or
nc -zv myhost.com 5432

The server logs often contain more specific information about why a connection was rejected. Check the log file (usually /var/log/postgresql/postgresql.log on Linux):

sudo tail -50 /var/log/postgresql/postgresql.log

Or use psql to view the last few messages:

sudo -u postgres psql -c "SELECT * FROM pg_read_file('log/logfile', 0, 100000);"

Look for messages like "no pg_hba.conf entry" or "authentication method mismatch". These directly indicate the issue.