How to use rpc with service_role to create tables without 42501 error

[adaptivebehaviour]

I have a function that creates a table, but when I try to call it using rpc with service_role I get the following error:

code: '42501',
details: null,
hint: null,
message: 'permission denied for schema public'

Do I need to explicitly grant certain privileges to the service_role? If so, how do I do it?

[GaryAustin1]

By default Supabase does not grant any of the API roles create privileges on the public schema...
grant usage on schema public to postgres, anon, authenticated, service_role; is what is used to set it up.

Not necessarily a good idea to change the grants on the schema as Supabase controls that.

You could use a postgres function set to security definer to act as postgres role to create a table. Then you protect the function by removing the ability of anon and authenticated to run it.

It is normally not a good idea to create tables on the fly though in a relational database unless you have a really special need to do so.