'password authentication failed for user "postgres"'

Question

I have installed PostgreSQL 8.4, Postgres client and Pgadmin 3. Authentication failed for user "postgres" for both console client and Pgadmin. I have typed user as "postgres" and password "postgres", because it worked before. But now authentication is failed. I did it before a couple of times without this problem. What should I do? And what happens?

psql -U postgres -h localhost -W
Password for user postgres: 
psql: FATAL: password authentication failed for user "postgres"
FATAL: password authentication failed for user "postgres"

• JJD: In my case the connection fails because the password is too complex ...
• elrobis: Be sure to read all the way down this page. I had to do several things to get this working. First, the ALTER step, then editing my pg_hba.conf file, then restarting postgres.
• Evan Carroll: Possible duplicate of How do I login and authenticate to Postgresql after a fresh install?
• koppor: Also ensure that you have not two Postgres instances running: one on Windows, one in Docker. Docker did not report to me that the port was already taken. So, the tool connected to the Windows machine Postgres, whereas in the Docker postgers, everything was fine.
• Charles Duffy: How is this a programming question? It may be system administration, but that's a separate discipline.

Answer 1

If I remember correctly the user postgres has no DB password set on Ubuntu by default. That means, that you can login to that account only by using the postgres OS user account.

Assuming, that you have root access on the box you can do:

sudo -u postgres psql

If that fails with a database "postgres" does not exists error, then you are most likely not on a Ubuntu or Debian server :-) In this case simply add template1 to the command:

sudo -u postgres psql template1

If any of those commands fail with an error psql: FATAL: password authentication failed for user "postgres" then check the file /etc/postgresql/8.4/main/pg_hba.conf: There must be a line like this as the first non-comment line:

local all postgres ident

For newer versions of PostgreSQL ident actually might be peer. That's OK also.

Inside the psql shell you can give the DB user postgres a password:

ALTER USER postgres PASSWORD 'newPassword';

You can leave the psql shell by typing CtrlD or with the command \q.

Now you should be able to give pgAdmin a valid password for the DB superuser and it will be happy too. :-)

• Cerin: Note, that pg_hba.conf must have the postgres user set to ident in order for the first steps to work. If you already set it to md5 or something else, you won't be able to auto-login.
• itsols: Very nice one. To other new users, DON'T FORGET THE SEMICOLON at the end of the ALTER USER line.
• frozenjim: @itsols You said: "Very nice one. To other new users, DON'T FORGET THE SEMICOLON at the end of the ALTER USER line"... You just ended a four hour ordeal!! I feel sooooo stupid and grateful right now. :-D
• Harry: I'm not so new, I configured PostgreSQL dbs many times, but I FORGOT THE SEMICOLON! ;) AGAIN ;)
• HFSDev: From the Comprehensive manual, postgresql.org/files/documentation/pdf/12/postgresql-12-A4.pdf, section 20.5: "If no password has been set up for a user, the stored password is null and password authentication will always fail for that user.". Thus, since the installation doesn't ask for a password, it is not set. This should be said in every tutorial, since it's one of the first things new users do, try to connect from a nice GUI...

Answer 2

The response of staff is correct, but if you want to further automate can do:

$ sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'postgres';"

Done! You saved User = postgres and password = postgres.

If you do not have a password for the User postgres ubuntu do:

$ sudo passwd postgres

• Brian Peterson: Careful if your password contains interesting characters like '!'
• BMLopes: As you are having the trouble to change the password of the user, a good practice would be to create a new user and a new password: Insde the SQL prompt, it would be: postgres-# CREATE ROLE your_username WITH LOGIN CREATEDB ENCRYPTED PASSWORD 'your_password'; A discussion about the use of the postgres user can be found in stackoverflow.com/questions/2172569/…
• Chris: The only solution that worked for me (Kali Linux 2020)
• Harshit Pant: Worked for me!! Thanks. Note that we need to set the username and password as it is in the knex.ts or js file
• abu_bua: On debian sid no passwd for user postgres was set. sudo passwd postgres did the job!!

Answer 3

This was frustrating, most of the above answers are correct but they fail to mention you have to restart the database service before the changes in the pg_hba.conf file will take affect.

so if you make the changes as mentioned above:

local all postgres ident

then restart as root ( on centos its something like service service postgresql-9.2 restart ) now you should be able to access the db as the user postgres

$psql
psql (9.2.4)
Type "help" for help.
postgres=# 

Hope this adds info for new postgres users

• Ann Kilzer: On ubuntu type: sudo service postgresql restart
• Spechal: for RHEL7 use sudo systemctl restart postgresql.service
• AustinT: What about on mac?
• Rohith Nandakumar: For mac, checkout this question on SO stackoverflow.com/questions/7975556/…
• Ustin: Thanks Miguel. I've cahnged the password manually, and my pgAdmin did not connect to the databese until I restart the postgres server.

Answer 4

Edit the pg_hba.conf file, for Debian on /etc/postgresql/9.3/main/pg_hba.conf and for Red Hat/IBM derivates at /var/lib/pgsql/9.4/data/pg_hba.conf

• Change all authentication methods to trust.
• Change Linux Password for postgres user.
• Restart Server.
• Login with psql -h localhost -U postgres and use the just set Unix password.
• If it works you should re-set the pg_hba.conf file to values with md5 or ident methods and restart.

• PapaK: If you get completely stuck, this is the only guaranteed method. Change all methods to trust, restart db, then as root: sudo su - postgres, next set/fix/unset password for postgres db (and in the shell, if necessary), then restore to secure md5 or ident methods and restart again so values stick. By the way, on Cent/RedHat 9.4 the file is located at: /var/lib/pgsql/9.4/data/pg_hba.conf
• ashoka.devanampriya: Thanks Papak, the file location on cent os was of great help.

Answer 5

For those who are using it first time and have no information regarding what the password is they can follow the below steps(assuming you are on ubuntu):

1. Open the file pg_hba.conf in /etc/postgresql/9.x/main

    sudo vi pg_hba.conf 
   

   2.edit the below line

    local all postgres peer
   

   to

    local all postgres trust
   

2. Restart the server

    sudo service postgresql restart
   

3. Finally you can login without need of a password as shown in the figureFinally you can login without need of a password as shown in the figure

Ref here for more info

• rickster: Getting an error W10 : Warning: Changing a readonly File Also can't open file for writing
• rickster: yeah, forgot to add sudo, my bad .

Answer 6

When you install postgresql no password is set for user postgres, you have to explicitly set it on Unix by using the command:

sudo passwd postgres

It will ask your sudo password and then promt you for new postgres user password. Source

• oriont: Funny how I've never seen this before! This is by far the easiest way to change the password in my opinion.

Answer 7

Once you are in your postgres shell, Enter this command

postgres=# \password postgres

After entering this command you will be prompted to set your password , just set the password and then try.

Answer 8

Try to not use the -W parameter and leave the password in blank. Sometimes the user is created with no-password.

If that doesn't work reset the password. There are several ways to do it, but this works on many systems:

$ su root
$ su postgres
$ psql -h localhost
> ALTER USER postgres with password 'YourNewPassword';

Answer 9

As a rule of thumb: YOU SHOULD NEVER EVER SET A PASSWORD FOR THE POSTGRES USER.

If you need a superuser access from pgAdmin, make another superuser. That way, if the credentials for that superuser is compromised, you can always ssh into the actual database host and manually delete the superuser using

sudo -u postgres -c "DROP ROLE superuser;"

• Gershom Maes: What's the reason for this rule of thumb?
• ardilgulez: So that you can never have all your superusers conpromised.
• Gershom Maes: How can setting a password for postgres lead to having all superusers compromised?
• ardilgulez: setting password doesn't lead to having all superusers compromised but not setting the password will guarantee that you'll never have all superusers compromised. the reason is: when you don't set the password, any password login attempt to postgres user will be denied whereas you can still use it yourself by trust.

Answer 10

If you are trying to login postgres shell as postgres user, then you can use following commands.

switch to postgres user

# su - postgres

login to psql

# psql

Hope that helps

• Prashanth Chandra: su - postgres asks for a password on posgresql 9.5 on Ubuntu 16.04
• icc97: su - postgres is the command that the official fedora documentation suggests, but I too get a password prompt. To get round that I followed this postgres forum email that uses the same command as the accepted answer here: sudo -u postgres psql. Don't forget to init and start the database server.
• Eugen Konkov: Why this is better to switch to postgres UNIX user before connecting to database? I suppose it is enough just to psql -U postges. Did I miss something?

Answer 11

Ancient thread, but I wasted half a day dealing with this in 2020, so this might help someone: Double-check your postgres port (on Ubuntu, it's in /etc/postgresql/9.5/main/postgresql.conf). The psql client defaults to using port 5432, BUT in my case, the server was running on port 5433. The solution was to specify the -p option in psql (e.g. psql --host=localhost --username=user -p 5433 mydatabase).

If you leave off the --host parameter, psql will connect via a socket, which worked in my case, but my Golang app (which uses TCP/IP) did not. Unfortunately, the error message was password authentication failed for user "user", which was misleading. The fix was to use a url connection string with the port (e.g. postgres://user:password@localhost:5433/mydatabase).

My setup was Ubuntu 18.04 on Digital Ocean, with postgres 9.5 installed via apt-get, so not sure why this happened. Hope this saves you some time.

Answer 12

Follow these steps :

1. sudo -u postgres -i
2. psql
3. \password postgres

After that, enter your password twice.

Then use that password in the pgAdmin4.

Answer 13

If you see error

FATAL: password authentication failed for user "postgres"

and you are sure that your password is correct, check that the password has any special characters, especially "%" or slashes. In my case, it was "%" in the password string. After removing this symbol, everything works fine.

• atsu85: looks like "!" in password causes same issues like "%" when connecting via psql

Answer 14

I faced the same error on Windows 10. In my case, when I setup the Postgres, my username was postgres by default. But when I ran the command psql, it as showing my the username as jitender which is my machine name, and I don't know why this username had been setup.

Anyway to solved it, I did the following steps: Run the command psql --help

• In the output, look for the Connection Option, here you will see your default user, in my case it as jitender.
• You will also get the command to set the anoter username, which should be psql --username postgres. You set the username whatever you require, and that's all, problem got solved.

• the_new_mr: I found that I have to use --username=postgres on everyone command. e.g. createdb --username=postgres mydb which is super annoying. I tried doing psql --username but this brought up a DB prompt and the default username was not changed.

Answer 15

I was also faced this issue while login the postgres. I was followed the below steps and able to login with postgres and pgadmin.

Step1: Open Postgres using terminal.

sudo su postgres

Step2: Open psql.

psql

Step3: Reset the password of user

ALTER USER user_name WITH PASSWORD 'new_password';

Step4: Give the permission on database to user.

GRANT ALL PRIVILEGES ON DATABASE my_database TO db_user;

Answer 16

Here are some combinations which I tried to login:

# login via user foo
psql -Ufoo -h localhost
sudo -u postgres psql postgres
# user foo login to postgres db
psql -Ufoo -h localhost -d postgres

Answer 17

Time flies!

On version 12, I have to use "password" instead of "ident" here:

local all postgres password

Connect without using the -h option.

Answer 18

First of All password crate

ALTER USER postgres with encrypted password 'postgres';

then service restart:

sudo systemctl restart postgresql.service

End.

• MechaCode: FYI - Inside pg db docker container running first command - alter User... is sufficient. No need to restart service

Answer 19

I just wanted to add that you should also check if your password is expired.

See Postgres password authentication fails for details.

• Tanner: Please add some details and a summary of what that link provides in case in the future the link no longer exists. Otherwise this is likely to be flagged as a low-quality/link only answer.
• Tanner: I'd have a read of this: meta.stackexchange.com/questions/8231/…

Answer 20

Answer given is almost correct just missing some pointers which i'll be taking care of in my solution

First make sure your user have a sudo access if not you can use the below command to add your user as sudo user :-

sudo adduser <username> sudo

The change will take effect the next time the user logs in.

i) Now go to sudo vim /etc/postgresql/<your_postgres_version>/main/pg_hba.conf file and look for line that says :

local all postgres md5 #peer

and comment that. Just below that line there must be a commented line that says:

local all postgres peer

or for older versions it'll be :-

local all postgres ident

Uncomment that line.

ii) Now restart the postgres by using any of these commands :-

sudo /etc/init.d/postgresql restart

OR

sudo service postgresql restart

iii) Now you can simply log into postgres using the following command :

sudo -u postgres psql

iv) once you're in you can create any operation you want to in my case i wanted to create a new database you can do the same using below command :

CREATE DATABASE airflow_replica;

Answer 21

In my case, Ubuntu 20.04 Postgresql 12 was using the wrong port.

I've checked /etc/postgresql/12/main/postgresql.conf and realized it was 5433 instead of 5432.

Answer 22

1. Open pg_hba.conf in any text editor (you can find this file in your postgres instalation folder);
2. Change all the methods fields to trust (meaning you don't need a password for postgre);
3. Run in your console this comand: "alter user postgres with password '[my password]';" | psql -U postgres (meaning to alter some user password for [my password] for the user as parameter -U postgres)
4. Et voilà (don't forget to change back the method from trust for the one that should be best for you)

I hope this help someone someday.

Answer 23

The answer is @diego

I want to add some explanations of how I fixed error and I hope it will help other folks: ERROR: password authentication failed for user "postgres"

1. On Window

---

• Make sure you download Postgres software, install it, create and confirm password and make sure its not complicated with some symbols and characters.

• Open window, click SQL Shell (PSQL) and access it and create database

• Create connection string like postgres://postgres:your_password@localhost:port/your_database

2. On WSL

---

Follow Microsoft documentation

After successful installation

 // Open postgres
 su postgres
 
 // Type psql and hit enter
 psql
 // Create a user postgres if not exist or any other user you want 
 CREATE USER your_user_db WITH PASSWORD 'match_password_with_db_password';
 
 // Give user password same as the one you set up for postgres db
 ALTER USER your_user_db WITH PASSWORD 'match_password_with_db_password';
 // Restart the server
 sudo service postgresql restart

Answer 24

i had a similar problem. Ubuntu was left me log in in console with any password for superuser. Except when i connected with -h localhost in psql line command.

I Observed too that "localhost:8080/MyJSPSiteLogIn" - showed: Fatal: autentication error with user "user".

pg_hba.conf was ok.

I noted had two versions of postgres running in the same service.

Solved - uninstalling inutil version.

Answer 25

I had faced similar issue. While accessing any database I was getting below prompt after updating password "password authentication failed for user "postgres"" in PGAdmin

---

Solution:

1. Shut down postgres server
2. Re-run pgadmin
3. pgadmin will ask for password.
4. Please enter current password of mentioned user

Hope it will resolve your issue

enter image description here

Answer 26

This happens due to caching.

When you run, php artisan config:cache, it will cache the configuration files. Whenever things get change, you need to keep running it to update the cache files. But, it won't cache if you never run that command.

This is OK for production, since config don't change that often. But during staging or dev, you can just disable caching by clearing the cache and don't run the cache command

So, just run php artisan config:clear, and don't run the command previously to avoid caching.

Check original post

Password authentication failed error on running laravel migration

Answer 27

In my case, its Password was longer than 100 characters. Setting it to a smaller character password worked.

Actually I am wondering is there a reference somewhere to that.

Answer 28

Please remember if you have two versions of Postgres installed you need to Uninstall one of them, in my case on MacOS I had one version installed via .dmg and one via brew.

What worked for me was to uninstall the one installed via .dmg using the following steps

1. Go to /Library/PostgreSQL/13.
2. Open uninstall-postgres.app.

then try

psql postgres

it should work.

Answer 29

In my case it was so simple! I was taken error in application JAVA Spring because I needed remember the Database Superuser, it is showed during the install process PostgreSQL, in my case the datasource would be postgres. So, I added correctly the name and it works!

Answer 30

I encountered the identical error consistently when attempting to establish a connection to the database hosted within the Docker container using psql. The error message persisted, stating psql: FATAL: password authentication failed for user "postgres".

The problem arose because both the PostgreSQL Docker container and the PostgreSQL server on Windows were using the default port 5432 on localhost. Surprisingly, there were no errors encountered while starting either of them.

The options 1 and 2 are more straightforward and you can try both of them.

Solutions:

• Option 1: Stop the PostgreSQL service on Windows
• Option 2: Change the port of the docker container
• Option 3 (using WSL): Completely uninstall Protgres 12 from Windows and install postgresql-client on WSL (sudo apt install postgresql-client-common postgresql-client libpq-dev)

If none of these solutios work for you, please, check the link below: psql: FATAL: password authentication failed for user "postgres"