we may want to support email addresses as login, this can be discussed it was not designed this way to start with because we didn't support !system auth but this has changed and the use case has come up a few times
Gilles On Wed, Aug 20, 2014 at 06:08:54PM +0200, Giovanni Bechis wrote: > On Wed, Aug 20, 2014 at 02:16:05PM +0200, Martijn van Duren wrote: > > > > > query_alias select COALESCE(destination, REPLACE(maddr, '@', > > > '_')), ? as maddr from mail_forwarding where source=maddr; > > > > And of course I was a bit too fast with this one (column aliases can't > > be used in other columns), so if something should work it would be: > > > > query_alias SET @maddr = ?; SELECT COALESCE(destination, > > REPLACE(@maddr, '@', '_')) from mail_forwarding where source=@maddr; > > > > I've tested this one as a proof of concept, yet not in an > > table_mysql.conf setup. > > > > I am curious if this works, so keep me posted. :) > > > This one seems to work, I created a view to get rid of the SET instructions > that does not seem > to work. > I will do more tests soon. > Thanks & Cheers > Giovanni > # > # Sample PostgreSQL configuration file > # > # This is an example configuration file for using OpenSMTPD with the > PostgreSQL > # backend for lookups. OpenSMTPD does not have hardcoded tables and you may > # > # > > host localhost > username user > password pass > database db > > # Alias lookup query > # > # rows >= 0 > # fields == 1 (user varchar) > # > query_alias select destination from mail_valias where source=? > > > # Domain lookup query > # > # rows == 1 > # fields == 1 (domain varchar) > # > query_domain select domain from mail_domain where domain=?; > > > # User lookup query > # > # rows == 1 > # fields == 3 (uid int, gid int, directory varchar) > # > query_userinfo select uid,gid,maildir from mail_user where > REPLACE(login, '@', '_')=? and server_id=6; > > > # Credentials lookup query > # > # rows == 1 > # fields == 2 (username varchar, password varchar) > # > query_credentials select login, password from mail_user where login=?; > > > # Netaddr lookup query > # > # rows == 1 > # fields == 1 (netaddr varchar) > # > #query_netaddr select value from netaddr where value=$1; > MariaDB [dbispconfig]> show create view mail_valias\G > *************************** 1. row *************************** > View: mail_valias > Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` > SQL SECURITY DEFINER VIEW `mail_valias` AS select `mail_forwarding`.`source` > AS > `source`,replace(replace(`mail_forwarding`.`destination`,'@','_'),'\r\n',', > ') AS `destination` from `mail_forwarding` union select `mail_user`.`email` > AS `email`,replace(`mail_user`.`email`,'@','_') AS `replace(email, '@', '_')` > from `mail_user` > character_set_client: utf8 > collation_connection: utf8_general_ci > 1 row in set (0.00 sec) > > MariaDB [dbispconfig]> \q -- Gilles Chehade https://www.poolp.org @poolpOrg -- You received this mail because you are subscribed to [email protected] To unsubscribe, send a mail to: [email protected]
