Supabase Date Protection on PostgreSQL
7 min read
I'm currently building my first Supabase app. PostgreSQL is mature, feature plentiful, and hard tested.
UPDATE 10/26/22: - See this article
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
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.
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:
- BEFORE Trigger
INSERT / UPDATE
- 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.
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:
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!
So it turns out a field with
now() can still be updated. We need functions
ON UPDATE and
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
create_dates() for each table you want respectively.