Setting up Postgres

2023-06-25

In this post I am going to document the process of setting up a postgresql database on my machine to use in the expense tracking app. Here I am mainly following the several tutorials and documentation on the website and capturing any special steps I needed to do. The actual connection to the expense tracking app will be in a follow-up post.

Install and configure postgres

Installation

I followed the steps on the download page here to install the database via binaries. The steps here were fairly straightforward - the only issue I had was figuring out how to run the database. As it turns out, the database runs in the background automatically as a service so there’s no binary that I needed to run explicitly. Kind of gives me a hint as to how to deploy this as well - probably best done in a standalone docker container.

To check if the service is running I ran the following command:

usr@hostname: service postgresql status
‚óŹ postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Sun 2023-06-25 13:31:51 MDT; 1h 2min ago
   Main PID: 1654 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 16557)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Jun 25 13:31:51 bhavya-MS-7C95 systemd[1]: Starting PostgreSQL RDBMS...
Jun 25 13:31:51 bhavya-MS-7C95 systemd[1]: Finished PostgreSQL RDBMS.

Configuration

A thing that was not obvious to me at all was that postgresql actually has users with their own set of permissions etc. similar to users on the OS. However, the users are not the same as the OS and are also not automatically inherited - you need to create them explicitly. There is documentation which talks about how to manage these users. Having multiple users is kind of neat - it allows us to connect using a only a certain user and the access level, tables visible, operations allowed can be controlled very conveniently. This probably ties into the multi-tenant nature of the database.

In order to do anything with the DB you need to ’login’ using a user and default user is postgres. You can login using any user as follows:

psql -U <user>

However, out of the box this doesn’t work because postgresql is configured to use peer authentication by default for the postgres user. This is visible in the /etc/postgresql/<version>/<db>/pg_hba.conf file:

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

Consequently that the postgres user must be present as an OS user as well. This should be created during the installation process can the easiest way to find out if the user is there is to check if it exists in the /etc/passwd file:

cat /etc/passwd | grep postgres
postgres:x:1001:138::/home/postgres:/bin/bash

Once the user is setup, we first change the OS user to postgres, then run the psql command to drop into the command line for the database:

sudo su - postgres
postgres@host:~$ psql
psql (15.3 (Ubuntu 15.3-1.pgdg20.04+1))
Type "help" for help.

postgres=# 

To list the users available and their permissions, we can run the following command:

psql
psql (15.3 (Ubuntu 15.3-1.pgdg20.04+1))
Type "help" for help.

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

As a side note, this page is a good document on configuring user authentication in postgres, among other things.

Okay I think this is about it for this post. Now that the DB is up, I am going to play with it for a while to learn the basics and then connect to it using a Javascript server.