Welcome. We’re going to implement a sample REST API quickly using PostgREST.

Prerequisites

This is the list of all the prerequisites:

• Docker installed
• Docker compose is installed
• Postman / insomnia or any other API testing tool.

Overview

What is PostgREST?

PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. The structural constraints and permissions in the database determine the API endpoints and operations. CRUD endpoints (create, read, update and delete) no longer need to be manually created, which reduces the complexity of backend systems.

Setup PostgREST with Docker

There are several ways to install PostgREST (Binary OS source, Docker, and docker-compose, Deploying to Heroku). We’ll use Docker containers. We will create a docker-compose YAML file containing all the instructions to run the services PostgreSQL database, PostgREST, and Swagger OpenAPI.

# docker-compose.yml
version: '3'
services:

# postgrest-db
  db:
    image: postgres
    ports:
      - "5433:5432"
    environment:
      POSTGRES_DB: bookdb
      POSTGRES_USER: bookuser
      POSTGRES_PASSWORD: password
    volumes:
      - "./pgdata:/var/lib/postgresql/data"
      - "./scripts:/docker-entrypoint-initdb.d"
      
  server:
    image: postgrest/postgrest
    ports:
      - "3000:3000"
    environment:
      PGRST_DB_URI: postgres://bookuser:password@db:5432/bookdb
      PGRST_DB_SCHEMA: rest
      PGRST_DB_ANON_ROLE: anonymous
      PGRST_OPENAPI_SERVER_PROXY_URI: http://127.0.0.1:3000
    depends_on:
      - db
      
  swagger:
   image: swaggerapi/swagger-ui
   ports:
    - "8080:8080"
   expose:
    - "8080"
   environment:
    API_URL: http://localhost:3000/ 

The local ./scripts folder contains the database initialization scripts that will be executed once the Postgres instance is started.

Let’s take a look at the SQL script scripts/init-db.sql:

CREATE SCHEMA rest;

SET search_path TO api;

drop table if exists rest.author;
drop table if exists rest.book;

create table rest.author (
    id serial primary key,
    firstname character varying(255) COLLATE pg_catalog."default",
    lastname character varying(255) COLLATE pg_catalog."default"
);

create table rest.book (
    id serial primary key,
    description text,
    isbn character varying(255) COLLATE pg_catalog."default",
    page integer NOT NULL,
    price double precision NOT NULL,
    title character varying(100) COLLATE pg_catalog."default",
    author_id integer not null references rest.author(id)
);

-- init data 
INSERT INTO rest.author (id,firstname,lastname)
VALUES
  (1,'Bree','Nasim'),
  (2,'Kessie','Brenden'),
  (3,'Willow','Kirby'),
  (4,'Lareina','Lunea'),
  (5,'Flavia','Zane'),
  (6,'Noah','Maxwell'),
  (7,'Kelsey','Clinton'),
  (8,'Gage','Marsden'),
  (9,'Perry','Elijah'),
  (10,'Kennedy','Clementine');
  
  INSERT INTO rest.book (id,description,isbn,page,price,title,author_id)
VALUES
  (1,'netus et malesuada','X4J 5H8',62,529,'arcu. Vestibulum ut',9),
  (2,'mollis non,','M3Q 4G1',15,668,'Nullam ut',2),
  (3,'Maecenas mi felis, adipiscing fringilla, porttitor','B5W 1Y8',16,708,'et ipsum cursus',5),
  (4,'eros turpis non enim. Mauris quis turpis','Q1O 7Y6',46,642,'Nulla tincidunt,',4),
  (5,'tellus non magna. Nam ligula elit, pretium','Q0V 7Q9',86,656,'purus, in',1),
  (6,'a, facilisis non, bibendum sed, est.','V6Q 8T2',57,299,'sagittis',3),
  (7,'suscipit nonummy. Fusce fermentum fermentum arcu. Vestibulum ante ipsum','Q2T 8C5',68,891,'ligula. Donec',8),
  (8,'arcu. Vivamus sit amet risus. Donec egestas.','R5E 3I4',14,455,'vel',6),
  (9,'pede, nonummy ut, molestie in, tempus','I0W 6N9',33,874,'lorem semper',8),
  (10,'sed consequat auctor, nunc nulla vulputate dui, nec','U4E 5V8',7,185,'vel arcu.',4);

-- Create User-Group Roles
-- grant anonymous role access to certain tables etc
CREATE ROLE anonymous nologin;
CREATE ROLE webuser nologin;

-- creation of the authenticator role
CREATE ROLE authenticator WITH NOINHERIT LOGIN PASSWORD 'vfx44M4$l$Fu';

GRANT anonymous TO authenticator;
GRANT webuser TO authenticator;

GRANT USAGE on SCHEMA rest to anonymous;
GRANT SELECT on rest.author to anonymous;
GRANT SELECT on rest.book to anonymous;

GRANT ALL on SCHEMA rest to webuser;
GRANT ALL on rest.author to webuser;
GRANT ALL on rest.book to webuser;

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA rest TO webuser;

The first thing we’ll do is create a schema named “rest” for the database objects which will be exposed to the PostgREST service. We have two tables author and book with a one-to-many mapping between the tables.

The next section consists to create database roles. The anonymous role has permission to access things in the rest schema, and to read rows in the author and book tables. The webuser role has read, write, update, and delete rights to the schema and its tables.

Once you’ve created yml and configured the database script, open your CLI and run the following command:

docker-compose up -d

We start by opening a visual preview of our API in your browser with swagger-ui. http://localhost:8080

It’s now ready to serve requests. Let’s try to retrieve the list of authors and books from the database.

Done. It works fine. 🙂
As mentioned above, the anonymous role does not have permission to write to the database. We cannot add a new author.

JSON Web Token (JWT) Authentication

All PostgREST authorization happens through database roles and permissions. There are three types of roles used by PostgREST, the authenticator, anonymous, and user roles.

We use JSON Web Tokens (JWT) to authenticate API requests.

We’ll start by modifying our docker-compose.yml file and adding a new configuration to allow users to authenticate to our API.

https://boottechnologies-ci.medium.com/media/52a28c8c5c191cc282ef08359d3d680e

The first thing is the addition of a new extension on our Postgres instance which will allow cryptographically signing passwords on our server. We’ll create JWT tokens in SQL using the pgjwt extension.

Then the second thing is the creation of a new schema in our database which will manage all the authentications. It is composed of a user table that will manage our users. Finally, we add all the functions that allow us to manage user access.

ALTER DATABASE bookdb SET "app.jwt_secret" TO 'Q!6HLp@B5wD24Pbq*LNd!%S4&H%ly7bt';

-- add custom extensions
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pgjwt;


-- Authentication management schema
CREATE SCHEMA IF NOT EXISTS auth;

-- users table
CREATE TABLE IF NOT EXISTS auth.users (
  email			  TEXT PRIMARY KEY CHECK ( email ~* '^.+@.+\..+$' ),
  password	  TEXT NOT NULL CHECK (LENGTH(password) < 512),
  role			  NAME NOT NULL CHECK (LENGTH(role) < 512)
);


CREATE OR REPLACE FUNCTION auth.check_role_exists() RETURNS TRIGGER AS $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles AS r WHERE r.rolname = new.role) THEN
    raise foreign_key_violation USING message =
      'unknown database role: ' || new.role;
    RETURN NULL;
  END IF;
  RETURN new;
END
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS ensure_user_role_exists ON auth.users;
CREATE CONSTRAINT TRIGGER ensure_user_role_exists
  AFTER INSERT OR UPDATE ON auth.users
  FOR EACH ROW
  EXECUTE PROCEDURE auth.check_role_exists();

CREATE OR REPLACE FUNCTION
auth.encrypt_password() RETURNS trigger AS $$
BEGIN
  IF tg_op = 'INSERT' OR new.password <> old.password THEN
    new.password = crypt(new.password, gen_salt('bf'));
  END IF;
  RETURN new;
END
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS encrypt_password ON auth.users;
CREATE TRIGGER encrypt_password
  BEFORE INSERT OR UPDATE ON auth.users
  FOR EACH ROW
  EXECUTE PROCEDURE auth.encrypt_password();
  
-- add new type
DROP TYPE IF EXISTS auth.jwt_token cascade;
CREATE TYPE auth.jwt_token AS (
  token TEXT
);

-- login should be on our exposed schema
CREATE OR REPLACE FUNCTION
rest.login(email text, password text) RETURNS auth.jwt_token AS $$
DECLARE
  _user auth.users;
  result auth.jwt_token;
BEGIN
  -- check email and password
  SELECT users.* FROM auth.users
   WHERE users.email = login.email
     AND users.password = crypt(login.password, users.password)
  INTO _user;
  IF NOT FOUND THEN
    raise invalid_password USING message = 'invalid user or password';
  END IF;

  SELECT sign(
      row_to_json(r), current_setting('app.jwt_secret')
    ) AS token
    FROM (
      SELECT _user.role AS role, login.email AS email,
         extract(epoch FROM now())::INTEGER + 60*60 AS exp
    ) r
    INTO result;
  RETURN result;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION
rest.signup(email text, password text) RETURNS void
AS $$
BEGIN
  INSERT INTO auth.users (email, password, role) VALUES
    (signup.email, signup.password, 'webuser');
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT USAGE ON SCHEMA auth TO anonymous, webuser;
GRANT EXECUTE ON FUNCTION rest.login(text,text) TO anonymous;
GRANT EXECUTE ON FUNCTION rest.signup(text,text) TO anonymous;

Let’s restart our docker services and then test.

We need to create a user account.

We need now login to have a token. Function paths must be prefixed with /rpc.

We will need a Bearer Authorization with jwt token to make our requests. Let’s try to create a new author.

Congratulations! It’s done.

Conclusion

In this post, we created a Rest API without writing any code or using a web framework with PostgREST. It is really convenient to use to quickly produce simple REST APIs with fewer resources.

The complete source code is available on GitHub.

Happy coding!

References

• https://github.com/PostgREST/postgrest
• https://postgrest.org/en/stable/index.html
• https://github.com/michelp/pgjwt

👉 Link to Medium blog