Postgresql – Cannot login to Postgrest database despite setting password for user ‘postgres’

postgresql

I'm trying to use pgAdmin III to manage my Postgres database.

Here are the commands I've run on my machine:

sudo apt-get install postgresql

Then I installed the pgAdmin III application:

sudo apt-get install pgadmin3

Next I focused on setting my username and password in order to login:

sudo -u postgres psql postgres

Here I set my password
\password postgres

Finally I just created my database:

sudo -u postgres createdb repairsdatabase

When I try to login using pgAdmin III, I get the error:

An error has occurred:

Error connecting to the server: FATAL: Peer authentication failed for user "postgres"

enter image description here

Best Answer

This is a bit of a FAQ.

You're connecting via UNIX sockets, and your pg_hba.conf is set to use peer authentication, so it expects your UNIX user ID to be the same as your PostgreSQL user ID.

Change pg_hba.conf to use md5 for local connections, and/or use TCP/IP by specifying "localhost" as the host instead of leaving the host blank. Local TCP/IP connections might be set to use ident in pg_hba.conf so change that to md5 if it is and if you want to use password auth.

See the PostgreSQL manual on pg_hba.conf for more information, and search Google for your error message.

Related Solutions

Postgresql – What’s the default superuser username/password for postgres after a new install

CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.

What you should do is follow Chris James's answer:

sudo -u postgres psql postgres

# \password postgres

Enter new password:

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

Ubuntu – Can’t install libpq-dev, ubuntu 10.10 and postgres 9

I solved this for myself by running:

sudo apt-get remove libpq5
sudo apt-get install libpq-dev

Automatically fixed dependencies.

Best Answer

Related Solutions

Postgresql – What’s the default superuser username/password for postgres after a new install

Ubuntu – Can’t install libpq-dev, ubuntu 10.10 and postgres 9

Related Topic