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.