Postgresql: password authentication failed for use

问题:

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\"

回答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. :-)

回答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

回答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 do something like the 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

回答4:

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.

回答5:

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

回答6:

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\';

回答7:

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 figure

Ref here for more info

回答8:

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;\"

回答9:

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

See Postgres password authentication fails for details.

回答10:

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

回答11:

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.

回答12:

I hope this will help you short of time. You can change the password of postgres sql by using bellow command.

Command

sudo -u postgres psql

And next you can update the password

Command

Alter user postgres password \'YOUR_NEW_PASSWORD\';