Getting error: Peer authentication failed for user “postgres”, when trying to get pgsql working with rails

After installing Postgresql I did the below steps.

1. open the file pg_hba.conf for Ubuntu it will be in /etc/postgresql/9.x/main and change this line:

local   all             postgres                                peer

to

local   all             postgres                                trust

2. Restart the server

sudo service postgresql restart

3. Login into psql and set your password

psql -U postgres

ALTER USER postgres with password 'your-pass';

4. Finally change the pg_hba.conf from

local   all             postgres                                trust

to

local   all             postgres                                md5

After restarting the postgresql server, you can access it with your own password

Authentication methods details:

trust - anyone who can connect to the server is authorized to access the database

peer - use client's operating system user name as database user name to access it.

md5 - password-base authentication

for further reference check here

If you connect over localhost (127.0.0.1) you shouldn't experience that particular issue. I wouldn't muck much with the pg_hba.conf but instead I would adjust your connection string:

psql -U someuser -h 127.0.0.1 database

where someuser is your user you're connecting as and database is the database your user has permission to connect to.

Here is what I do on Debian to setup postgres:

http://www.postgresql.org/download/linux/debian/  (Wheezy 7.x)

as root …

    root@www0:~# echo "deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main" >> /etc/apt/sources.list

    root@www0:~# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

    root@www0:~# apt-get update

    root@www0:~# apt-get install postgresql-9.4        

    root@www0:~# su - postgres 

    postgres@www0:~$ createuser --interactive -P someuser
    Enter password for new role:
    Enter it again:
    Shall the new role be a superuser? (y/n) n
    Shall the new role be allowed to create databases? (y/n) y
    Shall the new role be allowed to create more new roles? (y/n) n

    postgres@www0:~$ createdb -O someuser database

    postgres@www0:~$ psql -U someuser -h 127.0.0.1 database

Enjoy!

I had the same problem.

The solution from depa is absolutely correct.

Just make sure that u have a user configured to use PostgreSQL.

Check the file:

$ ls /etc/postgresql/9.1/main/pg_hba.conf -l

The permission of this file should be given to the user you have registered your psql with.

Further. If you are good till now..

Update as per @depa's instructions.

i.e.

$ sudo nano /etc/postgresql/9.1/main/pg_hba.conf

and then make changes.

1. Go to this /etc/postgresql/9.x/main/ and open pg_hba.conf file

In my case:

$>  sudo nano /etc/postgresql/9.3/main/pg_hba.conf

2. Replace peer with md5

So this will be changed to:

Database administrative login by Unix domain socket local all postgres peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

This:

Database administrative login by Unix domain socket local all postgres md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

3. Then restart pg server:

   $> sudo service postgresql restart

Below is list of METHODS used to connect with postgres:

# METHOD can be "trust", "reject", "md5", "password", "gss", "sspi",
# "krb5", "ident", "peer", "pam", "ldap", "radius" or "cert".  Note that
# "password" sends passwords in clear text; "md5" is preferred since
# it sends encrypted passwords.

Note: If you have not create you postgres user yet. Create that and now you can access postgres server using that user credentials.

If you have problem, you need to locate your pg_hba.conf

find / -name 'pg_hba.conf' 2>/dev/null

And change configuration like image:

Postgresql 9.3

Postgresql 9.4

After that you should to restart service postgresql

Example for postgresql 9.3

service postgresql-9.3 restart

If you have any problems, you need to set password again:

ALTER USER db_user with password 'db_password';

the below command works for me:

psql -d myDb -U username -W

I was moving data directory on a cloned server having troubles to login as postgres and to reset postgres password just this worked for me

root# su postgres

postgres$ psql -U postgres

psql (9.3.6)

Type "help" for help.

postgres=#\password

Enter new password:

Enter it again:

postgres=#

The edits above worked for me, after I figured out that I needed to restart the postgres server after making them. For ubuntu:

sudo /etc/init.d/postgresql restart

You need just set METHOD to trust.

#TYPE  DATABASE        USER            ADDRESS                 METHOD
local    all             all                                     trust

And reload postgres server.

# service postgresql-9.5 reload

Changes in pg_hba.conf dont require RESTART postgres server. just RELOAD.

Use "host=localhost" in connection.

PGconn *conn = PQconnectdb("host=localhost user=postgres dbname=postgres password=123");

If you want to keep the default config but want md5 authentication with socket connection for one specific user/db connection, add a "local" line BEFORE the "local all/all" line:

# TYPE  DATABASE     USER         ADDRESS             METHOD

# "local" is for Unix domain socket connections only
local   username     dbname                           md5  # <-- this line
local   all          all                              peer
# IPv4 local connections:
host    all          all          127.0.0.1/32        ident
# IPv6 local connections:
host    all          all          ::1/128             ident