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...
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?
1 Answer
Open
/etc/postgresql/9.5/main/pg_hba.confwith root accesssudo nano /etc/postgresql/9.5/main/pg_hba.confChange
peertomd5in 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 peerAfter 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 md5Save the file with pressing Ctrl-O. Exit nano with Ctrl-X
Restart postgresql using
sudo service postgresql restart
Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.
