GraphQL Authorization with Hasura: How to prevent mutations after a time interval
Tags: authorization, graphql, postgres
Published: 2021-08-09 09:30:33

---
title: GraphQL Authorization with Hasura: How to prevent mutations after a time interval
published: true
date: 2021-08-04 16:18:22 UTC
tags: Authorization,GraphQL,PostgreSQL
canonical_url: https://hasura.io/blog/graphql-authorization-with-hasura/
---

 ![GraphQL Authorization with Hasura: How to prevent mutations after a time interval](https://hasura.io/blog/content/images/2021/08/graphql_auth.png)

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-1-postgres-triggers)
- [Approach #2 (Hasura's "Column Comparison" permission operators)](#approach-2-hasuras-column-comparison-permission-operators)
- [Bonus Approach (Postgres "Check Constraints")](#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](https://hasura.io/docs/latest/graphql/core/guides/postgres/triggers.html) 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`):

![GraphQL Authorization with Hasura: How to prevent mutations after a time interval](https://hasura.io/blog/content/images/2021/08/create_triggers_sql.png)

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

![GraphQL Authorization with Hasura: How to prevent mutations after a time interval](https://hasura.io/blog/content/images/2021/08/insert_tournament_row_from_past.png)

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

![GraphQL Authorization with Hasura: How to prevent mutations after a time interval](https://hasura.io/blog/content/images/2021/08/failed_insert_pg_trigger.png)

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:

![GraphQL Authorization with Hasura: How to prevent mutations after a time interval](https://hasura.io/blog/content/images/2021/08/permissions_tournament_start_date_gte_now.png)

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

![GraphQL Authorization with Hasura: How to prevent mutations after a time interval](https://hasura.io/blog/content/images/2021/08/failed_insert_column_comparison2.png)

## 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](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
  - ![GraphQL Authorization with Hasura: How to prevent mutations after a time interval](https://hasura.io/blog/content/images/2021/08/1_add_created_updated.png)
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:
  - ![GraphQL Authorization with Hasura: How to prevent mutations after a time interval](https://hasura.io/blog/content/images/2021/08/2_before_tournament_check_constraints.png)
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:
  - ![GraphQL Authorization with Hasura: How to prevent mutations after a time interval](https://hasura.io/blog/content/images/2021/08/3_1_constraint_violation.png)
<!--kg-card-end: markdown-->
* * *

If you'd like to learn more about Hasura's authorization system, we have a tutorial [here](https://hasura.io/learn/graphql/hasura-auth-slack/introduction/).

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](hasura.io/discord) / [GitHub Discussions](https://github.com/hasura/graphql-engine/discussions).