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.