Rework how tenant admin works

[vvalchev]

Currently, the tenant-admin is defined by setting auth.users.raw_app_metadata to

{
   "tenant_access": "write"
}

That works, but I was thinking that it might be better to create a "tenant_admin" postgresql role. And add it to auth.users.role column. This looks like a more native approach. However, if I create this role, it will not have access like the standard users.

And I'm not a PostgreSQL expert. Is this really a better way of doing it? What do you think?

[GaryAustin1]

You should be able to grant 'authenticated' to your postgres role and it should then be able to do whatever that role can do.
I think it is grant authenticated to bob Then if your RLS TO role is authenticated it should work for bob also. It will not work for the auth.role() or using the role in the jwt calls in RLS.

[vvalchev]

After a short research, I'm starting to think that using custom Postgres roles, might not be the best way to proceed.

@GaryAustin1 , In the referred chat, you mentioned that

I'll also chime in again on the Supabase has not "blessed" this approach. It worked last year and then did work for several months when they changed the Postgres role. In the thread I linked Supabase itself never says this is supported with their REST API.

Do you still believe it? Shall I continue using auth.users.raw_app_metadata instead of migrating to postgresql roles?

References:

• https://discord.com/channels/839993398554656828/1108484119626780783
• https://github.com/orgs/supabase/discussions/11948

[GaryAustin1]

I would say if you know postgres well you have a better shot of dealing with things if something is changed by Supabase. I was discouraging that user because they did not seem to understand grants and privileges with postgres roles.

Using this method relies on Supabase granting postgres role certain privileges (which they have done after they broke it), but that is also needed for test jwt RLS in the SQL editor so may not have been done for the issue in your github link).
It also relies on using a column in the auth schema that is also not shown to be used by Supabase anywhere that I know of.

I doubt role column will change or be unavailable though. PostgREST itself is probably fine as it uses that without Supabase.
I'm not sure what storage-api and realtime will do with the role field as far as RLS, have not tested it so may work.