Policy that limits colums that can be updated

[enricoschaaf]

I am wondering if there is a way to write a policy that restricts the ability to update certain fields.
Maybe something like this?

create policy "Allow restricted update access" on public.users for all with check ( auth.uid() = id and roles is null);

This does not work because "roles" is the new value of the row not the update that will be applied.

From https://www.postgresql.org/docs/13/sql-createpolicy.html
check_expression:
Note that the check_expression is evaluated against the proposed new contents of the row, not the original contents.

Probability not possible but maybe someone here knows something, would be awesome if it were possible.

[steve-chavez]

Edit: See this other answer for a simpler solution.

I am wondering if there is a way to write a policy that restricts the ability to update certain fields.

RLS applies to the whole row, so it's not possible to use it in that way. For securing certain columns you can:

• Split your table into two and add the policy to the extra table that has the secured columns.
• Only GRANT UPDATE to a subset of columns of the table(GRANT UPDATE(col1, col2)..).
• Hide the column behind a VIEW. Don't expose the table to clients and only expose a VIEW with a subset of the columns. The VIEW will be auto-updatable, so you can UPDATE directly.
• Hide the table behind a FUNCTION with SECURITY DEFINER. The table doesn't have GRANT UPDATE for clients and they can only UPDATE the table through the FUNCTION.

(Related discussion: #346 (comment))

[christopherreay]

what does "GRANT UPDATE" mean in this context? by role? or in a policy? But iit cant be in the policy, because you said that wasnt possible

ty for the great answer

[steve-chavez]

Yes, GRANT UPDATE to roles. For this it'd be for the authenticated and anon roles.

Note that you need to alter default privileges of those first as mentioned in #1493 (reply in thread)

[Nemmcy]

All this sounds delicate...

What about only keeping RLS for SELECT and only inserting/updating the wanted columns on the server with the service role key, With good server authentication and validation, it becomes impossible for anyone to modify other columns, right?

[mtte]

Yes that would be possible but that implies that you have an application server that can do these things. Isn't the whole vision behind Supabase to get rid of that?

[tanius]

This can be achieved with a BEFORE UPDATE trigger, called for each rows of your table. It simply has to raise an exception if the UPDATE statement tries to modify the "forbidden" columns. Compared to some of the other options, there is full UI support for this solution as of now:

1. Under "Database → Functions", create a function users_columns_updateable with this function body (assuming pgsql is set as your function language):

   BEGIN
     IF NEW.roles <> OLD.roles THEN
       RAISE EXCEPTION 'changing "roles" is not allowed';
     END IF;
   
     RETURN NEW;
   END;
   

2. Under "Database → Triggers", create a new trigger columns_updateable that fires before UPDATE statements, for each row, and select the above function to execute.

See also this StackOverflow question.

I consider this trigger based solution preferable to a VIEW, as you do not have to modify any Supabase default permissions, or to move the VIEW's underlying table to a custom database schema. (And in my opinion, messing less with Supabase internals and defaults is good for maintainability and ease of documentation and skill transfer.) For a VIEW based solution, in my understanding you would have to remove the UPDATE permission on the underlying table from your users with authenticated and anon roles. This can be done by changing default permissions, or by moving the table to a custom schema. Disabling row-level security (RLS) on that table is not enough, as a message in Supabase will then tell you:

Turning off RLS means that you are allowing anonymous access to your table As such, anyone with the anonymous key can modify or delete your data.

Also, with the VIEW based solution, I think (untested!) that you cannot use RLS at the same time: views themselves cannot have RLS, so if you need RLS you have to make it utilize the RLS policies of the underlying table by setting security_invoker = on (details). But that means that the invoking user needs UPDATE permission on the underlying table to be able to UPDATE anything through the view – and with UPDATE permission on that table, the user can circumvent the restriction on updateable columns imposed by the view.

[MentalGear]

Thx, this seems to be the best solution yet! I'm wondering though, if it is possible to have it as a positive list, e.g. where only the stated columns may be changed, and if all other column are different/are submitted, it will ignore them or error out.

[steve-chavez]

I agree this being the superior solution - marking it as answer. Straightforward and requires less database objects.

[arnoson]

Exactly what I needed! If you are a supabase and postgres newbie like me, keep this in mind:

• When creating the database function, choose the return type trigger (this can't be changed later).
• When creating the trigger, choose Orientation to be Row (otherwise OLD and NEW are null).

[christophemarois]

Was brought here from this StackOverflow post.

I had to make a small adjustment to this comment so that validation only happens when user is subjected to RLS (e.g. not in a service role or admin environment).

This example is everything needed for setting up a fictive profile table (with RLS assumed to be set up so the user can update the row with the user_id column matching its authenticated user id) where the user can update everything but the is_admin column.

CREATE OR REPLACE FUNCTION public.validate_profile_update()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$BEGIN
  IF NEW.is_admin <> OLD.is_admin THEN
    RAISE EXCEPTION 'Updating "is_admin" is not allowed';
  END IF;

  RETURN NEW;
END;$function$

CREATE TRIGGER before_profile_update
  BEFORE INSERT OR UPDATE
  ON profiles
  FOR EACH ROW
  WHEN (row_security_active('profiles'))
  EXECUTE FUNCTION validate_profile_update();

As Supabase doesn't seem to support adding trigger conditions through the admin UI at the present time, the trigger needs to be created in pure SQL.

Tested, works well. I think that it is, as of now, the best way to achieve column-level security.

[MentalGear]

Neat solution! Is there a way to have a positive list as well for more security, e.g. defining only the fields that the user is allowed to edit, instead of the ones they are not?

[z-x]

This helped me very much, thank you!

[christophemarois]

@MentalGear @tanius

I have since come up with the following abstracted trigger function that allows whitelisting columns that can be updated if RLS is active for the invoker.

CREATE OR REPLACE FUNCTION public.allow_updating_only()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
  whitelist TEXT[] := TG_ARGV::TEXT[];
  schema_table TEXT;
  column_name TEXT;
  rec RECORD;
  new_value TEXT;
  old_value TEXT;
BEGIN
  schema_table := concat(TG_TABLE_SCHEMA, '.', TG_TABLE_NAME);

  -- If RLS is not active on current table for function invoker, early return
  IF NOT row_security_active(schema_table) THEN
    RETURN NEW;
  END IF;

  -- Otherwise, loop on all columns of the table schema
  FOR rec IN (
    SELECT col.column_name
    FROM information_schema.columns as col
    WHERE table_schema = TG_TABLE_SCHEMA
    AND table_name = TG_TABLE_NAME
  ) LOOP
    -- If the current column is whitelisted, early continue
    column_name := rec.column_name;
    IF column_name = ANY(whitelist) THEN
      CONTINUE;
    END IF;

    -- If not whitelisted, execute dynamic SQL to get column value from OLD and NEW records
    EXECUTE format('SELECT ($1).%I, ($2).%I', column_name, column_name)
    INTO new_value, old_value
    USING NEW, OLD;

    -- Raise exception if column value changed
    IF new_value IS DISTINCT FROM old_value THEN
      RAISE EXCEPTION 'Unauthorized change to "%"', column_name;
    END IF;
  END LOOP;

  -- RLS active, but no exception encountered, clear to proceed.
  RETURN NEW;
END;
$function$

This function can then be executed in a trigger like this:

CREATE OR REPLACE TRIGGER profile_cls
  BEFORE INSERT OR UPDATE
  ON public.profiles
  FOR EACH ROW
  EXECUTE FUNCTION allow_updating_only('first_name', 'last_name');

Compared to my last approach, the check for RLS now happens inside the trigger function for better reusability.

Tested and works

[aboveyunhai]

@christopherreay this looks elegant, will this solution conflict with other RLS policies?
particularly this example for updated_at: #379 (comment), .
I want to update updated_at when anything changes but at the same time prevent user from modifying the updated_at column.
it seems like both executes at the moment of before insert.

[TomExMachina]

This looks great, and I was able to add it via a postgres connection. Why though wouldn't the supabase query editor allow me to add that trigger? It just kept saying invalid sql or some such with no further description.

[devhandler]

if protected cols are filled by other triggers, those triggers need to happen after profile_cls.
https://www.postgresql.org/docs/current/sql-createtrigger.html

If multiple triggers of the same kind are defined for the same event, they will be fired in alphabetical order by name.

[NiklasPor]

This one is awsome, if you're using uppercase tablenames somewhere, make sure to change this line:

  schema_table := concat('"', TG_TABLE_SCHEMA, '"."', TG_TABLE_NAME, '"');

Thanks 👍

[wave-light]

Is there a way to get this to work with default column values? For example I have a non-whitelisted uuid column populated with gen_random_uuid(). The generated value is appearing in the trigger and causing it to throw an exception.

[rokk4]

rokk4: How to check if all coulmns are equal in a postgres update trigger function.

GitHub Copilot: You can check if all columns are equal in a PostgreSQL update trigger function by comparing the OLD and NEW records. Here's an example function that checks if all columns are equal:

CREATE OR REPLACE FUNCTION check_all_columns_equal() RETURNS TRIGGER AS $$
DECLARE
    column_name text;
BEGIN
    FOR column_name IN
        SELECT column_name
        FROM information_schema.columns
        WHERE table_name = TG_TABLE_NAME
        AND table_schema = TG_TABLE_SCHEMA
    LOOP
        IF OLD[column_name] IS DISTINCT FROM NEW[column_name] THEN
            RAISE EXCEPTION 'Column % is not equal', column_name;
        END IF;
    END LOOP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This function loops through all columns in the table and checks if the OLD and NEW values are equal. If any column is not equal, it raises an exception with the column name. You can use this function in a trigger to prevent updates that change any column value:

CREATE TRIGGER prevent_update_different_columns
BEFORE UPDATE ON public.user_public_keys
FOR EACH ROW
EXECUTE FUNCTION check_all_columns_equal();

This trigger will prevent updates that change any column value in the public.user_public_keys table.

rokk4: Now make it possible to exclude certain columns from the check.

GitHub Copilot: To exclude certain columns from the check, you can pass a comma-separated list of column names as a parameter to the trigger function. Here's an updated version of the function that excludes the specified columns:

CREATE OR REPLACE FUNCTION check_all_columns_equal(exclude_columns text) RETURNS TRIGGER AS $$
DECLARE
    column_name text;
BEGIN
    FOR column_name IN
        SELECT column_name
        FROM information_schema.columns
        WHERE table_name = TG_TABLE_NAME
        AND table_schema = TG_TABLE_SCHEMA
    LOOP
        IF column_name <> ALL(string_to_array(exclude_columns, ',')) THEN
            IF OLD[column_name] IS DISTINCT FROM NEW[column_name] THEN
                RAISE EXCEPTION 'Column % is not equal', column_name;
            END IF;
        END IF;
    END LOOP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This function checks if the column name is not in the list of excluded columns before comparing the OLD and NEW values. You can use this function in a trigger and pass the excluded columns as a parameter:

CREATE TRIGGER prevent_update_different_columns
BEFORE UPDATE ON public.user_public_keys
FOR EACH ROW
EXECUTE FUNCTION check_all_columns_equal('created_at,deleted_at');

This trigger will prevent updates that change any column value except for created_at and deleted_at columns in the public.user_public_keys table.

[arinmirza]

I appreciate this a lot!

But I'm still in favor of defining a check_all_columns_equal function instead of a trigger and use that in the RLS policy. I don't really like the idea of separating the access rules into policies and triggers.

Unfortunately, I'm fairly new to plpgsql and the world of postgres. Would you be kind to write this as a function returning a boolean, which can be used in the WITH CHECK clause of RLS policies?

[kamerat]

I agree with you @arinmirza, having a function in the with check would be the far superiour way here as you will "see it all" at one glance when inspecting RLS.

I'm new to this myself, but I'm not sure this will work. The trigger has the new and old, but I don't think the with check has this data available, I guess it only has the new?

[ghost]

I might be getting this terribly wrong here, but if the intent is to restrict updates on certain columns for certain users or roles, can you not just do the following:

1. revoke select and update on the table for a user ( or ) role.
2. grant select on the table for a user ( or ) role ( for all columns or just specific columns, depending on what you want )
3. grant update on certain columns only for a user or role.
   this way, the user or role can only update certain columns and not all columns. Row level security continues as per policy.

[devhandler]

that sounds doable. it's just less convenient.

[lt692]

Thank you. This was very helpful.

I think many devs, including me, are craving an easier solution to protect databases from unintended usage. Which supabase allows by design. This is a very easy mistake to make. Good thing I watched a video about this problem, that a user can grab a URL, token, endpoint, and just update columns without any security from the front/backend and decide to protect from this "exploit".

Using your approach is almost enough, but it also requires more knowledge than a regular user of supabase could have. I too don't understand what is going on, but thanks to your comment I managed to protect columns without requiring any triggers/functions from unwanted modifying.

Sadly this approach still needs functions and triggers to ensure that data received from the user client or worst case scenario from cURL directly is valid to be stored/saved to the database.

[gc-ft]

The following function can be used in RLS to block editing certain fields, I just wrote it and it has its limits but works in general.

Main limit is: this version of the function requires the fields to be cast to TEXT. Which does not ALWAYS work.

CREATE OR REPLACE FUNCTION check_row_match(
    _table_name TEXT,
    _primary_key_field TEXT,
    _field_list TEXT[],
    _primary_key_value TEXT,
    _values TEXT[]
)
RETURNS BOOLEAN AS $$
DECLARE
    sql_query TEXT;
    result_json JSONB;
    field_name TEXT;
    idx INT;
    field_value TEXT;
BEGIN
    -- Dynamically build the SQL query to select the fields into a JSONB object
    sql_query := format(
        'SELECT jsonb_build_object(%s) FROM %I WHERE %I = %L',
        array_to_string(
            array(
                SELECT format('%L, %I', field, field)
                FROM unnest(_field_list) AS field
            ), ', '),
        _table_name,
        _primary_key_field,
        _primary_key_value
    );

    -- Execute the query and store the result in result_json
    EXECUTE sql_query INTO result_json;

    -- Check if the number of fields matches the number of values
    IF array_length(_field_list, 1) <> array_length(_values, 1) THEN
        RAISE EXCEPTION 'Number of fields and values do not match';
    END IF;

    -- Compare each value in the JSON object against the provided values
    FOR idx IN 1..array_length(_field_list, 1) LOOP
        field_name := _field_list[idx];

        -- Extract the value from the JSONB object
        field_value := result_json->>field_name;

        IF field_value IS DISTINCT FROM _values[idx] THEN
            RETURN FALSE;  -- If any value does not match, return FALSE
        END IF;
    END LOOP;

    RETURN TRUE;  -- If all values match, return TRUE
END;
$$ LANGUAGE plpgsql;

Update policy would look like this, this one would be for a table users, with a primary key of "id", and it blocks editing "status" and "username" but allows updates on all other fields, it assumes "status" is an enum type, which is why I cast it to TEXT:

CREATE POLICY update_users_policy_check
ON public.users
FOR UPDATE
USING (true)
WITH CHECK (
    public.check_row_match(
        'users'::text,
        'id'::text,
        ARRAY['username', 'status'],
        id::text,
        ARRAY[username, status::TEXT]
    )
);

This works, but I have one problem... this does of course fail if the primary key is updated, because I have no way of looking up the "old" primary key. This whole thing works because postgresql does not read-lock rows being updated, and at the time the policy runs the function, the row is still the old format.

Anyone have any idea how to make sure how to stop the primary key from changing? Maybe with a foreign key constraint to a dummy table that just has the primary key of the users table with an update restrict cascade? Or am I overthinking this?

[james-william-r]

Just for anyone else who might visit, I needed to achieve a similar thing, basically allow users to update their own row in the users table, with the exception of one column. As the users table isn't updated soo frequently, I achieved this by using a query inside the RLS CHECK. - It checks that the id of the row matches the current user, and then makes sure that the badge value matches the value it should - referencing a row in a different table that they don't have access to edit.

  ((( SELECT auth.uid() AS uid) = id) AND (guardian = ( SELECT plans.active
   FROM plans
  WHERE (plans."user" = ( SELECT auth.uid() AS uid))
 LIMIT 1)))

[Razikus]

Hey
I wrote this function
https://medium.com/@razikus/supanuggets-1-million-row-crud-with-only-few-columns-updatable-in-supabase-with-policy-not-e815c38d2f2e

Should handle nulls, convert to proper type etc

It should work with preventing primary key as well

It should not be in public schema most probably

Here example - only stars update works, rest is not
Impersonated as user that owns it

Also here in action https://www.youtube.com/watch?v=icm9_8LtKlk