sasl map multiple query question
in [Postfix]
|
Prev: relay help
Next: From: header correctness
From: Robert Schetterer on 29 Jan 2010 04:41 Hi all, i have a sasl mysql map matching existing users in mysql SELECT username FROM mailbox WHERE username='%s' AND active = 1 this works nice but now i need a query or chain for allowing specific admin users in mysql to allow so i am unsure about the syntax of the or statement i need something like SELECT username FROM mailbox WHERE username='%s' AND active = 1 OR SELECT username FROM domain_admins WHERE username='%s' AND domain = '%d' -- Best Regards MfG Robert Schetterer Germany/Munich/Bavaria
From: Brian Evans - Postfix List on 29 Jan 2010 08:57 On 1/29/2010 4:41 AM, Robert Schetterer wrote: > Hi all, > i have a sasl mysql map matching existing users in mysql > > SELECT username FROM mailbox WHERE username='%s' AND active = 1 > > this works nice > > but now i need a query or chain for allowing specific admin users in > mysql to allow so i am unsure about the syntax of the or statement > > i need something like > > SELECT username FROM mailbox WHERE username='%s' AND active = 1 OR > SELECT username FROM domain_admins WHERE username='%s' AND domain = '%d' > If the expected result is in either table, but not both, replace the word OR with UNION. If the expected result in in both tables and you want both answers, you can still do the replace. Test results using 'postmap -q' (see 'man postmap' for detail) to make sure you get the results you expect.
From: Robert Schetterer on 29 Jan 2010 10:58 Am 29.01.2010 14:57, schrieb Brian Evans - Postfix List: > On 1/29/2010 4:41 AM, Robert Schetterer wrote: >> Hi all, >> i have a sasl mysql map matching existing users in mysql >> >> SELECT username FROM mailbox WHERE username='%s' AND active = 1 >> >> this works nice >> >> but now i need a query or chain for allowing specific admin users in >> mysql to allow so i am unsure about the syntax of the or statement >> >> i need something like >> >> SELECT username FROM mailbox WHERE username='%s' AND active = 1 OR >> SELECT username FROM domain_admins WHERE username='%s' AND domain = '%d' >> > > If the expected result is in either table, but not both, replace the > word OR with UNION. > If the expected result in in both tables and you want both answers, you > can still do the replace. > > Test results using 'postmap -q' (see 'man postmap' for detail) to make > sure you get the results you expect. > things with union work but , the problem seems to be the double result of equal entries query = SELECT DISTINCT concat('@', domain,' ', username) username FROM domain_admins WHERE username='%s' AND domain = '%d' UNION SELECT username FROM mailbox WHERE username='%s' AND active = 1 i.e postmap -q "robert(a)schetterer.org" mysql:/etc/postfix/mysql_sasl_login_maps-test.cf @schetterer.org robert(a)schetterer.org,robert(a)schetterer.org equal doubles seems to be accepted with postfix sasl map so i like to supress the the second result if first matched allready any idea, destinct dousnt seem to help -- Best Regards MfG Robert Schetterer Germany/Munich/Bavaria
From: Robert Schetterer on 29 Jan 2010 11:02 Am 29.01.2010 16:58, schrieb Robert Schetterer: > Am 29.01.2010 14:57, schrieb Brian Evans - Postfix List: >> On 1/29/2010 4:41 AM, Robert Schetterer wrote: >>> Hi all, >>> i have a sasl mysql map matching existing users in mysql >>> >>> SELECT username FROM mailbox WHERE username='%s' AND active = 1 >>> >>> this works nice >>> >>> but now i need a query or chain for allowing specific admin users in >>> mysql to allow so i am unsure about the syntax of the or statement >>> >>> i need something like >>> >>> SELECT username FROM mailbox WHERE username='%s' AND active = 1 OR >>> SELECT username FROM domain_admins WHERE username='%s' AND domain = '%d' >>> >> >> If the expected result is in either table, but not both, replace the >> word OR with UNION. >> If the expected result in in both tables and you want both answers, you >> can still do the replace. >> >> Test results using 'postmap -q' (see 'man postmap' for detail) to make >> sure you get the results you expect. >> > > things with union work but , the problem > seems to be the double result of equal entries > > query = SELECT DISTINCT concat('@', domain,' ', username) username FROM > domain_admins WHERE username='%s' > AND domain = '%d' UNION SELECT username FROM mailbox WHERE username='%s' > AND active = 1 > > i.e > > postmap -q "robert(a)schetterer.org" > mysql:/etc/postfix/mysql_sasl_login_maps-test.cf > @schetterer.org robert(a)schetterer.org,robert(a)schetterer.org > > equal doubles seems to be accepted with postfix sasl map sorry here seems not to be accepted > > so i like to supress the the second result if first matched allready > any idea, destinct dousnt seem to help -- Best Regards MfG Robert Schetterer Germany/Munich/Bavaria
From: Brian Evans - Postfix List on 29 Jan 2010 11:24
On 1/29/2010 11:02 AM, Robert Schetterer wrote: > Am 29.01.2010 16:58, schrieb Robert Schetterer: > >> >> things with union work but , the problem >> seems to be the double result of equal entries >> >> query = SELECT DISTINCT concat('@', domain,' ', username) username FROM >> domain_admins WHERE username='%s' >> AND domain = '%d' UNION SELECT username FROM mailbox WHERE username='%s' >> AND active = 1 >> >> i.e >> >> postmap -q "robert(a)schetterer.org" >> mysql:/etc/postfix/mysql_sasl_login_maps-test.cf >> @schetterer.org robert(a)schetterer.org,robert(a)schetterer.org >> >> equal doubles seems to be accepted with postfix sasl map >> > sorry here seems not to be accepted > > >> so i like to supress the the second result if first matched allready >> any idea, destinct dousnt seem to help >> Try adding LIMIT 1 to the end. This will give only one result in the precedence specified by the query when used like in http://dev.mysql.com/doc/refman/5.0/en/union.html (SELECT concat('@', domain,' ', username) username FROM domain_admins WHERE username='%s' AND domain = '%d') UNION (SELECT username FROM mailbox WHERE username='%s' AND active = 1) LIMIT 1 The above would return a single row with domain_admins overruling what is in mailbox |