I am currently experimenting with adopting PostgreSQL row-level security for a server-side application in Laravel. 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.
So far, I've experimented with two approaches:
Modifying the query in a listener
This did not work because DB::listen callbacks trigger after the query has been executed. I could not find any other callbacks or Laravel Events that seemed appropriate.
Using HTTP middleware
DB::statement("SET app.tenant = '<current_tenant_id>'");
This seems to work, but it is less than ideal for a few reasons:
- It will break if I ever switch to separate read/write connections, because this code will choose a connection to operate on before performing any other DB operations.
- It's an incomplete solution, as it does not address, for example, background jobs. I could implement this in a background job base class, but that solution then suffers from the same problem as 1.
It also seems that I may be swimming upstream here, as I've yet to find any libraries, tutorials, or write-ups by anybody else attempting to do this. All of the many Laravel tenancy libraries I have looked at seem to rely entirely on either connection switching (multi DB) or WHERE clause (single DB) solutions.
Is there a way I can take advantage of row-level security from within Laravel, in a way that is mostly transparent to the development team?
