RPC fails when using an extension feature inside Postgres stored function about supabase-js HOT 6 CLOSED

However, even after all that, running the same RPC call results in 403 (permission denied for schema extensions).

Hm, maybe you're missing GRANT USAGE ON SCHEMA extensions TO service_role? (noted here)

Are there any potential downsides or security concerns with doing this?

No, granting usage to extensions is something we're going to do for all projects on a later fix. You only need to make sure your public schema tables/views/functions are secured according to your application logic.

from supabase-js.

Resolved - The pg_trgm extension is installed to the extensions schema by default. Removing it and reinstalling to pg_catalog schema resolved the issue:

DROP EXTENSION pg_trgm;

CREATE EXTENSION pg_trgm SCHEMA pg_catalog;

Could anyone clarify if this is safe to do, or if the original issue is a bug that could be fixed inside the library?

from supabase-js.

@ChronSyn Check this issue: supabase/postgrest-js#168

Also: supabase/supabase#1048 (comment)

from supabase-js.

Could anyone clarify if this is safe to do

Installing into pg_catalog is usually discouraged, since it pollutes an internal postgres namespace. Maybe you can just use extensions.similarity inside your function for now and grant the permissions as mentioned here.

from supabase-js.

Could anyone clarify if this is safe to do

Installing into pg_catalog is usually discouraged, since it pollutes an internal postgres namespace. Maybe you can just use extensions.similarity inside your function for now and grant the permissions as mentioned here.

I disabled the extension, and then re-enabled it via the dashboard. I've dropped my function, updated it to use the extensions.similarity call (from similarity), and then executed the query to add the function again. I then granted usage to anon and authenticated using the details in the link. I can see in pgAdmin that grantee's anon and authenticated have the usage privilege (U), both with a grantor of postgres. I have also checked the search path for the API, and it is defined as public, extensions.

However, even after all that, running the same RPC call results in 403 (permission denied for schema extensions).

The link also mentions it won't happen for new projects - Would I need to spin up a completely new project in order for this to take effect?

If I grant usage to service_role (grant usage on schema extensions to service_role;), I am able to execute the function via rpc(). Are there any potential downsides or security concerns with doing this?

from supabase-js.

However, even after all that, running the same RPC call results in 403 (permission denied for schema extensions).

Hm, maybe you're missing GRANT USAGE ON SCHEMA extensions TO service_role? (noted here)

Yep - turns out that was the issue. Is that the user that RPC calls are routed through, and should I be concerned about it causing any potential security or performance issues?

from supabase-js.