Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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
share|improve this answer
    
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! –  Sean Aug 15 '13 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? –  Sean Aug 15 '13 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. –  Daniel Vérité Aug 15 '13 at 12:56
add comment

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.

share|improve this answer
    
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? –  Sean Aug 14 '13 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 '13 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..." –  Sean Aug 14 '13 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 –  Sean Aug 14 '13 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 '13 at 20:48
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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