permission denied for table http_request_queue as an authenticated user

[psteinroe]

Bug report

Describe the bug

After pg_net got upgraded, it throws a permission denied for table http_request_queue when trying to make a request as an authenticated user.

To Reproduce

Upgrade to the latest version and attempt to make a http request using the authenticated role.

Expected behavior

Assumption is that any supabase extension works out of the box with the role and auth setup in the database. If it is expected behaviour, it should be documented somewhere.

[psteinroe]

The issue is fixed by applying this supabase/supabase#4883 (comment)

Nevertheless, the expectation is that pg_net, when enabled on supabase, automatically applies the correct permissions.

[steve-chavez]

We still get support tickets related to this issue. It seems that some tool resets all the postgres role privileges, which we rely on

pg_net/sql/pg_net.sql

Lines 365 to 366 in 4c1ca73

	grant all on schema net to postgres;
	grant all on all tables in schema net to postgres;

The solution should be to instead grant all privileges to PUBLIC, which is something pg_cron also does:

https://github.com/citusdata/pg_cron/blob/main/pg_cron.sql#L27

[PhilippS93]

We have the same error right now. Running the fix gives the result

must be owner of function net.http_get

[dexsnake]

We have the same error right now. Running the fix gives the result

must be owner of function net.http_get

Ran into the same thing this morning. Tried running the queries in that suggestion, same must be owner of function net.http_get error.

I saw another say turn pg_net off and on again and that seemed to do the trick.

[la289]

Yeah this issue just happened to us as well. I see they have another ticket for this but it's locked: https://github.com/orgs/supabase/discussions/21450