Connecting to a PostgreSQL instance isn’t hard generally, but sometimes you can run into issues. Sometimes a port isn’t open on a firewall, or the server is in a VLAN that you can’t get to, or perhaps the server isn’t running on the network interface you think it is. More commonly, you can reach the PostgreSQL instance but you’re connection isn’t authorized (which is not the same as being unable to authenticate). Fortunately, the error messages returned in these different failure scenarios are fairly verbose and distinct so you can easily tell which scenario you’re facing. Let’s dive into each scenario and see what the error looks like, shall we?

Scenario 1 - Bad password

Let’s first assume that everything is working and you can actually connect to the PostgreSQL instance, but you can’t authenticate. The error will look like this:

As you can see, the message makes it pretty clear that you were able to connect, but your credentials were wrong (you were authorized to connect, but failed to authenticate). Did you type the password incorrectly? Is there a ~/.pgpass file that is providing the password for you? Do you have $PGPASSWORD defined in your environment? Fix the password being passed to PostgreSQL, and you won’t have further issues in this scenario.

Scenario 2 - pg_hba.conf rejects you

For our second scenario, we’re going to assume that you can actually connect to the PostgreSQL instance, but there is an entry in pg_hba.conf denying you access. First, we’ll try connecting via a local Unix socket:

As you can see, it straight up tells you that you have been explicitly denied access. It may be rejecting connections via Unix sockets completely, or it may be rejecting connection as the specified user via Unix socket, or it may be rejecting connections to the specified database via Unix sock. You could determine which of these scenarios is true by trying a different user on the same database and then trying the same user on a different database. In any case, the problem is not networking or firewall related. The DBA needs to adjust pg_hba.conf to allow connections of this type, or you need to connect via a TCP port instead of a Unix socket. Discuss with your DBA.

Now, let’s try via a TCP port:

Again, you can see that it pretty plainly tells you that you have been explicitly denied. In this case though, it may be rejecting your IP address specifically, your entire network segment, your id, that database, or the fact that you didn’t make an SSL connection. You can whittle this down by trying a different user on the same db, trying the same user on a different db, or switching to an SSL connection and repeating these tests. (I assume that you can’t change your IP address. But perhaps you could make the same test cases from another computer). Again, you’ll probably need the DBA to resolve this with you.

Scenario 3 - pg_hba.conf doesn’t allow you

I know what you’re thinking. “Isn’t this the same as above?”. And the answer is “no it is not”. Above, the pg_hba.conf file had an entry that matched your incoming connection and said to explicitly reject it. In this scenario, there is no entry that matches your connection and you end up implicitly denied.

Again, we’ll start by using a Unix socket:

Once more, the message pretty clearly tells you what is wrong. As it says, there is no entry in pg_hba.conf that matches your incoming connection. And since PostgreSQL tries to err on the side of caution, when it can’t find an entry stating definitively what to do, it rejects you. The same troubleshooting steps as above apply (change the user/db, etc). And also like above, the DBA is going to need to edit pg_hba.conf to add an entry for your connection.

Now, what does it look like over a TCP port:

It’s the same error message as above but showing your IP address instead of [local]. And the same debugging applies. Once again, the DBA will need to add an entry to pg_hba.conf to resolve this.

Scenario 4 - everything else (no, really)

By default, PostgreSQL listens for connections on port 5432. Sometimes, your DBA (or your vendor) has chosen to run PostgreSQL on a different port for some reason. If you are not trying to connect to the correct port, you’ll get an error from psql.

If you were trying to connect via a Unix socket, you’ll see:

If you were trying a TCP port connection, you’d see:

This error indicates that it cannot establish a network connection with PostgreSQL at the IP address (or UNix socket) you specified on the default port. Check to make sure you don’t have $PGPORT set incorrectly in your environment.

If you are using the correct port, and you still see one of two errors above, then the issue will be one of the following:

• PostgreSQL isn’t running
• PostgreSQL is running, but on a different IP address than you’re trying to connect to
• PostgreSQL is running, but you cannot establish a network connection from here to there

If PostgreSQL isn’t running, talk to your DBA about why (maybe it died, maybe it’s a maintenance window). If you are affected by the 2^nd bullet item, you’ll have to talk with your DBA about the proper IP address to use when connecting. Note that, by default, PostgreSQL only listens on localhost and none of your other interfaces. If this hasn’t been changed (listen_addresses in postgresql.conf) then you’ll fall into this failure category. And if you’re plagued by the 3^rd bullet item, you’ll have to talk to your network admin (and probably your DBA) as there may be a firewall blocking you, or your VLAN can’t connect to the other VLAN, or some other layer 3/4 tomfoolery.

So now you know all the (common) ways that connecting to PostgreSQL can fail and how to distinguish between them. With just a little bit of knowledge, you can communicate exactly what is happening to your DBA and make it easier for him/her to rectify the issue. Go forth, and happy PostgreSQL-ing.