Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add support for shared-schema multi-tenancy #2682

Open
lukaseder opened this issue Aug 8, 2013 · 10 comments

Comments

Projects
None yet
5 participants
@lukaseder
Copy link
Member

commented Aug 8, 2013

With the implementation of #2665, jOOQ can implement shared-schema multi-tenancy out of the box - similar to the pre-existing separate-schema / separate-table multi-tenancy, which is currently implemented through schema-mapping and table-mapping.

However, it is very difficult to get VisitListener implementations right. For most use-cases, it would be much better if there were special types of tables that are "multi tenancy enabled", which essentially means that they're either replaced by views if the underlying database supports updatable views, or the SQL is transformed such that predicates are added to the local WHERE clause. Example:

-- Input SQL using multi-tenant table t, and single-tenant table u
SELECT t.a, u.b FROM t, u

UPDATE t
SET t.a = ?
WHERE t.b IN (SELECT b FROM u)

-- Output SQL in databases supporting updatable views
SELECT t.a, u.b FROM (SELECT * FROM t WHERE t.tenant = ?) AS t, u

UPDATE (SELECT * FROM t WHERE t.tenant = ?)
SET t.a = ?
WHERE t.b IN (SELECT b FROM u)

-- Output SQL in databases not supporting updatable views
SELECT t.a, u.b FROM t, u WHERE t.tenant = ?

UPDATE t
SET t.a = ?
WHERE t.b IN (SELECT b FROM u)
AND t.tenant = ?

More insight can be seen in this interesting MSDN documentation page:
http://msdn.microsoft.com/en-us/library/aa479086.aspx

This was also requested in this thread here:
https://groups.google.com/d/msg/jooq-user/xIL7_2q1QfU/7qeJ6DqTMxAJ

And in this discussion here:
#7642 (comment)

@bretwalker

This comment has been minimized.

Copy link

commented Sep 9, 2016

@lukaseder is this still targeted for 3.9.0? Thanks!

@lukaseder

This comment has been minimized.

Copy link
Member Author

commented Sep 10, 2016

@bretwalker Thanks for reaching out.

This kind of feature hasn't been discussed / requested for quite a while. Also, many databases seem to have better solutions for this problem, e.g. built-in row-level security.

What's your own use-case?

@mkurz

This comment has been minimized.

Copy link

commented Feb 15, 2017

SQL Server 2016 and Postgres 9.5 finally added Row-level security so probably this isn't an urgent issue anymore. However I vote +1 for this feature to land in JOOQ.

@lukaseder

This comment has been minimized.

Copy link
Member Author

commented Feb 15, 2017

@mkurz : Interesting, do you have a pointer to the PostgreSQL feature?

@lukaseder

This comment has been minimized.

Copy link
Member Author

commented Feb 16, 2017

Oh, very interesting. Well, could've googled that ... :)

@tianguiyuan

This comment has been minimized.

Copy link

commented Feb 16, 2017

very interesting

@Zynde

This comment has been minimized.

Copy link

commented Nov 12, 2017

Does anyone know if the is any shared-schema multi tenancy features in JOOQ or has there been and feature added for the Row Level Security in PostgreSQL?

@lukaseder

This comment has been minimized.

Copy link
Member Author

commented Nov 12, 2017

@Zynde: jOOQ doesn't support it out of the box, but you might be able to roll your own:
https://blog.jooq.org/2015/06/17/implementing-client-side-row-level-security-with-jooq

I'm not aware of such a feature in PostgreSQL thus far, unlike Oracle or SQL Server. You might be able to emulate it, however, using views and PL/PERL global variables, similar to Oracle's SYS_CONTEXT:
https://www.postgresql.org/docs/current/static/plperl-global.html

@mkurz

This comment has been minimized.

Copy link

commented Nov 12, 2017

@Zynde @lukaseder Like I pointed out a couple of comments above PostgreSQL does support Row-Level Security since version 9.5:

https://www.depesz.com/2014/10/02/waiting-for-9-5-row-level-security-policies-rls/
https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.5#Row-Level_Security_Policies
https://www.postgresql.org/docs/10/static/ddl-rowsecurity.html
Also, PostgreSQL 10 enhances RLS by adding support for restrictive policies (besides permissive policies)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.