How to reset password for PostgreSQL user "postgres"?

Created:

2016-11-16 13:09:33 UTC

Modified:

2017-04-24 11:14:29 UTC

2

Was this article helpful?


Have more questions?

Submit a request

How to reset password for PostgreSQL user "postgres"?

Applicable to:

  • Plesk 11.0 for Linux
  • Plesk 11.5 for Linux
  • Plesk 12.0 for Linux

Symptoms

  1. How to reset password for PostgreSQL user "postgres"?

  2. Authentication to PostgreSQL database not possible, I get the error:

    psql: FATAL: Ident authentication failed for user "username"

Cause

Password for PostgreSQL user "postgres" does not correspond password in Psa database.

Resolution

  1. Try to reset password for PostgreSQL admin user with command:

    # plesk bin database-server --update-server localhost:5432 -type postgresql -passwd 12345

    where 12345 is your new password.

  2. If it fails or authentication still does not work, modify pg_hba.conf file (for PostgreSQL 9.1 server - /etc/postgresql/9.1/main/pg_hba.conf). Add or modify these line as below:

    host all all 127.0.0.1/32 trust
    host all all ::1/128 trust
  3. Restart PostgreSQL server:

    /etc/init.d/postgresql restart
  4. Login to PostgreSQL server and reset the password:

    #  psql -U postgres -W  -h localhost
    Password for user postgres:
    psql (9.1.13)
    SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
    Type "help" for help.

    postgres=# ALTER USER postgres with password '011juillet2011';
  5. Revert changes in pg_hba.conf back.

  6. Restart PostgreSQL server again and make sure that you can connect to PostgreSQL server.
Have more questions? Submit a request
Please sign in to leave a comment.