pgAdmin III: Access to database denied

Question

I'm trying to connect to a remote database from pgAdmin III. I have create a "New Server Registration". When I connect to database I get "access to database denied".

I set up all correctly. These are my PostgreSQL settings:

• pg_hba.conf >

PostgreSQL Client Authentication Configuration File

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             postgres                                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                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
host    all             all             192.168.0.0/16          md5

• postgresql.conf > I allowed all incomming connection listen_addresses = '*'

Using SSH I can connect to databse:

[fuiba@test]$ psql -h localhost -p 26888 -d postgres
psql (9.1.11)
Type "help" for help.

postgres=# \l
                             List of databases
     Name     |  Owner  | Encoding | Collate | Ctype |  Access privileges  
--------------+---------+----------+---------+-------+---------------------
 postgres     |  fuiba  | UTF8     | C       | C     | 
 template0    |  fuiba  | UTF8     | C       | C     | =c/fuiba         +
              |         |          |         |       | fuiba=CTc/fuiba
 template1    |  fuiba  | UTF8     | C       | C     | =c/fuiba         +
              |         |          |         |       | fuiba=CTc/fuiba
(3 rows)

What am I doing wrong? Any help would be highly appreciated. Thank you!

ps: I'm running pgAdmin III on Windows 7 and PostgreSQL on Linux CentOS.

Answer 1

pgAdmin connects to PostgreSQL from another host than when you are logging in via SSH to the database server. The IP address mentioned in the error message (starting with 93.39) is not mentioned in your pg_hba.conf.

Either add the public IP address (the one starting with 93.39) of the host that runs pgAdmin to pg_hba.conf or connect via a SSH tunnel. Mind to reload PostgreSQL's configuration or restart PostgreSQL after modifying pg_hba.conf.

Answer 2

I once struggled with this, it worked by changing the IP mask for the entries in pg_hba.conf, but I can't quite remember, and besides that configuration is different for every network. The point is that you most likely have an error in one of those entries. Here, they even indicate that the error message is a hint to which of the entries is wrong. In the case they are indeed correct, I'd check the auth-method (see if my password is being passed as a MD5 hash, for example).

I hope this can help you =)