I forgot the password I entered during postgres in

Question

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.

Answer 1

This is what worked for me on windows:

Edit the pg_hba.conf file locates at C:\Program Files\PostgreSQL\9.3\data.

# IPv4 local connections: host all all 127.0.0.1/32 trust

Change the method from trust to md5 and restart the postgres service on windows.

After that, you can login using postgres user without password by using pgadmin. You can change password using File->Change password.

If postgres user does not have superuser privileges , then you cannot change the password. In this case , login with another user(pgsql)with superuser access and provide privileges to other users by right clicking on users and selecting properties->Role privileges.

Answer 2

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):

   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 password

   ALTER USER my_user_name with password 'my_secure_password';

7. Restore the old pg_hba.conf 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

   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

Answer 3

1. Edit the file /etc/postgresql/<version>/main/pg_hba.conf and find the following line:

   local   all             postgres                                md5
   

2. Edit the line and change md5 at the end to trust and save the file

3. Reload the postgresql service

   $ sudo service postgresql reload
   

4. This will load the configuration files. Now you can modify the postgres user by logging into the psql shell

   $ psql -U postgres
   

5. Update the postgres user's password

   alter user postgres with password 'secure-passwd-here';
   

6. Edit the file /etc/postgresql/<version>/main/pg_hba.conf and change trust back to md5 and save the file

7. Reload the postgresql service

   $ sudo service postgresql reload
   

8. Verify that the password change is working

   $ psql -U postgres -W
   

Answer 4

Just a note, on Linux You can simply run sudo su - postgres to become the postgres user and from there change what required using psql.