For PostgreSQL database, restrict remote connections to password

Question

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

• user1822: 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()
• McNets: I think you should change user=all by user=your_user in host all all x.x.x.x/x md5
• McNets: 'host all your_user x.x.x.x/x md5'
• Ashish Gamit: 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.

Answer 1

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.