Mysql virtual + unionmap

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

Mysql virtual + unionmap

DEPRÉ Gaëtan - NGServers.com
Hi !

 

I’d like [hidden email] being able to send mails from [hidden email].

 

The actual error is : 5.7.1 [hidden email]: Sender address rejected: not owned by user [hidden email]

 

My postfix configuration is a mysql one with map files.

 

I tried to use the unionmap fonctionnality in master.cf but i had no chance with it.

 

Could you please help me being able to create some mysql record to allow user1 to send emails with user2 address ?

 

My config :

 

I use « reject_sender_login_mismatch » in main.cf

 

I added this line in master.cf (just before mua restrictions) :

 

   […]

    -o smtpd_client_restrictions=$mua_client_restrictions

    -o smtpd_sender_login_maps=unionmap:{mysql:/etc/postfix/sql/sender-login-maps.cf,mysql:/etc/postfix/sql/sender_delegate_checks.cf}

    -o smtpd_sender_restrictions=$mua_sender_restrictions

    -o smtpd_relay_restrictions=$mua_relay_restrictions

    […]

 

My maps files :

sender-login-maps.cf :

 

user = dbusername

password = dbpassword

hosts = 127.0.0.1

dbname = dbname

query = SELECT email as user FROM virtual_users WHERE email='%s'

 

sender_delegate_checks.cf :

 

user = dbusername

password = dbpassword

hosts = 127.0.0.1

dbname = dbname

query = SELECT owned as user FROM virtual_delegation WHERE owner='%s'

 

Mysql virtual_delegation table :

 

MariaDB [mailserver]> select * from virtual_delegation;

+----+----------------------+-------------------------+

| id | owner                | owned                   |

+----+----------------------+-------------------------+

|  1 | [hidden email] | [hidden email] |

+----+----------------------+-------------------------+

1 row in set (0.000 sec)

 

Thx,

 

Regards,

 

Gaetan

 





Reply | Threaded
Open this post in threaded view
|

Re: Mysql virtual + unionmap

Viktor Dukhovni
On Mon, Mar 29, 2021 at 12:35:39AM +0200, Gaëtan DEPRÉ wrote:

> I’d like [hidden email] being able to send mails from [hidden email].
>
> The actual error is : 5.7.1 [hidden email]: Sender address rejected: not owned by user [hidden email]

Have you tested your unionmap?  For a minimal example:

    $ postmap -q foo "unionmap:{static:bar,static:baz}"
    bar,baz

In your case the lookup key is the sender address, so it would be

    $ postmap -q [hidden email] "..."

where "..." is detailed below.

> I added this line in master.cf (just before mua restrictions) :
>     -o smtpd_sender_login_maps=unionmap:{mysql:/etc/postfix/sql/sender-login-maps.cf,mysql:/etc/postfix/sql/sender_delegate_checks.cf}

It is far better to write the definion in main.cf, via:

    master.cf:
        -o smtpd_sender_login_maps=$mua_sender_login_maps

    main.cf:
        mua_sender_login_maps =
            unionmap:{mysql:/etc/postfix/sql/sender-login-maps.cf,mysql:/etc/postfix/sql/sender_delegate_checks.cf}

making sure to add the override to *all* the submission-related
master.cf entries (port 587 and 465 if both are in use).

In which case you'd write:

    $ postmap -q [hidden email] "$(postconf -xh mua_sender_login_maps)"

but, given that the map files contain passwors, perhaps they're not
world-readable.  If so, run the command as "root".

For bonus points, with the submission entry assumed to be
"submission/inet", you could run either or both of:

    $ postmap -q [hidden email] "$(postconf -Phx submission/inet/smtpd_sender_login_maps)"
    $ postmap -q [hidden email] "$(postconf -Phx smtps/inet/smtpd_sender_login_maps)"

and report the output.

> query = SELECT email as user FROM virtual_users WHERE email='%s'
> query = SELECT owned as user FROM virtual_delegation WHERE owner='%s'
>
> MariaDB [mailserver]> select * from virtual_delegation;
> +----+----------------------+-------------------------+
> | id | owner                | owned                   |
> +----+----------------------+-------------------------+
> |  1 | [hidden email]       | [hidden email]          |
> +----+----------------------+-------------------------+

The second query is backwards, it should be:

    query = SELECT owner as user FROM virtual_delegation WHERE owned='%s'

--
    Viktor.
Reply | Threaded
Open this post in threaded view
|

RE: Mysql virtual + unionmap

DEPRÉ Gaëtan - NGServers.com
Thank you Viktor, it's working.

I modified the files master.cf & main.cf as you adviced.

The command postmap -q [hidden email] "$(postconf -Phx smtps/inet/smtpd_sender_login_maps)"

returns me the two users that are able to send email from.

Everything, is working, the problem was the invert with 'owned' & 'owner'.

regards


-----Message d'origine-----
De : [hidden email] <[hidden email]> De la part de Viktor Dukhovni
Envoyé : lundi 29 mars 2021 07:35
À : [hidden email]
Objet : Re: Mysql virtual + unionmap

On Mon, Mar 29, 2021 at 12:35:39AM +0200, Gaëtan DEPRÉ wrote:

> I’d like [hidden email] being able to send mails from [hidden email].
>
> The actual error is : 5.7.1 [hidden email]: Sender address rejected:
> not owned by user [hidden email]

Have you tested your unionmap?  For a minimal example:

    $ postmap -q foo "unionmap:{static:bar,static:baz}"
    bar,baz

In your case the lookup key is the sender address, so it would be

    $ postmap -q [hidden email] "..."

where "..." is detailed below.

> I added this line in master.cf (just before mua restrictions) :
>     -o
> smtpd_sender_login_maps=unionmap:{mysql:/etc/postfix/sql/sender-login-
> maps.cf,mysql:/etc/postfix/sql/sender_delegate_checks.cf}

It is far better to write the definion in main.cf, via:

    master.cf:
        -o smtpd_sender_login_maps=$mua_sender_login_maps

    main.cf:
        mua_sender_login_maps =
            unionmap:{mysql:/etc/postfix/sql/sender-login-maps.cf,mysql:/etc/postfix/sql/sender_delegate_checks.cf}

making sure to add the override to *all* the submission-related master.cf entries (port 587 and 465 if both are in use).

In which case you'd write:

    $ postmap -q [hidden email] "$(postconf -xh mua_sender_login_maps)"

but, given that the map files contain passwors, perhaps they're not world-readable.  If so, run the command as "root".

For bonus points, with the submission entry assumed to be "submission/inet", you could run either or both of:

    $ postmap -q [hidden email] "$(postconf -Phx submission/inet/smtpd_sender_login_maps)"
    $ postmap -q [hidden email] "$(postconf -Phx smtps/inet/smtpd_sender_login_maps)"

and report the output.

> query = SELECT email as user FROM virtual_users WHERE email='%s'
> query = SELECT owned as user FROM virtual_delegation WHERE owner='%s'
>
> MariaDB [mailserver]> select * from virtual_delegation;
> +----+----------------------+-------------------------+
> | id | owner                | owned                   |
> +----+----------------------+-------------------------+
> |  1 | [hidden email]       | [hidden email]          |
> +----+----------------------+-------------------------+

The second query is backwards, it should be:

    query = SELECT owner as user FROM virtual_delegation WHERE owned='%s'

--
    Viktor.

Reply | Threaded
Open this post in threaded view
|

Re: Mysql virtual + unionmap

Viktor Dukhovni
On Mon, Mar 29, 2021 at 08:23:40AM +0200, DEPRÉ Gaëtan - NGServers.com wrote:

> I modified the files master.cf & main.cf as you adviced.
>
> The command postmap -q [hidden email] "$(postconf -Phx smtps/inet/smtpd_sender_login_maps)"
> returns me the two users that are able to send email from.
>
> Everything, is working, the problem was the invert with 'owned' & 'owner'.

Congrats.  I should perhaps note that with SQL you hardly need a
unionmap:

    query = SELECT email as user FROM virtual_users WHERE email='%s' UNION
            SELECT owned as user FROM virtual_delegation WHERE owner='%s'

--
    Viktor.
Reply | Threaded
Open this post in threaded view
|

RE: Mysql virtual + unionmap

DEPRÉ Gaëtan - NGServers.com
You're right! I think unionmap is useful if there is a mix with mysql maps and pcre maps, or other mixed maps.

I modified my sender-login-maps.cf to include UNION with both of the queries.

Thanks again, Viktor.

Regards

-----Message d'origine-----
De : [hidden email] <[hidden email]> De la part de Viktor Dukhovni
Envoyé : lundi 29 mars 2021 08:37
À : [hidden email]
Objet : Re: Mysql virtual + unionmap

On Mon, Mar 29, 2021 at 08:23:40AM +0200, DEPRÉ Gaëtan - NGServers.com wrote:

> I modified the files master.cf & main.cf as you adviced.
>
> The command postmap -q [hidden email] "$(postconf -Phx smtps/inet/smtpd_sender_login_maps)"
> returns me the two users that are able to send email from.
>
> Everything, is working, the problem was the invert with 'owned' & 'owner'.

Congrats.  I should perhaps note that with SQL you hardly need a
unionmap:

    query = SELECT email as user FROM virtual_users WHERE email='%s' UNION
            SELECT owned as user FROM virtual_delegation WHERE owner='%s'

--
    Viktor.

Reply | Threaded
Open this post in threaded view
|

Re: Mysql virtual + unionmap

Leonardo Rodrigues Magalhães
In reply to this post by Viktor Dukhovni
Em 29/03/2021 03:37, Viktor Dukhovni escreveu:
> Congrats. I should perhaps note that with SQL you hardly need a
> unionmap:
>
>      query = SELECT email as user FROM virtual_users WHERE email='%s' UNION
>              SELECT owned as user FROM virtual_delegation WHERE owner='%s'
>

     I have this exact setup running with UNION on the MySQL query for
years. Actually i wasn't even aware of the unionmap map type :)

--


        Atenciosamente / Sincerily,
        Leonardo Rodrigues
        Solutti Tecnologia
        http://www.solutti.com.br

        Minha armadilha de SPAM, NÃO mandem email
        [hidden email]
        My SPAMTRAP, do not email it