Allow users to modify only some (but not all) fields in a PostgreSQL table with row level security

Question

I have a Postgres table which looks like this:

user_id	first_name	last_name	email_address	role
68f00c4c-5dff-4886-a584-d44a23e47160	David	Mulberry	[email protected]	administrator
3ed36117-e632-4b8b-b672-0b29f5f8b5c9	Martin	Hughes	[email protected]	customer

I would like to write a policy to:

• allow customers to update the first_name, last_name and email_address in their own record, but obviously not their user_id or role (because this would grant a customer administrative privileges)
• allow administrators to edit all fields except user_id for all records (including change another user's role).

I'm new to Postgres and I'm trying to get the hang of writing Row Level Security policies. I'm having a hard time articulating more complex policies in SQL, so I would appreciate if someone could offer an example for a scenario from my application.

I'm using Supabase which has an auth.uid() function (example) containing the user's ID from the JSON Web Token.

Answer 1

I had a similar problem, also using Supabase. I ended up making a view with the editable columns for the users table, where the view does the row-level filtering:

CREATE OR REPLACE VIEW editable_user AS
 SELECT u.name
 FROM pub_users u
 WHERE auth.uid() = u.id;

In my case I only wanted my users to be able to edit their own name, but this should be straight forward to expand to more columns.

In my front-end I then write directly to this view (something I didn't even know was possible before today... )

• tanius: Nice trick! Views cannot have their own RLS policies. You work around that with this user-specific view that only contains what a user is allowed to edit. (Views can reuse RLS policies of the underlaying table (details), but here that's pretty useless (discussion).)
• The Mighty Chris: Correct me if I'm wrong—but in order for this to work than the user would need access to write to the underlying table too and this accomplishes nothing?

Answer 2

There is no way to do this with row level security, because there is no way to test if a column was modified or not.

But you can do it with a trigger:

CREATE FUNCTION check_permission() RETURNS trigger
 LANGUAGE plpgsql AS
$$DECLARE
 my_id uuid := get_my_id();
BEGIN
 /* everybody can change their own entry */
 IF my_id = OLD.user_id AND
 (OLD.user_id, OLD.role) IS NOT DISTINCT FROM (NEW.user_id, NEW.role)
 THEN
 RETURN NEW;
 END IF;
 /* administrators can change any row */
 IF (SELECT role FROM mytab WHERE user_id = my_id) = 'administrator' AND
 OLD.user_id IS NOT DISTINCT FROM NEW.user_id
 THEN
 RETURN NEW;
 END IF;
 RAISE EXCEPTION 'that update is not allowed';
END;$$;
CREATE TRIGGER check_permission BEFORE UPDATE OF mytab
 FOR EACH ROW EXECUTE PROCEDURE check_permission();

You will notice that that depends on a function get_my_id() which returns the user_id of the currently active user.

Answer 3

Why are you allowing Users (and even Administrators) anywhere near the database itself?

You should be providing an Application that allows them to manipulate the data and that Application should simply not offer any way for them to see things that they shouldn't see or change things that they shouldn't change.

If someone gets update access in the database itself, then all bets are off.

• Obvious_Grapefruit: I'm using Supabase as a backend for my React application (see here and also here for more on how policies are used in this context).