[exim] Seeking clarification about Oracle DB lookups

Αρχική Σελίδα
Delete this message
Reply to this message
Συντάκτης: Odhiambo G. Washington
Ημερομηνία:  
Προς: exim-users
Αντικείμενο: [exim] Seeking clarification about Oracle DB lookups

Gentlemen,


I would like to seek clarification and as well let this mail go into
the archives as a simple case of handling mail for one domain with
Oracle. It's very simplistic, but it will work well in a situation
where you run just one domain.
SMTP authentication, which I consider important, has not been
integrated, but that is probably the next step in this game.

Of course we use Oracle for our RADIUS, so if I could get info
on how to configure Exim to use RADIUS on OS X, I would have been alot
happier. Maybe!!


Here we go ....


The endeavour is to configure Exim to do lookups from Oracle database.


I have two major issues to raise:


(1) Configuration and delivery problems
(2) Starting Exim daemon to support Oracle lookups.


Our setup is as follows:

Exim - 4.43
Oracle - 10g
OS X Panther


(1) Configuration:

The global database name = main.wananchi.com
In that case then,

tns name     = main
service name    = main
user        = USERNAME
password    = PASSWORD



So I have in my Exim configure file:

hide oracle_servers = main/main/USERNAME/PASSWORD

# Lookup users from the Oracle DB:

oracle_user_router:
driver = accept
condition = \
${if eq {} \
{${lookup oracle {select userid from users \
where userid='${local_part}'}}} \
{no}{yes}}
retry_use_local_part
transport = oracle_delivery


# Oracle delivery transport

oracle_delivery:
  driver     = appendfile
  directory  = /var/mail/${local_part}/Maildir/
  user       = mailnull
  group      = mailnull



The spec.txt says:
<cut>
In the case of Oracle, the host name field is used for the 'service name',
and the database name field is not used and should be empty. For example:

hide oracle_servers = oracle.plc.example//ph10/abcdwxyz

</cut>

Now that seems incorrect, as far as I can see. Of course I could be damn
wrong since I am not any familiar with Oracle either.


TEST 1::

When I use the option

hide oracle_servers = main/main/USERNAME/PASSWORD
..then a delivery test (-bt) appears to work. Please note that this
_does_not_ agree with spec.txt!!


SERVER:/System/Library/StartupItems/Exim root#e /usr/local/sbin/exim -d -bt wash@??? 2>./debug2.txt
wash@???
router = oracle_user, transport = oracle_delivery


Here is the debug output:

Exim version 4.43 uid=0 gid=0 pid=853 D=fbb95cfd
Probably Berkeley DB version 1.8x (native mode)
Support for: Perl OpenSSL
Lookups: lsearch wildlsearch nwildlsearch iplsearch cdb dbm dbmnz dnsdb dsearch oracle
Authenticators: cram_md5 plaintext spa
Routers: accept dnslookup ipliteral iplookup manualroute queryprogram redirect
Transports: appendfile/maildir/mailstore autoreply pipe smtp
Fixed never_users: 0
changed uid/gid: forcing real = effective
uid=0 gid=0 pid=853
auxiliary group list: 0
configuration file is /usr/local/etc/exim/configure
log selectors = 00000ffc 00010400
trusted user
admin user
finduser used cached passwd data for mailnull
finduser used cached passwd data for mailnull
finduser used cached passwd data for mailnull
originator: uid=0 gid=0 login=root name=System Administrator
sender address = root@???
Address testing: uid=0 gid=42 euid=0 egid=42
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Testing wash@???
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Considering wash@???
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

routing wash@???
--------> dnslookup router <--------
local_part=wash domain=wananchi.co.ke
checking domains
wananchi.co.ke in "@ : wananchi.co.ke"? yes (matched "wananchi.co.ke")
wananchi.co.ke in "! +local_domains"? no (matched "! +local_domains")
dnslookup router skipped: domains mismatch
--------> system_aliases router <--------
local_part=wash domain=wananchi.co.ke
calling system_aliases router
rda_interpret (string): ${lookup{$local_part}lsearch{/etc/mail/aliases}}
search_open: lsearch "/etc/mail/aliases"
search_find: file="/etc/mail/aliases"
key="wash" partial=-1 affix=NULL starflags=0
LRU list:
:/etc/mail/aliases
End
internal_search_find: file="/etc/mail/aliases"
type=lsearch key="wash"
file lookup required for wash
in /etc/mail/aliases
lookup failed
expanded:
file is not a filter file
parse_forward_list:
system_aliases router declined for wash@???
--------> userforward router <--------
local_part=wash domain=wananchi.co.ke
checking for local user
calling userforward router
rda_interpret (file): $home/.forward
expanded: /Users/wash/.forward
stat(/Users/wash/.)=0
/Users/wash/.forward does not exist
userforward router declined for wash@???
--------> real_localuser router <--------
local_part=wash domain=wananchi.co.ke
real_localuser router skipped: prefix mismatch
--------> oracle_user router <--------
local_part=wash domain=wananchi.co.ke
checking "condition"
search_open: oracle "NULL"
search_find: file="NULL"
key="select userid from users where userid='wash'" partial=-1 affix=NULL starflags=0
LRU list:
:/etc/mail/aliases
End
internal_search_find: file="NULL"
type=oracle key="select userid from users where userid='wash'"
database lookup required for select userid from users where userid='wash'
ORACLE query: select userid from users where userid='wash'
ORACLE new connection: host=main database=main user=USERNAME
lookup yielded: wash
calling oracle_user router
oracle_user router called for wash@???
domain = wananchi.co.ke
set transport oracle_delivery
queued for oracle_delivery transport: local_part = wash
domain = wananchi.co.ke
errors_to=NULL
domain_data=NULL localpart_data=NULL
routed by oracle_user router
envelope to: wash@???
transport: oracle_delivery
search_tidyup called
close ORACLE connection: main/main/USERNAME
>>>>>>>>>>>>>>>> Exim pid=853 terminating with rc=0 >>>>>>>>>>>>>>>>





TEST1::


But when I use:

hide oracle_servers = main//USER/PASSWORD

..which conforms to what spec.txt says, then the test fails.

SERVER:/System/Library/StartupItems/Exim root# /usr/local/sbin/exim -d -bt wash@??? 2>./debug1.txt
wash@??? cannot be resolved at this time:
condition check lookup defer


Here is the debug output:

Exim version 4.43 uid=0 gid=0 pid=830 D=fbb95cfd
Probably Berkeley DB version 1.8x (native mode)
Support for: Perl OpenSSL
Lookups: lsearch wildlsearch nwildlsearch iplsearch cdb dbm dbmnz dnsdb dsearch oracle
Authenticators: cram_md5 plaintext spa
Routers: accept dnslookup ipliteral iplookup manualroute queryprogram redirect
Transports: appendfile/maildir/mailstore autoreply pipe smtp
Fixed never_users: 0
changed uid/gid: forcing real = effective
uid=0 gid=0 pid=830
auxiliary group list: 0
configuration file is /usr/local/etc/exim/configure
log selectors = 00000ffc 00010400
trusted user
admin user
finduser used cached passwd data for mailnull
finduser used cached passwd data for mailnull
finduser used cached passwd data for mailnull
originator: uid=0 gid=0 login=root name=System Administrator
sender address = root@???
Address testing: uid=0 gid=42 euid=0 egid=42
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Testing wash@???
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Considering wash@???
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

routing wash@???
--------> dnslookup router <--------
local_part=wash domain=wananchi.co.ke
checking domains
wananchi.co.ke in "@ : wananchi.co.ke"? yes (matched "wananchi.co.ke")
wananchi.co.ke in "! +local_domains"? no (matched "! +local_domains")
dnslookup router skipped: domains mismatch
--------> system_aliases router <--------
local_part=wash domain=wananchi.co.ke
calling system_aliases router
rda_interpret (string): ${lookup{$local_part}lsearch{/etc/mail/aliases}}
search_open: lsearch "/etc/mail/aliases"
search_find: file="/etc/mail/aliases"
key="wash" partial=-1 affix=NULL starflags=0
LRU list:
:/etc/mail/aliases
End
internal_search_find: file="/etc/mail/aliases"
type=lsearch key="wash"
file lookup required for wash
in /etc/mail/aliases
lookup failed
expanded:
file is not a filter file
parse_forward_list:
system_aliases router declined for wash@???
--------> userforward router <--------
local_part=wash domain=wananchi.co.ke
checking for local user
calling userforward router
rda_interpret (file): $home/.forward
expanded: /Users/wash/.forward
stat(/Users/wash/.)=0
/Users/wash/.forward does not exist
userforward router declined for wash@???
--------> real_localuser router <--------
local_part=wash domain=wananchi.co.ke
real_localuser router skipped: prefix mismatch
--------> oracle_user router <--------
local_part=wash domain=wananchi.co.ke
checking "condition"
search_open: oracle "NULL"
search_find: file="NULL"
key="select userid from users where userid='wash'" partial=-1 affix=NULL starflags=0
LRU list:
:/etc/mail/aliases
End
internal_search_find: file="NULL"
type=oracle key="select userid from users where userid='wash'"
database lookup required for select userid from users where userid='wash'
ORACLE query: select userid from users where userid='wash'
ORACLE new connection: host=main database=NULL user=USERNAME
ORACLE connection failed: ORA-01017: invalid username/password; logon denied

lookup deferred: ORACLE connection failed: ORA-01017: invalid username/password; logon denied

condition check lookup defer
search_tidyup called
>>>>>>>>>>>>>>>> Exim pid=830 terminating with rc=1 >>>>>>>>>>>>>>>>



My final question is: Who between me and spec.txt (no offense Philip)
is right? I believe the Exim bible (spec.txt) should always be right
when I am in conflict with it ;)



(2) Starting Exim.

This is another very confusing part. If I start the daemon as I would
normally, both tests above fail. It therefore appears that I must
initialize the Oracle environment as well for Exim to be able to do the
lookups. I would like to confirm that this is actually a requirement.

My Exim startup script looks like this:
Please note the line marked with <=====

And for the archives, please note that initializarion of the oracle ENV
must be done before calling /etc/rc.common.

That is how it ended up working for me.




#!/bin/sh

# Joel Rennich, www.afp548.com, Sept. 2002
# all copyrights waived


. /users/oracle/.profile <========== Without this line both lookups fail
. /etc/rc.common

# Script to control Exim

# this is to start it

if [ "$1" == "start" ]
then
     ConsoleMessage "Starting Exim SMTP service"
     /usr/local/sbin/exim -bd -q30m
     /usr/bin/spamd -i0 -d -x -F0


# this is to stop it

elif [ "$1" == "stop" ]
then
     ConsoleMessage "Stopping Exim"
     kill `cat /var/run/exim.pid`
     killall -9 spamd
# this is to restart it


elif [ "$1" == "restart" ]
then
     ConsoleMessage "Restarting Exim"
     kill -HUP `cat /var/run/exim.pid`
fi



Inasmuch as there seems to be no people running Exim+Oracle, I still
would like to get ideas why I have to initialize the Oracle environment
despite having given exim the correct params in the configuration.






        cheers
       - wash 
+----------------------------------+-----------------------------------------+
Odhiambo Washington                     . WANANCHI ONLINE LTD (Nairobi, KE)  |
<wash at wananchi dot com>              . 1ere Etage, Loita Hse, Loita St.,  |
GSM: (+254) 722 743 223                 . # 10286, 00100 NAIROBI             |
GSM: (+254) 733 744 121                 . (+254) 020 313 985 - 9             |
+---------------------------------+------------------------------------------+
"Oh My God! They killed init! You Bastards!"  
                         --from a /. post