Permissions denied for table using API but works with SupabaseClient

[jperestrelo]

Bug report

Describe the bug

When trying to access data from some API endpoints, like this: /rest/v1/leads?select=* I get this error:

{
    "message": "permission denied for table leads",
    "code": "42501",
    "hint": null,
    "details": null
}

when doing the same using JS client like this:

const { data, error } = await supabase
            .from('leads')
            .select()

I successfully get the data. Although enabling or disabling RLS has no impact.

I'm using the same anon API key in both situations.

To Reproduce

I am creating these tables using the following function:

BEGIN
EXECUTE format('
      CREATE TABLE IF NOT EXISTS %I (
       id serial PRIMARY KEY,
       created_at date default current_timestamp
      )', my_table);

EXECUTE format('
      ALTER TABLE %I ENABLE ROW LEVEL SECURITY;
', my_table);    

END

I tried to enable and disable RLS from UI and from SQL editor but didn't change anything.
I also tried to use the service role KEY when using API, but the same error above showed.

When creating the tables from the UI, everything seems to work as intended.

Expected behavior

I was expecting to be able to create tables programmatically from the client-side so I'm trying to do it using functions.
I'm using the function like this:

 const { data, error } = await supabase
            .rpc('create_table', { my_table: table_name })

Am I missing something?

[jperestrelo]

Update
I think I understand what's going on... when creating from the admin UI it creates a set of privileges on the table. While my function is not doing it.

So I'm guessing that there is a lot going on under the hood when creating a table from the UI. There is any recommended way that I can create a table using functions and maintain the same functionalities as if it was being created in the UI?

Thanks

[stephanbogner]

I am not sure if my issue is related:
Even if I disable RLS I can't update a row on a certain table. On other tables it works perfectly.

I am using:

const { data, error } = await supabase
  .from(tableName)
  .update(newRowData)
  .eq(columnName, columnValueAtRow)
  
  if (error) {
      console.error(error);
  }
  return { data, error }

I always get:
{message: 'permission denied for table http_request_queue', code: '42501', hint: null, details: null}

The weird thing is:
I am pretty sure it worked before the holidays. In the meantime my instance got paused due to inactivity, but not sure if that causes the issue.

Before I was using 1.2.1 but with 1.29.1 it's the same result.

[stephanbogner]

Ok ... very weird. I even get this error if I try to edit the data in the UI:

[tilmann]

I ran in the same error. (permission denied for table http_request_queue)

My finding: It seems to be connected with the functions hooks ALPHA. After removing the hooks for the table that throws the error an insert was possible again.

Adding the hook again leads to the same error.

My instance was paused due to the holidays as well.

Any ideas except setting up a new instance?

[dragarcia]

Hi guys 👋

If you guys haven't, would you mind sending over a ticket specifying the affected project over at https://app.supabase.io/support/new, and we'll take a closer look at each one from there.

Thanks!

[stephanbogner]

I ran in the same error. (permission denied for table http_request_queue)

My finding: It seems to be connected with the functions hooks ALPHA. After removing the hooks for the table that throws the error an insert was possible again.

Adding the hook again leads to the same error.

My instance was paused due to the holidays as well.

Any ideas except setting up a new instance?

Ahhh! Why didn't I think of that?! I can confirm the behaviour:
If I delete the function hook (which is a post-request on insert and update) then I can edit values in the table again. But if I re-add the hook it won't work. Exactly as @tilmann described.

[darora]

Hey folks-as a quick update: we've isolated the cause of the issue as an unfortunate interaction between the pause/restore and the mechanism used to enable function hooks via pg_net. We'll be working on both fixing this for the future, and posting a retroactive fix that you will be able to apply to your projects.

[stephanbogner]

@darora
Thanks for the update and your investigation! 👍

[w3b6x9]

hello everyone!

posting a retroactive fix that you will be able to apply to your projects

if you're currently blocked please run the following in your Supabase SQL editor:

GRANT USAGE ON SCHEMA net TO supabase_functions_admin, postgres, anon, authenticated, service_role, dashboard_user;

ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER;
ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER;
ALTER function net.http_collect_response(request_id bigint, async boolean) SECURITY DEFINER;

REVOKE ALL ON FUNCTION net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC;
REVOKE ALL ON FUNCTION net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC;
REVOKE ALL ON FUNCTION net.http_collect_response(request_id bigint, async boolean) FROM PUBLIC;

GRANT EXECUTE ON FUNCTION net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) TO supabase_functions_admin, postgres, anon, authenticated, service_role, dashboard_user;
GRANT EXECUTE ON FUNCTION net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) TO supabase_functions_admin, postgres, anon, authenticated, service_role, dashboard_user;
GRANT EXECUTE ON FUNCTION net.http_collect_response(request_id bigint, async boolean) TO supabase_functions_admin, postgres, anon, authenticated, service_role, dashboard_user;

[tilmann]

🙏 Thanks. That solved the problem for me!

[stephanbogner]

Looks good on my end too 🎉
Thanks ❤️

[jdahdah]

@w3b6x9 Thank you, this solved the issue for me. FYI this happened somewhere along following the lessons in your Egghead course. In lesson 24 I decided to delete the test user and signing up again was no longer possible. But my project has been paused once since I started the course so I guess it's more related to that than something instructed in the course?

[dstroot]

1. @w3b6x9 your fix works! Kudos.
2. This definitely happens after your project is paused (and you have hooks in place I think)