Thanks to Sachu, I now have Courier's pgsql auth working... and I'm
feeling ambitious!
I'm using Courier 0.37.3.20020309 on an up-to-date RedHat Linux 7.2
with SGI XFS kernel (from CVS) on software RAID1 and PostgreSQL 7.1.3.
I'm about to upgrade to the RPMS from:
ftp://ftp3.ca.postgresql.org/pub/binary/v7.2/RPMS/redhat-7.2/
I've configured Courier with an aliases file that looks like this for
each domain:
@foo.com: x_foo.com
@bar.com: x_bar.com
It would be nice if the aliases could also be pulled from PostgreSQL,
but I suppose I can just update the DBM file directly too :)
Anyways, each domain has a directory /usr/vmail/foo.com/ full of
.courier-user files for each user @ that domain (and subdirectories
with each user's maildir, and symlinks in a few places where users'
mailboxes exist in multiple domains; FWIW, I'm only doing POP3). Each
user also needs to be authenticated, but I wanted to keep the domains
and users in separate tables. Also, all of the files are owned by the
same uid and gid, so there's no point in actually storing them in the
database.
So, here's my first-ever VIEW in SQL:
DROP TABLE Users;
CREATE TABLE Users (
id varchar(16) PRIMARY KEY,
crypt varchar(16) DEFAULT '*' NOT NULL,
dom_id integer NOT NULL,
name varchar(96) DEFAULT '',
quota varchar(32) DEFAULT '10485760S,1024C' NOT NULL
);
DROP TABLE Domains;
CREATE TABLE Domains (
dom_id integer PRIMARY KEY,
domain varchar(64) NOT NULL
);
DROP SEQUENCE dom_seq;
CREATE SEQUENCE dom_seq START 1;
DROP VIEW Passwd;
CREATE VIEW Passwd AS
SELECT
u.id AS id,
u.crypt AS crypt,
402 AS uid,
402 AS gid,
'/usr/vmail/' || d.domain || '/' || u.id AS home,
'/usr/vmail/' || d.domain || '/' || u.id AS maildir,
u.quota AS quota
FROM Users u, Domains d
WHERE u.dom_id = d.dom_id
UNION
SELECT
'x_' || domain AS id,
'*' AS crypt,
402 AS uid,
402 AS gid,
'/usr/vmail/' || domain AS home,
'/usr/vmail/' || domain AS maildir,
'104857600S,10240C' AS quota
FROM Domains;
These pages may help with your interpretation of the above:
(they certainly helped me figure out how to do it!)
http://www.postgresql.org/idocs/?sql-select.html
http://www.postgresql.org/idocs/?sql-createview.html
Sample result: (sorry for the long lines :) )
courier=> select * from passwd;
id | crypt | uid | gid | home | maildir |
quota
-----------+-------+-----+-----+------------------------+------------------------+-------------------
foo | * | 402 | 402 | /usr/vmail/foo.com/foo | /usr/vmail/foo.com/foo |
1048576S,1024C
x_foo.com | * | 402 | 402 | /usr/vmail/foo.com | /usr/vmail/foo.com |
104857600S,10240C
Any comments or questions -- and especially caveats -- would be much
appreciated.
Cheers,
Tom
--
-- Tom Rathborne [EMAIL PROTECTED] http://www.aceldama.com/~tomr/
Most people can't understand how others can
blow their noses differently than they do. -- Turgenev
_______________________________________________
courier-users mailing list
[EMAIL PROTECTED]
Unsubscribe: https://lists.sourceforge.net/lists/listinfo/courier-users
