Postgres: local is not local (or how to solve connection issues)

When setting up RHQ and doing some testing around an open bug report, I stumbled once more over the PostgreSQL access configuration. Usually I just configure it and it works, but this time it just did not do what I wanted it to do ... It is not that it is not documented, but as I have seen others fighting here too, I will comment a little.

PostgreSQL use two files that configure who can access the database. The first one, postgresql.conf defines the network interfaces, PostgreSQL is listening on:

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all

If this line is commented, as shown it will only listen on TCP sockets on localhost (127.0.0.1) for
IPv4 and IPv6(!) and additionally on a unix domain socket.
If you want to give access to people from another computer, you need to uncomment the listen_address entry and list the respective network interfaces to listen on. Then restart the PostgreSQL server.

The second file is pg_hba.conf. It contains the specific about which user may access which database with which way of connecting and authentication:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
 
# "local" is for Unix domain socket connections only
#local   all         all                               trust
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
#host    all         all         ::1/128               trust

When you connect to the database via psql, you are by default using a unix domain socket connection - so the line starting with "local" applies.
If you try to connect via a JDBC driver, as RHQ and JBossON do, the connection will be done via TCP, which means the lines starting with "host" are applicable.

So even if you are successfully able to connect to Postgres via psql, it does not mean, that you can do so via other means or from remote hosts.

So how can you verify the connection setup without first starting your java app? Well, psql allows you to supply a hostname -- if this is there, it will use a TCP connection. Note that if you specify
-h localhost, the connection could go over TCPv6. To force the use of TCPv4 use -h 127.0.0.1.

With the above pg_hba.conf this could look like this:

snert$ psql -h localhost -Urhqadmin -d rhq 
psql: FATAL:  no pg_hba.conf entry for host "::1", user "rhqadmin", database "rhq", SSL off

This is ok, as the line for ::1/128 was commented out

snert$ psql -h 127.0.0.1 -Urhqadmin -d rhq 
Password for user rhq:
Welcome to psql 8.3.3, the PostgreSQL interactive terminal.
rqh=>

Now we succeeded.

---

Technorati Tags:
RHQ