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 thepg_hba.conf
file if a pattern that matches your client address / username / database is present and enabled before anySQL 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 likehost 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 thepg_hba.conf
editor that is built into pgAdmin III to edit thepg_hba.conf
configuration file.After changing
pg_hba.conf
, you need to trigger a server configuration reload usingpg_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?
Best Answer
This line:
will let through connections from IPs matching
192.168.0.X
whereX
is any byte.The IP address of your error message being
192.168.108.161
, it does not match this pattern because108
is not0
.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:
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.