12

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?

  • 4
    This is not Off-topic. because setting up software in Ubuntu is on-topic – Anwar Sep 4 '16 at 11:19
  • Is your problem solved? – Anwar Sep 4 '16 at 12:50
10
  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
    
  • 1
    thank you, i had to change the line local peer to md5, after that i could login successfully. – Qeychon Sep 4 '16 at 12:54
  • Updating for any version:, to check the conf file: sudo -u postgres psql -c "SHOW config_file" – Peter Krauss Jul 16 '18 at 2:29

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.