Grooming sprint 34

[vttranlina]

• Postgresql implemented for James

[chibenwa]

Let's keep the Postgres stuff as a TeamMail topic for now.

Scope for next sprint:

• Identify a reactive driver for PostgresSQL. https://github.com/pgjdbc/r2dbc-postgresql ?
• Implement a little DAO (subscriptions) for it and the unit tests that goes along using docker.
• Experiment with row level security (isolation should be done at the domain level)
• Experiment with 1 user=role per domain and mutualize them on the same connection. CC ask @shepilov . https://www.postgresql.org/docs/current/sql-set-role.html ?

[Arsnael]

@chibenwa

Implement a little DAO (subscriptions) for it and the unit tests that goes along using docker.

You mean for mailbox subscriptions?

Experiment with row level security (isolation should be done at the domain level) Experiment with 1 user=role per domain and mutualize them on the same connection.

Does that mean each user of a same domain should have the same permissions?

[chibenwa]

You mean for mailbox subscriptions?

Yes.

Anything simple would do.

[chibenwa]

Does that mean each user of a same domain should have the same permissions?

From a multi-tenant approach, yes: they share the same row-level security.

However on top of row level security (that prevents from an architecture level cross-domain data breaches), we would of course have the James right management system that enforces per user permissions (shared mailboxes, etc...)

IE if we f** up james access controls, we would not breach data accross domains.

Clearer?

[vttranlina]

Identify a reactive driver for PostgresSQL. https://github.com/pgjdbc/r2dbc-postgresql ?

Quan found another driver https://vertx.io/docs/vertx-pg-client/java/ But it uses RxJava, not reactor

I checked Spring r2dbc, it can integrate with r2dbc-postgresql. (James is not related to Spring r2dbc, but it is one more positive point)

[vttranlina]

we would of course have the James right management system that enforces per user permissions (shared mailboxes, etc...)

Why do we need row level security even if we do the right management inside James code?

[vttranlina]

Implement a little DAO (subscriptions) for it and the unit tests that goes along using docker.

A quick lab it: https://github.com/apache/james-project/commit/659ea4ee342211d19c9f47ce54dcc132f69432c8 https://github.com/vttranlina/james-project/tree/postgres_lab

[vttranlina]

If I understand correctly, to apply RLS, for each table, we need one column domain

[vttranlina]

@chibenwa what do you think about spring-data-r2dbc or jOOQ? https://github.com/spring-projects/spring-data-relational/blob/main/spring-data-r2dbc/pom.xml https://www.jooq.org/doc/latest/manual/sql-execution/fetching/reactive-fetching/

// team suggest jOOQ

[vttranlina]

RLS Quick video demo: https://www.youtube.com/watch?v=98yxrZ3Aip4&ab_channel=EDB

[vttranlina]

Split tasks:

• Do subscriptionDAO by using a chosen driver (JOOQ or spring-data-r2dbc)
• Experiment with domain RLS (POC) -> QUAN

[chibenwa]

jOOQ => does it supports PGSQL specific features like row security? Does it supports other PGSQL features like objectStorage, fullTextSearch, triggers?

Ideally avoid ORMs... Optimizing their perfs is awefull and we gonna come down to it...

:+1: to invest on several POC.

I insist on a POC on r2dbc-postgresql or r2dbc related...

[chibenwa]

Why do we need row level security even if we do the right management inside James code?

Hard requirement. Because we can fuck up james code. A second line of defense is needed. This can not be discussed.

[chibenwa]

If I understand correctly, to apply RLS, for each table, we need one column domain

YES!

And we will be able to get it from the MailboxSession user domain.

Can you add RLS to your nice little POC ?

[quantranhong1999]

Can you add RLS to your nice little POC ?

https://github.com/vttranlina/james-project/pull/44

[Arsnael]

Split tasks:

• Do subscriptionDAO by using a chosen driver (JOOQ or spring-data-r2dbc)
• Experiment with domain RLS (POC) -> QUAN

Can we create tasks for that on the board and close the grooming? :)

[vttranlina]

• Do subscriptionDAO by using a chosen driver (JOOQ or spring-data-r2dbc)

created here https://github.com/linagora/tmail-backend/issues/847 https://github.com/linagora/tmail-backend/issues/848