Re: Postfix(chroot) and Postgresql

[Stuart Henderson]

On 2007/12/25 12:57, badeguruji wrote:
 I want to setup postfix and dovecot. i want to authenticate my users thru 
 ldap.
 [...] (i do not want to be a ldap guru)

If you want to use ldap for critical infrastructure, you'd better
understand it (and an email setup big enough to need some kind of
distributed database implies it's going to be fairly critical).

 Is there a place where i can find some 'ponited' help on how to
 build such an 'email users' database? i do not want to have unix
 logins for them.

I don't have a clue what 'ponited' means but if you need a set of
instructions to follow to set it up, how are you going to fix it
when there's a problem?

Dovecot works nicely with virtual users (no unix logins) with
simple user/password databases (flat-file, Berkeley db, etc).
Have you tried it?

 From: Craig Skinner [EMAIL PROTECTED]
 Sent: Saturday, December 1, 2007 7:12:54 AM
 Subject: Re: Postfix(chroot) and Postgresql
 
  How about - using OpenLDAP? 
 
 Same thing. Flat files are fast and reliable [...]

To add to Craig's comment from the mail you quoted, they're
also a lot simpler.

Re: Postfix(chroot) and Postgresql

[Marc Balmer]

badeguruji wrote:

[...]


i am searching on google and have not found anything yet. i am
therefore looking into generic ldap manuals. (i do not want to be a
ldap guru)


I maintaining some larger email installations that use LDAP.  If you are
not willing/don't want to dig deep into the matter, then I suggest that
you don't use it all.  It can be complicated and complex.

Of course, once installed it works like a charm (oh, and we even have an
LDAP enabled version of the venerable vacation(8) program).

- Marc Balmer

[...]

--
   SELECT services FROM companies WHERE name = 'micro systems'

marc balmer, micro systems, wiesendamm 2a, postfach, ch-4019 basel
internet www.msys.ch, phone +41 61 383 05 10, fax +41 61 383 05 12

Re: Postfix(chroot) and Postgresql

[badeguruji]

I want to setup postfix and dovecot. i want to authenticate my users thru ldap.
for that i have installed openldap server package.
Is there a place where i can find some 'ponited' help on how to build such an 
'email users' database? i do not want to have unix logins for them.

i am searching on google and have not found anything yet. i am therefore 
looking into generic ldap manuals. (i do not want to be a ldap guru)

thank you.
-BG
 

~~Kalyan-mastu~~

- Original Message 
From: Craig Skinner [EMAIL PROTECTED]
To: misc@openbsd.org
Sent: Saturday, December 1, 2007 7:12:54 AM
Subject: Re: Postfix(chroot) and Postgresql


On Sat, Dec 01, 2007 at 12:07:54AM +0100, Bengt Frost wrote:
  
   Someone out there have any suggestions how use Postfix (and
 Dovecot)
   with PostgreSQL?
 
  Pull the user data from PostgreSQL and generate the files:
  /etc/sasldb2.db (copy to /var/spool/postfix/etc  postfix reload)
  /etc/cram-md5.pwd
 
  e.g: have a cron driven perl script check for changes to the user
 tables
  in the last 15 mins  if so, then generate new files. Stops
 PostgreSQL
  becoming a bottleneck when under high load (a spam attack).
 
 Ok. Not quite sure I'm following you. You mean pull user data from 
 PostgreSQL
 and generate flat(db) user file for smtp-auth using 
 p5-Authen-SASL-2.10p0 ...

Aye, using whatever you fancy, probably loads of modules on CPAN that
will do most of what you want for your site. Perl is in base, so you
wont run the risk of a broken port of ruby/python/whatever stopping you
working after an upgrade.

Same for /etc/postfix/{aliases,canonical.map,virtual.map}

 
  As your site grows, you can punt the flat files out across your
 mail
  farm from your central db/admin box, use rdist or something
 similar.
 
 Then pull out 'other'  Postfix data maps via (f.ex) Perl script
 across 
 my 'mail farm'.
 Not sure yet how to do it - but I figure it out.

PostgreSQL is brilliant as you can have views of multiple tables, such
as user id  passwd, then reference another accounts table with foriegn
keys to see if payment is upto date,  how much they paid (disk quota).
Then from this one view, just select * and dump that data into flat
files, then push to your front line smtp, imap, webmail, shell...
 boxes.
No fancy SQL in the scripts, let the DB do the work for you with views
 
stored procedures. (Your business logic is separate from the oily bits
of service implementation)

Implement another service, such as web hosting accounts, then just
 write
another SQL view, and another Perl script to config apache, etc, etc.

My basic point is this: you can go to a lot of bother to get some
services to auth against SQL, then you want to bring up another service
and there is no way of using SQL directly, so you write some scripts to
generate flat files. Then you bring up another service, So why not
just do it that way from the beginning? After all, the app was
 developed
to use flat files, so as a mere user of an app, why fight against the
developer?

 
 How about - using OpenLDAP? 

Same thing. Flat files are fast and reliable, and are basically the
 only
way to give users shell access (mutt/pine) on OpenBSD as login wont
 auth
against LDAP or SQL.

-- 
Craig Skinner | http://www.kepax.co.uk | [EMAIL PROTECTED]

Re: Postfix(chroot) and Postgresql

[johan beisser]

On Dec 25, 2007, at 12:57 PM, badeguruji wrote:

I want to setup postfix and dovecot. i want to authenticate my users  
thru ldap.

for that i have installed openldap server package.
Is there a place where i can find some 'ponited' help on how to  
build such an 'email users' database? i do not want to have unix  
logins for them.


i am searching on google and have not found anything yet. i am  
therefore looking into generic ldap manuals. (i do not want to be a  
ldap guru)


http://wiki.dovecot.org/VirtualUsers
http://wiki.dovecot.org/AuthDatabase/SQL

I think everything you asked about is documented right there.

Re: Postfix(chroot) and Postgresql

[Craig Skinner]

On Sat, Dec 01, 2007 at 12:07:54AM +0100, Bengt Frost wrote:
  
   Someone out there have any suggestions how use Postfix (and Dovecot)
   with PostgreSQL?
 
  Pull the user data from PostgreSQL and generate the files:
  /etc/sasldb2.db (copy to /var/spool/postfix/etc  postfix reload)
  /etc/cram-md5.pwd
 
  e.g: have a cron driven perl script check for changes to the user tables
  in the last 15 mins  if so, then generate new files. Stops PostgreSQL
  becoming a bottleneck when under high load (a spam attack).
 
 Ok. Not quite sure I'm following you. You mean pull user data from 
 PostgreSQL
 and generate flat(db) user file for smtp-auth using 
 p5-Authen-SASL-2.10p0 ...

Aye, using whatever you fancy, probably loads of modules on CPAN that
will do most of what you want for your site. Perl is in base, so you
wont run the risk of a broken port of ruby/python/whatever stopping you
working after an upgrade.

Same for /etc/postfix/{aliases,canonical.map,virtual.map}

 
  As your site grows, you can punt the flat files out across your mail
  farm from your central db/admin box, use rdist or something similar.
 
 Then pull out 'other'  Postfix data maps via (f.ex) Perl script across 
 my 'mail farm'.
 Not sure yet how to do it - but I figure it out.

PostgreSQL is brilliant as you can have views of multiple tables, such
as user id  passwd, then reference another accounts table with foriegn
keys to see if payment is upto date,  how much they paid (disk quota).
Then from this one view, just select * and dump that data into flat
files, then push to your front line smtp, imap, webmail, shell... boxes.
No fancy SQL in the scripts, let the DB do the work for you with views 
stored procedures. (Your business logic is separate from the oily bits
of service implementation)

Implement another service, such as web hosting accounts, then just write
another SQL view, and another Perl script to config apache, etc, etc.

My basic point is this: you can go to a lot of bother to get some
services to auth against SQL, then you want to bring up another service
and there is no way of using SQL directly, so you write some scripts to
generate flat files. Then you bring up another service, So why not
just do it that way from the beginning? After all, the app was developed
to use flat files, so as a mere user of an app, why fight against the
developer?

 
 How about - using OpenLDAP? 

Same thing. Flat files are fast and reliable, and are basically the only
way to give users shell access (mutt/pine) on OpenBSD as login wont auth
against LDAP or SQL.

-- 
Craig Skinner | http://www.kepax.co.uk | [EMAIL PROTECTED]

Re: Postfix(chroot) and Postgresql

[Bengt Frost]

Thanks! I have underestimate the use of flat files and you have give
me useful tips. I have to refresh my perl programming - lately most
C and Python (and sh of course ...)


--bfrost
(fvp.se, fvp.eu, fvpideas.com)

P.S
I am not sure if this gets through to misc mailinglist - sending
from my mailserver.

On Sat, Dec 01, 2007 at 01:12:54PM +, Craig Skinner wrote:
 On Sat, Dec 01, 2007 at 12:07:54AM +0100, Bengt Frost wrote:
   
Someone out there have any suggestions how use Postfix (and Dovecot)
with PostgreSQL?
   ...
  Ok. Not quite sure I'm following you. You mean pull user data from 
  PostgreSQL
  and generate flat(db) user file for smtp-auth using 
  p5-Authen-SASL-2.10p0 ...
 
 Aye, using whatever you fancy, probably loads of modules on CPAN that
 will do most of what you want for your site. Perl is in base, so you
 wont run the risk of a broken port of ruby/python/whatever stopping you
 working after an upgrade.
 
 Same for /etc/postfix/{aliases,canonical.map,virtual.map}
 
  
   ... 
  
  Then pull out 'other'  Postfix data maps via (f.ex) Perl script across 
  my 'mail farm'.
  Not sure yet how to do it - but I figure it out.
 
 PostgreSQL is brilliant as you can have views of multiple tables, such
 as user id  passwd, then reference another accounts table with foriegn
 keys to see if payment is upto date,  how much they paid (disk quota).
 Then from this one view, just select * and dump that data into flat
 files, then push to your front line smtp, imap, webmail, shell... boxes.
 No fancy SQL in the scripts, let the DB do the work for you with views 
 stored procedures. (Your business logic is separate from the oily bits
 of service implementation)
 
 Implement another service, such as web hosting accounts, then just write
 another SQL view, and another Perl script to config apache, etc, etc.
 
 My basic point is this: you can go to a lot of bother to get some
 services to auth against SQL, then you want to bring up another service
 and there is no way of using SQL directly, so you write some scripts to
 generate flat files. Then you bring up another service, So why not
 just do it that way from the beginning? After all, the app was developed
 to use flat files, so as a mere user of an app, why fight against the
 developer?
 
  
  How about - using OpenLDAP? 
 
 Same thing. Flat files are fast and reliable, and are basically the only
 way to give users shell access (mutt/pine) on OpenBSD as login wont auth
 against LDAP or SQL.
 
 -- 
 Craig Skinner | http://www.kepax.co.uk | [EMAIL PROTECTED]

Re: Postfix(chroot) and Postgresql

[Bengt Frost]

Thanks,

Not sure if this mail is showing in correct thread - lost your mail att 
google server.


On Fri, Nov 30, 2007 at 10:15:29PM +, Craig Skinner wrote:
 On Fri, Nov 30, 2007 at 10:33:04PM +0100, Bengt Frost wrote:
 
  Someone out there have any suggestions how use Postfix (and Dovecot)
  with PostgreSQL?

 Pull the user data from PostgreSQL and generate the files:
 /etc/sasldb2.db (copy to /var/spool/postfix/etc  postfix reload)
 /etc/cram-md5.pwd

 e.g: have a cron driven perl script check for changes to the user tables
 in the last 15 mins  if so, then generate new files. Stops PostgreSQL
 becoming a bottleneck when under high load (a spam attack).

Ok. Not quite sure I'm following you. You mean pull user data from 
PostgreSQL
and generate flat(db) user file for smtp-auth using 
p5-Authen-SASL-2.10p0 ...


 As your site grows, you can punt the flat files out across your mail
 farm from your central db/admin box, use rdist or something similar.

Then pull out 'other'  Postfix data maps via (f.ex) Perl script across 
my 'mail farm'.

Not sure yet how to do it - but I figure it out.

How about - using OpenLDAP? 


Thanks

--bfrost

Re: Postfix(chroot) and Postgresql

[Bengt Frost]

Ok,

Efficiency can sometimes be important. Had no idea about this solution - 
have to figure out how to do

it. Thanks!

Is OpenLDAP something to consider.

--bfrost

Genadijus Paleckis wrote:
Instead of that I would recommend you to use DB files generated at 
regular intervals instead of 'online' access to postgresql. It is less 
CPU expensive and much faster.
But if you wish to use SQL maps I guess you may want to use 127.0.0.1 
instead of local socket and of course you need to configure postgresql 
to accept network access.   *Addition* to above: In pg_hba.conf 
(PosgreSQL):

Re: Postfix(chroot) and Postgresql

[Craig Skinner]

On Fri, Nov 30, 2007 at 10:33:04PM +0100, Bengt Frost wrote:
 
 Someone out there have any suggestions how use Postfix (and Dovecot) 
 with PostgreSQL?

Pull the user data from PostgreSQL and generate the files:
/etc/sasldb2.db (copy to /var/spool/postfix/etc  postfix reload)
/etc/cram-md5.pwd

e.g: have a cron driven perl script check for changes to the user tables
in the last 15 mins  if so, then generate new files. Stops PostgreSQL
becoming a bottleneck when under high load (a spam attack).

As your site grows, you can punt the flat files out across your mail
farm from your central db/admin box, use rdist or something similar.

DB down? DB backing up? No probs as mail still goes through until you
are finished.

Probably not the answers you are looking for
-- 
Craig Skinner | http://www.kepax.co.uk | [EMAIL PROTECTED]

Re: Postfix(chroot) and Postgresql

[Bengt Frost]

*Addition* to above: In pg_hba.conf (PosgreSQL):
vmail(user)  access to datab with md5 password
local(and host)

--bfrost

Bengt Frost wrote:

Hi,

I am trying to use PostgreSQL as a backend for my Postfix virtual mail 
system and dovecot(psql) for smtp-auth.
'Postfix' is chrooted - most of it - and with MySQL socket there is no 
problem to auth users and use Postfix
transport_maps and virtual_*_maps. I have problem with postgresql 
socket(.s.PGSQL.5432). Neither
dovecot(auth) or Postfix(processes) can connect to PostgreSQL. I have 
tried to google, read OpenBSD misc and
ports maillinglists with no success. Here is some files with related 
'stuff':


### rc - system
/etc/rc.local:
# Postfix - PostgreSQL
if [ -x /usr/local/bin/pg_ctl ]; then
   echo -n ' postgresql'
   su -l _postgresql -c nohup /usr/local/bin/pg_ctl start \
   -D /var/postgresql/data -l /var/postgresql/logfile \
   -o '-D /var/postgresql/data' /dev/null
   su -l _postgresql -c ln -s /var/spool/postfix/tmp/.s.PGSQL.5432 /tmp
   su -l _postgresql -c ln -s 
/var/spool/postfix/tmp/.s.PGSQL.5432.lock /tmp

fi

/etc/rc.shutdown:
# Posfix - PostgreSQL
if [ -f /var/postgresql/data/postmaster.pid ]; then
su -l _postgresql -c /usr/local/bin/pg_ctl stop -m fast \
   -D /var/postgresql/data
   rm -f /var/postgresql/data/postmaster.pid \
 /var/spool/postfix/tmp/.s.PGSQL.5432 \
 /var/spool/postfix/tmp/.s.PGSQL.5432.lock \
 /tmp/.s.PGSQL.5432 \
 /tmp/.s.PGSQL.5432.lock
fi

### Dovecot:
/etc/dovecot.conf:
 passdb sql {
   args = /etc/dovecot-pgsql.conf
 }
...
userdb sql {
   args = /etc/dovecot-pgsql.conf
... socket listen {
   client {
   path = /var/spool/postfix/private/auth
   mode = 0660
   user = _postfix
   group = _postfix
   }
/etc/dovecot-pgsql:
# Currently supported schemes include PLAIN, PLAIN-MD5, DIGEST-MD5, 
and CRYPT.

default_pass_scheme = CRYPT  # also above schemes

# Database options
# UNIX socket - see host
connect = host=/tmp/.s.PGSQL.5432 dbname= user=vmail password=x

### Postfix - referensed from main.conf(pgsql: - no proxymap used):
/etc/postfix/pgsql_transport:
# UNIX socket - PostgreSQL - relative path(chroot)
hosts = unix:/tmp/.s.PGSQL.5432
# inet: for TCP connections (default)
#hosts = localhost
##hosts = 127.0.0.1

### PostgreSQL
/var/postgresql/postgresql.conf:
unix_socket_directory = '/var/spool/postfix/tmp'
# tmp directory in Postfix root : rwxrwxr-t permission and 'owned' 
by   _postfix  _postgresql


Someone out there have any suggestions how use Postfix (and Dovecot) 
with PostgreSQL?

Thanks!

--bfrost

Postfix(chroot) and Postgresql

[Bengt Frost]

Hi,

I am trying to use PostgreSQL as a backend for my Postfix virtual mail 
system and dovecot(psql) for smtp-auth.
'Postfix' is chrooted - most of it - and with MySQL socket there is no 
problem to auth users and use Postfix
transport_maps and virtual_*_maps. I have problem with postgresql 
socket(.s.PGSQL.5432). Neither
dovecot(auth) or Postfix(processes) can connect to PostgreSQL. I have 
tried to google, read OpenBSD misc and
ports maillinglists with no success. Here is some files with related 
'stuff':


### rc - system
/etc/rc.local:
# Postfix - PostgreSQL
if [ -x /usr/local/bin/pg_ctl ]; then
   echo -n ' postgresql'
   su -l _postgresql -c nohup /usr/local/bin/pg_ctl start \
   -D /var/postgresql/data -l /var/postgresql/logfile \
   -o '-D /var/postgresql/data' /dev/null
   su -l _postgresql -c ln -s /var/spool/postfix/tmp/.s.PGSQL.5432 /tmp
   su -l _postgresql -c ln -s 
/var/spool/postfix/tmp/.s.PGSQL.5432.lock /tmp

fi

/etc/rc.shutdown:
# Posfix - PostgreSQL
if [ -f /var/postgresql/data/postmaster.pid ]; then
su -l _postgresql -c /usr/local/bin/pg_ctl stop -m fast \
   -D /var/postgresql/data
   rm -f /var/postgresql/data/postmaster.pid \
 /var/spool/postfix/tmp/.s.PGSQL.5432 \
 /var/spool/postfix/tmp/.s.PGSQL.5432.lock \
 /tmp/.s.PGSQL.5432 \
 /tmp/.s.PGSQL.5432.lock
fi

### Dovecot:
/etc/dovecot.conf:
 passdb sql {
   args = /etc/dovecot-pgsql.conf
 }
...
userdb sql {
   args = /etc/dovecot-pgsql.conf
... 
socket listen {

   client {
   path = /var/spool/postfix/private/auth
   mode = 0660
   user = _postfix
   group = _postfix
   }
/etc/dovecot-pgsql:
# Currently supported schemes include PLAIN, PLAIN-MD5, DIGEST-MD5, and 
CRYPT.

default_pass_scheme = CRYPT  # also above schemes

# Database options
# UNIX socket - see host
connect = host=/tmp/.s.PGSQL.5432 dbname= user=vmail password=x

### Postfix - referensed from main.conf(pgsql: - no proxymap used):
/etc/postfix/pgsql_transport:
# UNIX socket - PostgreSQL - relative path(chroot)
hosts = unix:/tmp/.s.PGSQL.5432
# inet: for TCP connections (default)
#hosts = localhost
##hosts = 127.0.0.1

### PostgreSQL
/var/postgresql/postgresql.conf:
unix_socket_directory = '/var/spool/postfix/tmp'
# tmp directory in Postfix root : rwxrwxr-t permission and 'owned' by   
_postfix  _postgresql


Someone out there have any suggestions how use Postfix (and Dovecot) 
with PostgreSQL?

Thanks!

--bfrost

Re: Postfix(chroot) and Postgresql

[Genadijus Paleckis]

Instead of that I would recommend you to use DB files generated at 
regular intervals instead of 'online' access to postgresql. It is less 
CPU expensive and much faster.
But if you wish to use SQL maps I guess you may want to use 127.0.0.1 
instead of local socket and of course you need to configure postgresql 
to accept network access.



Bengt Frost wrote:

*Addition* to above: In pg_hba.conf (PosgreSQL):
vmail(user)  access to datab with md5 password
local(and host)

--bfrost

Bengt Frost wrote:

Hi,

I am trying to use PostgreSQL as a backend for my Postfix virtual mail 
system and dovecot(psql) for smtp-auth.
'Postfix' is chrooted - most of it - and with MySQL socket there is no 
problem to auth users and use Postfix
transport_maps and virtual_*_maps. I have problem with postgresql 
socket(.s.PGSQL.5432). Neither
dovecot(auth) or Postfix(processes) can connect to PostgreSQL. I have 
tried to google, read OpenBSD misc and
ports maillinglists with no success. Here is some files with related 
'stuff':


### rc - system
/etc/rc.local:
# Postfix - PostgreSQL
if [ -x /usr/local/bin/pg_ctl ]; then
   echo -n ' postgresql'
   su -l _postgresql -c nohup /usr/local/bin/pg_ctl start \
   -D /var/postgresql/data -l /var/postgresql/logfile \
   -o '-D /var/postgresql/data' /dev/null
   su -l _postgresql -c ln -s /var/spool/postfix/tmp/.s.PGSQL.5432 /tmp
   su -l _postgresql -c ln -s 
/var/spool/postfix/tmp/.s.PGSQL.5432.lock /tmp

fi

/etc/rc.shutdown:
# Posfix - PostgreSQL
if [ -f /var/postgresql/data/postmaster.pid ]; then
su -l _postgresql -c /usr/local/bin/pg_ctl stop -m fast \
   -D /var/postgresql/data
   rm -f /var/postgresql/data/postmaster.pid \
 /var/spool/postfix/tmp/.s.PGSQL.5432 \
 /var/spool/postfix/tmp/.s.PGSQL.5432.lock \
 /tmp/.s.PGSQL.5432 \
 /tmp/.s.PGSQL.5432.lock
fi

### Dovecot:
/etc/dovecot.conf:
 passdb sql {
   args = /etc/dovecot-pgsql.conf
 }
...
userdb sql {
   args = /etc/dovecot-pgsql.conf
... socket listen {
   client {
   path = /var/spool/postfix/private/auth
   mode = 0660
   user = _postfix
   group = _postfix
   }
/etc/dovecot-pgsql:
# Currently supported schemes include PLAIN, PLAIN-MD5, DIGEST-MD5, 
and CRYPT.

default_pass_scheme = CRYPT  # also above schemes

# Database options
# UNIX socket - see host
connect = host=/tmp/.s.PGSQL.5432 dbname= user=vmail password=x

### Postfix - referensed from main.conf(pgsql: - no proxymap used):
/etc/postfix/pgsql_transport:
# UNIX socket - PostgreSQL - relative path(chroot)
hosts = unix:/tmp/.s.PGSQL.5432
# inet: for TCP connections (default)
#hosts = localhost
##hosts = 127.0.0.1

### PostgreSQL
/var/postgresql/postgresql.conf:
unix_socket_directory = '/var/spool/postfix/tmp'
# tmp directory in Postfix root : rwxrwxr-t permission and 'owned' 
by   _postfix  _postgresql


Someone out there have any suggestions how use Postfix (and Dovecot) 
with PostgreSQL?

Thanks!

--bfrost