Automatically run SQL in a project from my Supabase Integration
I am building a tool that would allow my users to have tables automatically generated in their supabase project (e.g. what you can do with Lovable.dev).
I want to make sure I am not missing anything as at the moment I am unable to automatically run SQL on the users project after they have installed my OAuth App.
Reproduce.
- I have created an OAuth app that has read write to everything (see image)
- I can install the oauth app to an organization
- I can see everything in that organization using the management API.
- However when I try and use https://supabase.com/docs/reference/api/v1-run-a-query to run SQL and create tables etc I get an unauthorized error.
Is there a better way I can have SQL run on a project through code v's manually by the user?
Management API Reference | Supabase Docs
Management API reference for the Supabase API
7 Replies
That management API has to be run only with access keys you create AND only where you control them. You cannot share with a user as they can run any or most of the tasks in the Management API.
https://supabase.com/docs/reference/api/introduction#authentication
In general it is a bad idea to let users create tables unless this is a very limited set of users. You could also do that thru an rpc function call in that case.
Management API Reference | Supabase Docs
Management API reference for the Supabase API
What I am trying to do is allow the user to replicate the backend of their existing CRM to supabase as easily as possible. So in my case I am inspecting the CRM and getting the meta data; I can generate the SQL that would then recreate that schema in supabase. But instead of the user having to go into their project dashbaord> Navigate to SQL Editor then run the SQL I generated for them, I want to do it automatically for them.
With the Oauth app I have created I have the keys and control them, I am just getting an error when i try and run SQL on the project after the Oauth app has been installed.
So you are attempting to use this method?
https://supabase.com/docs/guides/integrations/build-a-supabase-integration
If so you might start a new topic on just using that with the management API. Not sure how many users here have done this versus just using one Management API key.
@garyaustin exactly. Thank you I will start a new thread.
Maybe not there yet....
from bottom of that page.
@Chris Collins Also did you set up the scopes?
https://supabase.com/docs/guides/integrations/build-a-supabase-integration/oauth-scopes
@garyaustin thanks for that link. Right now I am trying to do everything I can to avoid the user giving their password to the DB