PG protect columns

With postgres RLS, you can define triggers that prevent updates to rows based on certain criteria both before and after the update. However, there is no built-in way to prevent columns from being mutated once they were initially set. You can't do this directly with RLS because there is no access to the previous column values in the with check. This extension provides a set of postgres functions that allow you to protect those columns.

I initially implemented these functions in my own project based on the discussion in this thread, and decided they could be of use to the greater community with a few additional refinements.

Quick Start (recommended)

If you're using Supabase:

1. Install dbdev following the instructions here: github.com/supabase/dbdev
2. Install the extension

Copyselect dbdev.install('pg_protect_columns');
create extension if not exists "pg_protect_columns";

Usage

Once installed, you must create a trigger on the table you want to protect and identify the columns that should be protected. This trigger can be named however you like, but I recommend prefixing with an underscore so that it always runs before any other trigger functions you define.

Creating the table and protecting the columns would look like the below.

Copycreate table my_table(
	id bigint primary key generated by default as identity,
	mutable_column text,
	immutable_column text
);

create trigger _protect_columns_before_update
	before update on my_table for each row
	execute procedure protect_columns('immutable_column');

Disabling protection

There are some situations where these columns may need to be updated. I've found this is generally true in supabase when writing api functions that update columns based on other rows.

note: in the future, this may take on the shape of (or provide an option for) only applying restrictions if using an account where RLS is applicable.

In your function, you can do the following:

Copycreate function api_update_column(
	record_id bigint,
	new_value text
)
	returns void
	as $$
begin
	perform disable_protection_on_column('immutable_column');

	update
		my_table
	set
		immutable_column = new_value
	where
		id = record_id;

	perform re_enable_column_protection();
end;
$$
language plpgsql;