Change authentication method for postgres superuser

user_14131782 0
Viewed 4026

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?

psql debian postgresql
3 Answers

To change the authentication method in PostgreSQL:

  1. Open a terminal window

  2. 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.

  1. Type su – postgres and press Enter. This will change the logged in to the postgres user.

  2. From the bin directory type ./psql

  3. Type: ALTER USER your_username password 'new_password'; and press Enter. ALTER ROLE should be displayed.

  4. Type \q and press Enter

  5. Open /path_to_data_directory/pg_hba.conf

Example: /etc/postgresql/11/main/pg_hba.conf

  1. Modify the line at the bottom of the config file to resemble one of these examples.

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

  1. Save the changes

  2. Restart PostgreSQL service with systemctl restart postgresql.service

user_130661060

Before you assign the password, you probably need to set the password_encryption to "scram-sha-256". Otherwise, you stored the password in the md5 format, and such a password cannot be used to login when pg_hba.conf calls for "scram-sha-256".

The default setting of password_encryption is still md5. It will change to be "scram-sha-256" in v14.

The error message sent to the unauthenticated user is intentionally vague. The error message in the server log file will probably say DETAIL: User "postgres" does not have a valid SCRAM secret. (If it does not, then ignore this answer, and edit your question to tell us what it does say)

user_17212390

You need to 1st in the shell change to be the "postgres" user which you're not doing correctly above:

sudo su - postgres

Then you can do the following as peer auth: psql -d postgres -U postgres

Also recommend you set a pw for postgres sql user:

\password postgres & change the authentication method to "md5", not "peer".

user_34956150
Related