supabase - readonly columns

the challenge#

Supabase is a nice solution. With row level security you can define which users can create, read, update or delte certain roles. It helps, but when it comes to foreign or primary keys you want to have more granular access, which fields can be updated.

Imagine you have a table structure like this:

We want to prevent any changes to the id or to created_by columns. Once created the column should be readonly for every normal supabase user who has access to this row through RLS-policy.

the solution#

It seems a little bit hacky but it will work pretty effectivily. The idea is to add an trigger to every table where we want to protect certain fields. This trigger will run before the update and will check if the value in a readonly column is updated. If so the trigger raises an exeption and terminates the query.

In supabase we will need two parts for that. A function which will be invoked by the trigger and the trigger it self.

the function#

The function is pretty easy. We will use PSQL here:

BEGIN
  IF NEW."id" IS DISTINCT FROM OLD."id"
  THEN
    RAISE EXCEPTION '"id" column cannot get updated';
  END IF;

  RETURN NEW;
END;

So the function checks if the value of the new entry is the same as the value from the old entry. If so it will return the new value and the database will do it’s update. If not we raise an exception.

the trigger#

We must add one trigger for every table we want to protect. So give it a name, select the table, activate the trigger for updates and select the trigger type before the event. Make the trigger fire for every row.

Apply the function and you are done.

extend to more fields#

If you have fields like id, created_by, created_at all which are existing in all tables you can extend your function with more checks.

BEGIN
    IF NEW."id" IS DISTINCT FROM OLD."id"
    THEN
    RAISE EXCEPTION '"id" column cannot get updated';
    END IF;

    IF NEW."created_by" IS DISTINCT FROM OLD."created_by"
    THEN
    RAISE EXCEPTION '"created_by" column cannot get updated';
    END IF;

    --  Further Checks Here ---

    RETURN NEW;
END;

You still have to add one trigger to every table, but at least you can reuse that function in every trigger.

Summary#

This is a hacky way to add write protection to columns to make them readonly. Don’t forget to test your authorization triggers!