Supabase•3y ago

Create database function in private schema for edge function to call

Is it possible to create an rpc/database function in a private schema but call it from an edge function (using the service role in the edge function, which apparently is safe according to the docs)? Or would a private schema be off limits to the edge functions regardless of using the service role? Basically I have an edge function being called by pgcron and I will need to do a table insert – but I won't have an authed user to do RLS policy on as it's a shared table and no one really owns the rows so to speak anyway. My edge function is largely safe from abuse because you'll need a valid access token for a third-party API that it chats to anyway, so it bombs out immediately if I can't reach that third-party API. But, I have an RPC function that does the actual insert and is called by the edge function above (it does some checks and balances before inserting, hence the function and not an api insert). This currently works great but it's technically open to abuse because: 1. My Vue SPA app elsewhere leaks my anon key and url if you know where to look 2. RLS is on the table I'm inserting to stop it showing in the public API, but the rpc needs security definer to then get around it 🙄 3. So if you happen to guess the RPC function name and the right paramaters needed, you could in theory spam my table with junk I just wrote a huge post about having a custom secret/hash that the cron, edge and rpc can use to auth themselves. But then I thought, if the rpc is in a private schema it can't be abused, and as long as the service role in the edge function will let it call the rpc in the private schema, this should solve the problem right? And as I said, the edge function is effectively protected by needing an access token for a third-party API called within it anyway, so I just need to lock down the rpc function and I'm good?
Can this be done?

23 Replies

silentworks•3y ago

You can write it in a private schema, you just need to state that private schema when initialising the Supabase client along with updating the list of exposed schemas inside of the Supabase dashboard. In supabase-js v1

const supabase = createClient(supabaseUrl, supabaseKey, { schema: 'private_schema_name' })

const supabase = createClient(supabaseUrl, supabaseKey, { schema: 'private_schema_name' })

In supabase v2

const supabase = createClient(supabaseUrl, supabaseKey, { db: { schema: 'private_schema_name' } })

const supabase = createClient(supabaseUrl, supabaseKey, { db: { schema: 'private_schema_name' } })

GaryLakeOP•3y ago

That's fantastic, thanks very much. I will give this a go later 🙏🏻 @silentworks so I'm on Supabase v1, created the schema 'private', moved my rpc function to that schema, exposed that schema, and specified the schema in the options of createClient as above. In the edge function logs I'm getting permission denied for schema private which is the returned error from await supabaseClient.rpc() Am I missing anything? I also added the new private schema to the 'Extra search path' field as well but same result.

garyaustin•3y ago

https://github.com/supabase/supabase/issues/4916

GitHub

update custom schema docs with permissions SQL · Issue #4916 · supa...

Improve documentation Link https://supabase.com/docs/reference/javascript/initializing#api-schemas Describe the problem Currently we have a section for querying custom schemas, but we also need the...

GaryLakeOP•3y ago

Same deal 😦 Changed 'other_schema' to 'private' and didn't include the anon role but other wise ran verbatim... But also back to the boat I was in with the public schema where I've basically given access to call it via .rpc() again which was the point. I can see running these privileges for the service role only and letting the edge function run as service role would solve that, but currently the edge function is the thing getting the permission denied even with issue #4916 applied (Thanks folks, this is great btw, appreciate it) "But also back to the boat I was in with the public schema where I've basically given access to call it via .rpc() again which was the point. " ^^This was a red herring, I'd put some debug statements in and ran my create/replace and of course made the function again in public schema Edge function still getting permission denied even when using service role key I've even tried GRANT ALL ON ALL FUNCTIONS IN SCHEMA private TO service_role; but no joy

garyaustin•3y ago

I'm sort of lost, trying to sort your original issue. Without digging into it, I would think you could limit the schema and it's tables/functions to just the service_role having access. Also you can check for service_role in your function itself so that if it is called, no one can use it. It is possible if you don't recreate the function with the schema grants already set, it did not inherit the grants you need when you added them to the schema.

GaryLakeOP•3y ago

Ah ok, because yeah I made the function and then moved schema in the Supabase UI – I'll try remaking directly into the right schema in the morning, but thanks! To recap though, I ran:

GRANT USAGE ON SCHEMA private TO service_role;
GRANT ALL ON ALL TABLES IN SCHEMA private TO service_role;
GRANT ALL ON ALL ROUTINES IN SCHEMA private TO service_role;
GRANT ALL ON ALL SEQUENCES IN SCHEMA private TO service_role;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA private TO service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres, supabase_admin IN SCHEMA private GRANT ALL ON TABLES TO service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres, supabase_admin IN SCHEMA private GRANT ALL ON ROUTINES TO service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres, supabase_admin IN SCHEMA private GRANT ALL ON SEQUENCES TO service_role;

GRANT USAGE ON SCHEMA private TO service_role;
GRANT ALL ON ALL TABLES IN SCHEMA private TO service_role;
GRANT ALL ON ALL ROUTINES IN SCHEMA private TO service_role;
GRANT ALL ON ALL SEQUENCES IN SCHEMA private TO service_role;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA private TO service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres, supabase_admin IN SCHEMA private GRANT ALL ON TABLES TO service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres, supabase_admin IN SCHEMA private GRANT ALL ON ROUTINES TO service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres, supabase_admin IN SCHEMA private GRANT ALL ON SEQUENCES TO service_role;

GaryLakeOP•3y ago

Edge function supabase client:

GaryLakeOP•3y ago

And going to remake my rpc function with the private schema baked in Cheers @garyaustin

garyaustin•3y ago

In your deno function you are setting the authorization header with I assume the header from what ever called your edge function. I would think you would just use the client with the service_role key and not set the header to something else, it will override the service_role which is used as default. I believe. I know on the standard client if you signIn a user you act as that user, no matter if you set service_role in the init. I'm not positive though if it looks at the header to make that decision, or something internal.

silentworks•3y ago

What @garyaustin said ^

GaryLakeOP•3y ago

Ah yeah that makes sense, I think I've left that in as a hangover from initial efforts YASSS!!! Working, thanks so much folks! Cannot call it from .rpc() either so this is perfect 🙌🏻

silentworks•3y ago

Another thing to note is that you have full access to your database directly on the server side if you wanted to perform the action directly without using an .rpc function. You can find your db connection settings https://app.supabase.com/project/_/settings/database and scroll down to connection string

GaryLakeOP•3y ago

Thanks, noted. I'm only using a function at the moment as I do some processing to auto generate a slug from other data with unique key constraints etc, I may abandon this as I'm a bit worried about perf but going to see

GaryLakeOP•3y ago

@silentworks @garyaustin I don't suppose you know what the equivalent query is for the 'Exposed schemas' setting in the UI is? The same UI doesn't exist in local studio and setting up a wider local/remote workflow is still on my todo list and a little way down the line... I've replicated everything else above in local so I don't have to deploy my edge function after every change but I'm stuck on exposing my new private schema TIA

garyaustin•3y ago

https://github.com/supabase/supabase/blob/0d1c2a9873820874d6fa3f4d6ed6d363a098f805/docker/.env.example#L34 I can't do more than point you there for the docker config. I don't use local development.

GitHub

supabase/.env.example at 0d1c2a9873820874d6fa3f4d6ed6d363a098f805 ·...

The open source Firebase alternative. Follow to stay updated about our public Beta. - supabase/.env.example at 0d1c2a9873820874d6fa3f4d6ed6d363a098f805 · supabase/supabase

GaryLakeOP•3y ago

Thanks for trying @garyaustin – it looked like maybe I could set the PGRST_DB_SCHEMAS=public,storage,graphql_public,private in the .env.local but no joy As a bit of a punt, I also tried running ALTER ROLE service_role SET pgrst.db_schemas = 'public, storage, graphql_public, private'; locally but didn't seem to do it. Nevermind for now, cheers!

SupaBot•3y ago

garyaustin•3y ago

you got some work to do...

GaryLakeOP•3y ago

Haha, no deletes either 😆 FFS

SupaBot•3y ago

GaryLakeOP•3y ago

#legend

garyaustin•3y ago

You might want to ask a new question on your settings, it seems they are there for a reason, I just don't know the process to use the env files.

GaryLakeOP•3y ago

Yeah will do, I've got a whole "look into local/remote work flow" task on my list that I need to sort at some point so going to worry about it then. I was just getting annoyed deploying an edge function after every change at the moment 😩 This looks interesting: https://www.snaplet.dev/post/supabase-guide

Snaplet Blog: Clone your Supabase production data to a dev environm...

Gaming

Programming

Create database function in private schema for edge function to call

Did you find this page helpful?