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

The problem is still your pg_hba.conf file (/etc/postgresql/9.1/main/pg_hba.conf*).

This line:

local   all             postgres                                peer

Should be:

local   all             postgres                                md5

* If you can't find this file, running locate pg_hba.conf should show you where the file is.

After altering this file, don't forget to restart your PostgreSQL server. If you're on Linux, that would be sudo service postgresql restart.

These are brief descriptions of both options according to the official PostgreSQL docs on authentication methods.

Peer authentication

The peer authentication method works by obtaining the client's operating system user name from the kernel and using it as the allowed database user name (with optional user name mapping). This method is only supported on local connections.

Password authentication

The password-based authentication methods are md5 and password. These methods operate similarly except for the way that the password is sent across the connection, namely MD5-hashed and clear-text respectively.

If you are at all concerned about password "sniffing" attacks then md5 is preferred. Plain password should always be avoided if possible. However, md5 cannot be used with the db_user_namespace feature. If the connection is protected by SSL encryption then password can be used safely (though SSL certificate authentication might be a better choice if one is depending on using SSL).

Sample location for pg_hba.conf:
/etc/postgresql/9.1/main/pg_hba.conf

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
   db> 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!