Peer authentication failed for user with all privileges in Postgres 9.5

I want to create a user who only has access to a specified database. However, it should have all permission. I use Postgresql 9.5 on Ubuntu 14.04. So first of all, I create a new user:

$createuser --interactive joe Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) n Shall the new role be allowed to create more new roles? (y/n) n

Next I create a new database with owner joe:

 $sudo -u postgres psql $CREATE DATABASE myDB OWNER joe; $GRANT ALL ON DATABASE myDB TO joe;

After that, I try to connect with user joe to connect on my database myDB:

$psql myDB -U joe
psql: FATAL: Peer authentication failed for user "joe" 

What i have to do next?

2

1 Answer

1. Open /etc/postgresql/9.5/main/pg_hba.conf with root access

    sudo nano /etc/postgresql/9.5/main/pg_hba.conf

2. Change peer to md5 in these lines.

   Before changing

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

   After your change

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

3. Save the file with pressing Ctrl-O. Exit nano with Ctrl-X

4. Restart postgresql using

   sudo service postgresql restart

3

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy