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"
This question is related to
postgresql
ubuntu
pgadmin
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;
I just wanted to add that you should also check if your password is expired.
See Postgres password authentication fails for details.
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):
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.
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
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;"
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
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
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';
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.
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.
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
I hope this will help you short of time. You can change the password of postgres sql by using bellow command.
sudo -u postgres psql
And next you can update the password
Alter user postgres password 'YOUR_NEW_PASSWORD';
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
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.
Edit the pg_hba.conf file, e.g. with sudo emacs /etc/postgresql/9.3/main/pg_hba.conf
Change all authentication methods to trust
. Change Unix Password for "postgres" user. Restart Server. Login with psql -h localhost -U postgres
and use the just set Unix password. If it works you can re-set the pg_hba.conf file to the default values.
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
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:
Hope it will resolve your issue
Time flies!
On version 12, I have to use "password" instead of "ident" here:
local all postgres password
Connect without using the -h option.
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
/Library/PostgreSQL/13
.then try
psql postgres
it should work.
Source: Stackoverflow.com