[Dbmail] Signing off ...
DBMail folks, I'm signing off this list because I ended up using AOX rather than DBMail. However, I'm available to answer specific PostgreSQL troubleshooting issues if you e-mail me directly, or find me on IRC.freenode.net (agliodbs). -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com ___ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
Re: [Dbmail] OT: DBMail Administrator (DBMA) Performance Fix
In MySQL (using InnoDB engine) there is no difference for between COUNT(*) or COUNT(1) because it is 'optimized' to use the PRIMARY index. FWIW, there's no difference in PostgreSQL either. --Josh ___ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
Re: [Dbmail] PostgreSQL complains about string missuse on INSERTS related to quotations marks
Paul, DBMail-2.2 doesn't use \\ escaping, and hasn't done so for quite some time. All strings are escaped using PQescapeString and PQescapeBytea which I assume will produce safe queries. So in my understanding the postgres warning is a false positive. But - Josh - please correct me if I'm wrong. Could be. Windows filenames tend to produce false positives, if nothing else. Also, it's possible that the user is using 8.3 as the database, but DBI is still bound to an older version of libpq. Worth checking. Anyway, in 2.3+ no more escaping is used at all. All insertions are done using parameter binding - that is, except for some numerical type insertions where the values come from a trusted source (internal). Glad to hear it. You're way ahead of the curve. --Josh ___ DBmail mailing list DBmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
Re: [Dbmail] PostgreSQL complains about string missuse on INSERTS related to quotations marks
Angel, Sample from my logs (In Spanish) ... HINT: Use '' para escribir comillas en cadenas, o use la sintaxis de escape de cadenas (E'\\'). WARNING: uso no estandar de \\ en un literal de cadena LINE 1: ..., messageblk,blocksize, physmessage_id) VALUES (1,'Received:... ... Well, you can get rid of these log messages by setting escape_strings_warning = off. However, the reason why we added the warning is that we figured out in 2006 that \\ escapes are a SQL injection vulnerability (this goes for MySQL as well). It's possible by version 8.5 (2010) PostgreSQL will stop supporting them. As such, it would be nice to see DBMail gradual migrate away from the use of \\ escapes. --Josh ___ DBmail mailing list DBmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
Re: [Dbmail] PostgreSQL complains about string missuse on INSERTS related to quotations marks
Peter Rabbitson wrote: Josh Berkus wrote: However, the reason why we added the warning is that we figured out in 2006 that \\ escapes are a SQL injection vulnerability (this goes for MySQL as well). It's possible by version 8.5 (2010) PostgreSQL will stop supporting them. Interesting... Can you cite some CVE reference or what have you? Google does not seem to be my friend today. http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2006-2314 http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2006-2313 Not sure where the MySQL announcement is. These issues affect them equally. --Josh ___ DBmail mailing list DBmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
Re: [Dbmail] Adding a master user/password for spam learning
Colin, Is this the advisable way to approach this problem in the first place? If so, what do I need to do to get that master user going? What about using dspam, which is database-based? Then you could simply do a query-based table copy. Also, keep in mind that this scheme will require you to do more database maintenance if you're using Postgres or InnoDB. --Josh ___ DBmail mailing list DBmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
Re: [Dbmail] postgresql sql-ascii
Paul, I'm pretty sure though that IMAP SEARCH will be broken if you use sql-ascii. Hmmm. Not if you're just executing it as LIKE queries, and the search terms don't include non-ascii characters. -- --Josh Josh Berkus PostgreSQL San Francisco ___ DBmail mailing list DBmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
Re: [Dbmail] archiveopteryx
On Wednesday 06 August 2008 22:32, Sim Zacks wrote: Archive Opteryx has a dbmail comparison from their perspective. Has anyone used that system and is able to do a comparison from a dbmail perspective? It looks to me like dbmail currently has more features then archive opteryx Different features. I'm actually currently considering achiveopterix because Abjhit already has full text search and virtual folders working. -- Josh Berkus PostgreSQL San Francisco ___ DBmail mailing list DBmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
Re: [Dbmail] searching woes
Michael, It could be we can find other optimzations as well. Paul, is there a simple way to log all DB queries to a separate file? Like this, we could log for a typical day, and then see which are the most queries and where we could start to optimize. I know you, Paul, have other things to do - so a query log would be nice that we can share here, and let the community optimize it. I'm sure there are some DB gurus on this list :-) On PostgreSQL, the way you do this is by adding log_statement and log_duration options, which will add queries and their execution times to the activity log. If you can do this on PostgresQL 8.3, you can keep the log in CSV format and then run aggregate queries to determine which queries are tripping you up. I'll be tackling a lot of this (at least for the PostgreSQL version) once I'm up and running on it. --Josh ___ DBmail mailing list DBmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
Re: [Dbmail] searching woes
Michael, I just found in the postgres config: #log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements # and their durations, in milliseconds. Looks like that should be enough for a query log. Would I still need log_statement=all, No. or is =ddl enough? DDL is for schema changes, like ALTER TABLE. That doesnt' do you any good for this. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ___ DBmail mailing list DBmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
Re: [Dbmail] searching woes
Paul, Very good question. The only solution I can think of is full text indexing. I'll take a stab at this once I have my server up and running. Necessarily, though, the solution will be database-specific. -- Josh Berkus PostgreSQL San Francisco ___ DBmail mailing list DBmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
Re: [Dbmail] searching woes
Paul, 2) Wouldn't the query be much faster if the ENCODE(k.messageblk::bytea,'escape') LIKE '%sim%' was in the WHERE clause instead of the HAVING clause? Try it. Afaik, it simply wont work. That depends on whether you're trying to return just a list of IDs, or the full contents of the messages. If you just need a list of IDs, you can use the WHERE clause. --Josh ___ DBmail mailing list DBmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
Re: [Dbmail] Questions about initial DBmail setup
Paul, Don't use it for anything other than testing. There are still some pretty fundamental issues to iron out. Thanks for the warning. 2.2 it is. Now, if only Ubuntu would update the $%@ packages ... 2) If I run queries against the mail in the backend database, and update or delete things, are there parts of dbmail which don't get their data from the database? No. Everything is in the database. Unless you are using LDAP for authentication. That is the *only* exception. Oh? Is there a non-LDAP option? I'd love to avoid LDAP -- I thought it was required. --Josh ___ DBmail mailing list DBmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
Re: [Dbmail] Questions about initial DBmail setup
Aaron Stone wrote: On Jun 25, 2008, at 10:16 AM, Josh Berkus wrote: Paul, Don't use it for anything other than testing. There are still some pretty fundamental issues to iron out. Thanks for the warning. 2.2 it is. Now, if only Ubuntu would update the $%@ packages ... 2) If I run queries against the mail in the backend database, and update or delete things, are there parts of dbmail which don't get their data from the database? No. Everything is in the database. Unless you are using LDAP for authentication. That is the *only* exception. Oh? Is there a non-LDAP option? I'd love to avoid LDAP -- I thought it was required. The non-LDAP option is the default: everything is in the database :-) Keen! Hopefully I'll be able to do a write up on replicated e-mail with dbmail ... Where do attachments go? --Josh ___ DBmail mailing list DBmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
Re: [Dbmail] Questions about initial DBmail setup
Paul, Keen! Hopefully I'll be able to do a write up on replicated e-mail with dbmail ... On postgresql perhaps? What, like I'd use something else? ;-) In 2.3+ attachments are indeed stored as atomic blobs in the mimeparts table. The rfc822 header part of the complete message, and the headerpart of attached mimeparts is also stored as blob in the mimeparts table. Mimeparts are linked into actual messages by the partlists table. Retrieval put the right blobs in the right order and depth, separated by the correct boundary. Are you using BYTEA or LO for PostgreSQL? The former is vastly easier to manage. More to the point: it looks like I have to rewrite the whole mime parser from scratch. GMime is not threadsafe, and adding mutex locks appears to completely kill performance. But then again, things may be not quite that bad after all. I havent figured it out quite yet. Which makes it just one of those things: they take time to work themselves out. Hmmm. I don't know mime parsers *at all*. Are you storing the attachments in MIME form, or in original format? -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ___ DBmail mailing list DBmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
[Dbmail] Questions about initial DBmail setup
DBMail folks, I'm setting up DB mail for my personal server (this account). I've a number of questions; forgive me that I don't know much at all about adminning mailservers. I know a lot about databases, though. 1) How unstable is 2.3.3? I'd like to try out some of the new features. 2) If I run queries against the mail in the backend database, and update or delete things, are there parts of dbmail which don't get their data from the database? 3) What do people use for spam filtering with dbmail? 4) Does anyone use egroupware with dbmail PostgreSQL? What do you think of egroupware? 5) If I added full text search to the backend database, any thoughts on how I could expose this to the mail client? -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ___ DBmail mailing list DBmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
