I forgot the password I entered during PostgreSQL installation
forgot-password, postgresql, postgresql-9.1
Solution
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/`
Back it up
`cp pg_hba.conf pg_hba.conf-backup`
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
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`
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)
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';`
Restore the old pg_hba.conf file as it is very dangerous to keep around
`cp pg_hba.conf-backup pg_hba.conf`
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)
Problem
I either forgot or mistyped (during the installation) the password to the default user of PostgreSQL. I can't seem to be able to run it, and I get the following error: ``` psql: FATAL: password authentication failed for user "hisham" hisham-agil: hisham$ psql ``` Is there a way to reset the password or how do I create a new user with superuser privileges? I am new to PostgreSQL and just installed it for the first time. I am trying to use it with Ruby on Rails and I am running Mac OS X v10.7 (Lion).