Managing Banking Transactions using PostgREST

In this chapter, we will learn how to develop PostgREST. It is a standalone web server that automatically generates usable RESTful APIs from any PostgreSQL databases directly. The API endpoints and operations are implemented by the permissions and structural constraints of the PostgreSQL database. PostgREST serves as an automatic solution to the manual CRUD (Create, Read, Update, and Delete) querying. 

The project in this chapter will use PostgREST and Docker to set up a RESTful API for banking transactions to send GET, POST, PUT, and DELETE curl requests into the PostgreSQL 12 Relational Database Service (RDS) referred to in Chapter 2, Setting Up a PostgreSQL RDS for ATM Machines, from Amazon Web Services (AWS) to create, retrieve, update, and delete ATM machine locations within a typical city...

Technical requirements

This chapter will take developers around 10-12 hours of working to develop PostgREST.

The figures and code files of this chapter are available at the following link: https://github.com/PacktPublishing/Developing-Modern-Database-Applications-with-PostgreSQL/tree/master/Chapter06.

Introduction to PostgREST

In this section, we are going to practice Docker installation and will learn how to start Docker as a service. We will download the PostgREST image from Docker to start the first launching of PostgREST.

Using Docker

Docker is a tool that uses containers to package an application with all of its libraries and other dependencies and ship it all out as one package:

1. We will PuTTY into our ec2 instance with the built-in user for our current AMI, that is, the centos user, with the help of the following command:

[centos@ip-172-31-95-213 ~]$ sudo su
[root@ip-172-31-95-213 centos]# cd /usr/local/src/

2. You must check whether Docker is installed. If it is not installed, then you can get it installed with the help of the following command:

[root@ip-172-31-95-213 src]# yum install -y yum-utils device-mapper-persistent-data lvm2
[root@ip-172-31-95-213 src]# yum-config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo
[root@ip-172-31-95-213 src]# yum install docker-ce

3. During the installation, type y (=Yes) on the terminal if you are asked any questions, as shown in Figure 6.1:

Figure 6.1 – Installing Docker

4. Add your user to the docker group with the help of...

Installing standalone PostgREST

After we have started the Docker daemon, we can run Docker commands without daemon connection errors:

1. Let's pull and start the PostgREST image from Docker as shown here:

[root@ip-172-31-95-213 src]# docker run --name tutorial -p 5432:5432 -e POSTGRES_PASSWORD=mysecretpassword -d postgres

This will run the Docker instance as a daemon and expose port 5432 to the host system so that it looks like an ordinary PostgreSQL server to the rest of the system, as shown in Figure 6.2:

Figure 6.2 – Pulling a PostgREST image from Docker

2. Next, we will connect to the SQL console (psql) inside the container with the help of the following command:

[root@ip-172-31-95-213 src]# docker exec -it tutorial psql -U postgres
psql (11.5 (Debian 11.5-1.pgdg90+1))
Type "help" for help.
postgres=#

Once the preceding command is executed, we will see the psql command prompt. You can start to create your database schema and tables here so that later...

Creating a PostgREST API schema on an RDS (AWS)

We will install PostgREST on a CentOS ec2 instance, and then we will set up PostgREST to create an API for our PostgreSQL version RDS on the AWS cloud:

1. Let's install PostgREST from the binary release by using the command shown in the following code block:

[root@ip-172-31-95-213 src]# yum install postgresql-libs
Please answer y (=Yes) when being asked,
[root@ip-172-31-95-213 src]# wget https://github.com/PostgREST/postgrest/releases/download/v6.0.1/postgrest-v6.0.1-centos7.tar.xz
[root@ip-172-31-95-213 src]# tar xfJ postgrest-v6.0.1-centos7.tar.xz
[root@ip-172-31-95-213 src]# mv postgrest /usr/local/bin/
[root@ip-172-31-95-213 src]# rm postgrest-v6.0.1-centos7.tar.xz

2. We will now define a user role that has permission to access the RDS ATM database and to read records from the ATM locations table, as shown:

create role web_anon nologin;
grant web_anon to dba;

grant usage on schema public to web_anon;
grant select on public."ATM locations...

Executing PostgREST

We are now putting together what we have set up until now, the database schema, the user role, and the PostgreSQL version 12 connection of the RDS, into PostgREST in the following steps:

1. We will create the PostgREST configuration file as shown and will name it tutorial.conf:

[root@ip-172-31-95-213 src]# mkdir postgrest
[root@ip-172-31-95-213 src]# cd postgrest
[root@ip-172-31-95-213 postgrest]# vi tutorial.conf
db-uri = "postgres://dba:[e-mail]/atm" db-schema = "public" db-anon-role = "web_anon"

2. Now, we will call PostgREST to create an API on our RDS with the help of the following command:

[root@ip-172-31-95-213 postgrest]# postgrest tutorial.conf

PostgREST will start listening on port 3000 after it is connected to the RDS, hence the automatic API on the ATM database has been established. After you run the preceding command, your console will look as shown in the following screenshot...

Adding a trusted user

In order to perform more data manipulation on PostgREST, we will have to employ user roles with all privileges granted on the ATM locations table: 

1. First, we will execute the following query using pgAdmin to create a new role: 

create role atm_user nologin;
grant atm_user to dba;

grant usage on schema public to atm_user;
grant all on public."ATM locations" to atm_user;
grant usage, select on sequence public."ATM locations_ID_seq" to atm_user;

On execution of the preceding script, the result will be as shown in Figure 6.6:

Figure 6.6 – Adding a trusted user

2. Next, we will set a password and provide it to PostgREST. You can use the following statements to generate a random password or you can think out a nice password as well; this password must be at least 32 characters long:

[root@ip-172-31-95-213 postgrest]# export LC_CTYPE=C
[root@ip-172-31-95-213 postgrest]# < /dev/urandom tr -dc A-Za-z0-9 | head -c32...

Creating a PostgREST token

In order to make efficient usage of the trusted user and the password of PostgREST, we will combine these two values into a token so that any API requests carrying that token will be accepted by PostgREST as the correct password and correct trusted user:

Figure 6.9 – Creating a token

We'll get started with creating the token using the following steps:

1. We will first open https://jwt.io on the browser and fill in the required details as shown:
   • Password: DFZ49GQGubpzcSbt3t2uMIiBF6pU4PJ8
   • JSON: "role": "atm_user"
   • Copy the result token: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYXRtX3VzZXIifQ.ZL8hsLj5cewZvgb81EXm5vC3Jpn_4TzpszwMBStu-xo

You can see the preceding values are entered in Figure 6.9.

2. We will now write the following command to be able to use the encoded token to add a new ATM location, on the other terminal (not the one that PostgREST is running inside):

[centos@ip-172-31-95-213 ~]$ export...

PostgREST administration

We will now set up PostgREST so that whenever we start our ec2 instance, the API service will automatically start:

1. We will configure PostgREST as a service with the help of the following command:

[root@ip-172-31-95-213 postgrest]# mkdir /etc/postgrest
[root@ip-172-31-95-213 postgrest]# vi /etc/postgrest/config
------------------------------------------
 db-uri = "postgres://dba:[e-mail]/atm"
 db-schema = "public"
 db-anon-role = "web_anon"
 db-pool = 10
 server-host = "127.0.0.1"
 server-port = 3000
 jwt-secret = "DFZ49GQGubpzcSbt3t2uMIiBF6pU4PJ8"
------------------------------------------

After the preceding command is executed, the console will look as shown in Figure 6.15:

Figure 6.15 – Configuring PostgREST as a service

2. Then create the systemd service file by using the following command:

[root@ip-172-31-95-213 postgrest]# ln -s /usr/local/bin/postgrest...

PostgREST on TimescaleDB

TimescaleDB is an open source database for time series data; we first heard about TimescaleDB when we investigated standalone PostgREST because it had TimescaleDB as a built-in extension. Obviously, PostgREST is also able to create a timing API for TimescaleDB.

Unfortunately, the PostgreSQL RDS does not support TimescaleDB. If we would like to install TimescaleDB for AWS, we will have to use the recently invented Timescale Cloud to connect to AWS. Timescale Cloud can support TimescaleDB for AWS:

1. Please visit the following link to sign up for a new user account at Timescale Cloud: https://www.timescale.com/cloud-signup.
2. Please enter your full name, email address, and password for Timescale Cloud: 

Figure 6.17 – Registering for a new Timescale Cloud user account

3. Please press the Create an account button, which will lead you to the Email confirmed screen: 

Figure 6.18 – Email confirmed screen

4. Please find the Timescale...

Summary

In this chapter, we have implemented PostgREST step by step to create an automatic API service for PostgreSQL version 12. By working through this chapter, developers have also practiced Docker usage, Docker and SQL, PostgREST first execution, database users and roles, and JSON Web Token. We also learned how to run PostgREST as a service. 

The second part of the chapter focused on introducing PostgreSQL timing data with TimescaleDB from Timescale Cloud, a new extension related to PostgREST. This chapter concludes the PostgreSQL development part of the book.

In the next chapters, we will focus on PostgreSQL administration.