Postgresql installation and setup

Postgresql is one of the most popular opensource relational database systems available. It's been around for a long time and is used by large institutions.

The install

I'm doing this on an Ubuntu Server 24.04 LTS virtual machine.

To install, run - $ sudo apt install postgresql

This will install the latest version for the distro, which is version 16.9.

Also, install postgresql-contrib for management.

Setup

After the previous steps, you should have a functioning postgresql database server.

Connecting to the server

From localhost

From the local machine, run - $ sudo su - postgres

This will switch to the postgres user that was created during the installation of the postgresql package.

You can now run psql - $ psql

The prompt will change to - postgres=#

This will be your interface to the server to issue commands.

From the network

To allow connections from hosts/clients on the network, we need to change a few settings.

postgresql.conf

This will be at /etc/postgresql/16/main

Change listen-addresses = '*'

pg_hba.conf

This stands for 'host-based athentication', and configures how clients can connect to the database.

Add this entry:

host all all 192.168.1.0/24 scram-sha-256

This translates to 'allow connections to any database from any user on the 192.168.1.0/24 network with encryption (scram-sha-256)'

Also, add this line to enable you to login

Create a database

postgres=# create database testdb;

Create user

Normal user

postgres=# create user testuser with password 'supersecretpassword123';

Super user

postgres=# create user dbadmin with superuser password 'super#!secret@$password';

This grants the new user with superuser privileges.

Grant privileges

postgres=# grant all privileges on database testdb to testuser;

You can exit the psql prompt by typing exit.

Connecting to the database

At this point you can connect to the database using our newly created user to the newly created database.

$ psql -h localhost -U testuser -d testdb

If everything worked, you should see the psql prompt - testdb=>

If you noticed, the prompt has changed from =# to =>

Create tables

To be able to create tables, you need to be granted testdb=> create table hotspots ( id serial primary key, name varchar(100), description varchar(255), address varchar(255), phone varchar(15), rating numeric(2), );