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

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.

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

StackExchange Database Administrators Q#298931, answer score: 5