Multi tenant single database

[laurent512]

Hello,

I am building a SaaS which has to be multi tenant within a single database for cost efficiency reason.
My schema includes a Tenant table containing all the tenants with their Id , name and others props.
All the other models have a tenantId prop.

It means that in each update query for example, there is a where clause mentionning the relevant tenantId.
It creates a big security failure point if a dev forgets to specify the where clause.

Would there be a solution to enforce at database level that protection? Or during the connection process to the db ?
I am not an expert so I don't have any clear idea or implementation in mind but I would be curious if neon could provide such service :)

Thank you so much :)

[laurent512]

Any idea or suggestion? :)

[frenata]

At risk of directing you to a different tool/product, this is exactly what Nile does. Without something baked in like that, you can solve this with row based security rules, conditioned on a preset tenant_id, which you could set at database connection time.

I don't know off the top of my head if there's a way to enforce at the database level that tenant_id is always set but am particularly interested in this topic/space.

[frenata]

Here's a very rough POC:

CREATE TABLE IF NOT EXISTS store(id SERIAL PRIMARY KEY, balance integer not null, client_id integer not null);
create table if not exists client(id serial primary key, name text not null);

alter table store enable row level security;

create policy store_client on store 
using (client_id::text = current_setting('myparam.client_id', true));

-- now do some inserts to both tables

-- with a non-owner user! (owners bypass everything)

select * from store; -- should show nothing
set myparam.client_id = '1'
select * from store; -- should show just client 1 stores

-- at the application level, arrange things such that you set the custom param
-- based on whatever business context matters