PostgREST configuration — A Beginners Guide

Do you want to develop backend REST API server for your tables residing in PostgreSQL database using Haskell programming language?

You can do it using PostgREST without writing a single line of Haskell code.

About PostgREST ?

1. PostgREST is a standalone web server that turns a PostgreSQL database directly into a RESTful API.
2. It provides a cleaner, standards-compliant, faster API.
3. Security — PostgREST handles authentication (via JSON Web Tokens) and delegates authorization to the role information defined in the database.
4. Performance —
   a) Postgrest is written in Haskell using the Warp HTTP server (aka a compiled language with lightweight threads).
   b) It delegates as much calculation as possible to the database including Serializing JSON responses directly in SQL, Data validation Authorization,Combined row counting and retrieval.
   c) Uses Hasql library for keeping a pool of db connections and uses postgreSQL binary protocol.

Objective — PostgREST installation and configuration on RHEL7

1. Install PostgreSQL
   Refer Article https://hpratap.medium.com/install-postgresql13-on-rhel7-202b1d03cd94
2. Download PostgREST binary
   Download the binary from link — https://github.com/PostgREST/postgrest/releases/latest
   eg. postgrest-v7.0.1-linux-x64-static.tar.xz
3. Extract the bundle

SYNTAX
#tar xfJ postgrest-<version>-<platform>.tar.xz
Example
#tar xfJ postgrest-v7.0.1-linux-x64-static.tar.xz

4. Make the file executable and accessible from any directory

# chmod 755 posgrest
# mv posgrest /usr/local/bin

5. Verify successful installation of posgREST

$ postgrest --help

Now, we will create schema, roles and table for the postgreSQL database. We will use the default database postgres, to create table required by our web application.

6. Create a named schema for database object.

$psql -U postgres
postgres=# create schema api;

7. Create a table and insert data from psql

create table api.student(
id serial primary key,
name text not null,
age integer not null,
section text,
address text
);
insert into api.student(name,age,section,address)
values (‘Rohan’, 8, ‘IIA’, ‘Goregaon’), (‘Rishi’, 9, ‘IIIB’, ‘Malad’), (‘Rihana’, 8, ‘IIB’, ‘Jogeshwari’);

8. Create a role for anonymous web requests. (Read only right to table data)

postgres=# create role web_anon nologin;
postgres=# grant web_anon to postgres;
postgres=# grant usage on schema api to web_anon;
postgres=# grant select on api.student to web_anon;

9. Create a role who authenticate with API and can modify table data.

postgres=# create role student_user nologin;
postgres=# grant student_user to postgres;
postgres=# grant usage on schema api to student_user;
postgres=# grant all on api.student to student_user;
postgres=# grant usage, select on sequence api.student_id_seq to student_user;

10. Create a jwt secret password

$ openssl rand -base64 32

11. Create postgRESTconfiguration file

$ vim myconf.conf
db-uri = "postgres://postgres:mysecretpassword@localhost/postgres"
db-schema = "api"
db-anon-role = "web_anon"
jwt-secret = "<the password you created>"

12. Restart the postgREST server and verify read request access by annonymous user.

$ postgrest myconf.conf
$ curl http://localhost:3000/student

13. Create a bearer token manually
Got to jwt.io , Under debugger form modify fields as given below and copy the resulting token.

Verify Signature = your_jwt_secret_password
 Payload = {"role": "student_user"}
 Header =  {"alg": "HS256", "typ": "JWT"}

14. Make a POST request to insert new data to the table.

To insert single data 
$ curl http://localhost:3000/student -X POST \
     -H "Authorization: Bearer $TOKEN"    \
     -H "Content-Type: application/json"  \
     -d '{"name":"Rhea", "age":7}'
To insert multiple data
$ curl http://localhost:3000/student -X POST \
     -H "Authorization: Bearer $TOKEN"    \
     -H "Content-Type: application/json"  \
     -d '[{"name":"Ranjana", "age":10, "section":"IVA"}, "name":"Rashmi", "age":9, "section":"IVB"}]'

Thats It!

In next article i will show how to access these API using a ReactJS application.

Troubleshoot guide

1. To drop created roles — web_anon and student_user

postgres=# revoke usage on schema api from web_anon;
postgres=# revoke all on api.student from web_anon;
postgres=# drop role web_anon;
postgres=# revoke usage on schema api from student_user;
postgres=# revoke all on api.student from student_user;
postgres=# revoke all on sequence api.student_id_seq from student_user;
postgres=# drop role student_user;

2. If you drop the table and recreate it. You will need to run grant commands again for roles having access to the table.

postgres=# grant select on api.student to web_anon;
postgres=# grant all on api.student to student_user;
postgres=# grant usage, select on sequence api.student_id_seq to student_user;

References:

https://postgrest.org/en/v7.0.0/tutorials/tut0.html