Re: [exim] Using Postgres lookup for SMTP auth

Αρχική Σελίδα
Delete this message
Reply to this message
Συντάκτης: W B Hacker
Ημερομηνία:  
Προς: exim users
Αντικείμενο: Re: [exim] Using Postgres lookup for SMTP auth
Rory Campbell-Lange wrote:

> I'm having trouble coming up with the correct stanza to allow me to do
> smtp auth using a postgresql call.
>
> I'm trying to do something like this:
>
> fixed_plain:
>   driver = plaintext
>   public_name = PLAIN
>   server_prompts = :
>   server_condition = \
>     ${if and {{eq{$auth2}{username}}{eq{$auth3}{mysecret}}}\
>       {yes}{no}}
>   server_set_id = $auth2

>
> like this:
>
> fixed_plain:
>   driver = plaintext
>   public_name = PLAIN
>   server_prompts = :
>   server_condition = \
>   server_set_id = \
>     ${if and {{eq{$2}${lookup pgsql {
>     SELECT password FROM users where userid || '@' || domain = '${quote_pgsql:$1}'
>     }}}}{yes}{no}}
>   server_set_id = $auth1

>
> (I appreciate that there are security concerns with this example.)
>
> I'd be grateful for any tips.
>
> Rory
>


These are not fully optimized, but do work (portions altered/redacted for
simplicity):

plain:
      driver = plaintext
      server_advertise_condition = ${if eq{$tls_cipher}{}{no}{yes}}
      public_name = PLAIN
      server_prompts = :
      server_condition = ${lookup pgsql{SELECT '1' FROM users \
                 WHERE smtp_login='${quote_pgsql:$2}' \
                 AND smtp_pwclear='${quote_pgsql:$3}'} {yes}{no}}



login:
      driver = plaintext
      server_advertise_condition = ${if eq{$tls_cipher}{}{no}{yes}}
      public_name = LOGIN
      server_prompts = "Username:: : Password::"
      server_condition = ${lookup pgsql{SELECT 1 FROM users \
                 WHERE smtp_login='${quote_pgsql:$1}' \
                 AND smtp_pwclear='${quote_pgsql:$3}'} {yes}{no}}


Note that in both cases our user login identity may or may not have anything to
do with their email address and domain.tld, i.e. could just as well be
'FuZlNertZ' as 'user@???' (though that is not ruled out....).

The PostgreSQL files are type VARCHAR, with Unicode template.
'SELECT 1' grabs first match, as multiple e-mail addresses with common login/pwd
are supported here.

IMAP/POP UID and PWD are unrelated to smtp UID and PWD and *also* need not
appear to relate to the email address.

The DB separately takes care of all the mapping for router/transport sets, as
well as separate/consolidated and/or shared storage.

HTH,

Bill