Unable to perform table update using POST to function

[felipemullen]

Environment

• PostgreSQL version: PostgreSQL 14.1 on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 10.3.0-1ubuntu1~20.04) 10.3.0, 64-bit
• PostgREST version: 9.0.0.20220531 (pre-release) (d14f745)
• Operating system: Ubuntu (Supabase)

Description of issue

I have a function in Supabase (which uses postgREST) that is supposed to perform an UPDATE to a table, but it does not modify table contents.

I have slimmed down the contents of the function to avoid the need for any parameters and additional logic, and using hardcoded values:

CREATE OR REPLACE FUNCTION set_value() RETURNS INTEGER AS $$
BEGIN
  UPDATE values_table SET score = 123 WHERE device_id = '_deviceId';
  RETURN 321;
END
$$ LANGUAGE 'plpgsql' VOLATILE;

NOTIFY pgrst, 'reload schema';

The request used to interact with the above is as follows:

curl --location --request POST 'https://<app_id>.supabase.co/rest/v1/rpc/set_value' \
  --header 'Authorization: Bearer <token>' \
  --header 'apiKey: <key>' \
  --data-raw ''

I receive the hardcoded value in the response, but the values in the table do not change. What am I doing wrong?
I have also noted that when calling this function from SQL, it works as expected:

SELECT set_value();

I read through related issues, as well as documentation where it states that GET requests run in a read only transaction. Is there something missing in order for this to perform a table update?

Thanks ahead of time

[steve-chavez]

UPDATE values_table SET score = 123 WHERE device_id = '_deviceId';

Maybe there's an RLS policy that's filtering the rows UPDATE can affect.

Try disabling RLS or making your function SECURITY DEFINER.

[felipemullen]

You nailed it. SECURITY DEFINER was necessary for this to work. My best guess is whatever user is set up for the default /rpc routes does not have enough permissions for the transaction to be committed

[steve-chavez]

You'd get an error if it were lack of permissions. I'd recommend checking your RLS policies.

Also, if you're expecting a single row update you can enforce that condition inside your function with a SELECT INTO STRICT.