Re: [Dovecot] SQL Server

[Maxwell Reid]

You can use the old sybase driver on unix to access MSSQL.  You'll likely need 
to patch dovecot to support it beyond that.

~Max


On May 21, 2010, at 6:26 AM, Bryan Vyhmeister wrote:

> Is there any support for using Microsoft SQL Server 2005 or 2008 as
> the SQL database for users? Although I would prefer to do it other
> ways, the company I work for has all of their virtual hosting
> authentication tied into a SQL Server. Any ideas? Thank you.
> 
> Bryan


You can use the old sybase driver on unix to access MSSQL.  You'll likely need 
to patch dovecot to support it beyond that.

~Max

Re: [Dovecot] SQL Server

[Michael Orlitzky]

On 05/21/10 18:00, Bryan Vyhmeister wrote:

On Fri, May 21, 2010 at 4:10 PM, Jerry  wrote:


If you can access the Microsoft SQL Server from the machine hosting
Dovecot, there should be no problem at all. I have used Microsoft's SQL
sever for for several projects and it is an extremely fast and robust
piece of software.

Perhaps you could post a clearer picture of exactly what you are
attempting to accomplish.


Sorry. I should have been more specific. I want to use SQL Server for
the userdb and password database. I looked through the wiki and did a
number of searches but could not find an answer. If I were to use
sqlite, mysql, or pgsql, these would be listed under driver = in the
dovecot-sql.conf (or whatever you want to name it). How would you go
about using SQL Server? There is no sql-server driver as far as I
know. I have and am currently using dovecot from passwd files and from
sqlite databases so I am pretty familiar with how everything works. I
am just not sure how to go about this for SQL Server. Thank you.

Bryan


You can specify arbitrary .NET DLL functions to be executed from 
triggers in Microsoft SQL Server, and there are .NET libraries to talk 
to Postgres, so just do it the other way around: run Dovecot off of a 
local Postgres install, and feed it data from Microsoft SQL every time 
something changes.


Here's an example assembly.

/* Begin C# */
using System;
using System.Net;

public class StoredProcedures {

  public static void InsertPostgres(string username, string password) {
   /* This part is your job. */
  }

  public static void UpdatePostgres(string username, string password) {
   /* This part is your job. */
  }

  public static void DeletePostgres(string username, string password) {
   /* This part is your job. */
  }

}
/* End C# */

Now,

1. Compile the assembly to a DLL. Call it e.g. PostgresStoredProcedures.

2. Enable CLR code to run within the database:

  EXEC sp_configure 'clr enabled', 1;
  RECONFIGURE;

3. Make your database trustworthy. This is necessary because it will
be accessing external resources.

  ALTER DATABASE  SET TRUSTWORTHY ON

4. Add the assembly to your database. This will fail if you haven't
set TRUSTWORTHY ON in step 3. Note that this should be done *within*
the database, not to SQL server in general (i.e. the master database).

  CREATE Assembly PostgresStoredProcedures
  FROM ''
  WITH PERMISSION_SET = EXTERNAL_ACCESS

5. Create the stored procedures within the database. Their signatures
should match those of the class methods.

  CREATE PROCEDURE InsertPostgres(@username nvarchar(256),
  @password nvarchar(256))
  AS
  EXTERNAL NAME PostgresStoredProcedures.StoredProcedures.InsertPostgres

  CREATE PROCEDURE UpdatePostgres(@username nvarchar(256),
  @password nvarchar(256))
  AS
  EXTERNAL NAME PostgresStoredProcedures.StoredProcedures.UpdatePostgres

  CREATE PROCEDURE DeletePostgres(@username nvarchar(256),
  @password nvarchar(256))
  AS
  EXTERNAL NAME PostgresStoredProcedures.StoredProcedures.DeletePostgres

6. You can now execute the stored procedures from within SQL:

  exec InsertPostgres 'u...@example.com' 'password'


At this point, you just need to define three triggers on your user 
database -- one for each of insert, update, and delete -- that will 
execute those stored procedures. This is left as an exercise (but really 
is trivial once you've made it that far).

Re: [Dovecot] Thunderbird very slow startup, 1.2.11, mbox, postfix local delivery to /var/mail

[Stan Hoeppner]

Timo Sirainen put forth on 5/22/2010 3:47 AM:
> On 9.5.2010, at 9.54, Stan Hoeppner wrote:
> 
>> It's frustrating trying to solve this, to say the least. :(  I don't have
>> enough in depth knowledge of either application to figure this out on my
>> own.  It seems the only way to communicate with the Tbird devs is via bug
>> reports.  I can't really file a Tbird bug report as I just don't have enough
>> information available to file one that they can do anything with.
> 
> If you didn't solve it yet .. the way I normally try to figure out why 
> something is taking 100% CPU load is attaching gdb to the process and getting 
> a backtrace. I don't know if there's an easy way to do that with Windows, but 
> if you can manage to do that a few times for a build with debug symbols 
> enabled, it could be enough for developers to figure out what the problem is.

I turned every related knob I could find in about:config and couldn't fix it.
 And since I use Roundcube now and then I've been wanting to do server side
sorting anyway.  So, instead of attempting any kind of tracing of TBird, I
switched to LDA, wrote a sieve script, and set TBird to check for new mail in
IMAP folders.  Afterward, when I'd click on an IMAP folder containing new
mail, small folders would respond instantly displaying the new mail and the
old already cached mails.  On large folders (around 3000+ messages and up) it
would display the old cached mails instantly, but it would take a few seconds
to show the new messages, up to 10 seconds for folders containing 10k+
messages.  This didn't really make sense to me as I thought Dovecot's index
architecture would work faster.

Upon investigating this issue, I found that the imap process was spinning at
100% CPU until TBird displayed the new messages.  As a test I deleted all the
messages in my debian-users list mail folder which was at 13k+ messages.  I
rarely if ever searched it and there is a public archive, so I just whacked
it.  After doing so, upon opening the folder which would have say, 80 new
messages in it, the imap process ran 100% CPU for a second or less, and all
the new messages displayed pretty much instantly.

This led me to do more testing, and it basically appears that on my server,
there is a linear relationship between mailbox (and thus index.cache) size and
response time, regardless of how many new messages are in the imap folder
(mbox file).  Given that TBird caches all message headers, even when not using
gloda, when it asks Dovecot for new mail in a folder, Dovecot would simply
respond by sending new mail headers.  Maybe this is exactly what Dovecot does.
 But apparently it's reading a lot more from the cache file or the mailbox
file, as it takes forever to respond if the mbox file (imap folder) and
respective index.cache file is large.

I've since thinned out most of my list mail folders that were 3000-1+
messages.  All of my folders are at less than 1,000 messages now except for my
spam-l folder which is 13,200 messages.  All folders respond instantly now
when I select them regardless of the number of new messages, _except_ the
spam-l folder.  It can have one new message in it or 100 new messages, either
way, it takes almost exactly 10 seconds for TBird to display the folder
contents and the Dovecot imap process runs 100% CPU for that 10 seconds.  If
there are no new messages, the folder displays instantly.  The
dovecot.index.cache file is 30MB.  The mbox file is 57MB.  Apparently Dovecot
is reading more than just the new message headers?

-- 
Stan

Re: [Dovecot] dovecot from debian lenny-backports does not accept connections

[Charles Marcus]

On 2010-05-22 4:35 AM, Timo Sirainen wrote:
> On 20.5.2010, at 21.59, Charles Marcus wrote:
>> Hmmm... postfix handles this by simply letting the last one 'win', which
>> seems like a reasonable way to handle that.
>> 
>> Any chance 2.0 could work the same way? This way I could keep all of my
>> settings in a separate file, and then include them in the main file, and
>> I'd be sure that my custom settings overrode any of the defaults.

> That's how Dovecot works too,

 I did test this real quick before posting, but I goofed - what I
did was add 'protocols = pop' to the bottom, where above it was
'protocols = imaps' - and of course dovecot -n barfed since it should be
pop3, not pop.

Sorry for the noise - I haven't used POP in many many years, so gloss
over anything POP related - I'm glad to see it is indeed working like
this... :)

-- 

Best regards,

Charles

Re: [Dovecot] quota and postfix virtual users

[Simone Caruso]

Il 22/05/2010 04:28, gbot...@emailforall.no-ip.org ha scritto:



hi everyone i have a mailserver working with postfix 2.5.1 vda and
virtual user with mysql and dovecot, everything is working fine but i want
to offer quota and i have a problem with that because the quota in
roundcube appear but calculate is wrong i need to delete the maildirsize
file to force to recalculate, what can i do??? in my dovecot i have imap{
quota imap_quota, in plugins section i have quota=maildir and trough the
file dovecot-sql.conf i have the select expression concat(.. as
quota_rule, thanks a lot,

PD. sorry for my english im from south of
colombia - south america


the query should be like this if u have quota counted in bytes:
CONCAT('*:bytes=',imap_quota) AS quota_rule


--
Simone Caruso
IT Consultant

Re: [Dovecot] sshfs and outlok express imap directory problem.

[Timo Sirainen]

On 8.5.2010, at 1.32, ringo tr wrote:

> May  8 01:25:57 cbumail1 dovecot: imap(ya...@yvz.cbu.edu.tr): 
> link(/home/vpopmail/domains/yvz.cbu.edu.tr/yavuz/Maildir/cur/1273050327.14629.cbumail1,S=1385:2,S,
>  
> /home/vpopmail/domains/yvz.cbu.edu.tr/yavuz/Maildir/.zako/tmp/1273050327.14629.cbumail1,S=1385)
>  failed: Function not implemented

maildir_copy_with_hardlinks = no

Re: [Dovecot] Dovecot Won't start on SLITAZ Linux

[Timo Sirainen]

On 6.5.2010, at 11.51, Ludovic LEMARINEL wrote:

> 2010-05-06 12:35:12 dovecot: Fatal: setrlimit(RLIMIT_DATA, 256): Operation
> not permitted

auth_process_size = 0 probably helps.

Re: [Dovecot] Thunderbird very slow startup, 1.2.11, mbox, postfix local delivery to /var/mail

[Timo Sirainen]

On 9.5.2010, at 9.54, Stan Hoeppner wrote:

> It's frustrating trying to solve this, to say the least. :(  I don't have
> enough in depth knowledge of either application to figure this out on my
> own.  It seems the only way to communicate with the Tbird devs is via bug
> reports.  I can't really file a Tbird bug report as I just don't have enough
> information available to file one that they can do anything with.

If you didn't solve it yet .. the way I normally try to figure out why 
something is taking 100% CPU load is attaching gdb to the process and getting a 
backtrace. I don't know if there's an easy way to do that with Windows, but if 
you can manage to do that a few times for a build with debug symbols enabled, 
it could be enough for developers to figure out what the problem is.

Re: [Dovecot] dovecot from debian lenny-backports does not accept connections

[Timo Sirainen]

On 20.5.2010, at 21.59, Charles Marcus wrote:

>>> I also think it would be a good idea to report the line# in the
>>> config file where the error resides (can make it much easier to
>>> find the setting in a large, heavily commented config file)...
> 
>> This is unfortunately difficult. The config file is first parsed and
>> only afterwards validated. It's even more problematic if the setting is
>> overwritten in different places in config file..
> 
> Hmmm... postfix handles this by simply letting the last one 'win', which
> seems like a reasonable way to handle that.
> 
> Any chance 2.0 could work the same way? This way I could keep all of my
> settings in a separate file, and then include them in the main file, and
> I'd be sure that my custom settings overrode any of the defaults.

That's how Dovecot works too, but you could have for example:

foo = bar
protocol imap {
  foo = foo
}

The validation stage then tracks both imap and non-imap protocols and needs to 
give the line number for the right one. It probably wouldn't be too difficult 
to do, but not really something I want to spend time on right now.

Re: [Dovecot] SQL Server

[Pascal Volk]

On 05/22/2010 09:56 AM Stan Hoeppner wrote:
> …
> Have you ever used FreeTDS Pascal?

No, there was no need for it. I'm happy with PostgreSQL.


Regards,
Pascal
-- 
The trapper recommends today: 5e1f1e55.1014...@localdomain.org

Re: [Dovecot] dovecot/deliver ... Can't open log file /var/log/dovecot/error.log: Permission denied

[Timo Sirainen]

On 21.5.2010, at 16.04, Phil Howard wrote:

> There are a number of posters on the list where the reply goes directly to
> the list alone.  I don't know what it is they do with the headers to get it
> to come out that way.  Maybe you can ask them what they do, then do that.

http://dovecot.org/cgi-bin/mailman/listinfo/dovecot -> edit options -> Set 
Reply-To header to list = yes

I think that's the best solution for now. Some people who don't have time to 
read this list all the time prefer to get Cc'd when their question is answered, 
so that's why I think reply-to-all is the right solution usually.

Re: [Dovecot] SQL Server

[Stan Hoeppner]

Pascal Volk put forth on 5/22/2010 2:36 AM:

> In the meantime the FreeTDS  libraries may be
> useful:
> "FreeTDS is a set of libraries for Unix and Linux that allows your
> programs to natively talk to Microsoft SQL Server and Sybase databases."

I should have looked around before posting.  FreeTDS might just save Bryan a
little development time. :)

Have you ever used FreeTDS Pascal?

-- 
Stan

Re: [Dovecot] SQL Server

[Pascal Volk]

On 05/22/2010 09:03 AM Stan Hoeppner wrote:
> I think you're SOL Bryan until someone adds mssql driver support to Dovecot.

I think Timo will add SQL Server support as soon as the company in
Redmond has made the GPL/BSD licensed release. ;-)

> If you are a C veteran and know mssql at a development level, you could
> possibly grab the source for one of the other sql drivers and rewrite it for
> mssql duty. …

In the meantime the FreeTDS  libraries may be
useful:
"FreeTDS is a set of libraries for Unix and Linux that allows your
programs to natively talk to Microsoft SQL Server and Sybase databases."


Regards,
Pascal
-- 
The trapper recommends today: c01dcafe.1014...@localdomain.org

[Dovecot] quota and postfix virtual users

[gbotero]

hi everyone i have a mailserver working with postfix 2.5.1 vda and
virtual user with mysql and dovecot, everything is working fine but i want
to offer quota and i have a problem with that because the quota in
roundcube appear but calculate is wrong i need to delete the maildirsize
file to force to recalculate, what can i do??? in my dovecot i have imap{
quota imap_quota, in plugins section i have quota=maildir and trough the
file dovecot-sql.conf i have the select expression concat(.. as
quota_rule, thanks a lot, 

PD. sorry for my english im from south of
colombia - south america

Re: [Dovecot] SQL Server

[Stan Hoeppner]

Bryan Vyhmeister put forth on 5/21/2010 5:00 PM:
> On Fri, May 21, 2010 at 4:10 PM, Jerry  wrote:
>>
>> If you can access the Microsoft SQL Server from the machine hosting
>> Dovecot, there should be no problem at all. I have used Microsoft's SQL
>> sever for for several projects and it is an extremely fast and robust
>> piece of software.
>>
>> Perhaps you could post a clearer picture of exactly what you are
>> attempting to accomplish.
> 
> Sorry. I should have been more specific. I want to use SQL Server for
> the userdb and password database. I looked through the wiki and did a
> number of searches but could not find an answer. If I were to use
> sqlite, mysql, or pgsql, these would be listed under driver = in the
> dovecot-sql.conf (or whatever you want to name it). How would you go
> about using SQL Server? There is no sql-server driver as far as I
> know. I have and am currently using dovecot from passwd files and from
> sqlite databases so I am pretty familiar with how everything works. I
> am just not sure how to go about this for SQL Server. Thank you.

I think you're SOL Bryan until someone adds mssql driver support to Dovecot.
If you are a C veteran and know mssql at a development level, you could
possibly grab the source for one of the other sql drivers and rewrite it for
mssql duty.  The current sql driver source files are:

driver-mysql.c
driver-pgsql.c
driver-sqlite.c

Source files for stable Dovecot are available here:
http://www.dovecot.org/releases/1.2/dovecot-1.2.11.tar.gz

Good luck.

-- 
Stan