Curious, MySQL and SQLite use ? for parameters, table-postgresql help
also says the question mark is used, but the actual example has $1.
I've no idea whether both should work, but you might want to have a go
with ? instead of $1 in the query strings.
Also you might want to run in verbose mode:
smtpd -dv
Good luck!
Reio
On 27.03.18 16:58, Denis wrote:
Hello,
Having working OpenSMTPd with plain text file user authentication. The
auth from table(5) works like a charm currently. But I have a trouble to
authenticate users by OpenSMTPd + Dovecot from shared PGSQL database.
As for Dovecot, it authenticates users from PGSQL as expected. Dovecot's
LMTP authentication works pretty fine too when OpenSMTPd connects to it.
The trouble especially with smptd PGSQL users auth for now.
$ tail -n5 /var/log/maillog
smtpd[9579]: info: OpenSMTPD 6.0.0 starting
smtpd[9579]: warn: table-proc: pipe closed
smtpd[9579]: lookup: table-proc: exiting
smtpd[9579]: smtpd: process lka socket closed
After restart by
# rcctl restart smtpd
I see closed connection to a PGSQL which initiated by smtpd
# netstat -an | grep 5432
tcp 0 0 127.0.0.1.3245 127.0.0.1.5432 TIME_WAIT
Configuration steps I have listed below:
1. OpenSMTPd is a part of OpenBSD 6.2amd64 I'm running.
2. # pkg_add opensmtpd-extras opensmtpd-extras-pgsql
3. $ cat /etc/mail/smtpd.conf
...
table alias db:/etc/mail/alias
table domain posgres:/etc/mail/pgsql.conf
table virtuals postgres:/etc/mail/pgsql.conf
table pass postgres:/etc/mail/pgsql.conf
table userinfo postgres:/etc/mail/pgsql.conf
...
4. $ cat /etc/mail/pgsql.conf
# PostgreSQL
conninfo host='127.0.0.1:5432' dbname='maildatabase' user='maildbuser'
password='secret'
query_alias SELECT CONCAT (alias, ' ', dest) AS alias FROM maildatabase
WHERE alias=$1
query_domain SELECT domain FROM maildatabase WHERE domain=$1
query_credentials SELECT user, pass FROM maildatabase WHERE userid=$1
AND active=$1
query_userinfo SELECT CONCAT (user, realm) AS user, uid, gid FROM
maildatabase WHERE user=$1 AND active='1'
5. The database schema is self written, I can share it if it was necessary.
---
What can be wrong?
Denis
--
Tervitades
Reio Remma
MR Stuudio 25 aastat
*MR Stuudio OÜ*
Tondi 17b, 11316, Tallinn
Tel +372 650 4808
Mob +372 56 22 00 33
[email protected]
www.mrstuudio.ee
