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