17

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?

Anwar
  • 77,855
Qeychon
  • 285

1 Answers1

21

  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
Anwar
  • 77,855