[SOLVED] Create user in PostgreSQL sudo: unknown user

Issue

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 | {}

Solution

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.

Answered By - jjanes

Answer Checked By - Marilyn (WPSolving Volunteer)