PostgreSQL use two files that configure who can access the database. The first one,
postgresql.confdefines 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
With the above
pg_hba.confthis 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.
Now we succeeded.