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?
For most systems, the default Postgres user is postgres and a password is not required for authentication. Thus, to add a password, we must first login and connect as the postgres user.
To make that entry work, do not specify a hostname or port for psql , then it will use a "local" connection through named pipes. Alternatively, you can local with host and then add 127.0. 0.1 as the (client) IP address to "trust".
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With