Re: OpenSMTPd PostgreSQL user authentication

[Olivier Burelli]

Hello,

Maybe the semicolon <;> is missing at the end of each queries
in /etc/mail/pgsql.conf

if not, could you share the relations of your database ?

Olivier.

On Tue, 27 Mar 2018 18:16:47 +0300
Denis  wrote:

> smtpd auth works from PGSQL since than. Thank you for answers.
> 
> Right configs are below:
> 
> $ cat /etc/mail/smptd.conf
> ...
> table alias db:/etc/mail/alias
> table domains 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
> ...
> 
> $ cat /etc/mail/pgsql.conf
> # PostgreSQL
> conninfo host=127.0.0.1 dbname=maildatabase user=maildbuser
> password=secret
> query_alias SELECT dest FROM virtuals WHERE alias=$1
> query_domain SELECT domain FROM domains WHERE domain=$1 LIMIT 1
> query_credentials SELECT user, pass FROM users WHERE user=$1 AND
> active='1' query_userinfo SELECT CONCAT (user, realm) AS user, uid,
> gid FROM users WHERE user=$1 AND active='1'
> 
> The database schema can be any, queries can be made by changing
> table> names and columns in /etc/mail/pgsql.conf according to schema.
> 
> ---
> Denis
> 
> On 3/27/2018 5:25 PM, Reio Remma wrote:
> > On 27.03.18 16:58, Denis wrote:  
> >> 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  
> > 
> > I suspect query_credentials should also have active='1' instead of
> > active=$1.
> > 
> > Reio
> > 
> >   
> 




-- 
You received this mail because you are subscribed to misc@opensmtpd.org
To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org

Re: OpenSMTPd PostgreSQL user authentication

[Denis]

smtpd auth works from PGSQL since than. Thank you for answers.

Right configs are below:

$ cat /etc/mail/smptd.conf
...
table alias db:/etc/mail/alias
table domains 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
...

$ cat /etc/mail/pgsql.conf
# PostgreSQL
conninfo host=127.0.0.1 dbname=maildatabase user=maildbuser
password=secret
query_alias SELECT dest FROM virtuals WHERE alias=$1
query_domain SELECT domain FROM domains WHERE domain=$1 LIMIT 1
query_credentials SELECT user, pass FROM users WHERE user=$1 AND active='1'
query_userinfo SELECT CONCAT (user, realm) AS user, uid, gid FROM users
WHERE user=$1 AND active='1'

The database schema can be any, queries can be made by changing table
names and columns in /etc/mail/pgsql.conf according to schema.

---
Denis

On 3/27/2018 5:25 PM, Reio Remma wrote:
> On 27.03.18 16:58, Denis wrote:
>> 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
> 
> I suspect query_credentials should also have active='1' instead of
> active=$1.
> 
> Reio
> 
> 

-- 
You received this mail because you are subscribed to misc@opensmtpd.org
To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org

Re: OpenSMTPd PostgreSQL user authentication

[Reio Remma]

On 27.03.18 16:58, Denis wrote:

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


I suspect query_credentials should also have active='1' instead of 
active=$1.


Reio


--
You received this mail because you are subscribed to misc@opensmtpd.org
To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org

Re: OpenSMTPd PostgreSQL user authentication

[Reio Remma]

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
r...@mrstuudio.ee
www.mrstuudio.ee

OpenSMTPd PostgreSQL user authentication

[Denis]

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

-- 
You received this mail because you are subscribed to misc@opensmtpd.org
To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org