[Postgres] Optimize findNonPersonalMailboxes method in PostgresMailboxDAO

[hungphan227]

The postgres query generated by this method take a lot time to run

select * from mailbox where ( mailbox_acl is not null and mailbox_acl -> $2 is not null and cast(mailbox_acl -> $3 as varchar) like (($4 || replace( replace( replace($1, $5, $6), $7, $8 ), $9, $10 )) || $11) escape $12 )

Find a way to optimize!

[hungphan227]

I have tried creating index for hstore to improve performance https://github.com/apache/james-project/pull/2296 However, when rls is enabled, postgres db does not use the index for hstore. It seems that postgres db considers operators of hstore as non leakproof (a function or operator is considered leakproof when it does not leak data to wrong user) so postgres db does not use hstore index for any security modes such as rls. Therefore, I propose another solution:

• create new table mailbox_acl:

  CREATE TABLE mailbox_acl (
  username TEXT,
  mailbox_id UUID,
  rights TEXT,
  PRIMARY KEY (username, mailbox_id)
  );

• create index for username column
• in james, we retrieve mailbox_acl records from db by username then filter mailbox_id by right then retrieve mailbox records from db

@chibenwa do you agree?

[chibenwa]

@chibenwa do you agree?

Hi.

I am not really a big fan of this to say the least.

This move would force to get a join, either on the DB or the application side, and it is completly accidental when RLS is turned off...

I would agree if, and only if, we find a way to use mailbox_acl additional table if and only if RLS is turned on...

[hungphan227]

draft pr https://github.com/apache/james-project/pull/2296