Here is the brief step to setup the postgresql database on Ubuntu.

Install

sudo apt install postgresql postgresql-contrib

sudo systemctl start postgresql.service

Using postgres Roles and Database

A default user account postgres is associated with role.

sudo -i -u postgres psql

postgres=# \q

Create new role

createuser --interactive

or

sudo -u postgres createuser --interactive

create new database

createdb api_server

or

sudo -u postgres createdb api_server

open postgres with new role

sudo adduser kelvin

sudo -i -u kelvin

pql

or

sudo -u kelvin psql

or you can create user for the created database

CREATE DATABASE api_server;

CREATE USER myprojectuser WITH PASSWORD 'password';

other security config

• Change the pg_hda.conf
  /etc/postgresql/16.3/main/pg_hba.conf

  host all all 172.22.2.0/24 md5
  

• Change the /etc/postgresql/16.3/main/postgresql.conf

  listen_addresses = '*'
  

• Change the postgres user's password

  sudo su postgres -c psql template1
  ALTER USER postgres WITH PASSWORD ' <***password***> ';
  

• setup charactor encoding to utf-8

  ALTER ROLE api_server SET client_encoding TO 'utf8';
  ALTER ROLE api_server SET default_transaction_isolation TO 'read committed';
  ALTER ROLE api_server SET timezone TO 'UTC';
  

• give the new user access to administer the database

  GRANT ALL PRIVILEGES ON DATABASE api_server TO kelvin;
  

• sudo passwd postgres

• Restart the db server

  sudo systemctl start postgresql.service