SMTP auth, MySQL & passwords stored in clear

Discussion:

Konrad Michels

2004-01-07 16:20:35 UTC

Hi Folks
Someone has probably asked this before (can't recall seeing anything
about it in the last couple of thousand mails!), so apologies if this is
covering old ground again.

I've just setup a Exim 4.30 on a linux box, built with AUTH and MYSQL
lookups enabled. I've done the configs, and have managed to get plain
and login authentication working with the following authenticators:

mysql_plain:
driver = plaintext
public_name = PLAIN
server_condition = "${if eq{$3} \
{${lookup mysql{SELECT passwd FROM smtpauth \
WHERE login='${local_part:$2}'}{$value}{false}}}{1}{0}}"
server_prompts= "Login::"
server_set_id=$2

mysql_login:
driver = plaintext
public_name = LOGIN
server_condition = "${if eq{$2} \
{${lookup mysql{SELECT passwd FROM smtpauth \
WHERE login='${local_part:$1}'}{$value}{false}}}{1}{0}}"
server_prompts= "Username:: : Password:: "
server_set_id=$1

This seems to work fine, except for one thing: the passwords in the
database have to be stored in plain text, which is not entirely the best
thing. I've been dredging the docs and Google to see if there's some
built-in Exim functionality which will let me specify in the lookup that
the passwd is encrypted but can't seem to find anything. Any pointers?

The other thing that has got me flummoxed is getting the same
authenticator working for cram_md5 - I just can't seem to get the mysql
lookup syntax right. If I can get cram_md5 working, I'll be less fussed
about the plaintext passwords, but it would still be nice to get both
fixed.

Thanks in advance for your valuable time!

Konrad

--
***********************************************************
* Konrad Michels
* IT Manager
* Surfkitchen Limited
* +441189298079
***********************************************************

The information contained in this message is confidential. It is
intended solely for the use of the individual or entity to whom it is
addressed and other authorised to receive it. If the reader of this
message is not the intended recipient, you are hereby notified that any
use, copying, dissemination or disclosure of this information is
strictly prohibited. If you are not the intended recipient, please
delete it immediately and contact the sender by e-mail or telephone.
Internet e-mails are not necessarily secure. SurfKitchen accepts no
responsibility either for breaches of confidence that may arise through
the use of this medium or for changes to any e-mail which occur after
the e-mail has been sent.

--

## List details at http://www.exim.org/mailman/listinfo/exim-users Exim details at http://www.exim.org/ ##

Tim Jackson

2004-01-07 16:29:44 UTC

Permalink

Post by Konrad Michels
This seems to work fine, except for one thing: the passwords in the
database have to be stored in plain text

...

Post by Konrad Michels
The other thing that has got me flummoxed is getting the same
authenticator working for cram_md5

If you are going to be using CRAM-MD5 at all, you are going to have to
keep the passwords in plaintext on the server. (This is not a limitation
of Exim; it's to do with the algorithm, which requires the plaintext
password to be available at the server end in order to compute a hash)

Tim

--

## List details at http://www.exim.org/mailman/listinfo/exim-users Exim details at http://www.exim.org/ ##

Konrad Michels

2004-01-07 16:35:10 UTC

Permalink

Hi Tim
Thanks for the response! I'm happy to leave the passwords in cleartext
in the db if I can get CRAM-MD5 lookups working, as the machine is
locked down fairly tightly. Now just got to wait for those more gifted
in mysql lookup syntax to see if they've got any tips!

Thanks again
Konrad

Post by Tim Jackson

Post by Konrad Michels
This seems to work fine, except for one thing: the passwords in the
database have to be stored in plain text

...

Post by Konrad Michels
The other thing that has got me flummoxed is getting the same
authenticator working for cram_md5

If you are going to be using CRAM-MD5 at all, you are going to have to
keep the passwords in plaintext on the server. (This is not a limitation
of Exim; it's to do with the algorithm, which requires the plaintext
password to be available at the server end in order to compute a hash)
Tim
--
## List details at http://www.exim.org/mailman/listinfo/exim-users Exim details at http://www.exim.org/ ##

--
***********************************************************
* Konrad Michels
* IT Manager
* Surfkitchen Limited
* +441189298079
***********************************************************

The information contained in this message is confidential. It is
intended solely for the use of the individual or entity to whom it is
addressed and other authorised to receive it. If the reader of this
message is not the intended recipient, you are hereby notified that any
use, copying, dissemination or disclosure of this information is
strictly prohibited. If you are not the intended recipient, please
delete it immediately and contact the sender by e-mail or telephone.
Internet e-mails are not necessarily secure. SurfKitchen accepts no
responsibility either for breaches of confidence that may arise through
the use of this medium or for changes to any e-mail which occur after
the e-mail has been sent.

--

## List details at http://www.exim.org/mailman/listinfo/exim-users Exim details at http://www.exim.org/ ##

Jez Hancock

2004-01-07 16:39:24 UTC

Permalink

Post by Konrad Michels
I've just setup a Exim 4.30 on a linux box, built with AUTH and MYSQL
lookups enabled. I've done the configs, and have managed to get plain

<snip>

Post by Konrad Michels
This seems to work fine, except for one thing: the passwords in the
database have to be stored in plain text, which is not entirely the best
thing. I've been dredging the docs and Google to see if there's some
built-in Exim functionality which will let me specify in the lookup that
the passwd is encrypted but can't seem to find anything. Any pointers?

One idea could be to use the MySQL ENCODE() function to encode the
password strings submitted using a secret key which you'd store in the
exim config file. If the resulting encoded string matches that stored
in the db, accept the connection, otherwise deny.

This scheme would obviously require that you ENCODE() the password
strings in the first place when they're entered into the db originally
(or subsequently changed), but this would be the same for any other
hashing method of course :P

Using the MySQL ENCODE() function has the added benefit that you can
actually DECODE() password strings as well using the secret key -
a bonus for sending out forgotten passwords.

Just an idea.

The encode/decode functions are detailed here:

http://www.mysql.com/doc/en/Miscellaneous_functions.html

--
Jez Hancock
- System Administrator / PHP Developer

http://munk.nu/
http://jez.hancock-family.com/ - personal weblog
http://ipfwstats.sf.net/ - ipfw peruser traffic logging

--

## List details at http://www.exim.org/mailman/listinfo/exim-users Exim details at http://www.exim.org/ ##

Konrad Michels

2004-01-07 16:43:58 UTC

Permalink

Hi Jez
Yeah, I tried using the MySQL encode() function to encode the password
when I stored it in the database, but the lookup I have in the
authenticator reads it as plaintext, not as encoded, so authentication
against an encoded password fails . . .

Later
Konrad

Post by Jez Hancock

Post by Konrad Michels
I've just setup a Exim 4.30 on a linux box, built with AUTH and MYSQL
lookups enabled. I've done the configs, and have managed to get plain

<snip>

One idea could be to use the MySQL ENCODE() function to encode the
password strings submitted using a secret key which you'd store in the
exim config file. If the resulting encoded string matches that stored
in the db, accept the connection, otherwise deny.
This scheme would obviously require that you ENCODE() the password
strings in the first place when they're entered into the db originally
(or subsequently changed), but this would be the same for any other
hashing method of course :P
Using the MySQL ENCODE() function has the added benefit that you can
actually DECODE() password strings as well using the secret key -
a bonus for sending out forgotten passwords.
Just an idea.
http://www.mysql.com/doc/en/Miscellaneous_functions.html
--
Jez Hancock
- System Administrator / PHP Developer
http://munk.nu/
http://jez.hancock-family.com/ - personal weblog
http://ipfwstats.sf.net/ - ipfw peruser traffic logging
--
## List details at http://www.exim.org/mailman/listinfo/exim-users Exim details at http://www.exim.org/ ##

Torsten Mueller

2004-01-07 17:22:56 UTC

Permalink

Post by Konrad Michels
Hi Folks
Someone has probably asked this before (can't recall seeing anything
about it in the last couple of thousand mails!), so apologies if this is
covering old ground again.

...

Post by Konrad Michels
The other thing that has got me flummoxed is getting the same
authenticator working for cram_md5 - I just can't seem to get the mysql
lookup syntax right. If I can get cram_md5 working, I'll be less fussed
about the plaintext passwords, but it would still be nice to get both
fixed.
Thanks in advance for your valuable time!

I did it this way:

MYSQL_AUTH_CRAM = SELECT MYSQL_SMTPAUTH_PASS_FIELD from
MYSQL_SMTPAUTH_TABLE where MYSQL_SMTPAUTH_USER_FIELD = '${quote
_mysql:$1}' AND MYSQL_SMTPAUTH_PASS_FIELD != ''

cram:
driver = cram_md5
public_name = CRAM-MD5
server_secret = ${lookup mysql {MYSQL_AUTH_CRAM}}
server_set_id = $1

Torsten

--

## List details at http://www.exim.org/mailman/listinfo/exim-users Exim details at http://www.exim.org/ ##

Konrad Michels

2004-01-07 19:09:57 UTC

Permalink

Wicked - that works a treat! Thanks for that example - clears up a
whole lot of things for me and I've learned something in the process!

Later
Konrad

Post by Torsten Mueller

...

MYSQL_AUTH_CRAM = SELECT MYSQL_SMTPAUTH_PASS_FIELD from
MYSQL_SMTPAUTH_TABLE where MYSQL_SMTPAUTH_USER_FIELD = '${quote
_mysql:$1}' AND MYSQL_SMTPAUTH_PASS_FIELD != ''
driver = cram_md5
public_name = CRAM-MD5
server_secret = ${lookup mysql {MYSQL_AUTH_CRAM}}
server_set_id = $1
Torsten
--
## List details at http://www.exim.org/mailman/listinfo/exim-users Exim details at http://www.exim.org/ ##

Konrad Michels

2004-01-08 10:18:36 UTC

Permalink

BTW - this also works for SPA . . . thought I'd let everyone know.

Later
Konrad

Post by Torsten Mueller

...

Peter Bowyer

2004-01-07 18:50:26 UTC

Permalink

Post by Konrad Michels
This seems to work fine, except for one thing: the passwords in the
database have to be stored in plain text, which is not entirely the
best thing. I've been dredging the docs and Google to see if there's
some built-in Exim functionality which will let me specify in the
lookup that the passwd is encrypted but can't seem to find anything.
Any pointers?

crypteq

http://www.exim.org/exim-html-4.30/doc/html/spec_11.html#IX872

Peter

--

## List details at http://www.exim.org/mailman/listinfo/exim-users Exim details at http://www.exim.org/ ##

Holger Mauermann

2004-01-07 17:48:00 UTC

Permalink

Post by Konrad Michels
The other thing that has got me flummoxed is getting the same
authenticator working for cram_md5 - I just can't seem to get the
mysql lookup syntax right.

mysql_cram:
driver = cram_md5
public_name = CRAM-MD5
server_secret = ${lookup mysql{SELECT password FROM users \
WHERE user = '${quote_mysql:$1}'} {$value} fail}
server_set_id=$1

--
Holger Mauermann GnuPG/PGP Key ID: 0x8EA8C301

--

## List details at http://www.exim.org/mailman/listinfo/exim-users Exim details at http://www.exim.org/ ##

Svein E. Seldal

2004-01-11 14:14:41 UTC

Permalink

Post by Tim Jackson
If you are going to be using CRAM-MD5 at all, you are going to have to
keep the passwords in plaintext on the server. (This is not a
limitation of Exim; it's to do with the algorithm, which requires the
plaintext password to be available at the server end in order to
compute a hash)

I dont get it. I have a server with a sendmail MTA. It is able to use
CRAM-MD5 without using having the password available in cleartext. It
reads the /etc/shadow (which IFAIK is one-way hashed, right?). How is
that possible?

In all cases, what I want is not to use plain-text AUTH because I'm not
going to use encrypted connections, hence I want CRAM-MD5. At the same
time am I not particular happy about storing the pwd's in cleartext on
the server (because its readable by root). The MySQL ENCRYPT() DECRYPT()
scheme is better. However the mysql encrypt key must be stored in the
exim config file, leaving it possible for root to get the cleartext
passwords for the user if he wants to. /etc/shadow is just beautiful
that way -- the root cannot get the passwords without using brute force
methods.

But again, sendmail seems to be able to combine CRAM-MD5 and the hashed
passwords in /etc/shadow...

Regards,
Svein

--

## List details at http://www.exim.org/mailman/listinfo/exim-users Exim details at http://www.exim.org/ ##

Nigel Metheringham

2004-01-12 10:34:50 UTC

Permalink

Post by Svein E. Seldal
I dont get it. I have a server with a sendmail MTA. It is able to use
CRAM-MD5 without using having the password available in cleartext. It
reads the /etc/shadow (which IFAIK is one-way hashed, right?). How is
that possible?

It cannot be doing this.
I believe its possible to hold a pre-processed password at the server
end for doing CRAM-MD5 authentication, but due to the way the algorithm
works that password will be at least plain text equivalent (ie you could
use that to perform the authentication with an appropriate set of
transformations).

However if you are keeping the passwords in shadow then you are not
doing CRAM-MD5 - have you traced the authentication requests?

Nigel.
--
[ Nigel Metheringham ***@InTechnology.co.uk ]
[ - Comments in this message are my own and not ITO opinion/policy - ]

--

## List details at http://www.exim.org/mailman/listinfo/exim-users Exim details at http://www.exim.org/ ##

Philip Hazel

2004-01-14 00:30:48 UTC

Permalink

Post by Nigel Metheringham

It cannot be doing this.

...unless the encrypted password is kept at both ends, in which case the
encrypted string is essentially being used as the "password". I suppose
the client could be generating the encrypted string each time, assuming
it can discover which salt to use.

--
Philip Hazel University of Cambridge Computing Service,
***@cus.cam.ac.uk Cambridge, England. Phone: +44 1223 334714.
Get the Exim 4 book: http://www.uit.co.uk/exim-book

--

## List details at http://www.exim.org/mailman/listinfo/exim-users Exim details at http://www.exim.org/ ##

Nigel Metheringham

2004-01-14 09:18:57 UTC

Permalink

Post by Philip Hazel

Post by Nigel Metheringham

It cannot be doing this.

This simply means that the encrypted password on the server, if
retrieved, can be used in place of the original password - ie it is
plain text password equivalent.

Nigel.
--
[ Nigel Metheringham ***@InTechnology.co.uk ]
[ - Comments in this message are my own and not ITO opinion/policy - ]

--

## List details at http://www.exim.org/mailman/listinfo/exim-users Exim details at http://www.exim.org/ ##

Svein E. Seldal

2004-01-14 10:43:22 UTC

Permalink

Post by Nigel Metheringham

Post by Philip Hazel

Post by Nigel Metheringham

It cannot be doing this.

This simply means that the encrypted password on the server, if
retrieved, can be used in place of the original password - ie it is
plain text password equivalent.

Hmm. Maybe you're right that it isnt possible to use encr. passwds on
CRAM-MD5. -- That sendmail doesnt use CRAM-MD5 on shadow passwds at all.

The sendmail server sais it supports AUTH CRAM-MD5 PLAIN. When my
mailclient connects, it tries to auth via. CRAM-MD5 (as I told you
previously). The client and the server exchanges CRAM-MD5 data, however
I failed to notice that it actually fails to auth via CRAM-MD5 (500 auth
failure). So what happens next is that my mail client uses AUTH PLAIN
which succeeds! So much for that security.

Alas, If I knew this a little earlier... I'm sorry If I confused
someone. SSL here we come...

Regards embarrassed,
Svein

--

## List details at http://www.exim.org/mailman/listinfo/exim-users Exim details at http://www.exim.org/ ##