I either forgot or mistyped (during the installation) the password to the default user of Postgres. 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 anyway to reset the password or how do I create a new user with superuser privileges?
I am new to Postgres and just installed it for the first time. I am trying to use it with Rails and I am running Mac OS X Lion.
Best Answer
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):
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:
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 calledpgsql
, 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 password ('replace my_user_name with postgres since you are resetting postgres user)
ALTER USER my_user_name with password 'my_secure_password';
Restore the old
pg_hba.conf
as it is very dangerous to keep aroundcp pg_hba.conf-backup pg_hba.conf
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