TutorialsCourses

Use Before Insert Postgres Triggers as SQL Column Presets with Hasura

AuthorJason Brown

Introduction

Hasura offers a concept called Column Presets. These allow you to add static values, or values from the Hasura claims in the JWT upon insert/update. This means if a user creates an item we can have a created_by value inserted that is their user id.

Combined with select permissions this allows for the user to always create stuff that belongs to them and additionally access it.

There might be a time when the value you need to insert is not actually in the JWT. Hasura does not yet offer a way to reference the database for the column presets to retrieve a value. As a solution we will leverage Postgres functions.

The specific problem space for me is that one set of our uses logs in with Google via Auth0. These users also exist in our database with a UUID. I don't want to track people based upon their Auth0 user id, and additionally don't want to require a person to pass back a user_id for doing inserts.

The Solution Postgres Function

We will leverage Postgres functions. There is a lot of boilerplate and it can look intimidating but we'll break things down piece by piece so you can understand.

CREATE OR REPLACE FUNCTION public.staff_created_by_preset()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
DECLARE
  session_variables json;
  staff_id text;
  auth_zero_user_id text;
BEGIN
        session_variables := current_setting('hasura.user', 't');
        auth_zero_user_id := session_variables ->> 'x-hasura-user-id';
        SELECT id into staff_id FROM public.staff where "auth0_user_id" = auth_zero_user_id;
        NEW."createdBy" = staff_id;
	RETURN NEW;
END;
$$

The start tells Postgres a little bit of information. Specifically the name, what we are creating and the language. Postgres has additional languages you can use if installed, like python.

CREATE OR REPLACE FUNCTION public.staff_created_by_preset()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS

Not only does the $$ signs signify beginning and end of the function it has a meaning. The dollar-quoted string constraints are a way to inform Postgres that the following is all text.

Otherwise the whole function would be have to wrapped in single quotes. Then anytime you needed to use single quotes they would have to be escaped. So overall it's a nice method to define start/end and make it easier when dealing with strings.

$$

$$

Next we declare what variables we are going to use. In our case we have 3 variables. One for our Hasura session variables. Then we will pull out our hasura id from the variables and store it in another variable. Finally we will then query for our actual id we want to insert and store that in a separate variable.

We do need to declare the types, so in our case session_variables is json and the other 2 are just text.

DECLARE
  session_variables json;
  staff_id text;
  auth_zero_user_id text;

Then comes the actual function code.

BEGIN
        session_variables := current_setting('hasura.user', 't');
        auth_zero_user_id := session_variables ->> 'x-hasura-user-id';
        SELECT id into staff_id FROM public.staff where "auth0_user_id" = auth_zero_user_id;
        NEW."createdBy" = staff_id;
	RETURN NEW;
END;

The first line uses the current_setting(https://www.postgresql.org/docs/9.6/functions-admin.html) function. It's provided by Postgres. The hasura.user is set by hasura, and the t specifies that missing_ok. Meaning if the hasura.user setting isn't set during the query it's okay, and we will progress anyway. The current_setting('hasura.user', 't') call is only available during mutations, and is not available during queries.

The := is just how assignment is done. We are assigning the hasura user into session_variables which is json.

Next we need to pull out the x-hasura-user-id from our session_variables. We again use := assignment operator and then use ->> (get JSON object field as text) to instruct postgres to get the x-hasura-user-id and pass that back to our variable auth_zero_user_id. For more Postgres JSON operations you can see those here https://www.postgresql.org/docs/9.3/functions-json.html

session_variables := current_setting('hasura.user', 't');
auth_zero_user_id := session_variables ->> 'x-hasura-user-id';

Next we run our SQL statement. We search out staff table, where the column auth0_user_id is equal to our auth_zero_user_id variable from our session variables. The key is then selecting the id to be placed into our variable staff_id.

SELECT id into staff_id FROM public.staff where "auth0_user_id" = auth_zero_user_id;

During a function trigger there are helper variables that Postgres offers. For inserts you have access to NEW which is the new row that will be inserted. For UPDATE there would also have OLD which would be the same row but old values.

So here we can now set the field we want which is createdBy to our staff_id. Then finish of by returning NEW for Postgres to save it.

NEW."createdBy" = staff_id;
RETURN NEW;

Now all that is left to do is run in the Data > SQL tab and save it as a migration.

Create Trigger and Apply to Rows

Now that we have our function all setup we need to create our trigger. The function trigger can be re-used for many tables, but we need to tell Postgres what table to apply it to. Not only that we need to specify that it should run BEFORE INSERT. Additionally we want it to run for EACH ROW and execute our trigger function we setup before.

CREATE TRIGGER set_staff_id_preset
  BEFORE INSERT
  ON public.tasks
  FOR EACH ROW
  EXECUTE PROCEDURE public.staff_created_by_preset();

Triggers can be setup for more than just inserts. You can do stuff AFTER INSERT or BEFORE/AFTER UPDATE. We are also specifying for EACH ROW. This means if you insert 20 rows, this function will execute 20 times.

Ending

It's not often that you will need this, but we have effectively setup a way to leverage SQL queries as column presets in Hasura.

Home
Tutorials
Courses