Postgres Row Level Security needs a tenant id column in each table and some DB user security settings and can then allow a tenant to access only their data.
Anyway, to the point To those who have implemented a Row Level Security multi tenant solution in TimescaleDB: How did it affect performance?
Unfortunately using row-level security isn’t supported if you want to use Continuous Aggregates (and they’re a challenge more broadly if you want to use VIEWs). It’s one of the limitations listed in the CREATE MATERIALIZED VIEW docs.
I honestly haven’t done any testing around their performance impact more broadly, so I’ll be interested to see if others have real-world experience to share!
I haven’t implemented row level security for my Multi tenanted system but i am certain I would not go for it.
I have in other systems implemented the model described in
C1 - Shared tables with column for tenant identification
It has worked really well.
In timescale Db my requirement was for a.more dynamic approach where ownership can belong to one or many tenants and this can change via configuration.
I guess my model is similar to C1 but I have instead implemented a more dynamic security approach in my data access layer. It gives me the ultimate flexibility and control.
i don’t persist ownership of the row against single tenant. But rather I persist a collection data points which a single user can access.
Essentially the data access layer looks at the permissions of an individual user and then applies a "where deviceid IN (ListOfDevicesPermittedByUser)
Ofcourse this approach prevents direct data access by users to the database and all data access must be via the data access layer - ultimately exposed via a RESTful API.
Hope this gives you some food for thought.
The best resource I have ever found is below. I didnt follow exactly his approach but definitely took inspiration from it.
but wanting to hear the experience of others at this early stage of our project.