Can't Use Newly Created User to Run Postgres createdb Command

All we need is an easy explanation of the problem, so here it is.

Short Version:

I get:

error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "admin"

when I try to createdb --username admin --password mynewdb … but I created the "admin" user in both Linux and PostgreSQL, so I don’t understand why I can’t use them.

Long Version:

I am trying to create an "admin" user to run SQL on a brand new Ubuntu/AWS machine. I created the user with

$ sudo useradd -g admin admin

Then I set their password using:

$ sudo passwd admin

I know the password was set correctly, because I can switch to the "admin" user by typing that password in, with:

$ su - admin

I also created a database role for the user, with:

CREATE ROLE admin WITH SUPERUSER CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD 'samepassword';

and I can see that the user was created with \du (from inside psql):

 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 admin     | Superuser, Create role, Create DB                          | {}

Furthermore, I can confirm, from my pg_hba.conf file, that Postgres should use the "peer" (ie. system) password for that user:

local   all             all                                     peer

And I restarted Postgres, just to be safe:

sudo service postgresql restart

However, despite all of the above, when I try to create a new database with that "admin" user, using createdb, I get prompted for my password, twice, and then it fails:

$ createdb --username admin --password mynewdb
Password: 
Password: 
createdb: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed:
FATAL:  Peer authentication failed for user "admin"

I’ve tried reading other SO answers for this error, but they all suggest things I’ve tried (ie. changing pg_hba.conf, restarting Postgres, etc.) But, I’m sort of a Postgres newb, so if anyone has any further suggestions I would love to hear them.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

As someone over on Stack Overflow helpfully explained, it’s not enough to just add the --username admin argument; you also have to be logged-in (in your operating system) as that user.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply