From: Serge Fonville on
On Thu, Jan 28, 2010 at 10:40 PM, Brian Evans - Postfix List
<grknight(a)scent-team.com> wrote:
> On 1/28/2010 4:12 PM, Serge Fonville wrote:
>>>>> I using virtual_alias_maps with mysql for storage. Working fine.
>>>>>
>>>>> Does anyone have a suggestion on how to update a timestamp field in the
>>>>> mysql table when postfix finds a virtual_alias_maps match?
>>>>>
>>>>> I'm looking for a way to measure alias usage and cull unused aliases.
>>>>>
>>>>>
>>>> Have you considered a stored procedure?
>>>>
>>>>
>>> Stored procedures do not work in Postfix without code changes because
>>> the |CLIENT_MULTI_RESULTS connect flag, for MySQL API, is not turned on.
>>>
>> From the manual:
>> http://dev.mysql.com/doc/refman/5.0/en/mysql-real-connect.html
>> CLIENT_MULTI_RESULTS
>>
>>
> [...]
>> If your program uses CALL statements to execute stored procedures, the
>> CLIENT_MULTI_RESULTS flag must be enabled.
>>
> Reread this ^^^.

If you use a SELECT query, does it use CALL?


--
http://www.sergefonville.nl

Convince Google!!
They need to support Adsense over SSL
https://www.google.com/adsense/support/bin/answer.py?hl=en&answer=10528
http://www.google.com/support/forum/p/AdSense/thread?tid=1884bc9310d9f923&hl=en

From: Bradley Giesbrecht on

On Jan 28, 2010, at 12:35 PM, Serge Fonville wrote:

> Hi,
>
>> I using virtual_alias_maps with mysql for storage. Working fine.
>>
>> Does anyone have a suggestion on how to update a timestamp field in
>> the
>> mysql table when postfix finds a virtual_alias_maps match?
>>
>> I'm looking for a way to measure alias usage and cull unused aliases.
> Have you considered a stored procedure?

I have but was hoping for something simpler like I do with dovecot
deliver where you create a script that calls deliver after you do what
you want for logging and then name your script in something like
deliver_exec = script.

Might be wrong with the names but thats more or less what takes place.

I'd prefer to keep as much of this type of thing in the config files.
It seems to be easier to quickly see what's up when there is a problem.

I'll try the stored procedure if nothing more attractive turns up.


Thank you,
Bradley Giesbrecht

From: Serge Fonville on
On Fri, Jan 29, 2010 at 9:19 AM, Bradley Giesbrecht
<bradley.giesbrecht(a)gmail.com> wrote:
>
> On Jan 28, 2010, at 12:35 PM, Serge Fonville wrote:
>
>> Hi,
>>
>>> I using virtual_alias_maps with mysql for storage. Working fine.
>>>
>>> Does anyone have a suggestion on how to update a timestamp field in the
>>> mysql table when postfix finds a virtual_alias_maps match?
>>>
>>> I'm looking for a way to measure alias usage and cull unused aliases.
>>
>> Have you considered a stored procedure?
>
> I have but was hoping for something simpler like I do with dovecot deliver
> where you create a script that calls deliver after you do what you want for
> logging and then name your script in something like deliver_exec = script.
>
> Might be wrong with the names but thats more or less what takes place.
>
> I'd prefer to keep as much of this type of thing in the config files. It
> seems to be easier to quickly see what's up when there is a problem.
>
> I'll try the stored procedure if nothing more attractive turns up.
>
Well, possibly you could edit your transport to use a script and pass
all the relevant variables to it, it can then also do an insert on
your database.


--
http://www.sergefonville.nl

Convince Google!!
They need to support Adsense over SSL
https://www.google.com/adsense/support/bin/answer.py?hl=en&answer=10528
http://www.google.com/support/forum/p/AdSense/thread?tid=1884bc9310d9f923&hl=en

From: Bradley Giesbrecht on

On Jan 29, 2010, at 12:29 AM, Serge Fonville wrote:

> On Fri, Jan 29, 2010 at 9:19 AM, Bradley Giesbrecht
> <bradley.giesbrecht(a)gmail.com> wrote:
>>
>> On Jan 28, 2010, at 12:35 PM, Serge Fonville wrote:
>>
>>> Hi,
>>>
>>>> I using virtual_alias_maps with mysql for storage. Working fine.
>>>>
>>>> Does anyone have a suggestion on how to update a timestamp field
>>>> in the
>>>> mysql table when postfix finds a virtual_alias_maps match?
>>>>
>>>> I'm looking for a way to measure alias usage and cull unused
>>>> aliases.
>>>
>>> Have you considered a stored procedure?
>>
>> I have but was hoping for something simpler like I do with dovecot
>> deliver
>> where you create a script that calls deliver after you do what you
>> want for
>> logging and then name your script in something like deliver_exec =
>> script.
>>
>> Might be wrong with the names but thats more or less what takes
>> place.
>>
>> I'd prefer to keep as much of this type of thing in the config
>> files. It
>> seems to be easier to quickly see what's up when there is a problem.
>>
>> I'll try the stored procedure if nothing more attractive turns up.
>>
> Well, possibly you could edit your transport to use a script and pass
> all the relevant variables to it, it can then also do an insert on
> your database.

I was kinda hoping something like this was possible.

Does anyone have an example of something like this?

Maybe add a filter to my relay in master.cf?

http://www.postfix.org/FILTER_README.html

filter unix - n n - 10 pipe
flags=Rq user=filter null_sender=
argv=/path/to/script -f ${sender} -- ${recipient}

relay unix - - n - 10 smtp
-o content_filter=filter:dummy

OR

smtp unix - - n - 10 smtp
-o content_filter=filter:dummy


As everyone probably notices other then setting up postfix with
dovecot and virtual users in mysql I don't know postfix that well.
It's been working real well for over a year.

Thanks for any help. This isn't crucial, I'd just like to be able to
view counts of message passing through aliased users.

// Brad

From: "Rob Sterenborg" on
> > I have but was hoping for something simpler like I do with
> > dovecot deliver where you create a script that calls deliver
> > after you do what you want for logging and then name your
> > script in something like deliver_exec = script.
> >
> > Might be wrong with the names but thats more or less what takes
> > place.
> >
> > I'd prefer to keep as much of this type of thing in the config
> > files. It seems to be easier to quickly see what's up when
> > there is a problem.
> >
> > I'll try the stored procedure if nothing more attractive turns
> > up.
>
> Well, possibly you could edit your transport to use a script and
> pass all the relevant variables to it, it can then also do an
> insert on your database.

Or write a simple policy daemon. All necessary information is sent to a
policy deamon which in turn can put data in a table. (I wrote something
in PHP using pcntl because I don't know how write it in C or Perl. It
writes data to a MYSQL table taken from the details sent by Postfix. Our
mailflow is not as big as some here, but so far it's proven to be quite
stable and it fulfills our needs.)


--
Rob