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
  2. Download PostgREST binary
    Download the binary from link —
    eg. postgrest-v7.0.1-linux-x64-static.tar.xz
  3. Extract the bundle

4. Make the file executable and accessible from any directory

5. Verify successful installation of posgREST

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.

7. Create a table and insert data from psql

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

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

10. Create a jwt secret password

11. Create postgRESTconfiguration file

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

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

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

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

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




System Administrator and Full stack web developer.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store