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

I noticed on the docs theupdated_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 theupdated_at andcreated_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 thenow() function to get the current date. This means the default value will always be the current date on anINSERT. Just like any field with a default value, it can be changed.

How do we prevent this?

My first thought was to create apolicy 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:

CREATEFUNCTIONprofiles_created_at()RETURNSTRIGGERAS$$BEGINNEW.created_at=OLD.created_at;RETURNNEW;END;$$LANGUAGEplpgsqlSECURITYDEFINER;CREATETRIGGERon_profiles_updateBEFOREUPDATEONprofilesFOREACHROWEXECUTEPROCEDUREprofiles_created_at();

This video really helped explain the function part.

updated_at

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

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

CREATETRIGGERprofiles_updated_atAFTERUPDATEonprofilesFOREACHROWEXECUTEPROCEDUREmoddatetime(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 withnow() can still be updated. We need functionsON UPDATE andON INSERT.

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

-- PROCEDURES for created_at and updated_atCREATEORREPLACEFUNCTIONupdate_dates()RETURNSTRIGGERAS$$BEGINNEW.updated_at=now();NEW.created_at=OLD.created_at;RETURNNEW;END;$$LANGUAGEplpgsqlSECURITYDEFINER;CREATEORREPLACEFUNCTIONinsert_dates()RETURNSTRIGGERAS$$BEGINNEW.created_at=now();NEW.updated_at=NULL;RETURNNEW;END;$$LANGUAGEplpgsqlSECURITYDEFINER;-- FUNCTIONS for profiles tableDROPTRIGGERIFEXISTSprofiles_update_datesONprofiles;CREATETRIGGERprofiles_update_datesBEFOREUPDATEONprofilesFOREACHROWEXECUTEPROCEDUREupdate_dates();DROPTRIGGERIFEXISTSprofiles_insert_datesONprofiles;CREATETRIGGERprofiles_insert_datesBEFOREINSERTONprofilesFOREACHROWEXECUTEPROCEDUREinsert_dates();

You can reuse the proceduresupdate_dates() andcreate_dates() for each table you want respectively.

J