I forgot the password I entered during PostgreSQL installation

by

  1. Find the file pg_hba.conf. It may be located, for example, in /etc/postgresql-9.1/pg_hba.conf.

    cd /etc/postgresql-9.1/

  2. Back it up

    cp pg_hba.conf pg_hba.conf-backup

  3. Place the following line (as either the first uncommented line, or as the only one):

    For all occurrence of below (local and host) , except replication
    section if you don’t have any it has to be changed as follow ,no MD5
    or Peer authentication should be present.

    local all all trust

  4. Restart your PostgreSQL server (e.g., on Linux:)

    sudo /etc/init.d/postgresql restart

    If the service (daemon) doesn’t start reporting in log file:

    local connections are not supported by this build

    you should change

    local all all trust

    to

    host all all 127.0.0.1/32 trust

  5. You can now connect as any user. Connect as the superuser postgres (note, the superuser name may be different in your installation. In some systems it is called pgsql, for example.)

    psql -U postgres

    or

    psql -h 127.0.0.1 -U postgres

    (note that with the first command you will not always be connected with local host)

  6. Reset the password (‘replace my_user_name with postgres since you are resetting the postgres user)

    ALTER USER my_user_name with password 'my_secure_password';

  7. Restore the old pg_hba.conf file as it is very dangerous to keep around

    cp pg_hba.conf-backup pg_hba.conf

  8. Restart the server, in order to run with the safe pg_hba.conf file

    sudo /etc/init.d/postgresql restart

Further reading about that pg_hba file: 19.1. The pg_hba.conf File (official documentation)

More Related Contents:

  1. I forgot the password I entered during postgres installation
  2. How to exit from PostgreSQL command line utility: psql
  3. Generating time series between two dates in PostgreSQL
  4. Generate series of dates – using date type as input
  5. Test for null in function with varying parameters
  6. Postgres query optimization (forcing an index scan)
  7. Dynamically generate columns for crosstab in PostgreSQL
  8. PostgreSQL ERROR: canceling statement due to conflict with recovery
  9. Change type of varchar field to integer: “cannot be cast automatically to type integer”
  10. Postgresql insert trigger to set value
  11. How to add column if not exists on PostgreSQL?
  12. now() default values are all showing same timestamp
  13. Convert escaped Unicode character back to actual character in PostgreSQL
  14. invalid byte sequence for encoding “UTF8”
  15. How to make “case-insensitive” query in Postgresql?
  16. Cannot create a database table named ‘user’ in PostgreSQL
  17. Postgresql Truncation speed
  18. Buffers (Circle) in PostGIS
  19. Heroku “psql: FATAL: remaining connection slots are reserved for non-replication superuser connections”
  20. Changing primary key int type to serial
  21. Return SETOF rows from PostgreSQL function
  22. Switch role after connecting to database
  23. SQL Sub queries in check constraint
  24. When do Postgres column or table names need quotes and when don’t they?
  25. How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?
  26. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  27. PostgreSQL how to concat interval value ‘2 days’
  28. Displaying image in Ireports using PostgreSql
  29. What’s the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)
  30. JPA and PostgreSQL with GenerationType.IDENTITY

Leave a Comment