Permissions denied for table using API but works with SupabaseClient #4883

jperestrelo · 2022-01-09T03:18:51Z

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?

The text was updated successfully, but these errors were encountered:

jperestrelo · 2022-01-09T12:49:55Z

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 · 2022-01-11T15:07:13Z

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 · 2022-01-11T15:15:46Z

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

tilmann · 2022-01-17T15:16:46Z

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 · 2022-01-17T18:15:19Z

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 · 2022-01-18T08:16:52Z

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 · 2022-01-20T12:41:03Z

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 · 2022-01-20T18:42:15Z

@darora
Thanks for the update and your investigation! 👍

w3b6x9 · 2022-01-20T21:45:30Z

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 · 2022-01-21T14:51:47Z

🙏 Thanks. That solved the problem for me!

stephanbogner · 2022-01-24T20:05:28Z

Looks good on my end too 🎉
Thanks ❤️

jdahdah · 2022-03-19T17:16:55Z

@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 · 2022-04-15T01:27:12Z

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

w3b6x9 · 2022-04-22T17:44:39Z

@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?

@jdahdah Sorry, completely missed your comment. I'll have the team look into this and report back here! There was another user who ran into the same issue and I'm trying to confirm now whether their project was also paused at one point.

jwright04 · 2022-06-08T00:53:16Z

The comment left by @w3b6x9 solved the issue for me although I still see errors when logging in/out specifically
TypeError: destroy is not a function. If I refresh the page and login again, i still see the error, but i'm redirect back to my success page. It's worth noting this is happening on my localhost, I haven't tried deploying yet.

GaryAustin1 · 2022-06-15T16:47:43Z

A user on Discord, following one of the guides for setting up Stripe with an auth trigger to a profile table and then a function hook seems to be encountering this issue. After following the chain of debugging their trigger function and table set up finally they found the error in the logs, which I had seen here. "permission denied for table http_request_queue"

I've asked them to contact support, unless they feel comfortable running the suggested SQL. https://discord.com/channels/839993398554656828/986571085724139550
This is a pretty "random" and nasty bug to have floating about, especially for beginning users following a guide.

oalbacha · 2022-06-15T18:03:07Z

I ran the suggested SQL and I can sign in but noticed some changes to my RLS policy. Make sure to review your policies after running the suggested SQL

GaryAustin1 · 2022-06-15T18:10:45Z

@oalbacha If you are creating users again, then you are on to some other issues in your code/setup. Probably best to ask in Discord or Github/Discussions here with details.

julandev · 2022-08-17T12:16:43Z

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;

I ran this code in the editor and since then, I have been unable to create new users. Also, I keep getting a type error: destroy is not a function.

As I do not know how to rollback the code , I deleted my projects and organizations and created new ones hoping it would make a difference. It didnt.

Then I created a new supabase account and I am still getting the errors. Is there any other thing I can do?

rrakso · 2023-08-09T17:12:28Z

Every time I try to make a request POST 'https://<id>.supabase.co/rest/v1/<table_name>' I get permission denied for table <table_name> (the state of the RLS doesn't matter, always the same result...), but once I created a whole new project, then I was able to create new records.

Does anyone know what could be the cause of this strange behavior?

PS

The problem persists even when I create the new table using UI (website) (the rest of the tables are created using SQL queries).

EDIT

I checked it with Supabase JS client - same result (even if service key was provided...) (also I didn't test the solution from this comment - too risky on this dataset 😅)

EDIT2

Pause/Restart? Nothing helped...

lastEDIT

I found it! The cause of my problem... Maybe someone will find my "discovery" as useful.
So while developing, I cleared the Postgres with SQL query from this response, and then I filled "public schema" with proper data - so everything was working EXCEPT the JS library and REST requests.
So with "fresh" project REST/client are working fine! After dropping all the data 😐 it breaks - and if I want to fix it back, then (I think) the only solution is to create a new project.

SQL query used to drop all :)

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

Maikpwwq · 2023-08-25T06:41:44Z

This help me solve that error, In my supabase table config I visit View Policies

Then I added two instances of New Policy, for the Select and Insert methods respectively

prithvi2k2 · 2023-11-05T02:38:50Z

This help me solve that error, In my supabase table config I visit View Policies Then I added two instances of New Policy, for the Select and Insert methods respectively

Is this secure - what does give access to public exactly mean, like anyone without supabase keys/urls can access the db?

johnsimeroth · 2023-11-17T11:45:03Z

lastEDIT

I found it! The cause of my problem... Maybe someone will find my "discovery" as useful. So while developing, I cleared the Postgres with SQL query from this response, and then I filled "public schema" with proper data - so everything was working EXCEPT the JS library and REST requests. So with "fresh" project REST/client are working fine! After dropping all the data 😐 it breaks - and if I want to fix it back, then (I think) the only solution is to create a new project.

SQL query used to drop all :)
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

This was it for me as well. Thank you for sharing. New project solved it. Would love to know if there's an alternative that doesn't require a new project in the future!

nahtnam · 2023-11-20T08:09:19Z

The SQL queries in this thread did not help, but turning the pg_net extension off and on fixed it for me

HanLok420 · 2023-11-23T09:44:52Z

The SQL queries in this thread did not help, but turning the pg_net extension off and on fixed it for me

How did you do this?

nahtnam · 2023-11-24T02:37:04Z

The SQL queries in this thread did not help, but turning the pg_net extension off and on fixed it for me

How did you do this?

In the left menu bar, go to database, then extensions, then look for pg_net

HanLok420 · 2023-11-24T07:14:17Z

The SQL queries in this thread did not help, but turning the pg_net extension off and on fixed it for me

How did you do this?

In the left menu bar, go to database, then extensions, then look for pg_net

Thank you, that did not work for me either. :(
The support is on it hopefully they find a way to solve it soon. I'll let you know.

emileond · 2023-12-18T23:24:59Z

The SQL queries in this thread did not help, but turning the pg_net extension off and on fixed it for me

Thanks, this worked for me. I got the issue after upgrading postgresql version from the admin UI.

henriquegdantas · 2024-02-14T20:21:48Z

Just got this after updating from 15.1.1.9 to 15.1.1.13 via the Admin UI. Disabling and reenabling the pg_net solved it.

stanifert · 2024-02-20T15:04:54Z

Just got this after updating from 15.1.1.9 to 15.1.1.13 via the Admin UI. Disabling and reenabling the pg_net solved it.

Ditto!

ryonwhyte · 2024-02-26T21:58:32Z

@nahtnam Thank you for your comment. Saved me from deleting my entire database

caipicoder · 2024-02-27T10:00:11Z

same here:

got this after updating via the Admin UI.
Disabling and reenabling the pg_net solved it

Thanks a lot @nahtnam !

EricStrohmaier · 2024-07-15T08:16:27Z

Just got this after updating from 15.1.1.9 to 15.1.1.13 via the Admin UI. Disabling and reenabling the pg_net solved it.

how did you update the version via Admin Ui?? cant find that option

GaryAustin1 · 2024-07-15T14:36:20Z

@EricStrohmaier Go to the infrastructure tab in settings.

jperestrelo added the bug Something isn't working label Jan 9, 2022

w3b6x9 mentioned this issue Jan 21, 2022

fix: alter api functions to be security definer ones supabase/pg_net#60

Merged

w3b6x9 closed this as completed Jul 1, 2022

egor-romanov added the p3 Priority 3 label Jul 4, 2022

psteinroe mentioned this issue Sep 26, 2022

permission denied for table http_request_queue as an authenticated user supabase/pg_net#72

Closed

Permissions denied for table using API but works with SupabaseClient #4883

Permissions denied for table using API but works with SupabaseClient #4883

Comments

jperestrelo commented Jan 9, 2022 • edited Loading

Bug report

Describe the bug

To Reproduce

Expected behavior

jperestrelo commented Jan 9, 2022

stephanbogner commented Jan 11, 2022

stephanbogner commented Jan 11, 2022

tilmann commented Jan 17, 2022

dragarcia commented Jan 17, 2022

stephanbogner commented Jan 18, 2022

darora commented Jan 20, 2022

stephanbogner commented Jan 20, 2022

w3b6x9 commented Jan 20, 2022

tilmann commented Jan 21, 2022

stephanbogner commented Jan 24, 2022

jdahdah commented Mar 19, 2022 • edited Loading

dstroot commented Apr 15, 2022

w3b6x9 commented Apr 22, 2022 • edited Loading

jwright04 commented Jun 8, 2022

GaryAustin1 commented Jun 15, 2022 • edited Loading

oalbacha commented Jun 15, 2022 • edited Loading

GaryAustin1 commented Jun 15, 2022 • edited Loading

julandev commented Aug 17, 2022

rrakso commented Aug 9, 2023 • edited Loading

PS

EDIT

EDIT2

lastEDIT

Maikpwwq commented Aug 25, 2023

prithvi2k2 commented Nov 5, 2023

johnsimeroth commented Nov 17, 2023

lastEDIT

nahtnam commented Nov 20, 2023

HanLok420 commented Nov 23, 2023

nahtnam commented Nov 24, 2023

HanLok420 commented Nov 24, 2023

emileond commented Dec 18, 2023

henriquegdantas commented Feb 14, 2024

stanifert commented Feb 20, 2024

ryonwhyte commented Feb 26, 2024

caipicoder commented Feb 27, 2024

EricStrohmaier commented Jul 15, 2024

GaryAustin1 commented Jul 15, 2024

jperestrelo commented Jan 9, 2022 •

edited

Loading

jdahdah commented Mar 19, 2022 •

edited

Loading

w3b6x9 commented Apr 22, 2022 •

edited

Loading

GaryAustin1 commented Jun 15, 2022 •

edited

Loading

oalbacha commented Jun 15, 2022 •

edited

Loading

GaryAustin1 commented Jun 15, 2022 •

edited

Loading

rrakso commented Aug 9, 2023 •

edited

Loading