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	david@example.com	administrator
3ed36117-e632-4b8b-b672-0b29f5f8b5c9	Martin	Hughes	martin@example.com	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... )

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.