Change authentication method for postgres superuser

Question

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?

leo95batista · Accepted Answer · 2021-12-21 14:54:00Z

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

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

Save the changes
Restart PostgreSQL service with systemctl restart postgresql.service

jjanes · Accepted Answer · 2021-02-24 22:19:09Z

3

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)

answered Feb 24, 2021 at 22:19

jjanes

45k5 gold badges39 silver badges48 bronze badges

2 Comments

leole Over a year ago

Thanks for adding this error message (User "postgres" does not have a valid SCRAM secret). This helped the search engines to guide me here :-)

Pyrocks Apr 2 at 9:17

This was my case on a cloud-managed DB - I have upgraded the cluster from v13 to v16. After the upgrade I received the "does not have a valid SCRAM secret" error when using a dblink/fdw to itself - I have reset the user's password to the exact same password - and now it was saved with the "scram-sha-256" encryption and the error is gone

cachedrive · Accepted Answer · 2021-02-24 17:43:53Z

0

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

answered Feb 24, 2021 at 17:43

cachedrive

531 silver badge6 bronze badges

Comments

demet47 · Accepted Answer · 2024-12-09 08:16:46Z

I had another superuser named "demole". I first modified pg_hba.conf file. It's location may differ in your system. I coincided with two variations in two different systems:

/var/lib/pgsql/14/data/pg_hba.conf
/etc/postgresql/14/main/pg_hba.conf

A brief realisation here: The ordering of lines I setup authentication method is significant. An example content here is as below, setting auth method for postgres to md5 being an obligation for the solution:

local all postgres md5
local all demole peer
local all all md5

I then logged in via the superuser demole and set a password for postgres user:

psql -U demole -d database_name
ALTER USER postgres password 'some_password'

Now, it doesn't revert to the behaviour where it didn't require password, nor does it enforce me to set authentication method of postgres to trust, which also solves the problem but I think has security worsening.

Collectives™ on Stack Overflow

Change authentication method for postgres superuser

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related