Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

I forgot the password I entered during postgres installation

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.

like image 674
hilarl Avatar asked Jun 01 '12 07:06

hilarl


People also ask

Where is postgres password stored?

PostgreSQL database passwords are separate from operating system user passwords. The password for each database user is stored in the pg_authid system catalog. Passwords can be managed with the SQL commands CREATE ROLE and ALTER ROLE, e.g., CREATE ROLE foo WITH LOGIN PASSWORD 'secret' , or the psql command \password .

What is the default password for postgres?

For most systems, the default Postgres user is postgres and a password is not required for authentication. Thus, to add a password, we must first login and connect as the postgres user.

What is default password for postgres Windows?

By Default, the user is 'postgres' and the password is the one which you enter while installing the database.


1 Answers

  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) , exepct replication section if you don't have any it has to be changed as follow ,no MD5 or Peer autehication 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

like image 123
Arsen7 Avatar answered Sep 20 '22 13:09

Arsen7