1

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             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
host    all             all             0.0.0.0/0               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        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident
4
  • 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()
    – user1822
    Nov 25, 2016 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, 2016 at 14:18
  • 'host all your_user x.x.x.x/x md5'
    – McNets
    Nov 25, 2016 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. Nov 25, 2016 at 14:34

1 Answer 1

2

The line

host    all             all             0.0.0.0/0               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 and acknowledge that you have read and understand our privacy policy and code of conduct.

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