PostgreSQL 12 on Ubuntu 20.04

For backend of our services and applications we usually need some good storage for persistent data, usually some centralized database. PostgreSQL is good pick for many cases. Check other places to find out why it’s good choice, I will strictly go after making it working here with few more words in the end. There is already PostgreSQL version 13 but we will use version 12 for two major reasons:

1. It’s packed with the Ubuntu 20.04 distribution
2. It’s already well tested and widely used

As we operate on Ubuntu 20.04, let’s log in as root and do the ‘magic’ without unnecessary ‘sudo’ typing.

su - root

STEP#1 — Install PostgreSQL

apt install postgresql postgresql-client

• postgresql — server
• postgresql — client to connect PostgreSQL server

STEP#2 — Check the service

systemctl status postgresql.service

you should see something like this

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Sat 2020-11-21 08:40:52 UTC; 4min 20s ago
    Process: 848 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 848 (code=exited, status=0/SUCCESS)
Nov 21 08:40:52 vm1 systemd[1]: Starting PostgreSQL RDBMS...
Nov 21 08:40:52 vm1 systemd[1]: Finished PostgreSQL RDBMS.

STEP#3 — Check server can stop and start properly

It’s not necessary step but it would be good to know if service can restart without issues.

# stop the service
systemctl stop postgresql.service
# check it stopped as expected
systemctl status postgresql.service
# start the service
systemctl start postgresql.service# check it started as expected
systemctl status postgresql.service

STEP#4 — Check the executable we can use

Let’s find out what we can use with using apt-file. Let’s install it and create indexes.

apt install apt-file
apt-file update

Now check the files. Note we need to check postgresql-client-12 package because postgresql-client is just a metapackage and will not help here.

apt-file list postgresql-client-12

We should see these utilities

• clusterdb — to recluster tables in PostgreSQL database
• createdb — create new PostgreSQL database
• createuser — define new PostgreSQL user account
• dropdb — remove a PostgreSQL database
• dropuser — remove a PostgreSQL user account
• pg_basebackup — to make a base backup of a PostgreSQL cluster
• pg_config — list exports related to PostgreSQL instance
• pg_dump — extract a PostgreSQL db into a script or archive file
• pg_dumpall — extract a PostgreSQL database cluster into a script file
• pg_isready — check PostgreSQL connection status
• pg_receivewal — stream PostgreSQL log
• pg_recvlogical — control PostgreSQL logical decoding streams
• pg_restore — restore PostgreSQL database from pg_dump archive
• psql — PosgreSQL interactive terminal
• reindexdb — reindex PostgreSQL database
• vacuumdb — garbage collect and analyze PostgreSQL database

We can check the status of PostgreSQL and if it is ready to accept connections.

STEP#5 — Check if postgres user is created

cat /etc/passwd | grep postgres

We should see something like

postgres:x:112:118:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

This means we have postgres user created. Let’s set password for this user

passwd postgres

STEP#6 — Connect to the database

Let’s login as postgres user

su -l postgres

And now connect to PostgreSQL instance and open interactive terminal

psql

STEP#7 —Check the client

Let’s create new database called ‘medium_db’ to see if it is working as expected.

create database medium_db;

Now check if the database exists

\l

We should see something like this

Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 medium_db | postgres | 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
(4 rows)

All looks good.

STEP#8 — Set postgres DB user password

alter user postgres password 'what_acomplex_password^&*';

This is database user password, don’t confuse with postgres Linux user machine password.

Let’s leave psql now with this command

\q

STEP#9 — Enable remote access

We might need to work with the database remotely.

For that, we need to do some setting changes Let’s edit postgresql.conf accordingly. Switch to root account again and use vim.

vim /etc/postgresql/12/main/postgresql.conf

If you want to allow all IP addresses to remotely access your PostgreSQL instance, you can add this line into connection and authentication section.

listen_addresses = '*'

Also edit /etc/postgresql/12/main/pg_hba.conf which is settings for client authentication control and add line below to the end of the file

host all all 0.0.0.0/0 md5

Be aware this is not good practice as it increase security risk. You should limit remote access as much as possible and rather put list of allowed IP addresses if you need remote access. Also don’t use psql remotely if you don’t have to.

Now restart the server

service postgresql restart

Also make sure your firewall is not blocking default port 5432 or whatever your config states. If your firewall is inactive (which is by default) you are good.

ufw status
# if inactive you should see
Status: inactive

If your ufw (Uncomplicated Firewall) is active and port 5432 is blocked you can simply execute this command under root

ufw allow 5432

STEP#10 —Connect DB from remote machine

Let’s find out DB machine IP address

ip address | grep global

We should see something like

inet [IP]/24 brd [MASK] scope global dynamic enp1s0

On remote machine install just postgresql-client

apt install postgresql-client

Now connect to the remote database

psql -U postgres -p 5432 -h [IP] medium_db

Input ‘ what_acomplex_password^&*’ and you should see

psql (12.5 (Ubuntu 12.5-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
medium_db=#

Now you can use your database remotely and even with remote psql client.

That’s basically is for this tutorial, now you have your database ready to be used. Databases are usually the most critical part of the IT infrastructure. Election results can give you good example how critical database data, settings and security around your database might be. Once your database data is compromised it is very hard to fix it and find out what happened. You might unfortunately not even notice that your data were compromised!

Also be sure that for some use cases, traditional centralized databases are not even the best/right approach. Again, collecting election results or handling financial operations over centralized database is poor approach. For highly critical data, transparent public block-chains or some other similar approaches can give you much more security, transparency and fraud protection.