How to Force Postgresql User Login with Password

Question

All I did is in my local machine(Mac OS).

After installing the postgresql, I created a user named poet with password, then created a database named poems and the database's owner is poet.

What I am curious is that I can log into poems with poet without password.

The command is psql -U poet -d poems. Then what's the usage of the password?

I know that I can add -W to get the password prompt psql -U poet -d poems -W, but I can still log in even with a wrong password!

Then I modify the config file(/usr/local/var/postgres/pg_hba.conf) like below:

# "local" is for Unix domain socket connections only
#local   all             all                                     trust
local   all             all                                     password
# IPv4 local connections:
#host    all             all             127.0.0.1/32            trust
host    all             all             127.0.0.1/32            password
# IPv6 local connections:
#host    all             all             ::1/128                 trust
host    all             all             ::1/128                 password

And restart the server, I still don't need to provide the password to log into the database.

Can someone tell how to force the server to verify the correctness of login password?

Solution

This was happening to me as well. Non privileged accounts were able to login with invalid passwords. The steps I took to resolve it were.

Find the right pg_hba.conf file.

$ psql -h 127.0.0.1 -U admin -W postgres
Password for user admin: 
psql (9.6.5)
Type "help" for help.

openproject_dev=# SHOW hba_file ;
              hba_file               
-------------------------------------
 /usr/local/var/postgres/pg_hba.conf
(1 row)

Edit the file to force password login ... Leave admin (superuser) as trust. I used md5 instead of password .. password worked as well.

# "local" is for Unix domain socket connections only
local   all             admin                                   trust
local   all             all                                     md5
# IPv4 local connections:
host    all             admin           127.0.0.1/32            trust
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             admin           ::1/128                 trust
host    all             all             ::1/128                 md5

Save pg_hba.conf file and restart the postgres server.

Comments

[TheRealChx101]

Is it possible to script all of this? This is insane. I'd like to be able to create a user with a password automatically and not have to worry about editing the damn file. :(.

[Keith John Hutchison]

It's an edge case. I've only had to do this once with all the postgres installs I've done and yes ... it could be scripted.

[TheRealChx101]

Yes. I was able to script it using HBA_FILE=$(cd /tmp && sudo -u postgres psql -qAt -F '|' -c "SELECT current_setting('hba_file')") and then sudo sed -i "s/host.*all.*all.*127.0.0.1\/32.*ident/host all all 127.0.0.1\/32 md5/" ${HBA_FILE} sudo sed -i "s/host.*all.*all.*::1\/128.*ident/host all all ::1\/128 md5/" ${HBA_FILE}

[Keith John Hutchison]

Impressive :-) Cheers!

[Kevin G]

This did not solve my issue. I can run psql -U <username> <dbname> without a password and still log in

[Keith John Hutchison]

Did you reboot the server after changing the pg_hba.conf file?