Supabase Date Protection on PostgreSQL

I'm currently building my first Supabase app. PostgreSQL is mature, feature plentiful, and hard tested.

I noticed on the docs the updated_at was added on the front end. What!?

This bothered me, so I started researching. It is dangerous like Iceman. Also, after digging deeper, it seems the problem has been ignored by lots of people in PostgreSQL for a while. I would bet money there are tons of SQL databases where you can hack the updated_at and created_at fields by adding a new value on the front end.

However, in this particular case, I am 99% sure the tutorial was written before the Functions and Triggers UI was added to Supabase. I do not have a time machine... yet... so I am not sure whether or not running SQL Queries was always an option.

created_at

The created date is populated internally by using the now() function to get the current date. This means the default value will always be the current date on an INSERT. Just like any field with a default value, it can be changed.

How do we prevent this?

My first thought was to create a policy to prevent that change. Then I thought about a constraint. Finally, I after asking questions and researching, I realized a trigger is the way to go.

For INSERT and UPDATE statements, WITH CHECK expressions are enforced after BEFORE triggers are fired, and before any actual data modifications are made. Thus a BEFORE ROW trigger may modify the data to be inserted, affecting the result of the security policy check. WITH CHECK expressions are enforced before any other constraints.

So, for reference it turns out the lifecycle for SQL Procedures is:

1. BEFORE Trigger
2. Policies
3. Constraints
4. INSERT / UPDATE
5. AFTER Trigger

In this case, we need to use a Trigger Function:

CREATE FUNCTION profiles_created_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.created_at = OLD.created_at;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER on_profiles_update
BEFORE UPDATE ON profiles
FOR EACH ROW EXECUTE PROCEDURE profiles_created_at();

This video really helped explain the function part.

updated_at

The updated date requires the use of the moddatetime extension, which is not enabled by default. Enable it.

Then, you need to run a trigger that automatically updates the value.

CREATE TRIGGER profiles_updated_at AFTER UPDATE on profiles
FOR EACH ROW EXECUTE PROCEDURE moddatetime (updated_at);

You don't need a before trigger, as it would ultimately get replaced by the after trigger no matter what someone inputs.

I also found these posts helpful:

• this stackexchange post
• this stackoverflow post

I still would like to see a one button click to automatically implement this on a field!

Either way, now your dates are protected from hackers!

J

UPDATE: 4/2/22

So it turns out a field with now() can still be updated. We need functions ON UPDATE and ON INSERT.

Here is the code you need for all tables with dates:

-- PROCEDURES for created_at and updated_at
CREATE OR REPLACE FUNCTION update_dates()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    NEW.created_at = OLD.created_at;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION insert_dates()
RETURNS TRIGGER AS $$
BEGIN
    NEW.created_at = now();
    NEW.updated_at = NULL;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- FUNCTIONS for profiles table
DROP TRIGGER IF EXISTS profiles_update_dates
  ON profiles;
CREATE TRIGGER profiles_update_dates
BEFORE UPDATE ON profiles
FOR EACH ROW EXECUTE PROCEDURE update_dates();

DROP TRIGGER IF EXISTS profiles_insert_dates
  ON profiles;
CREATE TRIGGER profiles_insert_dates
BEFORE INSERT ON profiles
FOR EACH ROW EXECUTE PROCEDURE insert_dates();

You can reuse the procedures update_dates() and create_dates() for each table you want respectively.

J