password authentication failed for user "postgres" [duplicate]

Question

This question already has answers here:

'password authentication failed for user "postgres"' (32 answers)

Closed 12 years ago.

I have installed PostgreSQL server 8.4 on my CentOS server sucessfully. After that, I login to CentOS server by using 'postgres' user, but I can not run any command, the error occur:

password authentication failed for user "postgres"

Here is some configurations in pg_hba.conf and postgresql.conf:

--------------------configuration in postgresql.conf-----------
listen_addresses = '*'
--------------------configuration in pg_hba.conf---------------
local all all md5 
local all postgres md5 
host all all 127.0.0.1/32 md5 
host all all ::1/128 md5

Answer 1

I tend to use the following in pg_hba.conf:

# Database administrative login by UNIX sockets
local all postgres ident
# TYPE DATABASE USER CIDR-ADDRESS METHOD
local all all md5
host all all 127.0.0.1/32 md5
host all all ::1/128 md5

That means that once you're "postgres", you don't need a password, but you need sudo rights to become postgres, so that's pretty darn secure. Any other user can login using simple md5 authentication, which means you don't have to sudo all over the place. This works well for me.

Answer 2

Ensure you use the password of the postgres PostgreSQL account, not the postgres system account. Try "trust" instead of "md5" in the pg_hba.conf to connect to the db an change your pass.

• Chan Pye: what's the difference between postgres system account and postgres PostgreSQL account? and how can I check whether i'm using postgres PostgreSQL account or not?
• Chan Pye: I have used password for PostgreSQL account.
• Daniel: Are you sure? How do you run any command?Do you get a shell using psql?
• Chan Pye: I'm sure for that. I remote Linux server from XP desktop by using Putty. I use PostgresSQL account(postgres) to login, move to /opt/postgres/8.4/bin, run command .createdb abc, and error occur. Note that, I uninstalled PostgreSQL 9.0 before install PostgreSQL 8.4. Is that a reason?
• Daniel: No, you never use the PostgreSQL account to login with putty, you use the system account (possibly named postgres). Then you enter "psql" at the command line, and use a PostgreSQL account to log into PostgreSQL, maybe without giving a password when using "ident" or "trust" in your pg_hba conf file.

Answer 3

I met a similar problem (the same error message). Like Daniel answer, it's because that I confuse between :

"postgres PostgreSQL account, not the postgres system account."

To be sure about the postgres password, just change it to '123456' (or any other password)

# su postgres
# psql -d template1
template1=# ALTER USER postgres WITH PASSWORD '123456';