Skip to content

5min Intro

Overview

One hour tutorial? Ain't Nobody Got Time for That! Ok, ok, I hear you, time is money, that's why we'll concentrate on getting something up and running fast, the essence of the system. We won't bother with git, migrations, test, loose model-api coupling, CI and all those other nice and important things for a production system (we'll do that in the next tutorial). We'll build a barebones API for a to-do app. We'll have only two models, "todo" and "user" and the system will provide signup and login for users, and they will be able to perform CRUD operations on the to-do's.

Database Instance

The first thing we'll need is a database. subZero service runs in AWS us-east-1 so that's where we'll create our PostgreSQL RDS instance to have low latency between the database and subZero (other regions will be available). If you have a spare/test PostgreSQL database server in another AWS region (or even outside of AWS), you can skip a few steps and use it for this tutorial, you'll just see a slight increase in latency.

Create the database

If you prefer the AWS UI, you can follow this step by step tutorial to bring up your db. Alternatively, if you have aws-cli installed, run these commands

aws rds create-db-instance \
    --region us-east-1 \
    --db-instance-identifier five-min-db \
    --db-name app \
    --db-instance-class db.t2.micro \
    --engine postgres \
    --allocated-storage 20 \
    --publicly-accessible \
    --no-multi-az \
    --master-username master \
    --master-user-password SET-YOUR-RDS-MASTER-PASSWORD-HERE

Configure the security group

Right now the database is being created so we'll use that time to add a rule to the database security group to allow subZero service to connect to it.

The short version: Get the database security group id

export SG_ID=`aws rds describe-db-instances --db-instance-identifier five-min-db --output text --query 'DBInstances[0].VpcSecurityGroups[0].VpcSecurityGroupId'`
Add a rule to allow outside connections to the database
aws ec2 authorize-security-group-ingress \
    --group-id $SG_ID \
    --protocol tcp \
    --port 5432 \
    --cidr 0.0.0.0/0

The long UI version:

Click to your database instance details page and find it's security group (and click the link) Database security group

On the security group page, click the Inbound tab then click the edit button Edit security group

Click the Add Rule button and add the row (PostgreSQL) like in the image Add rule

Note

It's possible that your database was added to a common default VPC security group so if you plan to keep this database after the tutorial, it's a good idea to create a separate security group for the database and add the ingress rule there (and remove it from the default SG)

Connect to the database

If you are using the UI method, follow Step 4 in the tutorial. The cli version is:

export DB_HOST=`aws rds describe-db-instances --db-instance-identifier five-min-db --output text --query 'DBInstances[0].Endpoint.Address'`

connect uisng psql

psql postgres://master@$DB_HOST:5432/app

Create the needed database roles

subZero does not need or use your database master password, we'll create a separate user with no privileges, the convention is to use authenticator name. Run this SQL query on the database

CREATE ROLE authenticator WITH LOGIN PASSWORD 'password-goes-here' NOINHERIT;

We'll also need a role under which to execute queries coming from unauthenticated users.

create role anonymous;
grant anonymous to authenticator;

The database is ready; let's move to the next step.

subZero Account

Go to the signup page and create your account then login. Once in, click the big "plus" button and follow the wizard.

Choose a custom subdomain for your API w

Provide the database connection info (remember to use the authenticator role here) w

Fill in the settings as shown below (and write down your JWT secret key, we'll need it later) w

For now, we don't need to send our database events for processing, so we'll go with "None" w

Once your infrastructure is up and running (it might take up to 5min) you'll see a page like the one below

w

Open the page https://your-subdomain-here.subzero.cloud

w

and give GraphiQL IDE a spin (there is a link to it on the previous page)

w

Toy Schema

So now we have our API live, but it's empty, so let's start our clock and see how long it will take us to get a to-do api ready.

Install PGJWT functions

To be able to create JWT tokens from our login function we'll use the (slightly altered) pgjwt lib. Run these queries in your db

create extension if not exists pgcrypto schema public;
drop schema if exists pgjwt cascade;
create schema pgjwt;
set search_path to pgjwt, public;

CREATE OR REPLACE FUNCTION url_encode(data bytea) RETURNS text LANGUAGE sql AS $$
    SELECT translate(encode(data, 'base64'), E'+/=\n', '-_');
$$;


CREATE OR REPLACE FUNCTION algorithm_sign(signables text, secret text, algorithm text)
RETURNS text LANGUAGE sql AS $$
WITH
  alg AS (
    SELECT CASE
      WHEN algorithm = 'HS256' THEN 'sha256'
      WHEN algorithm = 'HS384' THEN 'sha384'
      WHEN algorithm = 'HS512' THEN 'sha512'
      ELSE '' END AS id)  -- hmac throws error
SELECT pgjwt.url_encode(hmac(signables, secret, alg.id)) FROM alg;
$$;


CREATE OR REPLACE FUNCTION sign(payload json, secret text, algorithm text DEFAULT 'HS256')
RETURNS text LANGUAGE sql AS $$
WITH
  header AS (
    SELECT pgjwt.url_encode(convert_to('{"alg":"' || algorithm || '","typ":"JWT"}', 'utf8')) AS data
    ),
  payload AS (
    SELECT pgjwt.url_encode(convert_to(payload::text, 'utf8')) AS data
    ),
  signables AS (
    SELECT header.data || '.' || payload.data AS data FROM header, payload
    )
SELECT
    signables.data || '.' ||
   pgjwt.algorithm_sign(signables.data, secret, algorithm) FROM signables;
$$;

Data Model

And now the meat of the API, the thing we are here for. First we create the api schema and the core tables/models that will hold our data.

drop schema if exists api cascade;
create schema api;

-- core models
create table api.users (
    id                   serial primary key,
    email                text not null unique,
    password             text not null,
    check (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$')
);

create table api.todos (
    id    serial primary key,
    todo  text not null,
    owner_id int references api.users(id) default current_setting('request.jwt.claim.user_id', true)::int
);

Authentication

Let's create the functions handling login / signup functionality. I bet even if you've never written a Pl/pgSQL function in your life, you can figure out in 2 seconds what's going on. Remember to replace JWT-SECRET-GOES-HERE with the JWT secret you used when creating the subZero app. I know, I know, hardcoding secrets in code is a big no-no (and of course there is a better way than this) but we are going for "fast" here so bear with me.

-- login / signup
create type api.session as (me json, token text);

create or replace function public.new_session(usr api.users) returns api.session as $$
begin
    return (
        json_build_object(
            'user_id', usr.id,
            'email', usr.email
        )::json,
        pgjwt.sign(
            json_build_object(
            'role', 'customer',
            'user_id', usr.id,
            'exp', extract(epoch from now())::integer + 3600
            ),
            'JWT-SECRET-GOES-HERE'
        )
    );
end
$$ stable language plpgsql;

create or replace function api.login(email text, password text) returns api.session as $$
declare
    usr api.users;
begin
    select *
    from api.users as u
    where u.email = $1 and u.password = public.crypt($2, u.password)
    into usr;

    if usr is NULL then
        raise exception 'invalid email/password';
    else
        return public.new_session(usr);
    end if;
end
$$ stable security definer language plpgsql;
revoke all privileges on function api.login(text, text) from public;


create or replace function api.signup(email text, password text) returns api.session as $$
declare
    usr api.users;
begin
    insert into api.users as u
    (email, password) values
    ($1, public.crypt($2, public.gen_salt('bf')))
    returning u.* into usr;

    return public.new_session(usr);
end
$$ security definer language plpgsql;
revoke all privileges on function api.signup(text, text) from public;

Authorization

Just because there are tables and functions in our api schema, does not mean people can just start accessing them. We need to give each role (anonymous and customer) specific priveleges.

-- privileges
drop role if exists customer;
create role customer;
grant customer to authenticator;

-- allow customers to perform all CRUD operations
-- notice how we don't allow access to owner_id field
grant usage on schema api to customer;
grant
  select(id, todo), 
  insert(todo), 
  update(todo), 
  delete 
on api.todos to customer;
grant usage on sequence api.todos_id_seq to customer;

-- allow customers to access only their rows
alter table api.todos enable row level security;
create policy todos_access_policy on api.todos to customer 
using ( current_setting('request.jwt.claim.user_id', true)::int = owner_id );

-- allow anonymous users to access login/signup
grant usage on schema api to anonymous;
grant execute on function api.login(text,text) to anonymous;
grant execute on function api.signup(text,text) to anonymous;

Now that we changed the schema in our database, we need to refresh it (subZero caches your database schema structure).

w

Pencils Down

Take it for a spin

Open the GraphiQL IDE and try these GraphQL queries. Note that each query has a corresponding REST request, we'll try them out in the next tutorial.

mutation {
  signup(email: "alice@email.com", password: "pass"){
    me
  }
}

{
  login(email: "alice@email.com", password: "pass"){
    me
  }
}
mutation {
  insert {
    todo(input:{todo: "Complete the tutorial"}){
      id
      todo
    }
  }
}
mutation {
  insert {
    todos(input:[
      {todo: "Give it a spin"},
      {todo: "Check out the REST API"}
    ]){
      id
      todo
    }
  }
}
{
  todo(id:1){
    todo
  }
}
{
  todos(
    where:{
      id:{gte: 2}
    }
  ){
    id
    todo
  }
}
{
  todos(
    where:{
      todo:{ilike: "*REST*"}
    }
  ){
    id
    todo
  }
}

That's all Folks

Belive it or not, we are done, that's the entire code needed to power this toy API. There is no ball of mud that we are somehow loading into your database behinde the scene (remember the authenticator user has no privileges?). If it weren't for the login/signup functionality (and JWT related code), we'd basically have only the table definitions and the security policies (grant and policy statements).

Now I know people with some backend experience are thinking things like "OMG, there are so many steps and good practices skipped here, this will turn bad. There is no separation between the data model and the api, there is no input validation, not to mention that debuging, testing and deploying code in the database is going to be a special kind of torture and don't even get me started on business logic in the database, I've read those articles".

Before you close the browser and say "well this is 5 minutes I am not getting back", let me just say two things:

  • This tutorial is to demonstrate the absolute essence of the system, it is not something you would put in production and running queries directly on your live database is not how you deploy the code.

  • Step back for a second and appreciate how little code was required to go from a simple table in your database to a quite powerful and flexible API that is capable of understanding both REST & GraphQL and has some basic authentication/authorization mechanism in place and you have full control of that mechanism, it does not come "as a service".

If I've sparked your curiosity and you have some time to spare, go to the next tutorial and see how a production system is setup with this stack.