How to login postgresql access psql from ubuntu user

Posted on January 11, 2012 by admin

I use Ubuntu 11.10 Oneric and Postgresql 9.1 in this cases. So, when I need to dump database using “psql -U postgres” from ubuntu, I got several error like this :

1
2
3
psql: FATAL:  role "ubuntu" does not exist

psql: FATAL:  Peer authentication failed for user "postgres"

psql: FATAL:  Peer authentication failed for user "postgres"

Actually, this problem happen because postgresql not allowing other than user “postgres’ accessing database through psql. This is why if you login into postgres user, you don’t have any problem :

1
sudo su postgres

The main problem is located in “pg_hba.conf” :

1
sudo vim /etc/postgresql/9.1/main/pg_hba.conf

We see that postgres use “peer” for authentication. This is why we can’t execute psql from linux users. Change this peer into md5.

1
2
3
4
5
6
7
#local   all             postgres                                peer

local   all             postgres                                trust



# TYPE  DATABASE        USER            ADDRESS                 METHOD



# "local" is for Unix domain socket connections only

local   all             all                                     trust

Now you can access psql from Ubuntu users