On 28-05-14 13:35, Jean-Michel Pouré - GOOZE wrote: > Are there reasons for splitting the message table into several > sub-tables.
Lightweight copying of messages, ability to link messages to multiple mailboxes. > > For example: dbmail_messages and dbmail_physmessage and > dbmail_messageblks messageblks is not used anymore. Message are now decomposed into mime-parts and those are stored de-duplicated in mimeparts. > CREATE TABLE dbmail_messages dbmail_messages contains only meta-data (flags) specific to a mailbox context. > CREATE TABLE dbmail_physmessage is a link table with some meta-data (sizes and date) that are independent of the mailbox or owner. > The only reasons I see for separating those tables would be storing them > in different tablespaces. But even this is questionable. You misunderstand. > I am worried that this kind of separation might result on sequential > scans instead of memory and index scans. A JOIN without a reason is > always an expansive CPU task. Each row needs several CPU cycles. JOINS are unavoidable in a highly normalized schema. If you know a better way to retrieve the same information, please share. > > Under modern systems like PostgreSQL, it seems more interesting to have > a single table with advanced indexing: partial indexes and/or full-text > seach (FTS) and/or materialized views. These indexes can be placed on a > different tablespace, i.e. an SSD drive. Rebuilding FTS happens in > background, so that inserts are fast. This is a much modern approach > that splitting a table in three. FTS is not supported. DBMail works on multiple DBMS systems (mysql, postgresql, sqlite and oracle). FTS is non-standard. I've been thinking about a SOLR or ELK solution, but nothing has come of it so far. > Also, I am worried that running a lot of LEFT JOINs in the database > might open security breaches. This is the second issue also. Security issues from JOINs?? what are you talking about? > 2) Privilege separation > > I read the post on the WIKI with interest and would like to add this > information: what post is that? > > PostgreSQL supports CREATE SCHEMA: > http://www.postgresql.org/docs/9.4/static/sql-createschema.html > > CREATE SCHEMA is compatible with the prefix of tables. But it is more > powerful as we can restrict a connection to a schema. > > When connecting, you may restrict to a schema: > set search_path to 'schema' > > You may also "glue" a user to a specific schema: > ALTER USER user_name SET search_path to 'schema' Eh, the only user connecting to the database is the user specified in the dbmail.conf file. Also, running the dbmail schema inside a non-dedicated database is not standard. Allowing other users to connect is not supported for the reasons you describe. > Using schemas, it may be possible to create as many database structures > using an imap identifier (j...@doe.fr). Ok. Sounds cool. But can you still share some tables? Like the main message store? > > This is to prevent the scope of any SQL injection and limit it to the > sole account of the user. SQL injection is DBMail will be very, very hard, if at all possible. All queries that contain user generated values run as prepared statements. I really don't think that much of an issue. Being able to drop privileges to the imap user could be very useful for some use-cases. I'm not sure which - but it sounds cool. > > 3) Database performance / SQL injection > > Could you point me to any filter against database injection in the > source code. Where is it located in source code? That is not needed, as far as I know. Prepared statements dominate, and plain queries are only used where the parameters are internally generated from trusted values like integers. > 4) Performance / memory usage > > I found this kind of code in dm_mailbox.c > [snip] > IMHO , DBmail is not usable in production: Well, that is not what I hear from users, some of whom are running some pretty massive installations. > I am worried that it contains source code that allows SQL injection and > gives access to all messages at once. Without SCHEMA support I will > probably not install DBmail. Your worry is misguided in this respect. Talking about SQL injection without proof is pure FUD. This is not some crappy PHP wordpress module :-) > Would you welcome a discussion followed by a patch/review for PostgreSQL > to make it work using SCHEMAs? It should not alter table structure. > > Would you welcome a SCHEMA patch? What are your recommendations and > guidelines? Patches and discussions are always welcome. I know a thing or two, but some others will always know more, especially about optimizing queries. As to guidelines: Try not to introduce new coding styles. Make sure all tests run: cd jenkins; make test this will run all known tests agains postgresql and mysql backends. Patches that break compatibility with the supported systems I mentioned can not be merged. If it requires a schema migration, a migration for all systems is required. Only exception is Oracle, since that is not readily available. There is a small framework in place for those in the master branch. It's not yet documented so I'll have to explain it if and when. -- ________________________________________________________________ Paul J Stevens pjstevns @ gmail, twitter, github, linkedin www.nfg.nl/i...@nfg.nl/+31.85.877.99.97 _______________________________________________ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
