Debian Question posted in
The oficial documentation can be found here.

Change authentication method for postgres superuser – Debian

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?

Tags:

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 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".

    Login or Signup to reply.
  3. 0

    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)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search