Altering queries to contain tenant_id columns in multi-tenancy

[Jarkko Miettinen]

Hi,

I am planning to transform an existing piece of software using jOOQ to support multi-tenant data. As tenants share some data, multiple databases or schemas seem to make things very hard.

So next idea is to use a discriminator column (tenant_id) in tables that have tenant-dependent data.

I thought I could use VisitListener to rewrite queries to contain a where-clause limiting such queries into only one tenant (basically, add where tenant_id = ?).

Is this doable in theory? In practice?

To me, at least doable in theory seems correct as I'll get to visit all parts of the query.

But as I did not find anyone having done that, in practice seems less certain.

Is this a silly approach?

[Lukas Eder]

Hi Jarkko,

Thanks for your message. A future version of jOOQ will support shared schema multi tenancy out of the box:

https://github.com/jOOQ/jOOQ/issues/2682

It will be based on the new experimental replacement API:

https://www.jooq.org/doc/latest/manual/sql-building/model-api/model-api-replacement/

You can already build your own implementation based on the above (though note it's experimental, so there are still some missing features (e.g. no support for unions) and slight risk of breaking changes).

You can also use a VisitListener, of course, or combine the two approaches. There's a high-level example in this blog post showing how to do this with a VisitListener:

https://blog.jooq.org/implementing-client-side-row-level-security-with-jooq/

Though, with the replacement API, it will be much simpler.

I hope this helps,
Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/59c1d6d3-0dcc-4a7e-94b1-37a7cf872d39n%40googlegroups.com.

[Jarkko Miettinen]

Thanks!

It seems that VisitListener is indeed the way to go. 

It seems that Clauses will get removed at some point, so would it be best to do the same inference using QueryPart-subinterfaces?

I'll also take a look into the replacement API.

Direct support sounds great but I guess it's somewhere a bit further in the future?

[Lukas Eder]

On Thu, Aug 10, 2023 at 6:23 PM Jarkko Miettinen <eon...@gmail.com> wrote:

Thanks!

It seems that VisitListener is indeed the way to go. 

It seems that Clauses will get removed at some point, so would it be best to do the same inference using QueryPart-subinterfaces?

Clauses were flawed in a way that they are very hard to formalise forward compatibly. For example, assume a jOOQ version where GROUP BY only supported simple grouping sets. So GROUP BY would immediately contain fields. But then, suddenly, it would start supporting GROUPING SETS, which can be nested. This would require new clauses, probably? But how to communicate this to client code?

With QueryPart only checks, you can't really know "where you are" within a query. E.g. when you're visiting a Condition from within a Select, is it a WHERE, HAVING, QUALIFY condition?

So, with Clause being deprecated, the goal was to move towards the new experimental model API. You won't wait until you reach a Condition within a Select to make transformations. Instead, you transform the Select itself, depending on its Condition. It's only at the level of the Select object where you have complete information allowing to decide what to do. If you're waiting for the Condition visit event, then you'd need some stack maintenance to "remember" what the Select was, and that's way more complicated.

The blog post I've linked to is complicated as well. It is "clause aware", i.e. it tries to remember whether WHERE has already been rendered. That's because the VisitListener is triggered during the rendering of your SQL query. It would be much simpler to just transform the Select object itself (which you can do from within a VisitListener, btw).

This is why I said a combination of both VisitListener and replacement API will offer the simplest solution.

 

I'll also take a look into the replacement API.

Direct support sounds great but I guess it's somewhere a bit further in the future?

"Somewhere", yes. I don't think it'll make it in jOOQ 3.19, as that is already quite the release and I'd love to ship implicit path correlations and to-many paths:

https://www.jooq.org/doc/dev/manual/sql-building/sql-statements/select-statement/implicit-path-correlation/

But it's a high priority for me as I think it would add a lot of value, and I haven't seen many other ORMs capable of doing such things.

Best Regards,

Lukas 

[Fatih Eser]

Hi Lucas,
We are trying out JOOQ policies to check if it fits with our multi-tenancy requirements.
Is it possible to propagate ExecuteContext to the PolicyProvider.provide, so that we can have conditional provider based on current query context?

Here is an example to by pass tenant id based on query context.

```

        override fun <R : Record?> provide(table: Table<R>?, ctx: ExecuteContext): MutableList<Policy<R>> {
            val byPassTenantId = ctx.data(BY_PASS_TENANT_ID)
            val tenantId = ctx.data(TENANT_ID)

            return if(byPassTenantId != true) {
                DefaultPolicyProvider()
                    .append(ITEMS, ITEMS.TENANT_ID.eq(tenantId))
                    .provide(table)
            } else {
                DefaultPolicyProvider()
                    .provide(table)
            }
        }

```

Best Regards,

Fatih

[Lukas Eder]

Hi Fatih,

Thanks for your message. This sounds similar to what I've done in jOOQ 3.18 with the Converter SPI:

https://github.com/jOOQ/jOOQ/issues/13961

There, a new ContextConverter SPI was introduced, which optionally received a ConverterContext containing the usual Scope information.

The main reason to do that there is because Converters are typically attached to generated code, which is initialised statically, so there is no other way to supply context information. This is different in your case. A PolicyProvider is always attached to a Configuration, so it's possible to have 1 Configuration instance per tenant. I specifically thought of this use-case and expected users to have multiple Configuration instances in those cases, as is already common when having multiple DataSources, one per tenant.

I can see how this may not be the desired approach in some cases though, especially when there are many tenants, in case of which caching in the Configuration won't work well anymore. I've created a feature request for this, but cannot promise prioritisation, since I'm not convinced this is really necessary:

https://github.com/jOOQ/jOOQ/issues/16311

As a quick workaround, you can always use a ThreadLocal that you initialise and clean up in an ExecuteListener, and read its contents from the PolicyProvider.

I hope this helps,

Lukas

On Mon, Feb 19, 2024 at 7:42 PM 'Fatih Eser' via jOOQ User Group <jooq...@googlegroups.com> wrote:

Hi Lucas,
We are trying out JOOQ policies to check if it fits with our multi-tenancy requirements.
Is it possible to propagate ExecuteContext to the PolicyProvider.provide, so that we can have conditional provider based on current query context?

Here is an example to by pass tenant id based on query context.

```

        override fun <R : Record?> provide(table: Table<R>?, ctx: ExecuteContext): MutableList<Policy<R>> {
            val byPassTenantId = ctx.data(BY_PASS_TENANT_ID)
            val tenantId = ctx.data(TENANT_ID)

            return if(byPassTenantId == true) {

                DefaultPolicyProvider()
                    .append(ITEMS, ITEMS.TENANT_ID.eq(tenantId))
                    .provide(table)
            } else {
                DefaultPolicyProvider()
                    .provide(table)
            }
        }

```

Best Regards,

Fatih

--

You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/1925b4b6-1a2c-4339-99f7-6dfc6b2a2349n%40googlegroups.com.

[Lukas Eder]

To give some more context, PolicyProvider isn't the only SPI that could profit from such an enhancement. In fact, most SPIs don't have access to a Scope instance, simply because they are always part of a Configuration that can already contain all the necessary user information. I've listed the other SPIs here:

https://github.com/jOOQ/jOOQ/issues/16311#issuecomment-1953605555

I don't think it's worth looking into this in isolation, i.e. I don't want to change PolicyProvider without considering all the others, like TransactionListenerProvider, for example.

[Fatih Eser]

Thanks for the response! We will track the GitHub issue.