postgresqldebianpsql

Change authentication method for postgres superuser


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?


Solution

  • 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