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.
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
json and the other 2 are just
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.
:= is just how assignment is done. We are assigning the hasura user into
session_variables which is
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
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.
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
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.
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.