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 https://github.com/supabase/supabase/issues/4883#issuecomment-1073047443

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

https://github.com/supabase/pg_net/blob/4c1ca73646ad60eedacae3feb4cd8595749bcb67/sql/pg_net.sql#L365-L366

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