A set of postgres functions that allow you to protect columns that should not be updated.

# 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](https://github.com/orgs/supabase/discussions/656#discussioncomment-5594653), 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](https://github.com/supabase/dbdev)
2) Install the extension

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

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

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


danahartweg

Packages