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

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

Answer 2

If you are in windows you can just run

net user postgres postgres

and login in postgres with postgres/postgres as user/password

Answer 3

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.

Answer 4

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=#

Answer 5

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.

Answer 6

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.