7

I'm trying to connect to an external database from pgAdmin III (which is installed on both machines). The client complains:

Access to database denied

The server doesn't grant access to the database: the server reports

FATAL: no pg_hba.conf entry for host "192.168.108.161",
                                user "postgres",
                            database "postgres",
                                 SSL off 

To access a database on a PostgreSQL server, you first have to grant primary access to the server for your client (Host Based Authentication). PostgreSQL will check the pg_hba.conf file if a pattern that matches your client address / username / database is present and enabled before any SQL GRANT access control lists are evaluated.

The initial settings in pg_hba.conf are quite restrictive, in order to avoid unwanted security holes caused by unreviewed but mandatory system settings. You'll probably want to add something like

host all all 192.168.0.0/24 md5 

This example grants MD5 encrypted password access to all databases to all users on the private network 192.168.0.0/24. You can use the pg_hba.conf editor that is built into pgAdmin III to edit the pg_hba.conf configuration file.

After changing pg_hba.conf, you need to trigger a server configuration reload using pg_ctl or by stopping and restarting the server process.

Where the server explicitly states that all connections from the internal network are accepted.

pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    all             all             192.168.0.0/24          md5

I have already restarted the postmaster for the changes to take effect, and have gone so far as to restart the entire machine. What else could be going wrong here?

2 Answers 2

7

This line:

 host    all             all             192.168.0.0/24          md5

will let through connections from IPs matching 192.168.0.X where X is any byte.

The IP address of your error message being 192.168.108.161, it does not match this pattern because 108 is not 0.

To enable addresses like 192.168.X.Y, you'd need a /16 instead of /24 meaning that the first 16 bits only are fixed.

Like this:

 host    all             all             192.168.0.0/16          md5

Don't forget to reload Postgresql. From the official docs, use pg_ctl reload. If that doesn't work, then there are other ways to do it listed in this question.

3
  • I will try this as soon as I get in today! I didn't know the / was how many bits were fixed --- this makes a lot more sense now! Aug 15, 2013 at 12:08
  • This was the problem, thanks for pointing it out! Now, if I wanted to specify an exact IP address, would I use 192.168.108.161/32 or simply 192.168.108.161? Aug 15, 2013 at 12:42
  • 1
    This should be 192.168.108.161/32. Also take the time to read the beginning of the pg_hba.conf file about the ADDRESS column, it's all explained. Aug 15, 2013 at 12:56
2

Two things come to mind.

  1. The first is to make sure the server is listening on the network interface. By default it only listens on local host. Edit your postgresql.conf file and change:

    listen_addresses = 'localhost'
    

    To look like this instead:

    listen_addresses = '*'
    
  2. If you're connecting remotely then your postgres user needs a password. By default there is no password associated with that account so only local trust based login works. For remote password login (eg. md5 in pg_hba.conf`) to work either add a password or create a new user with a password.

Also, instead of using host in pg_hba.conf consider using hostssl to require that remote connections use SSL. Otherwise the username/password credentials are sent over the wire in plain text.

6
  • 1) I forgot to mention that I had edited listen_addresses thusly, but it will be helpful for future users to see that too. 2) When on the server, I can open up pgAdmin and then connect to the database. It prompts me for a password, I put one in (our company has a standard password that we use for test stuff), and it connects. Wouldn't it fail similarly if the postgres user had no password? Aug 14, 2013 at 19:57
  • Try connecting from the command line via psql and see what error it reports. It should be verbose enough to figure out the cause. If it says FATAL: password authentication failed for user then it's probably a mismatched password. If it says could not connect to server: Connection refused then it's probably a network/port issue. Is the CIDR you've specified of 192.168.0.0/24 correct? Should that be 192.168.1.0/24 (1 instead of 0 for the third byte)?
    – sehrope
    Aug 14, 2013 at 20:14
  • psql --host=192.168.108.160 --port=5433 --user=postgres postgres fails with the same error (and no ability to make it more verbose): "FATAL: no pg_hba.conf entry for host..." Aug 14, 2013 at 20:35
  • I just checked the server, and it actually says something very interesting (considering this problem doesn't show up using pgAdmin): i.stack.imgur.com/g9aLK.png Aug 14, 2013 at 20:38
  • Well one more point for psql then. How did pgAdmin connect locally then? I'm guessing you must have been connecting to a different database. Otherwise I can't see how a local connection would work either if the server won't startup.
    – sehrope
    Aug 14, 2013 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.