0. Login as root

user@host$ sudo su

1. Switch to postrgres account

root@host# su - postgres

2. Creating a New Role

If you are logged in as the postgres account, you can create a new user by typing (for example db-user-1):

postgres@host$ createuser --interactive

Shall the new role be a superuser? (y/n)

3. Create a New Database

This means that, if the user you created in the last section is called db-user-1, that role will attempt to connect to a database which is also called db-user-1 by default. You can create the appropriate database with the createdb command.

postgres@host$ createdb db-user-1

4. Allow password authentication

postgres@host$ psql

postgres=# ALTER USER "db-user-1" PASSWORD 'mypassword';

5. Test

user@host$ psql -h localhost -p 5432 -U db-user-1

---

Attention!

Your user db-user-1 is superuser. If you want to limit access to tables enter this commands:

ALTER USER "db-user-1" WITH NOSUPERUSER;

ALTER USER "db-user-1" WITH NOCREATEROLE;

ALTER USER "db-user-1" WITH NOCREATEDB;

And now you can grant permissions:

GRANT CONNECT ON DATABASE db-user-1 TO "db-user-1";

GRANT ALL PRIVILEGES ON DATABASE db-user-1 TO  db-user-1;

---

Related articles:

• How To Install and Use PostgreSQL on Ubuntu 18.04
• Postgres password authentication fails