SASL postgresql backend doesn't work. Please help.

Re: SASL postgresql backend doesn't work. Please help.

Andreas Winkelmann wrote:

On Donnerstag, 15. Mai 2008, Chris St Denis wrote:
I am trying to get SASL to work authenticated to a postgresql database for SMTP auth with postfix. But it sasl is being very uncooperative. basic system info barium# uname -mrs FreeBSD 7.0-RELEASE-p1 amd64 cyrus-sasl version: 2.1.22 postfix version: 2.5.1 One of my biggest problems is I can't find any documentation of the smtpd.conf file, but form what I've pieced together from tutorials and such I've got this. pwcheck_method: auxprop auxprop_plugin: sql sql_engine: pgsql

allowanonymouslogin: no
Not a Cyrus-SASL Option
allowplaintext: yes
Not a Cyrus-SASL Option
mech_list: LOGIN PLAIN

password_format: plaintext
Not a Cyrus-SASL Option. Maybe implemented with a Patch?

These are what I've pulled from various tutorials, mailing list posts, etc If they are wrong they are gone now as per Patrick's post.

sql_user: mail sql_passwd: sql_hostnames: localhost sql_database: mail sql_select: SELECT pass FROM emails_view WHERE email = '%u <at> %r' log_level: 7 sql_verbose: true If I use saslpasswd2 on an account I get "generic failure". Does saslpasswd2 even work on sql or is it sasldb only?
It works generally with MySQL or PostgreSQL, too. But not with your Config-File above. To add or change Data to/in a SQL Database, normally someone would expect UPDATE- or INSERT-Commands. I see none in your config. The associated Cyrus-SASL Options would be "sql_insert:" or "sql_update:".

I am not interested it using it to maintain data. I was just using it to try to get account data as part of my debug process. I have a web interface that manipulates the database directly.

barium# saslpasswd2 -a smtpd jeann <at> darkadsl.ca saslpasswd2: generic failure If I run "pluginviewer -a" it only lists sasldb. Shouldn't SQL be in here? barium# pluginviewer -a Installed auxprop mechanisms are: sasldb List of auxprop plugins follows Plugin "sasldb" , API version: 4 supports store: yes barium# pluginviewer -s Installed SASL (server side) mechanisms are: LOGIN ANONYMOUS PLAIN GSSAPI DIGEST-MD5 CRAM-MD5 EXTERNAL List of server plugins follows Plugin "login" [loaded], API version: 4 SASL mechanism: LOGIN, best SSF: 0, supports setpass: no security flags: NO_ANONYMOUS features: Plugin "anonymous" [loaded], API version: 4 SASL mechanism: ANONYMOUS, best SSF: 0, supports setpass: no security flags: NO_PLAINTEXT features: WANT_CLIENT_FIRST Plugin "plain" [loaded], API version: 4 SASL mechanism: PLAIN, best SSF: 0, supports setpass: no security flags: NO_ANONYMOUS features: WANT_CLIENT_FIRST|PROXY_AUTHENTICATION Plugin "gssapiv2" [loaded], API version: 4 SASL mechanism: GSSAPI, best SSF: 56, supports setpass: no security flags: NO_ANONYMOUS|NO_PLAINTEXT|NO_ACTIVE|PASS_CREDENTIALS|MUTUAL_AUTH features: WANT_CLIENT_FIRST|PROXY_AUTHENTICATION Plugin "digestmd5" [loaded], API version: 4 SASL mechanism: DIGEST-MD5, best SSF: 128, supports setpass: no security flags: NO_ANONYMOUS|NO_PLAINTEXT|MUTUAL_AUTH features: PROXY_AUTHENTICATION Plugin "crammd5" [loaded], API version: 4 SASL mechanism: CRAM-MD5, best SSF: 0, supports setpass: no security flags: NO_ANONYMOUS|NO_PLAINTEXT features: SERVER_FIRST

And the files are there barium# ll /usr/local/lib/sasl2/*sql* -rw-r--r-- 1 root wheel 28568 May 13 10:27 /usr/local/lib/sasl2/libsql.a -rwxr-xr-x 1 root wheel 826 May 13 10:27 /usr/local/lib/sasl2/libsql.la lrwxr-xr-x 1 root wheel 11 May 13 10:27 /usr/local/lib/sasl2/libsql.so -> libsql.so.2 -rwxr-xr-x 1 root wheel 27026 May 13 10:27 /usr/local/lib/sasl2/libsql.so.2 For some reason I get some mysql related errors in the syslog like these. I'm using postgresql not mysql. It's compiled --without-mysql and mysql isn't even installed in the server.
"mysql" is the default sql_engine if no other is specified. In your case this means your smtpd.conf is not read. Maybe wrong Directory? Some Distributions do a lot of Patching.

This is where the ports system installed it so it seems the logical place. Ports tend to work together very well.

May 13 15:05:42 barium pluginviewer: SQL engine 'mysql' not supported May 13 15:05:42 barium pluginviewer: auxpropfunc error no mechanism available
Check where your Cyrus-SASL expects the Config File. Maybe trace the saslpasswd Binary.

I'm not very familiar with tracing programs, but from what I tried here is what I've found

libpq (postgres's client library) is getting loaded

1493 saslpasswd2 NAMI "/usr/local/lib/libpq.so.5"
1493 saslpasswd2 RET   access 0
1493 saslpasswd2 CALL open(0x80052a480,O_RDONLY,<unused>0x62eee0)
1493 saslpasswd2 NAMI "/usr/local/lib/libpq.so.5"
1493 saslpasswd2 RET   open 3
1493 saslpasswd2 CALL fstat(0x3,0x7fffffffe7f0)
1493 saslpasswd2 RET   fstat 0
1493 saslpasswd2 CALL read(0x3,0x80062dec0,0x1000)
1493 saslpasswd2 GIO   fd 3 read 4096 bytes

and it is opening /usr/local/lib/sasl2/smtpd.conf just fine.

1493 saslpasswd2 CALL open(0x801b19080,O_RDONLY,<unused>0x1b6)
1493 saslpasswd2 NAMI "/usr/local/lib/sasl2/smtpd.conf"
1493 saslpasswd2 RET   open 3
1493 saslpasswd2 CALL fstat(0x3,0x7fffffffd4c0)
1493 saslpasswd2 RET   fstat 0
1493 saslpasswd2 CALL read(0x3,0x801b1b000,0x1000)
1493 saslpasswd2 GIO   fd 3 read 219 bytes
       "pwcheck_method: auxprop
        auxprop_plugin: sql
        sql_engine: pgsql
        mech_list: LOGIN PLAIN
        sql_user: mail
        sql_passwd:
        sql_hostnames: localhost
        sql_database: mail
        sql_select: SELECT pass FROM emails_view WHERE email = '%u <at> %r'
       "
1493 saslpasswd2 RET   read 219/0xdb
1493 saslpasswd2 CALL write(0x2,0x7fffffffe2b0,0x1d)
1493 saslpasswd2 GIO   fd 2 wrote 29 bytes
       "saslpasswd2: generic failure
       "
1493 saslpasswd2 RET   write 29/0x1d
1493 saslpasswd2 CALL exit(0x1)

May 13 15:05:46 barium pluginviewer: SQL engine 'mysql' not supported May 13 15:05:46 barium pluginviewer: auxpropfunc error no mechanism available May 13 15:05:51 barium pluginviewer: SQL engine 'mysql' not supported May 13 15:05:51 barium pluginviewer: auxpropfunc error no mechanism available May 13 15:17:38 barium server: SQL engine 'mysql' not supported May 13 15:17:38 barium server: auxpropfunc error no mechanism available Other than that, I only get generic errors like May 13 15:31:07 barium postfix/smtpd[79672]: warning: SASL per-process initialization failed: generic failure May 13 15:31:07 barium postfix/smtpd[79672]: fatal: SASL per-process initialization failed using the client/server in "sample" Client barium# ./client -s smtpd -m LOGIN localhost receiving capability list... recv: {48} LOGIN ANONYMOUS PLAIN GSSAPI DIGEST-MD5 CRAM-MD5 LOGIN ANONYMOUS PLAIN GSSAPI DIGEST-MD5 CRAM-MD5 send: {5} LOGIN send: {1} N recv: {9} Username: please enter an authentication id: jeann <at> darkadsl.ca Password: send: {17} jeann <at> darkadsl.ca recv: {9} Password: send: {6} asdfgh authentication failed closing connection Server accepted new connection send: {48} LOGIN ANONYMOUS PLAIN GSSAPI DIGEST-MD5 CRAM-MD5 recv: {5} LOGIN recv: {1} N send: {9} Username: recv: {17} jeann <at> darkadsl.ca send: {9} Password: recv: {6} asdfgh performing SASL negotiation: user not foundclosing connection