Installing PostgreSQL on Ubuntu

First, we need to install PostgreSQL and some popular extensions. Ubuntu has some pretty good documentation on how to install PostgreSQL (https://help.ubuntu.com/community/PostgreSQL):

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

The installation creates a default postgres user without a password. However, we are going to create our own user with admin rights:

sudo -u postgres createuser --superuser sa
sudo -u postgres psql

\password sa
\password
\password
\q

The sudo -u postgres command means we are going to execute the next command as the postgres user. The command --superuser sa command creates a user named sa (for system admin). The psql command puts us in the PostgreSQL terminal. This means that password sa, which sets the password for the user sa, is executed by PostgreSQL. You can then set the password for the sysadmin. You have to enter the password twice; the second time is a confirmation. The final command, q, gets us out of the PostgreSQL terminal.

In theory, this should be enough. Unfortunately, or maybe it is better this way, PostgreSQL has some pretty strict default settings when it comes to connecting. So, we will need to change some settings before we can connect from our host computer. The first file we need to change is pg_hba.conf. The following file path has a version in it. My version is 9.5(.5), but if you have another version (which you could see in the PostgreSQL terminal), change the file path accordingly. So change the file using sudo vi /etc/postgresql/9.5/main/pg_hba.conf. You might have to scroll a bit, but you should find a line that reads host all all 127.0.0.1/32 md5. In this line, replace 127.0.0.1 with 0.0.0.0/0. The next file we are going to change is postgresql.conf. So, open the file using sudo vi /etc/postgresql/9.5/main/postgresql.conf and mind the version number. This file is pretty big, but you can scroll to the bottom pretty quickly using the page down key. At the bottom, add the following line to the file listen_addresses='*'.

Last, but not least, we must restart PostgreSQL for the changes to take effect:

sudo systemctl restart postgresql