4

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".

enter image description here

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.

Mustapha Aoussar
  • 5,833
  • 15
  • 62
  • 107

2 Answers2

6

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.

Augustus Kling
  • 3,303
  • 1
  • 22
  • 25
  • I tried to add IP `93.39.12.345` but doesn't work. I restarted Postgres and I get `psql: could not connect to server: Connection refused ..`. Probably I need to change my IP to `93.39.12.345/32` or `93.39.12.345/16` ? – Mustapha Aoussar Mar 14 '14 at 23:31
  • 1
    To quote PostgreSQL's excellent documentation: “Typical examples of an IP address range specified this way are 172.20.143.89/32 for a single host, or 172.20.143.0/24 for a small network, or 10.6.0.0/16 for a larger one. 0.0.0.0/0 represents all IPv4 addresses, and ::/0 represents all IPv6 addresses.”. It has an exhaustive example towards the page end of http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html – Augustus Kling Mar 14 '14 at 23:52
  • How do u slove this ?? @Fuiba – Mahesh K May 07 '17 at 03:59
1

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 =)

Community
  • 1
  • 1
ArthurChamz
  • 2,039
  • 14
  • 25