>>>>> I've found that in conjunction with a 2nd query (the
>>>>> original
>>>>> normal one), everything seems to work as expected (including
>>>>> aliases with only local parts like "postmaster")
>>>>>
>>>>> But I'm still unsure if this kind of query is correct, if
>>>>> I'm
>>>>> on the right track. Can anyone tell me if there's a better
>>>>> way
>>>>> to do it?
>>>>>
>>>>> Where are all those examples that are supposedly posted on
>>>>> this list previously?
>>>
>>> You have been pointed to postfixadmin before, which has all of
>>> this builtin. Did you check their documentation? Every existing
>>> db-based postfix adminsitration suite should have an example for
>>> you.
>>
>> Sorry, I hoped not to have to learn a whole new tool when it was said
>> there were already examples posted to this list. I'll try to take a
>> look and see how easy it is to pick up parts like this
>>
>>> My query for alias domains on postgres, using postfixadmin database
>>> model:
>>>
>>> query = SELECT goto FROM postfix_alias AS alias,
>>> postfix_alias_domain AS alias_domain WHERE
>>> alias_domain.alias_domain = '%d' AND alias.address =
>>> '%u' || '@' ||
>>> alias_domain.target_domain AND alias.active = '1' AND
>>> alias_domain.active = '1'
>>>
>>> Definitely less hurting the head than your query this early in the
>>> morning, imho.
>>
>> Beauty is in the eye of the beholder I guess.
>
> This is not beauty, this is KISS. ;)
I don't think mine is so confusing as you suggest if you take a second to
understand it. Maybe if you read it with some whitespace:
select
if ('%d' = 'example-2.com',
IFNULL(
(select dest from aliases where addr = '%u...@example.com'),
(select addr from users where addr = '%u...@example.com')
),
NULL)
Or, adding some pseudocode back to it
select
if ('%d' = 'example-2.com', THEN {
IF THE FOLLOWING ISN'T NULL THEN USE IT {
(select dest from aliases where addr = '%u...@example.com'),
} ELSE {
(select addr from users where addr = '%u...@example.com')
}
} ELSE NULL)
Where you can substitute your "target_domain" for "example.com" and
"alias_domain" for "example-2.com"
But again, to each his own, and I'm glad to see your query to confirm if I'm
not doing something terribly wrong.
>> I also gave pseudocode
>> and after staring at your query, it does part of what mine does, but
>> I have a question why it does not do the other part.
>>
>> What my tests have shown to work is:
>>
>> 0) if %d isn't the aliased domain "example-2.com" then forget
> it
>> 1) Look in alias table to see if there is an alias for "user" in
> the
>> target/primary domain "example.com", if there is, return it
>> 2) If there was not an alias, we must look in the account table to
>> find if there is a real account address for "user" in the
> target/primary
>> domain "example.com", if there is, return it
>> 3) otherwise, return NULL so postfix can reject the address
>
> pfa has this covered by adding a record in the alias table for both
> aliases (b...@example.com->a...@example.com) and mailboxes
> (a...@example.com->a...@example.com). This means that a list of all mail
> addresses (aliases and mailboxes) are available in alias.address, and
> that a single query on that table is enough to check all variants.
Eiw. Not very normalized. :) But OK, that explains things.
> If you don't like that design (I don't, but I have other things to worry
> about), write a difficult query or again, split the work over separate
> lookups: one for aliases in alias domains, and one for accounts in alias
> domains.
My query takes care of the situation by checking for aliases first and falling
back to checking the accounts if no alias was found.
I think if you wanted to make your query work if you didn't have the accounts
listed in the alias table, you can simply stuff the whole query inside the
first argument of the IFNULL function (not sure what the pgsql equivalent of
IFNULL is). Then the second argument to IFNULL would be a copy of the same
query aimed at the account table instead of the alias table.
I think if you wanted to maintain two separate queries and make it work similar
to my query, this might work (untested):
alias lookup:
select
if ('%d' = 'example-2.com',
IFNULL(
(select dest from aliases where addr = '%u...@example.com'),
(NULL)
),
NULL)
account lookup:
select
if ('%d' = 'example-2.com',
IFNULL(
(select addr from users where addr = '%u...@example.com'),
(NULL)
),
NULL)
again, these need to be *additional* queries to the normal ones configured for
the alias maps and account maps.
I'm glad someone could respond on this thread, so thank you, Tom
