Using events to override insert() #11389
Replies: 1 comment 1 reply
-
hi - having fields "required" for an INSERT means you just make sure the target column is NOT NULL in the database. an INSERT that fails to include a value for that column will fail. your request is "enforce the usage of tenant ids" so "enforce" usually means, "disallow it to be absent, i.e. fail". if someone is coding an insert() statement to pass directly to session.execute(), that's a special case for the ORM which one does if they are doing bulk inserts so it's reasonable that they need to include the tenant id as well in their statement. Also, for the ORM-enabled delete() and update(), the tenant id can be included in the same way as you do for select(), using the For unit of work, which is All of the above covers, "make sure a tenant_id is present, if not fail". now if you are trying to enforce that all tenant_ids within a transaction match a per-request transaction id, and fail if they dont match, that's a different level of enforcement. For that kind of thing I might want to look at PG's row-level security policies (https://www.postgresql.org/docs/current/ddl-rowsecurity.html) - this typically assigns per-row permissions to individual database users, however you'd want to use a session/transaction-level setting for this rather than individual database logins - I found an interesting blog post on how to do this at https://aws.amazon.com/blogs/database/multi-tenant-data-isolation-with-postgresql-row-level-security/ . if I were really worried about tenant security I would see if I could make that happen at the DB level, though I dont have direct experience using this feature. |
Beta Was this translation helpful? Give feedback.
-
I'm working on a multi tenant application and have desperately been trying to enforce the usage of tenant id's on all queries made in the application. It seems based on what I've read so far that the events, e.g do_orm_execute is the way to go since 2.0. Right now I'm attempting to cover select(), delete(), update(), insert() from the core using the do_orm_execute event. However, it seems like I'll also need to find ways to intercept the unit-of-work-style methods from the session .add(), delete(), update() (which might be a separate discussion, even though I'm highly curious on how to do that consistently)
For standard selects i've been using the with_loader_critera:
For delete's or updates I'm trying this (update looks identical more or less):
However, I'm lost as to how to approach inserts. Is there any decent approach here?
The is_insert attribute indeed exists, I'm just quite lost as to how to automatically add the tenant_id-key & value to the values(). Currently I always add the tenant_id to the session, so I can grab it from session.info, the challenge is doing something like this:
Edit 1:
This seems to work, at least on single inserts:
Edit 2 - This seems to support bulk inserts or single inserts with a dictionary:
E.g session.execute(insert(Course), {...})
or session.execute(insert(Course), [{}, {}])
and as previously mentioned, using .values()
session.execute(insert(Course).values(...))
Feedback?
Beta Was this translation helpful? Give feedback.
All reactions