I forgot the password I entered during postgres 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 un-commented 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

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

  2. 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)

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

    ALTER USER my_user_name with password 'my_secure_password';

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

    cp pg_hba.conf-backup pg_hba.conf

  5. restart the server, in order to run with the safe pg_hba.conf

    sudo /etc/init.d/postgresql restart

Further Reading about that pg_hba file: http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html

More Related Contents:

  1. I forgot the password I entered during PostgreSQL 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. PostgreSQL LIKE query performance variations
  15. Calculating and saving space in PostgreSQL
  16. PostgreSQL naming conventions
  17. How to set value of composite variable field using dynamic SQL
  18. In-order sequence generation
  19. Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
  20. Postgres password authentication fails
  21. Copy a few of the columns of a csv file into a table
  22. ERROR: permission denied for relation tablename on Postgres while trying a SELECT as a readonly user
  23. Joining Results from Two Separate Databases
  24. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  25. Postgresql query array of objects in JSONB field
  26. PostgreSQL next value of the sequences?
  27. Multicolumn index on 3 fields with heterogenous data types
  28. Select from a table variable
  29. PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values
  30. input file appears to be a text format dump. Please use psql

Leave a Comment