I am currently experimenting with adopting PostgreSQL row-level security for an application. My goal is to move multi-tenancy access rights into the DB, so as to completely remove them from application logic. This requires me to add a SET app.tenant = 'current_tenant_id' statement to the beginning of all database sessions. This needs to happen for both Eloquent ORM queries and raw DB queries.
In my research thus far, I've yet to find any libraries, tutorials, or write-ups supporting doing this in Laravel. All of the solutions out there seem to either rely on connection switching (multi DB) or injecting WHERE clauses (single DB). Obviously I could choose one of these options, but I would prefer to take advantage of RLS. I've also yet to find an obvious way to inject the necessary SQL on every connection.
Is there a good place to "hook into" the connection or SQL building processes to make this happen most seamlessly?
After learning some more advanced SQL and postgres I was looking into it too and afaik I found no way other than writing raw sql triggers and functions to leverage rls at the database level.
Arguably my quest felt much like trying to recreate supabase approach to permissions in laravel (or symphony). This thought, my lack of sql expertise and the powerful gates and policies of laravel led me to completely question the approach.
I used spatie multitenancy (because it supports octane) and spatie permissions (more granular control). Both packages are more than excellent and combined together I really don't miss anything feature-wise and performance-wise.
RLS indeed is a nice feature but imho it would only make sense in very minimalist frameworks.
Off topic ramble.
I wish there were more postgres specific packages expanding our eloquent experience and some sql wizards are already after it:
