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

* Chris St Denis <chris <at> smartt.com>:
> 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
>    allowplaintext: yes
>    mech_list: LOGIN PLAIN
>    password_format: plaintext
>    sql_user: mail
>    sql_passwd:
>    sql_hostnames: localhost
>    sql_database: mail
>    sql_select: SELECT pass FROM emails_view WHERE email = '%u@%r'
>    log_level: 7
>    sql_verbose: true

Reduce it to this:

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@%r'

> If I use saslpasswd2 on an account I get "generic failure". Does 
> saslpasswd2 even work on sql or is it sasldb only?

It's sasldb only (it is said to be others too, but that's hearsay).

> 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

Did you create the required, symbolic link from /usr/local/lib/sasl2/ to
/usr/lib/sasl2/?

> 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.
>
>    May 13 15:05:42 barium pluginviewer: SQL engine 'mysql' not supported
>    May 13 15:05:42 barium pluginviewer: auxpropfunc error no mechanism
>    available
>    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

It can't find any mechanisms such as PLAIN and LOGIN.

> 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

The mechanisms are there.

Are the passwords in your database crypted? They must not. They must be
plaintext.

Have you checked the access permissions to the database? Can your "mail" user
connect and SELECT FROM as you need it?

p <at> rick

--

-- 
The Book of Postfix
<http://www.postfix-book.com>
saslfinger (debugging SMTP AUTH):
<http://postfix.state-of-mind.de/patrick.koetter/saslfinger/>