PostgreSQL: How To - Installation , Create User , DB and manage permissions

Installation

sudo apt install postgresql postgresql-contrib

• Start the DB
  • you will have instruction printed in the output

performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctlcluster 12 main start

Ver Cluster Port Status Owner    Data directory              Log file
12  main    5432 down   postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
update-alternatives: using /usr/share/postgresql/12/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmast
er.1.gz (postmaster.1.gz) in auto mode
invoke-rc.d: could not determine current runlevel
invoke-rc.d: policy-rc.d denied execution of start.

We are interested in pg_ctlcluster 12 main start line.

Creating User Account

• login into DB

root@7204604adf5f:/# sudo -u postgres psql
psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
Type "help" for help.

• create DB and user from psql console

postgres=# create database mydb;
create user myuser with encrypted password 'mypass';
grant all privileges on database mydb to myuser;
CREATE DATABASE
CREATE ROLE
GRANT
postgres=# exit

• create user and DB from OS console( contrib package provides these utils)

root@7204604adf5f:/# sudo -u postgres createuser user01
root@7204604adf5f:/# sudo -u postgres createdb db01

### Now drop to psql shell and give necessary permissions
root@7204604adf5f:/# sudo -u postgres psql
psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# alter user user01 with encrypted password 'Password#222';
grant all privileges on database db01 to user01;
ALTER ROLE
GRANT
postgres=# exit

Now lets verify login

root@7204604adf5f:/# psql -h localhost -U user01 -d db01
Password for user user01: 
psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

db01=> exit
root@7204604adf5f:/# 

User Management file ( Work in Progress )

So the users we created and provided as default with installation are based here, /etc/postgresql/12/main/pg_hba.conf postgres support 3rd Party/external identify management systems.
Follow reference links for more information.

depending upon the OS, this file located at different location, but postgres can help identify this file location

admin@7204604adf5f:~$ psql -t -P format=unaligned -c 'SHOW hba_file;'
/etc/postgresql/12/main/pg_hba.conf
admin@7204604adf5f:~$ psql
psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
Type "help" for help.

admin=# SHOW hba_file;
              hba_file               
-------------------------------------
 /etc/postgresql/12/main/pg_hba.conf
(1 row)