I forgot the password I entered during postgres installation

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

User · Answer

FOR WINDOWS   what has helped me  This is the link I am referring to  https   qsartoolbox org content documents ResettingPostgreSQLPassword pdf  Open your cmd and go to C  Program Files PostgreSQL 12 data This is usually the right path  You might have it stored somewhere else  Note that  if you have a different postgresql version  there will be a different number  That doesn t matter   Find a pg hba conf file and copy it to somewhere else  That way you will have an unmodified version of this file  so you will be able to look at it after we make some changes   Open pg hba conf file  not the backup  but the original   Find the multiple lines that start with host near the bottom of the file  host    all             all             127 0 0 1 32            md5 host    all             all               1 128                 md5 host    replication     all             127 0 0 1 32            md5 host    replication     all               1 128                 md5  Replace md5 with trust  host    all             all             127 0 0 1 32            trust host    all             all               1 128                 trust host    replication     all             127 0 0 1 32            trust host    replication     all               1 128                 trust  Close this file  Go to your search bar on windows and open Services app  Find postgres and restart it  picture of services app  Write cd   in cmd and then cd bin  Your path should be C  Program Files PostgreSQL 12 bin  Enter  psql -U postgres -h localhost  Enter  ALTER USER postgres with password   lt your new password gt   Make sure that you include   at the end    ALTER ROLE    should be displayed as an indication that the previous line was executed successfully  Open original pg hba conf file and change back from trust to md5  Restart the server with Services app as before

User · Answer

When connecting to postgres from command line  don t forget to add -h localhost as command line parameter  If not  postgres will try to connect using PEER authentication mode   The below shows a reset of the password  a failed login with PEER authentication and a successful login using a TCP connection     sudo -u postgres psql could not change directory to   root  psql  9 1 11  Type  help  for help   postgres    password Enter new password  Enter it again  postgres    q   Failing     psql -U postgres -W Password for user postgres  psql  FATAL   Peer authentication failed for user  postgres    Working with -h localhost     psql -U postgres -W  -h localhost Password for user postgres  psql  9 1 11  SSL connection  cipher  DHE-RSA-AES256-SHA  bits  256  Type  help  for help   postgres

User · Answer

If you are running postgresql on mac os  try these    Edit the pg hba conf file sudo vi  Library PostgreSQL 9 2 data pg hba conf Change the  md5  method for all users to  trust  near the bottom of the file Find the name of the service ls  Library LaunchDaemons Look for postgresql Stop the postgresql service sudo launchctl stop com edb launchd postgresql-9 2 Start the postgresql service sudo launchctl start com edb launchd postgresql-9 2 Start psql session as postgres psql -U postgres  shouldn t ask for password because of  trust  setting  Reset password in psql session by typing ALTER USER postgres with password  secure-new-password    q enter Edit the pg hba conf file Switch it back to  md5  Restart services again

User · Answer

The file  pgpass in a user s home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password  and no password has been specified otherwise   On Microsoft Windows the file is named  APPDATA  postgresql pgpass conf  where  APPDATA  refers to the Application Data subdirectory in the user s profile    This file should contain lines of the following format   hostname port database username password   You can add a reminder comment to the file by copying the line above and preceding it with     Each of the first four fields can be a literal value  or    which matches anything  The password field from the first line that matches the current connection parameters will be used   Therefore  put more-specific entries first when you are using wildcards   If an entry needs to contain   or    escape this character with   A host name of localhost matches both TCP  host name localhost  and Unix domain socket  pghost empty or the default socket directory  connections coming from the local machine  In a standby server  a database name of replication matches streaming replication connections made to the master server  The database field is of limited usefulness because users have the same password for all databases in the same cluster   On Unix systems  the permissions on  pgpass must disallow any access to world or group  achieve this by the command chmod 0600    pgpass  If the permissions are less strict than this  the file will be ignored  On Microsoft Windows  it is assumed that the file is stored in a directory that is secure  so no special permissions check is made

User · Answer

For Windows installation  a Windows user is created  And  psql  use this user for connection to the port  If you change the PostgreSQL user s password  it won t change the Windows one  The commandline juste below works only if you have access to commandline   Instead you could use Windows GUI application  c  Windows system32 lusrmgr exe   This app manage users created by Windows  So you can now modify the password

User · Answer

I was just having this problem on Windows 10 and the issue in my case was that I was just running psql and it was defaulting to trying to log in with my Windows username   Nathan    but there was no PostgreSQL user with that name  and it wasn t telling me that   So the solution was to run psql -U postgres rather than just psql  and then the password I entered at installation worked

User · Answer

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

User · Answer

Adding the answer for Windows User for the latest postgres version   10    Go to your postgres installation location  and search for pg hba conf  you will find it in    postgres data pg hba conf  Open that file with notepad  find this line      TYPE  DATABASE        USER            ADDRESS                 METHOD    IPv4 local connections   host    all             all             127 0 0 1 32            md5   IPv6 local connections  host    all             all               1 128                 md5       Change the method from md5 to trust     TYPE  DATABASE        USER            ADDRESS                 METHOD    IPv4 local connections   host    all             all             127 0 0 1 32            trust   IPv6 local connections  host    all             all               1 128                 trust         Now go to your SQL Shell PSQL  and leave everything blank   Server  localhost   Database  postgres   Port  8000   Username  postgres      It will not ask for password this time  and you will be logged in   Now run this line         ALTER USER yourusername WITH SUPERUSER  Now you can leave the shell with  q  Again go to the file pg hba conf and change METHOD from trust to md5 again  and save it   Now login with your new user and password and you can check  du for its attributes

User · Answer

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

User · Answer

Edit the file  etc postgresql  lt version gt  main pg hba conf and find the following line   local   all             postgres                                md5  Edit the line and change md5 at the end to trust and save the file Reload the postgresql service     sudo service postgresql reload  This will load the configuration files  Now you can modify the postgres user by logging into the psql shell     psql -U postgres  Update the postgres user s password  alter user postgres with password  secure-passwd-here    Edit the file  etc postgresql  lt version gt  main pg hba conf and change trust back to md5 and save the file Reload the postgresql service     sudo service postgresql reload  Verify that the password change is working    psql -U postgres -W

User · Answer

The pg hba conf  C  Program Files PostgreSQL 9 3 data  file has changed since these answers were given  What worked for me  in Windows  is to open the file and change the METHOD from md5 to trust      TYPE  DATABASE        USER            ADDRESS                 METHOD    IPv4 local connections  host    all             all             127 0 0 1 32            trust   IPv6 local connections  host    all             all               1 128                 trust   Then  using pgAdmin III  I logged in using no password and changed user postgres  password by going to File - gt  Change Password

User · 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        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    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 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 around  cp 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

User · Answer

Add below line to your pg hba conf file  which will be present in installation directory of postgres hostnossl    all          all            0 0 0 0 0  trust     It will start working

User · Answer

What I did to resolve the same problem was   Open pg hba conf file with gedit editor from the terminal   sudo gedit  etc postgresql 9 5 main pg hba conf   It will ask for password  Enter your admin login password  This will open gedit with the file  Paste the following line   host  all   all  127 0 0 1 32  trust   just below -    Database administrative login by Unix domain socket   Save and close it  Close the terminal and open it again and run this command   psql -U postgres   You will now enter the psql console  Now change the password by entering this    ALTER USER  your prefered user name  with password   desired password      If it says user does not exist then instead of ALTER use CREATE   Lastly  remove that certain line you pasted in pg hba and save it

User · Answer

If you are in windows you can just run  net user postgres postgres   and login in postgres with postgres postgres as user password

[postgresql] I forgot the password I entered during postgres installation

Examples related to postgresql

Examples related to postgresql-9.1

Examples related to forgot-password