Postgress Setup

Install PostgreSQL 12 on Ubuntu 20.04 LTS

sudo apt updatesudo apt install -y postgresql postgresql-contrib postgresql-clientsudo systemctl status postgresql.service

Initial database connection

A local connection (from the database server) can be done by the following command:

sudo -u postgres psqlpsql (12.12 (Ubuntu 12.12-0ubuntu0.20.04.1))Type "help" for help.postgres=#

Set password for postgres database user

The password for the postgres database user can be set the the quick command \password or by alter user postgres password 'Supersecret'. A connection using the postgres user is still not possible from the "outside" hence to the default settings in the pg_hba.conf.

Update pg_hba.conf to allow postgres user connections with password

In order to allow connections of the postgres database user not using OS user authentication, you have to update the pg_hba.conf which can be found under /etc/postgresql/12/main/pg_hba.conf.

sudo vi /etc/postgresql/12/main/pg_hba.conf...local   all             postgres                                peer...

Change the last section of the above line to md5.

local   all             postgres                                md5

A restart is required in order to apply the new configuration:

sudo systemctl restart postgresql

Now a connection from outside the database host is possible e.g.

psql -U postgres -d postgres -h databasehostname

Creation of additional database users

A database user can be created by the following command:

create user myuser with encrypted password 'Supersecret';
CREATE ROLE

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 myuser    |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Creation of additional databases

One can create new Postgres databases within an instance. Therefore you can use the psql command to login (see above).

CREATE DATABASE dbname OWNER myuser;
CREATE DATABASE

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 dbname    | myuser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

You can leave the OWNER section of the command, when doing so, the current user will become owner of the newly created database.

To change the owner of an existing database later, you can use the following command:

postgres=# alter database dbname owner to myuser;
ALTER DATABASE