I am using psql to connect to a PostgreSQL database on Debian 10. I am trying to connect as the postgres user, to the default postgres database. By default, this is using the 'peer' authentication method, which does not require a password.
If I log in using the 'peer' authentication and set a password using the following command:
ALTER USER postgres WITH PASSWORD 'myPassword';
The query executes successfully, however when I edit pg_hba.conf to change the authentication method from:
local all postgres peer
to:
local all postgres scram-sha-256
and restart the server, I get the following error:
~$ sudo -u postgres psql postgres
Password for user postgres:
psql: FATAL: password authentication failed for user "postgres"
~$
Does anyone know how to do this?
To change the authentication method in PostgreSQL:
Open a terminal window
Change into the postgres bin directory
Example: cd /usr/local/pgsql/bin
Note: Depending on your install environment the path to the bin directory may vary.
Type su – postgres
and press Enter. This will change the logged in to the postgres
user.
From the bin directory type ./psql
Type:
ALTER USER your_username password 'new_password';
and press Enter. ALTER ROLE
should be displayed.
Type \q
and press Enter
Open /path_to_data_directory/pg_hba.conf
Example: /etc/postgresql/11/main/pg_hba.conf
Note: You will probably only have to change the word trust
to md5
. The line or lines should already exist.
host all postgres peer
host all your_username your.ip your.subnet md5
Save the changes
Restart PostgreSQL service with systemctl restart postgresql.service