Multitenancy with tenant_id column

[Daniel Lister]

Hi all,

I'm working on a multi tenant web app with SQLAlchemy and I would like to use a tenant_id column on some tables to isolate data per tenant. Ideally, filtering on and adding this tenant_id would be automatic. I found this talk by the creator of Flask that briefly mentions a way to do this with sqlalchemy. This recipe goes into more detail on this approach but also points out some flaws. Primarily that it will not handle update or delete queries. To me this feels like a major flaw, a developer, used to having tenant_id handled automatically, may easily forget when performing an update or delete, thus creating a vulnerability. It proposes to solve the update and delete issue by using before_cursor_execute() event handler. However, as far as I can tell, this approach would involve editing the SQL text directly, defeating much of the point of using SQLAlchemy. Not as much of an issue but inserts are also not handled.

I also found this library that tries to solve the problem, however it is described as experimental and hasn't been updated in 5 years. It seems to have the problems of the above solution as well.

It seems to me that this is a common pattern and I'm surprised there aren't good, solid solutions out there for SQLAlchemy. This library supports this approach for Django.

Ideally I would love to have a session that is scoped to a tenant and automatically applies a tenant_id on every select, update, delete, and insert so that queries can be written without the need to consider tenant at all. Is this feasible? I would greatly appropriate if anyone could help or point me in the right direction. Thanks!

[Mike Bayer]

hi there -

tenant ID that is done on a per-schema basis is handled by the schema
name translation feature at
https://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=execution_options#schema-translating

for a SELECT criteria you would use the filtered query which is at
https://github.com/sqlalchemy/sqlalchemy/wiki/FilteredQuery and has
been modified and simplified to use events (note the bitbucket wiki is
obsolete, it's not supposed to be up). The issue with
query.update() and query.delete() not being included can be addressed
once we add event hooks that are analogous to before_compile() event
hook for Query into the persistence.BulkUpdate and
persistence.BulkDelete classes. The SQLAlchemy project would welcome
contributors who want to work on this as it's quite simple to add.

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

please see the patches in
https://github.com/sqlalchemy/sqlalchemy/issues/4461, in 1.2.17 the
recipe will be altered to read:

@event.listens_for(Query, "before_compile", retval=True)
@event.listens_for(Query, "before_compile_update", retval=True)
@event.listens_for(Query, "before_compile_delete", retval=True)
def before_compile(query, context=None):
"""A query compilation rule that will add limiting criteria for every
subclass of HasPrivate"""


until that release, you can also subclass Query to override the
update() and delete() methods so that the Query can be modified in
place.

[Mike Bayer]

On Tue, Jan 22, 2019 at 4:49 PM Daniel Lister <mrh...@gmail.com> wrote:

> Not as much of an issue but inserts are also not handled.

the Query object doesn't do INSERTs, INSERTs are controlled by
creating an instance of your mapped class with the desired values upon
it. I would imagine if tenant_id is required, you simply make that a
NOT NULL column in your table (which it would be anyway) then have
tenant_id be the first argument positional argument on all __init__()
methods. Or use the "init" mapper event which is called alongside
__init__(). There are lots of other ways to get at INSERTs too as
well as UPDATE/DELETE within the normal ORM flush process (e.g. not
bulk update() or delete()) using the mapper or session hooks.. How
you organize these depends on the coding patterns you seek to use
(e.g. explicit call everywhere, pulling from a thread local registry,
etc.)

[Daniel]

Hi Mike,

Thanks for your quick response. And quick patch! I look forward to the 1.2.17 release.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.
---

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/20ieAvQCp_4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--

-Daniel