Thank you for explanations.
> Lightweight copying of messages, ability to link messages to multiple
> mailboxes.
[...]
> JOINS are unavoidable in a highly normalized schema. If you know a
> better way to retrieve the same information, please share.
OK, I understand the logic now.
IMHO, it should not be allowed to link a single message to multiple
mailboxes. Because multiple mailboxes are read by different people. This
opens a possible security breach.
As far as I know, you are not reading someone else message because you
are in copy. The same should apply for DBMail.
> Security issues from JOINs?? what are you talking about?
This one in imapd.c:
p_string_printf(query, "SELECT m.message_idnr, n.headername,
v.headervalue%s "
"FROM %sheader h "
"LEFT JOIN %smessages m ON h.physmessage_id=m.physmessage_id "
"LEFT JOIN %sheadername n ON h.headername_id=n.id "
"LEFT JOIN %sheadervalue v ON h.headervalue_id=v.id "
"WHERE m.mailbox_idnr = %" PRIu64 " "
"AND m.message_idnr %s "
"AND n.headername %s IN ('%s') "
"ORDER BY message_idnr, seq",
not?"":fieldorder->str,
DBPFX, DBPFX, DBPFX, DBPFX,
self->mailbox->id, p_string_str(range),
not?"NOT":"", bodyfetch->hdrnames);
IF(?) someone is able to inject m.mailbox_idnr value using a modified
imap client, this query allows to read any emails in the database.
An SQL injection can happen when there is a security breach somewhere
else.
> > 2) Privilege separation
> >
> > I read the post on the WIKI with interest and would like to add this
> > information:
>
> what post is that?
http://www.dbmail.org/dokuwiki/doku.php/privsep
> >
> > 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.
Why not change this and have:
* one DB user
* and one SCHEMA
=> for each IMAP account?
> > 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?
Again, I think sharing information between users is a security breach.
The main reasons why servers are getting hacked are (1) dns forgery (2)
email systems. Penetration of email systems are very painful as they
reveal secrets.
> > Could you point me to any filter against database injection in the
> > source code. Where is it located in source code?
It is very likely that some SQL injection is possible in the code. I did
not test or looked deeply, but since you dont' filter SQL statements AND
share tables between users, it is ***VERY*** likely.
> 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.
> Well, that is not what I hear from users, some of whom are running some
> pretty massive installations.
Some queries seem pretty intensive, like reading all messages and
transferring in memory. Should use LIMIT and OFFSET. This is not a
priority.
> 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.
OK, thanks it is clear and I agree.
I will first install DBmail as a copy of our Postfix server to build a
database and will then study the SQL logs and source code.
I am not sure whether mySQL supports schemas, for sure it can be
emulated using table prefix and proper ownership. By the way, I am not
very interested in MySQL, as the developer community is dead and MySQL
is non-standard and slow and low-featured (but always boasting to be the
best).
Kind regards,
Kellogs
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
