Many thanks to Adam Sherman for suggesting what would have been obvious to a more seasoned PostgreSQL user: CREATE VIEW solved the problem. In order to "see" what courier-imap was looking for from the database, I was obliged to sniff the connection between the mailserver and the sql server. This yielded the actual query being sent to the database, and I was able to create the view of the table that courier-imap wanted with no further ado. The sql syntax for concatentating two fields with an escaped character between them in the output is very difficult to grok from the PostgreSQL docs (http://www.postgresql.org/idocs) for this particular query. It is:
CREATE VIEW courierauth as SELECT mailusers.id, mailusers."password", mailusers.uid, mailusers.gid, mailusers.home, ((mailusers.username || '@'::"varchar") || mailusers.domainname) AS username FROM mailusers; After this query is run, the view can be queried as if it were a table, i.e. "SELECT * FROM courierauth;". The only thing left to do is make the appropriate adjustments in ~/etc/authpgsqlrc and restart imapd, or at a minimum restart the authdaemon. A suggestion to the developers: develop a way in the code to customize queries, and document the implementation of a customized query. My initial instinct was to leave the database alone and hack the C code to create a customized query; I thank Mr. Sherman for talking me out of it. Keep up the good work! Original question follows: > > -----Original Message----- > From: Bruce Cannon > Sent: Monday, March 18, 2002 3:34 PM > To: '[EMAIL PROTECTED]' > Subject: authpgsql for multiple domains > > Current configuration: working qmail Maildir installation > with courier-imap-1.4.3. Home directories for users under > /home/mail/<domainname>/<username>. I host multiple domains. > > PostgreSQL mailusers table with fields: id (unique per user), > uid (same for everyone), gid (same for everyone), username > (i.e. 'ralph'), domainname (i.e. 'dilligaf.com'), home (i.e. > /home/mail/dilligaf.com/ralph), and password. > > I need authpgsql or a suitable module to query the database > as it exists today for the 'username'@'domainname', perhaps: > > "SELECT id, password, uid, gid, home, username FROM mailusers > WHERE username = 'ralph' and domainname = 'dilligaf.com'; > > for authentication. I could find no obvious way to do this. > The current query is sent to the sql server as: > > "SELECT id, '', password, uid, gid, home, '', '', username > FROM mailusers WHERE id = '[EMAIL PROTECTED]' > > This obviously won't work because of the differing databases schema. > > I read under ~/authlib/README.authmysql.myownquery and was > curious as to how to apply the patch mentioned there as it > may be close to what I'm looking for. I obviously need to > alter the query that courier-imap is passing to the pgsql > server for authentication. Changing the database schema is > not an option. > > If anyone can point me in the right direction, I'd greatly > appreciate the help. It has been a frustrating day ;-) > > Thanks in advance, > > Bruce Cannon > http://www.cimtel.net > _______________________________________________ courier-users mailing list [EMAIL PROTECTED] Unsubscribe: https://lists.sourceforge.net/lists/listinfo/courier-users
