smtpd_sender_login_maps problem

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

smtpd_sender_login_maps problem

Paul Enlund-2
  Hello

I am having problems with creating a new mysql lookup statement which
queries 2 tables
for valid sasl login names

The new query is

cat peq.sql

SELECT aliases.id,sasl_aliases.id FROM sasl_aliases,aliases WHERE
aliases.address = sasl_aliases.address AND aliases.address =
'[hidden email]';


paule@cedar:~# mysql -u mailuser -p maildb <peq.sql
Enter password:
username1 username2

Which is what I believe I need ie returning a white spaced list of valid
login names for [hidden email].

However postfix coughs and fails with this query. log shows

Aug  3 14:17:46 larch postfix/submit/smtpd[21172]: fatal:
/etc/postfix/mysql/new
.cf, line 4: missing '=' after attribute name: "SELECT
aliases.id,sasl_aliases.i
d FROM sasl_aliases,aliases WHERE aliases.address=sasl_aliases.address
AND alias
es.address='%s'"


Any pointers or suggestions

Paul Enlund




Reply | Threaded
Open this post in threaded view
|

Re: smtpd_sender_login_maps problem

/dev/rob0
On Wed, Aug 03, 2016 at 02:21:25PM +0100, Paul wrote:

> I am having problems with creating a new mysql lookup statement
> which queries 2 tables for valid sasl login names
>
> The new query is
>
> cat peq.sql
>
> SELECT aliases.id,sasl_aliases.id FROM sasl_aliases,aliases WHERE
> aliases.address = sasl_aliases.address AND aliases.address =
> '[hidden email]';

That's fine I suppose, but it's not valid syntax for a Postfix
mysql_table(5) query file.

> paule@cedar:~# mysql -u mailuser -p maildb <peq.sql
> Enter password:
> username1 username2
>
> Which is what I believe I need ie returning a white spaced list of
> valid login names for [hidden email].
>
> However postfix coughs and fails with this query. log shows
>
> Aug 3 14:17:46 larch postfix/submit/smtpd[21172]: fatal:
> /etc/postfix/mysql/new.cf, line 4: missing '=' after attribute
> name: "SELECT aliases.id,sasl_aliases.id FROM sasl_aliases,aliases
> WHERE aliases.address=sasl_aliases.address AND
> aliases.address='%s'"
>
>
> Any pointers or suggestions

http://www.postfix.org/MYSQL_README.html
http://www.postfix.org/mysql_table.5.html

Your query file would generally have these lines:
  user = ...
  password = ...
  dbname = ...

and then your query:

  query = SELECT aliases.id,sasl_aliases.id
        FROM sasl_aliases,aliases
        WHERE aliases.address=sasl_aliases.address AND
        aliases.address='%s'
--
  http://rob0.nodns4.us/
  Offlist GMX mail is seen only if "/dev/rob0" is in the Subject:
Reply | Threaded
Open this post in threaded view
|

Re: smtpd_sender_login_maps problem

Paul Enlund-2
On 03/08/2016 15:43, /dev/rob0 wrote:

> On Wed, Aug 03, 2016 at 02:21:25PM +0100, Paul wrote:
>> I am having problems with creating a new mysql lookup statement
>> which queries 2 tables for valid sasl login names
>>
>> The new query is
>>
>> cat peq.sql
>>
>> SELECT aliases.id,sasl_aliases.id FROM sasl_aliases,aliases WHERE
>> aliases.address = sasl_aliases.address AND aliases.address =
>> '[hidden email]';
> That's fine I suppose, but it's not valid syntax for a Postfix
> mysql_table(5) query file.
>
>> paule@cedar:~# mysql -u mailuser -p maildb <peq.sql
>> Enter password:
>> username1 username2
>>
>> Which is what I believe I need ie returning a white spaced list of
>> valid login names for [hidden email].
>>
>> However postfix coughs and fails with this query. log shows
>>
>> Aug 3 14:17:46 larch postfix/submit/smtpd[21172]: fatal:
>> /etc/postfix/mysql/new.cf, line 4: missing '=' after attribute
>> name: "SELECT aliases.id,sasl_aliases.id FROM sasl_aliases,aliases
>> WHERE aliases.address=sasl_aliases.address AND
>> aliases.address='%s'"
>>
>>
>> Any pointers or suggestions
> http://www.postfix.org/MYSQL_README.html
> http://www.postfix.org/mysql_table.5.html
>
> Your query file would generally have these lines:
>    user = ...
>    password = ...
>    dbname = ...
>
> and then your query:
>
>    query = SELECT aliases.id,sasl_aliases.id
> FROM sasl_aliases,aliases
> WHERE aliases.address=sasl_aliases.address AND
> aliases.address='%s'

'The complete file is

# /etc/posfix/mysql/new.cf

user=mailuser
password=xxxxxxxx
dbname=maildb
SELECT aliases.id,sasl_aliases.id FROM sasl_aliases,aliases WHERE
aliases.address=sasl_aliases.address AND aliases.address='%s'
hosts=unix:/chroot/web/var/run/mysqld/mysqld.sock

which causes


Aug  3 15:49:16 larch postfix/submit/smtpd[4384]: fatal:
/etc/postfix/mysql/new.cf,

line 4: missing '=' after attribute name: "SELECT
aliases.id,sasl_aliases.id FROM sasl_aliases,aliases WHERE
aliases.address=sasl_aliases.address AND aliases.address='%s'"

PE
Reply | Threaded
Open this post in threaded view
|

Re: smtpd_sender_login_maps problem

Jan Ceuleers
On 03/08/16 16:51, Paul wrote:
> line 4: missing '=' after attribute name: "SELECT
> aliases.id,sasl_aliases.id FROM sasl_aliases,aliases WHERE
> aliases.address=sasl_aliases.address AND aliases.address='%s'"

You might have gone over this so many times that you're missing it:

You need to prepend "query = " to the SELECT statement on line 4 in
/etc/posfix/mysql/new.cf

Reply | Threaded
Open this post in threaded view
|

Re: smtpd_sender_login_maps problem

Noel Jones-2
In reply to this post by Paul Enlund-2
On 8/3/2016 9:51 AM, Paul wrote:

> 'The complete file is
>
> # /etc/posfix/mysql/new.cf
>
> user=mailuser
> password=xxxxxxxx
> dbname=maildb
> SELECT aliases.id,sasl_aliases.id FROM sasl_aliases,aliases WHERE
> aliases.address=sasl_aliases.address AND aliases.address='%s'
> hosts=unix:/chroot/web/var/run/mysqld/mysqld.sock


query = SELECT ...



  -- Noel Jones

Reply | Threaded
Open this post in threaded view
|

Re: smtpd_sender_login_maps problem

Paul Enlund-2
In reply to this post by Jan Ceuleers
Blushing thanks for the 2nd set of eyes spotting the obvious.

Trouble now is the query only returns id results if the address is in
both tables.
So back to the drawing  mysql drawing board.

PE



On 03/08/2016 16:44, Jan Ceuleers wrote:

> On 03/08/16 16:51, Paul wrote:
>> line 4: missing '=' after attribute name: "SELECT
>> aliases.id,sasl_aliases.id FROM sasl_aliases,aliases WHERE
>> aliases.address=sasl_aliases.address AND aliases.address='%s'"
> You might have gone over this so many times that you're missing it:
>
> You need to prepend "query = " to the SELECT statement on line 4 in
> /etc/posfix/mysql/new.cf
>
>
>

Reply | Threaded
Open this post in threaded view
|

Re: smtpd_sender_login_maps problem

Phil Stracchino
On 08/03/16 13:41, Paul wrote:
> Blushing thanks for the 2nd set of eyes spotting the obvious.
>
> Trouble now is the query only returns id results if the address is in
> both tables.
> So back to the drawing  mysql drawing board.

What do your tables look like?


--
  Phil Stracchino
  Babylon Communications
  [hidden email]
  [hidden email]
  Landline: 603.293.8485
Reply | Threaded
Open this post in threaded view
|

Re: smtpd_sender_login_maps problem

Paul Enlund-2


On 03/08/2016 18:53, Phil Stracchino wrote:
> On 08/03/16 13:41, Paul wrote:
>> Blushing thanks for the 2nd set of eyes spotting the obvious.
>>
>> Trouble now is the query only returns id results if the address is in
>> both tables.
>> So back to the drawing  mysql drawing board.
> What do your tables look like?
>
>
sasl_aliases | CREATE TABLE `sasl_aliases` (
   `address` text NOT NULL,
   `id` varchar(128) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1

aliases | CREATE TABLE `aliases` (
   `address` varchar(255) NOT NULL DEFAULT '',
   `goto` varchar(255) DEFAULT NULL,
   `active` tinyint(4) DEFAULT NULL,
   `id` varchar(128) NOT NULL DEFAULT '',
   PRIMARY KEY (`address`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1