Postfix and MySQL

[Anish Mathew]

I think this would help u..Sorry for pasting the
tutorial below.. as i forgot the link..
credit goes to Craig at http://small.dropbear.id.au


SMTP Authentication with Postfix and MySQL


 There are times when you need to have users
authenticate their SMTP sessions. Perhaps you have
roaming users and you don't want to be an open relay,
but you cannot predict where these users are. You need
a way for them to say to your SMTP server "hey I
belong here, let me send email". 


One way to do is is using SMTP Authentication. The
user's username and password are sent to the SMTP
server. The server then checks the pair is correct and
lets the user then send mail (or not if they are
incorrect). SMTP Authentication is defined in RFC2554.



Postfix has a method of authentication, but it is tied
up with SASL so you cannot simply make a LDAP or MySQL
table and be done with it. The way I have implemented
it here Postfix uses SASL which uses PAM which uses
MySQL; a round-about way but it does work. There is
some sporadic documentation about this around The
Internet, but I wrote this up in the hope you find it
useful and so I don't have to remember it or relearn
it all over again. 


You might also be able to adapt this method to use
other sorts of PAM authentication. For example I'm
pretty sure this method with a little adaption would
also work for LDAP authentication. Obviously you could
use other databases other than MySQL, its just what I
was using here. 


Required Packages


 The following Debian packages are required to get
this all working. I'm using Debian 3.0 ("Woody") here
but for the most part it should work for other
versions and dists with some small changes. Some other
packages will be needed, but will be pulled in as
dependencies. 

postfix-tls 1.1.11+tls0.7.15-0.woody1
The main postfix server with TLS and SASL support.
libsasl-modules-plain 1.5.27-3
Modules that provide the LOGIN, PLAIN and CRAM-MD5
authentication methods.
libsasl-digestmd5-des 1.5.24-11
Provides the DIGEST-MD5 authentication method.
libpam-mysql 0.4.7-1
PAM module to query a MySQL database.
metamail
Useful for base64 encoding and decoding using
mimencode.

 You have to make sure that either one or both of the
authentication modules packages are installed. If you
don't and you setup postfix to use SASL (see below)
then the smtpd process will be throttled. 


Postfix setup


 If you do not read anything else from this page then
read the next sentence. I could only get this working
when smtpd was not chrooted!!. This had me going for a
long, long time. To change this, edit
/etc/postfix/master.cf and change the following line: 


smtp inet n - n - - smtpd 


The second 'n' means it is not chrooted. There may be
a way of running smtpd in a chroot with the SASL
authentication but I'm not sure how. 


The following lines are added to /etc/postfix/main.cf 


smtpd_sasl_auth_enable = yes
 smtpd_sasl_local_domain = myserver
 broken_sasl_auth_clients = yes
 smtpd_recipient_restrictions = permit_mynetworks
permit_sasl_authenticated reject 


So far the postfix server knows it has to use SASL if
it gets an authentication request, but it doesn't know
what to do with it. The default SASL method is to use
a Berkley DB file called /etc/sasldb that can be
manipulated with the saslpasswd program. But we want
to get it to authenticate to the MySQL database. 


SASL Setup


 The next step is to get SASL to ask PAM to
authenticate the user. There's some confusion because
the location of this file has moved around. On my
system with the versions of the packages given above,
it is found at /etc/postfix/sasl/smtpd.conf but it
also has been found in /usr/local/lib/sasl/smtpd.conf
and /usr/lib/sasl/smtp.conf. The file is real simple
one-liner: 


pwcheck_method: pam 


That's it for SASL, it will then use standard PAM as
we all know and love for authenticating.


PAM Setup


 The PAM setup is pretty standard. All you need to
know is the PAM service is called smtp, so you need to
create a file /etc/pam.d/smtp. SASL only uses the
authentication management group. 


It might be useful to test how things are going so
far. To do this, and only for testing, you can use the
pam_permit module. This module permits anything you
send, so its useful for testing or for some strange
circumstances, but shouldn't be used in a production
environment. The file /etc/pam.d/smtp would then look
like: 



auth required pam_permit.so 


If you are going to run it with MySQL, use a
configuration similar to that shown below. The
configuration is similar to a user doing the
following: 


server$ mysql -u postfix -psecret postfixdb
 mysql> SELECT id FROM users WHERE id='givenusername'
AND password='givenpassword'; 



auth required pam_mysql.so user=postfix passwd=secret
db=postfixdb table=users usercolumn=id
passwdcolumn=password crypt=0 


The table users has two columns. The first is called
id and has the username, the second is password it has
the unencrypted password in it. A select is made
checking both username and password. If there is a
single row returned, authentication is successful. 


Testing


 I use the plain authentication method for testing. To
do this you need to convert the username and password
into a base64 encoded string. For example, if you have
username user and password pass, you would type: 


server$ printf 'user\0user\0pass' | mimencode
 dXNlcgB1c2VyAHBhc3M= 


So the string is the username and password joined
together with \0 between them. The username is needed
twice. To test it, telnet to the SMTP port of your
server and type the auth commands. 


server$ telnet mail.my.server 25
 Trying 10.1.2.3
 Connected to 10.1.2.3.
 Escape character is '^]'.
 220 mail.my.server ESMTP Postfix
 EHLO blah
 250-mail.my.server
 250-PIPELINING
 250-SIZE 10240000
 250-VRFY
 250-ETRN
 250-AUTH LOGIN PLAIN CRAM-MD5 DIGEST-MD5
 250-AUTH=LOGIN PLAIN CRAM-MD5 DIGEST-MD5
 250-XVERP
 250 8BITMIME
 auth plain dXNlcgB1c2VyAHBhc3M=
 235 Authentication successful
 


I've used a EHLO instead of the normal HELO as this is
an extended hello, so the server gives you a list of
things it can do. Notice that there are two AUTH
lines, this is due to the broken_sasl_auth_clients
line in /etc/postfix/main.cf. 


LOGIN, PLAIN and CRAM-MD5 appear if you have
libsasl-modules-plain installed, DIGEST-MD5 appear if
you have libsasl-digestmd5-des installed, so those
lines may look different on your setup. 

The important thing is the server's response to your
commands is 235 Authentication successful. This means
that it recognizes the username and password. If it
doesn't, it returns a 535 Error: authentication
failed. 

Instead of using the plain authentication, you might
want to use the LOGIN method. Once again mimencode is
used to get the base64 encoding: 


server$ printf 'user' | mimencode
 dXNlcg==
 server$ printf 'pass' | mimencode
 cGFzcw==
 


You now have the two base64 encoded strings, to test
this method is very similar to the PLAIN method. 


server$ telnet 10.1.2.3 25
 Trying 10.1.2.3...
 Connected to 10.1.2.3.
 Escape character is '^]'.
 220 my.mail.server ESMTP Postfix
 EHLO blah
 250-my.mail.server
 250-PIPELINING
 250-SIZE 10240000
 250-VRFY
 250-ETRN
 250-AUTH LOGIN PLAIN CRAM-MD5 DIGEST-MD5
 250-AUTH=LOGIN PLAIN CRAM-MD5 DIGEST-MD5
 250-XVERP
 250 8BITMIME
 auth login
 334 VXNlcm5hbWU6
 dXNlcg==
 334 UGFzc3dvcmQ6
 cGFzcw==
 235 Authentication successful
 


You might wonder what that strange text is after the
334 numbers. Once again mimencode can help. It's a
base64 encoding of the response from the mail server. 


server$ printf 'VXNlcm5hbWU6' | mimencode -u ; echo
 Username:
 gonzo$ printf 'UGFzc3dvcmQ6' | mimencode -u ; echo
 Password:
 


So the mail server is asking for a username and
password, in base64. I don't know why they bother to
do this as it doesn't make it that much more secure
but at least you now know what it is

Anish

--- kenwardc <kenwardc at tgis.co.uk> wrote: 
> Hi Folks
> 
> I want to set up postfix so it uses the MySQL
> database on the local
> machine but have absoutely no idea how to do that.
> The database is
> already there and is populated by a package called
> Hivemail that I'm
> using as a web mail server.
> 
> Anyone done this before? I'm desperate - have
> everything working
> except the postfix with MySQL.
> 
> Regards
> Chris
> 
> 
> 
> ---
> All messages scanned by AVG 7.0 Anti-Virus scanner
> and TGIS Anti-Spam Firewall.
> 
> 
> -- 
> redhat-list mailing list
> unsubscribe
>
mailto:redhat-list-request at redhat.com?subject=unsubscribe
> https://www.redhat.com/mailman/listinfo/redhat-list
>  


	
	
		
___________________________________________________________ALL-NEW Yahoo! Messenger - all new features - even more fun!  http://uk.messenger.yahoo.com