I have recently set-up a PostgreSQL database on client's CentOs machine. Until now the remote access was restricted.

Now I have tried to open the remote connections for us, with following entry in the pg_hba.conf.

host    all             all             x.x.x.x/x      trust

The connection is fine and we are able to connect the db just with providing the user name. Password in not provided while connecting.

Now we need to restrict the access to valid credentials only.

For this I have changed the entry to

host    all             all             x.x.x.x/x      md5

And the postgresql service is restarted (I have restarted multiple times). Still if we try to connect without a password or with a wrong password, the database is connected.

I think I am doing something wrong with the pg_hba.conf. Any pointers to what is going wrong?

Following is the content of the file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all               ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
host    all             all                  trust
# IPv4 remote connections:
host    all             all             x.x.x.x/x      md5
host    all             all             x.x.x.x/x          md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres            ident
#host    replication     postgres        ::1/128                 ident
  • Are you testing this from a different computer or from the computer where Postgres is running? Do you maybe have a .psqlrc file with the password on the client? Btw: you don't need to restart Postgres to apply changes to pg_hba.conf, a reload of the configuration is enough (e.g. by using select pg_reload_conf() – a_horse_with_no_name Nov 25 '16 at 14:08
  • I think you should change user=all by user=your_user in host all all x.x.x.x/x md5 – McNets Nov 25 '16 at 14:18
  • 'host all your_user x.x.x.x/x md5' – McNets Nov 25 '16 at 14:19
  • I made following change host all postgres x.x.x.x/x md5 where postgres is the user name. Still the connection is possible without providing a password or with a wrong password. On the server I checked with psql prompt to login with this user. There it does not allow to login without a correct password. – Ashish Gamit Nov 25 '16 at 14:34

The line

host    all             all                  trust

Lets anyone in without a password (other than connections over the unix file socket, connections over the loopback device, and replication users). Since it occurs earlier in the file, it takes priority over the x.x.x.x/x line.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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