On Thursday 23 September 2004 8:41 am, Kirti S. Bajwa wrote:
> Simon:
>
> Thank you for your response. Previously, I had seen & read the document you
> mentioned in your response. Acutally, there is great description of setting
> up FrreRADIUS with MySQL as backend in RADIUS by Jonathan Hassell. I have
> read the book and have successfully setup a freeRADIUS server with MySQL as
> backend.
>
> My question is about using dbMail as backend. I guess I need to change some
> select statements in freeRADIUS to accomplish that. I am not sure. I am
> hoping that somebody has done that and they direct me to the right
> direction.
Here is what we have for using dbmail users for freeradius. You will have to
do some work though. We had to add the radius specific tables to the dbmail
database, and add the radius specific columns to the users table.
Dang.. I started to cut and paste this info inline but it is *way* too much.
I will attach our sql.conf for freeradius, and our dbmail sql layout to this
email. Hopefully this list allows attachments.
This is all with dbmail 1.2.9, freeradius 1.0.0 and Mysql 4.0.18 using Innodb.
Also we have a bit of modification to the SQL tables for dbmail... humm
hopefully you can wade through it.
>
> Kirti
>
> -----Original Message-----
> From: Simon Gray [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 23, 2004 5:02 AM
> To: DBMail mailinglist
> Subject: RE: [Dbmail] dbMail with Radius Server
>
> > Hello List:
>
> Howdy,
>
> > I am posting this message both on freeRADIUS & dbMail list. Please
>
> read
>
> > on..
> >
> > Couple of months ago I learned about dbMail from somebody on
>
> freeRADIUS
>
> > list. I was told that freeRADIUS can authenticate users with/from
>
> dbMail
>
> > data. I have lost the email (damn Win NT server crashed) but if
>
> anybody on
>
> > these lists know how freeRADIUS can authenticate users from dbMail,
>
> please
>
> > let me know. I just got dbMail working & am ready to work on
>
> freeRADIUS.
>
> I've used both freeradius and dbmail and at a first glance I'd of though
> sharing the database user information between the 2 applications.
>
> A quick look in google found this: (freeradius mysql howto)
> http://www.frontios.com/freeradius.html
>
> However, you'd need to modify freeradius's configs to use dbmail style
> databases (or modify dbmail to use freeradius style databases).
>
> > Thank you.
> >
> > Kirti
>
> Simon
>
> _______________________________________________
> Dbmail mailing list
> [email protected]
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
> _______________________________________________
> Dbmail mailing list
> [email protected]
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
--
Bret Baptist
Systems and Technical Support Specialist
[EMAIL PROTECTED]
Internet Exposure, Inc.
http://www.iexposure.com
(612)676-1946 x17
Web Development-Web Marketing-ISP Services
------------------------------------------
Today is the tomorrow you worried about yesterday.
# phpMyAdmin SQL Dump
# version 2.5.7-pl1
# http://www.phpmyadmin.net
#
# Host:
# Generation Time: Sep 23, 2004 at 11:22 AM
# Server version: 4.0.18
# PHP Version: 4.1.2
#
# Database : `dbmail`
#
# --------------------------------------------------------
#
# Table structure for table `abook`
#
CREATE TABLE `abook` (
`uid` int(11) NOT NULL default '0',
`alias` varchar(50) NOT NULL default '',
`address` varchar(250) NOT NULL default '',
UNIQUE KEY `abookindx2` (`uid`,`alias`),
KEY `abookindx` (`uid`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `administrator`
#
CREATE TABLE `administrator` (
`administrator_ID` int(11) NOT NULL auto_increment,
`username` varchar(255) NOT NULL default '',
`password` varchar(255) NOT NULL default '',
PRIMARY KEY (`administrator_ID`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `aliases`
#
CREATE TABLE `aliases` (
`alias_idnr` bigint(21) NOT NULL auto_increment,
`alias` varchar(100) NOT NULL default '',
`deliver_to` varchar(250) NOT NULL default '',
`client_idnr` bigint(21) NOT NULL default '0',
`policy_id` int(10) NOT NULL default '1',
`priority` int(10) NOT NULL default '7',
PRIMARY KEY (`alias_idnr`),
UNIQUE KEY `alias_idnr_2` (`alias_idnr`),
KEY `alias` (`alias`),
KEY `idx_alias` (`alias`,`deliver_to`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `client`
#
CREATE TABLE `client` (
`clientidnr` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`domain` varchar(255) NOT NULL default '',
`max_users` int(11) NOT NULL default '0',
`max_aliases` int(11) NOT NULL default '0',
PRIMARY KEY (`clientidnr`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `config`
#
CREATE TABLE `config` (
`configid` int(11) NOT NULL default '0',
`item` varchar(128) NOT NULL default '',
`value` varchar(128) NOT NULL default ''
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `dbsession`
#
CREATE TABLE `dbsession` (
`sessid` varchar(32) NOT NULL default '',
`uid` bigint(21) NOT NULL default '0',
`login` varchar(255) NOT NULL default '',
`clientip` varchar(15) NOT NULL default '',
`sessts` datetime NOT NULL default '0000-00-00 00:00:00',
UNIQUE KEY `sessidx` (`sessid`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `foldercount`
#
CREATE TABLE `foldercount` (
`mailboxidnr` bigint(21) NOT NULL default '0',
`msgcount` int(11) NOT NULL default '0',
`msgnew` int(11) NOT NULL default '0'
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `log`
#
CREATE TABLE `log` (
`log_id` int(11) NOT NULL auto_increment,
`administrator_FK` int(11) NOT NULL default '0',
`action` enum('delete','edit','create','password') NOT NULL default 'delete',
`entity` enum('user','alias','client','domain') NOT NULL default 'user',
`dt` datetime NOT NULL default '0000-00-00 00:00:00',
`name` varchar(255) NOT NULL default '',
`parent` int(11) NOT NULL default '0',
`user_FK` int(11) NOT NULL default '0',
PRIMARY KEY (`log_id`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `loginauth`
#
CREATE TABLE `loginauth` (
`authid` varchar(32) NOT NULL default '',
`username` varchar(32) NOT NULL default '',
`password` varchar(32) NOT NULL default '',
`authts` datetime default NULL,
KEY `authidx` (`authid`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `mailaddr`
#
CREATE TABLE `mailaddr` (
`id` int(10) unsigned NOT NULL auto_increment,
`priority` int(10) NOT NULL default '7',
`email` varchar(255) NOT NULL default '',
`client_idnr` bigint(21) NOT NULL default '0',
`global` int(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `email` (`email`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `mailboxes`
#
CREATE TABLE `mailboxes` (
`mailbox_idnr` bigint(21) NOT NULL auto_increment,
`owner_idnr` bigint(21) NOT NULL default '0',
`name` varchar(100) NOT NULL default '',
`seen_flag` tinyint(1) NOT NULL default '0',
`answered_flag` tinyint(1) NOT NULL default '0',
`deleted_flag` tinyint(1) NOT NULL default '0',
`flagged_flag` tinyint(1) NOT NULL default '0',
`recent_flag` tinyint(1) NOT NULL default '0',
`draft_flag` tinyint(1) NOT NULL default '0',
`no_inferiors` tinyint(1) NOT NULL default '0',
`no_select` tinyint(1) NOT NULL default '0',
`permission` tinyint(1) default '2',
`is_subscribed` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`mailbox_idnr`),
UNIQUE KEY `mailboxidnr_2` (`mailbox_idnr`),
KEY `idx_subscribed` (`owner_idnr`,`is_subscribed`),
KEY `idx_owner_idnr` (`owner_idnr`,`name`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `messageblks`
#
CREATE TABLE `messageblks` (
`messageblk_idnr` bigint(21) NOT NULL auto_increment,
`message_idnr` bigint(21) NOT NULL default '0',
`messageblk` longtext NOT NULL,
`blocksize` bigint(21) NOT NULL default '0',
PRIMARY KEY (`messageblk_idnr`),
UNIQUE KEY `messageblk_idnr_2` (`messageblk_idnr`),
KEY `messageblk_idnr` (`messageblk_idnr`),
KEY `msg_index` (`message_idnr`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `messages`
#
CREATE TABLE `messages` (
`message_idnr` bigint(21) NOT NULL auto_increment,
`mailbox_idnr` bigint(21) NOT NULL default '0',
`messagesize` bigint(21) NOT NULL default '0',
`seen_flag` tinyint(1) NOT NULL default '0',
`answered_flag` tinyint(1) NOT NULL default '0',
`deleted_flag` tinyint(1) NOT NULL default '0',
`flagged_flag` tinyint(1) NOT NULL default '0',
`recent_flag` tinyint(1) NOT NULL default '0',
`draft_flag` tinyint(1) NOT NULL default '0',
`unique_id` varchar(70) NOT NULL default '',
`internal_date` datetime NOT NULL default '0000-00-00 00:00:00',
`status` tinyint(3) unsigned zerofill NOT NULL default '000',
`rfcsize` bigint(21) NOT NULL default '0',
`tpp` tinyint(3) unsigned default NULL,
PRIMARY KEY (`message_idnr`),
KEY `idx_answered_flag` (`mailbox_idnr`,`status`,`unique_id`,`answered_flag`),
KEY `idx_deleted_flag` (`mailbox_idnr`,`status`,`unique_id`,`deleted_flag`),
KEY `idx_flagged_flag` (`mailbox_idnr`,`status`,`unique_id`,`flagged_flag`),
KEY `idx_recent_flag` (`mailbox_idnr`,`status`,`unique_id`,`recent_flag`),
KEY `idx_draft_flag` (`mailbox_idnr`,`status`,`unique_id`,`draft_flag`),
KEY `idx_mailbox_idnr` (`mailbox_idnr`,`status`,`unique_id`,`seen_flag`),
KEY `idx_status` (`status`),
KEY `idx_unique_id` (`unique_id`),
KEY `idx_expunge` (`mailbox_idnr`,`deleted_flag`,`status`),
KEY `idx_flag` (`message_idnr`,`unique_id`,`mailbox_idnr`,`status`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `msgdata`
#
CREATE TABLE `msgdata` (
`uid` bigint(21) NOT NULL default '0',
`msgid` bigint(21) NOT NULL default '0',
`mbid` bigint(21) NOT NULL default '0',
`h_from` varchar(255) NOT NULL default '',
`h_reply` varchar(255) NOT NULL default '',
`h_to` varchar(255) NOT NULL default '',
`h_cc` text NOT NULL,
`h_subject` varchar(255) NOT NULL default '',
`h_date` varchar(255) NOT NULL default '',
`h_size` int(11) NOT NULL default '0',
`s_new` tinyint(4) NOT NULL default '0',
`s_read` tinyint(4) NOT NULL default '0',
`s_unread` tinyint(4) NOT NULL default '0',
`s_replied` tinyint(4) NOT NULL default '0',
`s_forwarded` tinyint(4) NOT NULL default '0',
`s_sent` tinyint(4) NOT NULL default '0',
`s_importance` tinyint(4) NOT NULL default '0',
`s_attachment` tinyint(4) NOT NULL default '0',
`s_deleted` tinyint(4) NOT NULL default '0',
`s_deletedate` datetime default '0000-00-00 00:00:00',
`m_virus` tinyint(4) NOT NULL default '0',
`m_pspam` tinyint(4) NOT NULL default '0',
`m_spam` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`uid`),
UNIQUE KEY `idxmsgdata` (`uid`,`msgid`,`mbid`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `myrelay`
#
CREATE TABLE `myrelay` (
`id` int(7) NOT NULL auto_increment,
`domain` varchar(60) NOT NULL default '',
`comment` varchar(40) default NULL,
`client_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `domain` (`domain`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `mytransport`
#
CREATE TABLE `mytransport` (
`id` int(7) NOT NULL auto_increment,
`domain` varchar(60) NOT NULL default '',
`transport` varchar(40) NOT NULL default 'dbmail:',
`comment` varchar(40) default NULL,
`client_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `domain` (`domain`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `pbsp`
#
CREATE TABLE `pbsp` (
`idnr` bigint(21) NOT NULL auto_increment,
`since` datetime NOT NULL default '0000-00-00 00:00:00',
`ipnumber` varchar(40) NOT NULL default '',
PRIMARY KEY (`idnr`),
UNIQUE KEY `idnr_2` (`idnr`),
UNIQUE KEY `ipnumber_2` (`ipnumber`),
KEY `idnr` (`idnr`),
KEY `idx_since` (`since`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `policy`
#
CREATE TABLE `policy` (
`id` int(10) unsigned NOT NULL auto_increment,
`policy_name` varchar(64) default NULL,
`bypass_virus_checks` char(1) default NULL,
`virus_lover` char(1) default NULL,
`banned_files_lover` char(1) default NULL,
`bypass_spam_checks` char(1) default NULL,
`spam_modifies_subj` char(1) default NULL,
`spam_tag_level` float default NULL,
`spam_tag2_level` float default NULL,
`spam_kill_level` float default NULL,
PRIMARY KEY (`id`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `prefs`
#
CREATE TABLE `prefs` (
`uid` int(11) NOT NULL default '0',
`realname` varchar(70) NOT NULL default 'webdbmail user',
`mpp` tinyint(3) unsigned default '15',
`mrows` tinyint(3) unsigned default '15',
`mcols` tinyint(3) unsigned default '78',
`mecc` tinyint(1) default '0',
`remotelogin` tinyint(1) default '1',
`theme` varchar(16) default 'iexposure',
`usesig` tinyint(1) default '0',
`signature` tinytext,
`repinc` tinyint(1) default '1',
`fullheaders` tinyint(1) default '0',
`foldercount` tinyint(1) default '0',
`incstring` varchar(4) default '>',
`htmlemail` tinyint(1) default '0',
`repincfullheaders` tinyint(1) default '0',
`preloadsig` tinyint(1) default '0',
`compnewwin` tinyint(1) default '0',
`hideemptyfold` tinyint(1) default '0',
`usereplyto` tinyint(1) default '0',
`replyto` varchar(80) default NULL,
`trashdays` tinyint(3) unsigned default '2',
`advprefs` tinyint(1) default '1',
`urlhighlighting` tinyint(1) default '0',
`preserveformatting` tinyint(1) default '0',
`composecc` tinyint(1) default '0',
`composebcc` tinyint(1) default '0',
`mainpage` tinyint(1) default '1',
`httpreferer` tinyint(1) default '0',
`clientaddr` tinyint(1) default '1',
`ssllogin` tinyint(1) default '1',
`revsort` tinyint(1) default '1',
PRIMARY KEY (`uid`),
UNIQUE KEY `prefs_uid` (`uid`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `profile`
#
CREATE TABLE `profile` (
`profileid` bigint(21) NOT NULL auto_increment,
`uid` bigint(21) NOT NULL default '0',
`profilename` varchar(250) NOT NULL default '',
`aliasid` bigint(21) default '0',
`remoteid` tinyint(4) default '0',
`orreply` tinyint(4) NOT NULL default '0',
`replyto` varchar(250) NOT NULL default '',
`orreal` tinyint(4) NOT NULL default '0',
`realname` varchar(50) NOT NULL default 'WeDBmail User',
`orsig` tinyint(4) NOT NULL default '0',
`signature` tinytext,
PRIMARY KEY (`profileid`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `radacct`
#
CREATE TABLE `radacct` (
`RadAcctId` bigint(21) NOT NULL auto_increment,
`AcctSessionId` varchar(32) NOT NULL default '',
`AcctUniqueId` varchar(32) NOT NULL default '',
`UserName` varchar(64) NOT NULL default '',
`Realm` varchar(64) default '',
`NASIPAddress` varchar(15) NOT NULL default '',
`NASPortId` int(12) default NULL,
`NASPortType` varchar(32) default NULL,
`AcctStartTime` datetime NOT NULL default '0000-00-00 00:00:00',
`AcctStopTime` datetime NOT NULL default '0000-00-00 00:00:00',
`AcctSessionTime` int(12) default NULL,
`AcctAuthentic` varchar(32) default NULL,
`ConnectInfo_start` varchar(32) default NULL,
`ConnectInfo_stop` varchar(32) default NULL,
`AcctInputOctets` int(12) default NULL,
`AcctOutputOctets` int(12) default NULL,
`CalledStationId` varchar(10) NOT NULL default '',
`CallingStationId` varchar(10) NOT NULL default '',
`AcctTerminateCause` varchar(32) NOT NULL default '',
`ServiceType` varchar(32) default NULL,
`FramedProtocol` varchar(32) default NULL,
`FramedIPAddress` varchar(15) NOT NULL default '',
`AcctStartDelay` int(12) default NULL,
`AcctStopDelay` int(12) default NULL,
PRIMARY KEY (`RadAcctId`),
KEY `UserName` (`UserName`),
KEY `FramedIPAddress` (`FramedIPAddress`),
KEY `AcctSessionId` (`AcctSessionId`),
KEY `AcctUniqueId` (`AcctUniqueId`),
KEY `AcctStartTime` (`AcctStartTime`),
KEY `AcctStopTime` (`AcctStopTime`),
KEY `NASIPAddress` (`NASIPAddress`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `radgroupcheck`
#
CREATE TABLE `radgroupcheck` (
`id` int(11) unsigned NOT NULL auto_increment,
`GroupName` varchar(64) NOT NULL default '',
`Attribute` varchar(32) NOT NULL default '',
`Value` varchar(253) NOT NULL default '',
`op` char(2) NOT NULL default ':=',
PRIMARY KEY (`id`),
KEY `GroupName` (`GroupName`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `radgroupreply`
#
CREATE TABLE `radgroupreply` (
`id` int(11) unsigned NOT NULL auto_increment,
`GroupName` varchar(64) NOT NULL default '',
`Attribute` varchar(32) NOT NULL default '',
`Value` varchar(253) NOT NULL default '',
`op` char(2) NOT NULL default '=',
`prio` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `GroupName` (`GroupName`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `radpostauth`
#
CREATE TABLE `radpostauth` (
`id` int(11) NOT NULL auto_increment,
`user` varchar(64) NOT NULL default '',
`pass` varchar(64) NOT NULL default '',
`reply` varchar(32) NOT NULL default '',
`date` timestamp(14) NOT NULL,
PRIMARY KEY (`id`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `radreply`
#
CREATE TABLE `radreply` (
`id` int(11) unsigned NOT NULL auto_increment,
`UserName` varchar(64) NOT NULL default '',
`Attribute` varchar(32) NOT NULL default '',
`Value` varchar(253) NOT NULL default '',
`op` char(2) default NULL,
PRIMARY KEY (`id`),
KEY `UserName` (`UserName`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `remotemail`
#
CREATE TABLE `remotemail` (
`uid` int(11) NOT NULL default '0',
`mbid` tinyint(4) NOT NULL default '0',
`mbname` varchar(32) NOT NULL default '',
`user` varchar(100) NOT NULL default '',
`host` varchar(150) NOT NULL default '',
`passwd` varchar(50) NOT NULL default '',
`realname` varchar(50) NOT NULL default 'WeDBmail User',
`keep` tinyint(4) NOT NULL default '1',
`type` tinyint(4) NOT NULL default '0',
`mboxid` bigint(21) default NULL,
UNIQUE KEY `rmt_mb` (`uid`,`mbname`),
UNIQUE KEY `rmt_rmtid` (`uid`,`mbid`),
KEY `rmt_uid` (`uid`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `survey_choice`
#
CREATE TABLE `survey_choice` (
`survey_choice_ID` int(11) NOT NULL auto_increment,
`choice_order` int(11) NOT NULL default '0',
`survey_value` varchar(255) NOT NULL default '',
`survey_question_FK` int(11) NOT NULL default '0',
PRIMARY KEY (`survey_choice_ID`)
) TYPE=MyISAM;
# --------------------------------------------------------
#
# Table structure for table `survey_data`
#
CREATE TABLE `survey_data` (
`survey_data_ID` int(11) NOT NULL auto_increment,
`survey_question_FK` int(11) NOT NULL default '0',
`survey_choice_FK` int(11) NOT NULL default '0',
`survey_choice_other` varchar(255) NOT NULL default '',
`survey_user_FK` int(11) NOT NULL default '0',
`survey_fake` int(1) NOT NULL default '0',
PRIMARY KEY (`survey_data_ID`)
) TYPE=MyISAM;
# --------------------------------------------------------
#
# Table structure for table `survey_question`
#
CREATE TABLE `survey_question` (
`survey_question_ID` int(11) NOT NULL auto_increment,
`survey_question` varchar(255) NOT NULL default '',
`hasOther` int(1) NOT NULL default '0',
PRIMARY KEY (`survey_question_ID`)
) TYPE=MyISAM;
# --------------------------------------------------------
#
# Table structure for table `survey_user`
#
CREATE TABLE `survey_user` (
`survey_user_ID` int(11) NOT NULL auto_increment,
`email` varchar(255) NOT NULL default '',
PRIMARY KEY (`survey_user_ID`)
) TYPE=MyISAM;
# --------------------------------------------------------
#
# Table structure for table `themes`
#
CREATE TABLE `themes` (
`themename` varchar(16) NOT NULL default '',
`themedesc` varchar(50) default NULL
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `tmpmessage`
#
CREATE TABLE `tmpmessage` (
`message_idnr` bigint(21) NOT NULL auto_increment,
`mailbox_idnr` int(21) NOT NULL default '0',
`messagesize` bigint(21) NOT NULL default '0',
`seen_flag` tinyint(1) NOT NULL default '0',
`answered_flag` tinyint(1) NOT NULL default '0',
`deleted_flag` tinyint(1) NOT NULL default '0',
`flagged_flag` tinyint(1) NOT NULL default '0',
`recent_flag` tinyint(1) NOT NULL default '0',
`draft_flag` tinyint(1) NOT NULL default '0',
`unique_id` varchar(70) NOT NULL default '',
`internal_date` datetime NOT NULL default '0000-00-00 00:00:00',
`status` tinyint(3) unsigned zerofill NOT NULL default '000',
PRIMARY KEY (`message_idnr`),
UNIQUE KEY `message_idnr_2` (`message_idnr`),
KEY `message_idnr` (`message_idnr`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `tmpmessageblk`
#
CREATE TABLE `tmpmessageblk` (
`messageblk_idnr` bigint(21) NOT NULL auto_increment,
`message_idnr` bigint(21) NOT NULL default '0',
`messageblk` longtext NOT NULL,
`blocksize` bigint(21) NOT NULL default '0',
PRIMARY KEY (`messageblk_idnr`),
UNIQUE KEY `messageblk_idnr_2` (`messageblk_idnr`),
KEY `messageblk_idnr` (`messageblk_idnr`),
KEY `msg_index` (`message_idnr`),
KEY `idx_message_idnr` (`message_idnr`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `uidl`
#
CREATE TABLE `uidl` (
`uid` int(11) NOT NULL default '0',
`host` varchar(250) NOT NULL default '',
`uidl` varchar(200) NOT NULL default '',
UNIQUE KEY `uidl_uido` (`uid`,`host`,`uidl`),
KEY `uidl_uid` (`uid`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `uidltemp`
#
CREATE TABLE `uidltemp` (
`uid` int(11) NOT NULL default '0',
`host` varchar(250) NOT NULL default '',
`uidl` varchar(70) NOT NULL default '',
UNIQUE KEY `uidltemp_ndx2` (`uid`,`host`,`uidl`),
KEY `uidltemp_ndx` (`uid`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `users`
#
CREATE TABLE `users` (
`user_idnr` bigint(21) NOT NULL auto_increment,
`vacmessage_idnr` bigint(21) NOT NULL default '0',
`userid` varchar(100) NOT NULL default '',
`passwd` varchar(34) NOT NULL default '',
`client_idnr` bigint(21) NOT NULL default '0',
`maxmail_size` bigint(21) NOT NULL default '0',
`encryption_type` varchar(20) NOT NULL default 'crypt',
`last_login` datetime NOT NULL default '1979-11-03 22:05:58',
`Attribute` varchar(32) NOT NULL default 'Crypt-Password',
`op` char(2) NOT NULL default ':=',
`created_on` datetime NOT NULL default '2002-07-26 00:00:00',
`GroupName` varchar(64) NOT NULL default 'reject',
`description` varchar(100) NOT NULL default '',
`administrator` int(1) NOT NULL default '0',
PRIMARY KEY (`user_idnr`),
UNIQUE KEY `userid_2` (`userid`),
UNIQUE KEY `useridnr_2` (`user_idnr`,`userid`),
KEY `idx_userid` (`userid`,`passwd`),
KEY `idx_usergroup` (`userid`,`GroupName`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `vacdate`
#
CREATE TABLE `vacdate` (
`vacdate_id` int(11) NOT NULL auto_increment,
`start` date NOT NULL default '0000-00-00',
`end` date NOT NULL default '0000-00-00',
`alias_idnr` int(11) NOT NULL default '0',
`at_id` varchar(255) NOT NULL default '',
PRIMARY KEY (`vacdate_id`),
UNIQUE KEY `alias_idnr` (`alias_idnr`)
) TYPE=MyISAM;
# --------------------------------------------------------
#
# Table structure for table `vacmessages`
#
CREATE TABLE `vacmessages` (
`vacmessage_idnr` bigint(21) NOT NULL auto_increment,
`from_addr` varchar(100) NOT NULL default '',
`subject` varchar(200) NOT NULL default '',
`body` longtext NOT NULL,
PRIMARY KEY (`vacmessage_idnr`)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `wblist`
#
CREATE TABLE `wblist` (
`rid` int(10) unsigned NOT NULL default '0',
`sid` int(10) unsigned NOT NULL default '0',
`wb` char(1) NOT NULL default '',
PRIMARY KEY (`rid`,`sid`)
) TYPE=InnoDB;
#
# Constraints for dumped tables
#
#
# Constraints for table `mailboxes`
#
ALTER TABLE `mailboxes`
ADD CONSTRAINT `0_56` FOREIGN KEY (`owner_idnr`) REFERENCES `users` (`user_idnr`) ON DELETE CASCADE;
#
# Constraints for table `messageblks`
#
ALTER TABLE `messageblks`
ADD CONSTRAINT `0_81` FOREIGN KEY (`message_idnr`) REFERENCES `messages` (`message_idnr`) ON DELETE CASCADE;
#
# Constraints for table `messages`
#
ALTER TABLE `messages`
ADD CONSTRAINT `0_62` FOREIGN KEY (`mailbox_idnr`) REFERENCES `mailboxes` (`mailbox_idnr`) ON DELETE CASCADE;
#
# Configuration for the SQL module, when using MySQL.
#
# The database schema is available at:
#
# src/radiusd/src/modules/rlm_sql/drivers/rlm_sql_mysql/db_mysql.sql
#
# If you are using PostgreSQL, please use 'postgresql.conf', instead.
# If you are using Oracle, please use 'oracle.conf', instead.
# If you are using MS-SQL, please use 'mssql.conf', instead.
#
# $Id: sql.conf,v 1.29 2003/09/23 04:18:03 phampson Exp $
#
sql {
# Database type
# Current supported are: rlm_sql_mysql, rlm_sql_postgresql,
# rlm_sql_iodbc, rlm_sql_oracle, rlm_sql_unixodbc, rlm_sql_freetds
driver = "rlm_sql_mysql"
# Connect info
server = "sql-server"
login = "dbmail-login"
password = "yoursecretpass"
# Database table configuration
radius_db = "dbmail"
# If you want both stop and start records logged to the
# same SQL table, leave this as is. If you want them in
# different tables, put the start table in acct_table1
# and stop table in acct_table2
acct_table1 = "radacct"
acct_table2 = "radacct"
# Allow for storing data after authentication
postauth_table = "radpostauth"
authcheck_table = "users"
authreply_table = "radreply"
groupcheck_table = "radgroupcheck"
groupreply_table = "radgroupreply"
usergroup_table = "users"
# Remove stale session if checkrad does not see a double login
deletestalesessions = yes
# Print all SQL statements when in debug mode (-x)
sqltrace = no
sqltracefile = ${logdir}/sqltrace.sql
# number of sql connections to make to server
num_sql_socks = 5
# number of seconds to dely retrying on a failed database
# connection (per_socket)
connect_failure_retry_delay = 60
#######################################################################
# Query config: Username
#######################################################################
# This is the username that will get substituted, escaped, and added
# as attribute 'SQL-User-Name'. '%{SQL-User-Name}' should be used
below
# everywhere a username substitution is needed so you you can be sure
# the username passed from the client is escaped properly.
#
# Uncomment the next line, if you want the sql_user_name to mean:
#
# Use Stripped-User-Name, if it's there.
# Else use User-Name, if it's there,
# Else use hard-coded string "DEFAULT" as the user name.
#sql_user_name = "%{Stripped-User-Name:-%{User-Name:-DEFAULT}}"
#
sql_user_name = "%{User-Name}"
#######################################################################
# Default profile
#######################################################################
# This is the default profile. It is found in SQL by group membership.
# That means that this profile must be a member of at least one group
# which will contain the corresponding check and reply items.
# This profile will be queried in the authorize section for every user.
# The point is to assign all users a default profile without having to
# manually add each one to a group that will contain the profile.
# The SQL module will also honor the User-Profile attribute. This
# attribute can be set anywhere in the authorize section (ie the users
# file). It is found exactly as the default profile is found.
# If it is set then it will *overwrite* the default profile setting.
# The idea is to select profiles based on checks on the incoming
packets,
# not on user group membership. For example:
# -- users file --
# DEFAULT Service-Type == Outbound-User, User-Profile :=
"outbound"
# DEFAULT Service-Type == Framed-User, User-Profile := "framed"
#
# By default the default_user_profile is not set
#
#default_user_profile = "DEFAULT"
#
# Determines if we will query the default_user_profile or the
User-Profile
# if the user is not found. If the profile is found then we consider
the user
# found. By default this is set to 'no'.
#
#query_on_not_found = no
#######################################################################
# Authorization Queries
#######################################################################
# These queries compare the check items for the user
# in ${authcheck_table} and setup the reply items in
# ${authreply_table}. You can use any query/tables
# you want, but the return data for each row MUST
# be in the following order:
#
# 0. Row ID (currently unused)
# 1. UserName/GroupName
# 2. Item Attr Name
# 3. Item Attr Value
# 4. Item Attr Operation
#######################################################################
# Use these for case sensitive usernames. WARNING: Slower queries!
# authorize_check_query = "SELECT id,UserName,Attribute,Value,op FROM
${authcheck_table} WHERE Username = '%{SQL-User-Name}' ORDER BY id"
authorize_check_query = "SELECT user_idnr,userid,Attribute,passwd,op
FROM ${authcheck_table} WHERE userid = '%{SQL-User-Name}' ORDER BY user_idnr"
authorize_reply_query = "SELECT id,UserName,Attribute,Value,op FROM
${authreply_table} WHERE Username = '%{SQL-User-Name}' ORDER BY id"
# Use these for case sensitive usernames. WANRING: Slower queries!
# authorize_group_check_query = "SELECT
${groupcheck_table}.id,${groupcheck_table}.GroupName,${groupcheck_table}.Attribute,${groupcheck_table}.Value,${groupcheck_table}.op
FROM ${groupcheck_table},${usergroup_table} WHERE ${usergroup_table}.Username
= '%{SQL-User-Name}' AND ${usergroup_table}.GroupName =
${groupcheck_table}.GroupName ORDER BY ${groupcheck_table}.id"
authorize_group_check_query = "SELECT
${groupcheck_table}.id,${groupcheck_table}.GroupName,${groupcheck_table}.Attribute,${groupcheck_table}.Value,${groupcheck_table}.op
FROM ${groupcheck_table},${usergroup_table} WHERE ${usergroup_table}.userid =
'%{SQL-User-Name}' AND ${usergroup_table}.GroupName =
${groupcheck_table}.GroupName ORDER BY ${groupcheck_table}.id"
# authorize_group_reply_query = "SELECT
${groupreply_table}.id,${groupreply_table}.GroupName,${groupreply_table}.Attribute,${groupreply_table}.Value,${groupreply_table}.op
FROM ${groupreply_table},${usergroup_table} WHERE ${usergroup_table}.Username
= '%{SQL-User-Name}' AND ${usergroup_table}.GroupName =
${groupreply_table}.GroupName ORDER BY ${groupreply_table}.id"
authorize_group_reply_query = "SELECT
${groupreply_table}.id,${groupreply_table}.GroupName,${groupreply_table}.Attribute,${groupreply_table}.Value,${groupreply_table}.op
FROM ${groupreply_table},${usergroup_table} WHERE ${usergroup_table}.userid =
'%{SQL-User-Name}' AND ${usergroup_table}.GroupName =
${groupreply_table}.GroupName ORDER BY ${groupreply_table}.id"
#######################################################################
# Accounting Queries
#######################################################################
# accounting_onoff_query - query for Accounting On/Off packets
# accounting_update_query - query for Accounting update packets
# accounting_start_query - query for Accounting start packets
# accounting_start_query_alt - query for Accounting start packets
# (alternate in case first query fails)
# accounting_stop_query - query for Accounting stop packets
# accounting_stop_query_alt - query for Accounting start packets
# (alternate in case first query doesn't
# affect any existing rows in the table)
#######################################################################
accounting_onoff_query = "UPDATE ${acct_table1} SET AcctStopTime='%S',
AcctSessionTime=unix_timestamp('%S') - unix_timestamp(AcctStartTime),
AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay =
%{Acct-Delay-Time} WHERE AcctSessionTime=0 AND AcctStopTime=0 AND NASIPAddress=
'%{NAS-IP-Address}' AND AcctStartTime <= '%S'"
accounting_update_query = "UPDATE ${acct_table1} SET FramedIPAddress =
'%{Framed-IP-Address}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName
= '%{SQL-User-Name}' AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStopTime = 0"
accounting_start_query = "INSERT into ${acct_table1} (RadAcctId,
AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId,
NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic,
ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets,
CalledStationId, CallingStationId, AcctTerminateCause, ServiceType,
FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('',
'%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}',
'%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', '%S', '0',
'0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0',
'%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}',
'%{Framed-Protocol}', '%{Framed-IP-Address}', '%{Acct-Delay-Time}', '0')"
accounting_start_query_alt = "UPDATE ${acct_table1} SET AcctStartTime
= '%S', AcctStartDelay = '%{Acct-Delay-Time}', ConnectInfo_start =
'%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName =
'%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime = 0"
accounting_stop_query = "UPDATE ${acct_table2} SET AcctStopTime = '%S',
AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets =
'%{Acct-Input-Octets}', AcctOutputOctets = '%{Acct-Output-Octets}',
AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay =
'%{Acct-Delay-Time}', ConnectInfo_stop = '%{Connect-Info}' WHERE AcctSessionId
= '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress =
'%{NAS-IP-Address}' AND AcctStopTime = 0"
accounting_stop_query_alt = "INSERT into ${acct_table2} (RadAcctId,
AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId,
NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic,
ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets,
CalledStationId, CallingStationId, AcctTerminateCause, ServiceType,
FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('',
'%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}',
'%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}',
DATE_SUB('%S',INTERVAL (%{Acct-Session-Time:-0} + %{Acct-Delay-Time:-0})
SECOND), '%S', '%{Acct-Session-Time}', '%{Acct-Authentic}', '',
'%{Connect-Info}', '%{Acct-Input-Octets}', '%{Acct-Output-Octets}',
'%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Acct-Terminate-Cause}',
'%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '0',
'%{Acct-Delay-Time}')"
#######################################################################
# Simultaneous Use Checking Queries
#######################################################################
# simul_count_query - query for the number of current connections
# - If this is not defined, no simultaneouls use
checking
# - will be performed by this module instance
# simul_verify_query - query to return details of current
connections for verification
# - Leave blank or commented out to disable
verification step
# - Note that the returned field order should not
be changed.
#######################################################################
# Uncomment simul_count_query to enable simultaneous use checking
simul_count_query = "SELECT COUNT(*) FROM ${acct_table1} WHERE
UserName='%{SQL-User-Name}' AND AcctStopTime = 0"
simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName,
NASIPAddress, NASPortId, FramedIPAddress, CallingStationId, FramedProtocol FROM
${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctStopTime = 0"
#######################################################################
# Group Membership Queries
#######################################################################
# group_membership_query - Check user group membership
#######################################################################
group_membership_query = "SELECT GroupName FROM ${usergroup_table}
WHERE UserName='%{SQL-User-Name}'"
#######################################################################
# Authentication Logging Queries
#######################################################################
# postauth_query - Insert some info after authentication
#######################################################################
postauth_query = "INSERT into ${postauth_table} (id, user, pass, reply,
date) values ('', '%{User-Name}', '%{User-Password}', '%{reply:Packet-Type}',
NOW())"
}
