I forgot the password I entered during postgres installation
PostgresqlPostgresql 9.1Forgot PasswordPostgresql Problem Overview
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.
Postgresql Solutions
Solution 1 - Postgresql
-
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 un-commented 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
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 ('replace my_user_name with postgres since you are resetting postgres user)
`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
Solution 2 - Postgresql
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=#
Solution 3 - Postgresql
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 -> Change Password
Solution 4 - Postgresql
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.
Solution 5 - Postgresql
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.
Solution 6 - Postgresql
-
Edit the file
/etc/postgresql/<version>/main/pg_hba.conf
and find the following line:local all postgres md5
-
Edit the line and change
md5
at the end totrust
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 thepsql
shell$ psql -U postgres
-
Update the
postgres
user's passwordalter user postgres with password 'secure-passwd-here';
-
Edit the file
/etc/postgresql/<version>/main/pg_hba.conf
and changetrust
back tomd5
and save the file -
Reload the postgresql service
$ sudo service postgresql reload
-
Verify that the password change is working
$ psql -U postgres -W
Solution 7 - Postgresql
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 '<your new password>';
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
Solution 8 - Postgresql
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.
Solution 9 - Postgresql
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.
Solution 10 - Postgresql
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.
Solution 11 - Postgresql
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
Solution 12 - Postgresql
If you are in windows you can just run
net user postgres postgres
and login in postgres with postgres/postgres as user/password
Solution 13 - Postgresql
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.
Solution 14 - Postgresql
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.
Solution 15 - Postgresql
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.