7

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.

0

3 Answers 3

5

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... )

1
  • 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).)
    – tanius
    Commented Feb 14, 2023 at 23:04
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.

-1

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.

1
  • 2
    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). Commented Sep 3, 2021 at 0:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.