search

supabase / supavisor

A cloud-native, multi-tenant Postgres connection pooler.
https://supabase.github.io/supavisor/
Apache License 2.0
1.66k stars 55 forks source link

Isolate tenant's database #290

Closed jahnavisana2812 closed 5 months ago

jahnavisana2812 commented 5 months ago

Improve documentation

Link

https://supabase.github.io/supavisor/configuration/tenants/

Describe the problem

I'm currently operating Supavisor on a locally hosted PostgreSQL server, enabling me to create tenants within a specific database. Given that there's a product serving multiple clients from this single database, the objective is to configure each tenant in accordance with its respective client. Is it possible to isolate each tenant's data?If yes can you please share any documentation on how this can be executed ?

fenos commented 5 months ago

@jahnavisana2812 Your use case seems more aligned with the use of RLS policies.

Potentially Supavisor could be injecting the tenant-id as a local configuration during connection, ex: SELECT set_config('supavisor.tenant_id', 'abcd') so that you can apply RLS policies using this value.

currently is not setting this option afaik, but you could use this same approach at the application level.

const client = sqlClient.connection()

// session mode
client.raw(`SELECT set_config('pooler.tenant_id', request.tenantId', false)` 

// tnx mode
const tnx = await client.transaction()
tnx.raw(`SELECT set_config('pooler.tenant_id', request.tenantId', true)` 

tnx.select('*').from('sometable')

then with the proper RLS it will isolate data for each tenant

ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_access ON my_table
FOR ALL
USING (tenant_id = current_setting('pooler.tenant_id'))
WITH CHECK (tenant_id = current_setting('pooler.tenant_id'));

Closing for now, as this can be done at the application level, but will re-open if we are thinking to introduce the local setting at connection time