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

Question

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.

There's a raft of possible causes for the error you're seing above ... do any of the answers/suggestions here help? — tink
– tink, Commented Aug 1, 2022 at 0:00

jjanes · Accepted Answer · 2022-07-31 23:16:51Z

1

The simplest form of peer authentication means the OS user can log into PostgreSQL as the PostgreSQL user which is spelled the same. You didn't indicate who you ran createdb as, but I would guess it wasn't as the OS user 'admin', even though you did appear to create that user (you said you can switch to it, but not that you did switch to it for the rest of the question).

If you want to use password based authentication, you need to change pg_hba to do so. If you want to use peer authentication, you need to use the OS user you created for that purpose.

edited Jul 31, 2022 at 23:16

answered Jul 31, 2022 at 23:11

jjanes

45k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

machineghost Over a year ago

Thank you, that makes sense. So, if I wanted to be on Linux user A, but run createdb (or any DB command) as user B, in my pg_hba I'd want md5 instead of peer?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related