GraphQL Authorization with Hasura: How to prevent mutations after a time interval

Recently, a user got in contact with us to ask about how to solve a particularly challenging issue they were facing:

I have a requirement to disallow my users to create/update/delete a record in a table after a certain period of time. To be more specific, I have an application that allows my users to predict who will win a sports tournament, which needs to be made before the tournament starts. Obviously I want to prevent them from changing their prediction after the tournament starts.

As far as I am aware it is not possible to do this with Permissions because you can only check against "static" values (or hasura session variables), but I need to check if the start_date of the Tournament is <= today, where today obviously is NOT static. I guess my first question is if this assumption that this cannot be done with Permissions is correct?

Fortunately for this person, their assumption was NOT correct!

In this post I cover three approaches to solve this problem:

• Approach #1 (Postgres Triggers)
• Approach #2 (Hasura's "Column Comparison" permission operators)
• Bonus Approach (Postgres "Check Constraints")

General Table Layout

The table structures we're assuming for the purposes of this post will be as follows:

CREATE TABLE football_tournament (
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  start_date timestamptz NOT NULL,
  name text NOT NULL
);

CREATE TABLE football_tournament_prediction (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    football_tournament_id int NOT NULL REFERENCES football_tournament (id),
    prediction text NOT NULL
);

Approach #1 (Postgres Triggers)

This solution uses native Postgres triggers to validate row input during creation and when updating.

Depending on your familiarity and comfort level with SQL, this approach may seem more or less appealing than the approach which uses Hasura's features.

What we'll do is ask Postgres to run a function every time that a football_tournament_prediction row is created or updated.

This function will check that the football_tournament the prediction is attached to, has a start_date which hasn't happened yet.

Writing that in pl/pgSQL looks something like this:

CREATE FUNCTION ensure_predication_tournament_date_is_before_now() RETURNS trigger AS $$
    DECLARE
      tournament football_tournament;
    BEGIN
        SELECT INTO tournament * FROM football_tournament WHERE id = NEW.football_tournament_id;

        IF tournament.start_date <= now() THEN
            RAISE EXCEPTION 'Tournament is happening or has already happened';
        END IF;

        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ensure_predication_tournament_date_is_before_now_trigger
    BEFORE INSERT OR UPDATE
    ON football_tournament_prediction
    FOR EACH ROW
    EXECUTE PROCEDURE ensure_predication_tournament_date_is_before_now();

If we take these statements, and run them from the "SQL" page (/console/data/sql):

And then we create a football_tournament row which has a date in the past, let's say 01-01-2020:

Then finally, we attempt to create a prediction for this football tournament:

Ta-da! 🎉 It won't let us. Which we're happy about!

Note: If you're following along and plan to try both options out, at this point you should delete the Postgres trigger so that you can configure the second option without interference:

DROP TRIGGER ensure_predication_tournament_date_is_before_now_trigger ON football_tournament_prediction;

Approach #2 (Hasura's "Column Comparison" permission operators)

As opposed to the DB-centric approach given above, this variant uses Hasura's permission operators to emulate a similar sort of constraint.

NOTE: Off the bat, it may be useful to note that this type of constraint can ONLY verify integrity within the bounds of Hasura's permission and roles system.

That means that it does not prevent ill-formed or invalid records from being inserted (intentionally or accidentally) by admins or roles which don't have access restricted.

What we will do is go to the "Permissions" tab of the football_tournament_predictions table, and for the role (here we use user) say that no records may be inserted/updated if the result of the SQL now() function is less than the football_tournament.start_date.

To do this, configure the permission as below:

Now, if we attempt to insert a new football_tournament_prediction as role user, we can see it has failed the constraint check:

Bonus Approach (Postgres "Check Constraints")

If the value we were checking was a column on the same table, it would be possible also to use a third (and simpler) option. Below we'll show an example of how this could be used, just so that you know how to use this feature in Hasura.

NOTE: For this example, we will use a somewhat nonsensical scenario

That we don't want to allow football_tournments to be created if they already happened (IE, no historical records). While this MAY be something you might model in real situations, it's probably less realistic than the other two.

This solution will be using the Check Constraints feature of Postgres to validate changes to rows at a database level.

If you are't familiar with Check Constraints don't worry. We'll assume zero-knowledge and we'll walk through it step-by-step.

Note: If you'd like to read more about Check Constraints in Postgres, you can do so at

• https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTSs

What we will be doing is asking Postgres to ensure that the value of this row is never allowed to be anything but BEFORE the start_date of the sports game.

To accomplish this, the steps we would take are:

1. Adding "created_at" and "updated_at" from the "Frequently Used Column" button, from the Table -> Modify page:
   • This will also create Postgres triggers to set updated_at to "now()" when the row changes, and created_at = now() by default
   • 
2. Underneath the table columns, there's a section called "Check Constraints". Here you can visually add SQL constraints that have to hold true for any row insert/updates.
   • In "Check Constraints" we can add a new constraint, "created_at_before_tournament_start" with the following condition:
   • 
3. Now, if we attempt to insert a new record into "football_tournament" which is being created NOT before the start_date of the tournament, we get:
   • <!--kg-card-end: markdown--> * * *

If you'd like to learn more about Hasura's authorization system, we have a tutorial here.

I hope you found these solutions helpful! If you have any further questions or interesting use-cases that you'd like to share with us, hit us up on Discord / GitHub Discussions.