Wednesday, June 18, 2008

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 ( 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:

# "local" is for Unix domain socket connections only
#local all all trust
local all all md5
# IPv4 local connections:
host all all 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

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 -Urhqadmin -d rhq
Password for user rhq:
Welcome to psql 8.3.3, the PostgreSQL interactive terminal.

Now we succeeded.

Technorati Tags:


Thomas Dignan said...

Thank you for the explanation. This saved me a lot of digging.

Catherine said...

Thank you so much! I love PostgreSQL but pg_hba.conf will be the death of me... being able to debug the IPv4 connections with psql helps tons.

zazuge mikufan said...

i forgot that psql connect with unix socket by default and wasted my time trying to fix the "host" line ....