Create user in PostgreSQL sudo: unknown user

Question

I'm trying to create a new user in PostgreSQL installed on Ubuntu 20.04:

$ sudo -u postgres createuser --superuser tommy

But when I try to create a database with that user it says unknown user:

$ sudo -u tommy createdb tommy_db
sudo: unknown user: tommy
sudo: unable to initialize policy plugin

The think is that I already know that there are UNIX users and PostgreSQL users and I don't know if I have to create first a UNIX user tommy or not.

I would like to take advantage of the post and ask what is the difference between a postgreSQL user and a UNIX user in postgreSQL in terms of database management.

PD: If I list the users using /du in plsql I can see the created user tommy:

 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 tommy     | Superuser, Create role, Create DB                          | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Answer 1

Your system is probably using simple "peer" authentication by default. That is a common default for Linux-based package managers to use when they create a database for you.

To keep using that set up, then you yes you will need to set up "tommy" as a Linux user. Or, you will have to edit pg_hba.conf to pick a different method, like md5 (password) or gss (kerberos), or to add an ident map along with "peer" to describe which OS users are allowed to log in as which database users.

The only connection between the Linux user and the PostgreSQL user is the one created by the use of peer authentication.

Answer 2

Be sure to be logged as a root user by using sudo su or sudo -i

So that's likely part of the problem - when you use sudo -u postgres from the root account, it's trying to access root's home directory as the non-root user postgres.

That should work pretty well.