Supabase Date Protection on PostgreSQL
Tags: supabase, postgres, security, sql
Published: 2022-02-08 04:47:48

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!?



![Supabase Date Field](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e2jfzjmj0eh0t1wrv3du.png)

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](https://www.postgresql.org/docs/9.5/sql-createpolicy.html) 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:

```sql
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.

{% youtube 0N6M5BBe9AE %}

## updated_at

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

![MODDATETIME](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ge7fome0cnbwsnixd88x.png)

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

```sql
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](https://dba.stackexchange.com/a/194850/30920)
- [this stackoverflow post](https://stackoverflow.com/questions/71004643/prevent-updated-at-and-created-at-from-being-hacked-in-postgresql/71005659?noredirect=1#comment125558726_71005659)

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:

```sql
-- 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