Postgresql server not asking for password for remote connections

Question

I found my posgresql database server is not asking password for user postgres when remote connecting through pgadmin. I mean this is when I connect to remote database server from my local computer through pgAdmin.

I did add a password in psql, ALTER USER postgres PASSWORD 'mypassword'.

This is my pg_hba.config file:

/usr/local/pgsql/bin/psql -qAt -c "show hba_file" | xargs grep -v -E '^[[:space:]]*#'

local   all             all                                     trust
host    all             all             127.0.0.1/32            md5

host    all             all               0.0.0.0/0             md5
host    all             all             ::1/128                 md5

So, I do not quite understand what is happening here.

Can anyone help with this?

Thanks a lot.

UPDATE:

If i change:

local   all             all                                     trust

to

local   all             all                                     md5

Now, local connections (via SSH) will be asked for password ( wasn't asking for password before.) but remote connections will still connect without a password.

Acutally, I tried connecting to this database server by a rails appliaction from another server, without a password, and the rails server started without a problem.

---

PUTTING RESULT HERE FOR THE CONVENIENCE

The real reason of this issue was the .pgpass file. Mac stored the password locally in the .pgpass file under user home folder. Then every time when user try to login without a password, PostgreSQL will send the password for user.

Official doc here

Answer 1

Reading the documentation at Postgresql.org

https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

I would suggest that you change the user field with the names of the few users allowed to connect remotely:

host    all             john,charles    0.0.0.0/0   scram-sha-256
host    all             john,charles    ::1/128     scram-sha-256

Further, for security reasons, I would advice that you look into using hostssl and also that you specify the name of the database(s) that can be accessed remotely:

hostsll webapp123     john,charles    0.0.0.0/0     scram-sha-256

And if the remote access is only from specific computers, specify their static IP addresses (if DHCP is used, use a mask accordingly.)

hostsll webapp123     john,charles    1.2.3.4/32    scram-sha-256

This way you only compromise database webapp123, to what users john and charles can do, and only from computer 1.2.3.4.

As mentioned in the documentation, you can have any number of entries, so if you want to add a test server (i.e. your server at home) then you can add one line so it looks like this:

hostsll webapp123     john,charles    1.2.3.4/32    scram-sha-256
hostsll webapp123     henry           home-ip/32    scram-sha-256

By not specifying the users, you probably allow any user, including those without passwords and one of them is selected and it works...

Of course, I would strongly advice that you do not name a user who has administration rights in your database unless you also specify his static IP address.