Topic: Global Address book
==== REQUIRED BASIC INFO OF YOUR IREDMAIL SERVER ====
- iRedMail version (check /etc/iredmail-release): 1.3.2
- Deployed with iRedMail Easy or the downloadable installer? downloable
- Linux/BSD distribution name and version: Ubuntu 20.04 LTS
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): Mysql
- Web server (Apache or Nginx): Nginx
- Manage mail accounts with iRedAdmin-Pro? NO
====
I have just implemented iRedMail as a ou mailserver and it is still in POC, but functioning as required.
DMAC, SF and DKIM are all now passing and have configured the disclaimer as well.
My installation is a iRedmail with MYSQL.
The only two items that are missing an a prober domain address book and a global address book.
They hold the telephone numbers address and notes.
After a lot of searching I have managed to create these they they show up correctly.
Database table
I had to do was create a new table ( under the sogo db, if you want you can create a new db and then the table, just make sure that you give the the following users:
vmail
vmailadmin
sogo
read/write access to your DB), and a couple of views and triggers.
The in this case the table name is "contacts" and the table structure is below along with the two triggers:
-- phpMyAdmin SQL Dump
-- version 4.9.5deb2
-- [url]https://www.phpmyadmin.net/[/url]
--
-- Host: localhost:3306
-- Generation Time: Dec 13, 2020 at 05:25 AM
-- Server version: 10.3.25-MariaDB-0ubuntu0.20.04.1
-- PHP Version: 7.4.3
SET FOREIGN_KEY_CHECKS=0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `sogo`
--
-- --------------------------------------------------------
--
-- Table structure for table `contacts`
--
DROP TABLE IF EXISTS `contacts`;
CREATE TABLE IF NOT EXISTS `contacts` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`c_uid` text NOT NULL,
`c_name` text DEFAULT NULL,
`c_cn` text DEFAULT NULL,
`nsaimid` text DEFAULT NULL,
`mail` varchar(128) NOT NULL,
`mozillasecondemail` text DEFAULT NULL,
`givenName` text NOT NULL,
`middleName` text DEFAULT NULL,
`sn` text NOT NULL,
`employeeID` varchar(255) DEFAULT NULL,
`telephoneNumber` varchar(25) DEFAULT NULL,
`homephone` varchar(25) DEFAULT NULL,
`mobile` varchar(25) DEFAULT NULL,
`facsimiletelephonenumber` varchar(25) DEFAULT NULL,
`pager` varchar(25) DEFAULT NULL,
`room` varchar(25) DEFAULT NULL,
`mozillahomestreet` text DEFAULT NULL,
`mozillahomestreet2` text DEFAULT NULL,
`mozillahomelocalityname` text DEFAULT NULL,
`mozillahomestate` text DEFAULT NULL,
`mozillahomepostalcode` text DEFAULT NULL,
`mozillahomecountryname` text DEFAULT NULL,
`title` text DEFAULT NULL,
`ou` text DEFAULT NULL,
`o` text DEFAULT NULL,
`street` text DEFAULT NULL,
`mozillaworkstreet2` text DEFAULT NULL,
`l` text DEFAULT NULL,
`st` text DEFAULT NULL,
`postalCode` text DEFAULT NULL,
`c` text DEFAULT NULL,
`birthyear` int(11) DEFAULT NULL,
`birthmonth` int(11) DEFAULT NULL,
`birthday` int(11) DEFAULT NULL,
`description` text DEFAULT NULL,
`photo` mediumblob DEFAULT NULL,
`domain` varchar(128) NOT NULL,
`mozillahomeurl` text DEFAULT NULL,
`mozillaworkurl` text DEFAULT NULL,
`active` tinyint(1) NOT NULL DEFAULT 1,
`employeeType` varchar(25) NOT NULL DEFAULT 'Employee',
PRIMARY KEY (`uid`),
UNIQUE KEY `mail_2` (`mail`,`domain`),
KEY `active` (`active`),
KEY `domain` (`domain`),
KEY `mail` (`mail`),
KEY `employeeType` (`employeeType`),
KEY `telephoneNumber` (`telephoneNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- RELATIONSHIPS FOR TABLE `contacts`:
--
--
-- Triggers `contacts`
--
DROP TRIGGER IF EXISTS `adduiandcnname`;
DELIMITER $$
CREATE TRIGGER `adduiandcnname` BEFORE INSERT ON `contacts` FOR EACH ROW BEGIN
set new.c_uid = new.mail, new.c_name = new.mail, new.c_cn = CONCAT(new.givenName, " ", new.sn), NEW.nsaimid = CONCAT(new.givenName, " ", new.sn);
end
$$
DELIMITER ;
DROP TRIGGER IF EXISTS `updateUID`;
DELIMITER $$
CREATE TRIGGER `updateUID` BEFORE UPDATE ON `contacts` FOR EACH ROW BEGIN
set new.c_uid = new.mail, new.c_name = new.mail, new.c_cn = CONCAT(new.givenName, " ", new.sn), NEW.nsaimid = CONCAT(new.givenName, " ", new.sn);
END
$$
DELIMITER ;
--
-- Metadata
--
USE `phpmyadmin`;
--
-- Metadata for table contacts
--
SET FOREIGN_KEY_CHECKS=1;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;The two triggers are there as the email (mail) is repeated in two other fields (c_uid and c_name), so the trigger automatically adds or updates the other two field. After some experementing I come to the conclusion that the only one is needed say "c_uid" and the other can be implied in the views.
In the contact table there is a field/attribute called "employeeType" with a default value of "Employee", these account will be listed under the "Domain Address Book" and, if the "employeeType" filed is not-equal <> "Employee" they they will be listed under the "Global Address Book".
This allows you to have company wide address that are not part of the domain email system, but that all users will can get in contact with.
The Global address book is defined in the sogo config file to be detailed further down.
The only issue that I have at the moment and cannot seem to resolve is the "telephoneNumber" Work number is not being displayed for some reason, if any one could shed some light on that it will be appriacated.
Views
To make this work sith SOGo including user authentication the the three views were created:
users2
con_view
aliases - this for displaying mailing list/group - optional
View - users2
This view will be replacing the main view in the SOGo Config file.
select `c`.`c_uid` AS `c_uid`,`c`.`c_name` AS `c_name`,`c`.`c_cn` AS `c_cn`,`c`.`nsaimid` AS `nsaimid`,`c`.`mail` AS `mail`,`c`.`mozillasecondemail` AS `mozillasecondemail`,`c`.`givenName` AS `givenName`,`c`.`sn` AS `sn`,`c`.`employeeID` AS `employeeid`,`c`.`telephoneNumber` AS `telephoneNumber`,`c`.`homephone` AS `homephone`,`c`.`mobile` AS `mobile`,`c`.`facsimiletelephonenumber` AS `facsimiletelephonenumber`,`c`.`pager` AS `pager`,`c`.`mozillahomestreet` AS `mozillahomestreet`,`c`.`mozillahomestreet2` AS `mozillahomestreet2`,`c`.`mozillahomelocalityname` AS `mozillahomelocalityname`,`c`.`mozillahomestate` AS `mozillahomestate`,`c`.`mozillahomepostalcode` AS `mozillahomepostalcode`,`c`.`mozillahomecountryname` AS `mozillahomecountryname`,`c`.`title` AS `title`,`c`.`ou` AS `ou`,`c`.`o` AS `o`,`c`.`street` AS `street`,`c`.`mozillaworkstreet2` AS `mozillaworkstreet2`,`c`.`l` AS `l`,`c`.`st` AS `st`,`c`.`postalCode` AS `postalCode`,`c`.`c` AS `c`,`c`.`birthyear` AS `birthyear`,`c`.`birthmonth` AS `birthmonth`,`c`.`birthday` AS `birthday`,`c`.`description` AS `description`,`c`.`photo` AS `photo`,`c`.`domain` AS `domain`,`c`.`mozillahomeurl` AS `mozillahomeurl`,`c`.`mozillaworkurl` AS `mozillaworkurl`,`c`.`active` AS `active`,`v`.`password` AS `c_password` from (`sogo`.`contacts` `c` left join `vmail`.`mailbox` `v` on(`v`.`username` = `c`.`c_uid`)) where `v`.`enablesogo` = 1 and `v`.`active` = 1You can edit this to reduce the number of filed tha you do not want to be displayed, like removing the details of the users home address, or other fields that you do not want displayed ( I will be removing that in an updated version)
Updated view
select `c`.`c_uid` AS `c_uid`,`c`.`c_name` AS `c_name`,`c`.`c_cn` AS `c_cn`,`c`.`nsaimid` AS `nsaimid`,`c`.`mail` AS `mail`,`c`.`mozillasecondemail` AS `mozillasecondemail`,`c`.`givenName` AS `givenName`,`c`.`sn` AS `sn`,`c`.`employeeID` AS `employeeid`,`c`.`telephoneNumber` AS `telephoneNumber`,`c`.`homephone` AS `homephone`,`c`.`mobile` AS `mobile`,`c`.`facsimiletelephonenumber` AS `facsimiletelephonenumber`,`c`.`pager` AS `pager`,`c`.`title` AS `title`,`c`.`ou` AS `ou`,`c`.`o` AS `o`,`c`.`street` AS `street`,`c`.`mozillaworkstreet2` AS `mozillaworkstreet2`,`c`.`l` AS `l`,`c`.`st` AS `st`,`c`.`postalCode` AS `postalCode`,`c`.`c` AS `c`,`c`.`birthyear` AS `birthyear`,`c`.`birthmonth` AS `birthmonth`,`c`.`birthday` AS `birthday`,`c`.`description` AS `description`,`c`.`photo` AS `photo`,`c`.`domain` AS `domain`,`c`.`mozillahomeurl` AS `mozillahomeurl`,`c`.`mozillaworkurl` AS `mozillaworkurl`,`c`.`active` AS `active`,`v`.`password` AS `c_password` from (`sogo`.`contacts` `c` left join `vmail`.`mailbox` `v` on(`v`.`username` = `c`.`c_uid`)) where `v`.`enablesogo` = 1 and `v`.`active` = 1View - con_view
This view is for the "Global Address book" that lists all other global contact where "employeeType" is not-equal to "Employee".
select `c`.`c_uid` AS `c_uid`,`c`.`c_name` AS `c_name`,`c`.`c_cn` AS `c_cn`,`c`.`nsaimid` AS `nsaimid`,`c`.`mail` AS `mail`,`c`.`mozillasecondemail` AS `mozillasecondemail`,`c`.`givenName` AS `givenName`,`c`.`sn` AS `sn`,`c`.`employeeID` AS `employeeid`,`c`.`telephoneNumber` AS `telephoneNumber`,`c`.`homephone` AS `homephone`,`c`.`mobile` AS `mobile`,`c`.`facsimiletelephonenumber` AS `facsimiletelephonenumber`,`c`.`pager` AS `pager`,`c`.`title` AS `title`,`c`.`ou` AS `ou`,`c`.`o` AS `o`,`c`.`street` AS `street`,`c`.`mozillaworkstreet2` AS `mozillaworkstreet2`,`c`.`l` AS `l`,`c`.`st` AS `st`,`c`.`postalCode` AS `postalCode`,`c`.`c` AS `c`,`c`.`birthyear` AS `birthyear`,`c`.`birthmonth` AS `birthmonth`,`c`.`birthday` AS `birthday`,`c`.`description` AS `description`,`c`.`photo` AS `photo`,`c`.`domain` AS `domain`,`c`.`mozillahomeurl` AS `mozillahomeurl`,`c`.`mozillaworkurl` AS `mozillaworkurl`,`c`.`active` AS `active`,`v`.`password` AS `c_password` from (`sogo`.`contacts` `c` left join `vmail`.`mailbox` `v` on(`v`.`username` = `c`.`c_uid`)) where `v`.`enablesogo` = 1 and `v`.`active` = 1Triggers
Beside the two triggers that are part of the "contacts" table there is also three more triggers defined as follows in the "vmail" database.
addContact - add a new contact when a new email users is created via the iRedamin panel.
deleteContact - Deletes contact when a email users is deleted via the iRedamin panel.
updateContact - updsates contact when a email users is updated via the iRedamin panel.
-
-- Triggers `mailbox`
--
DROP TRIGGER IF EXISTS `addContact`;
DELIMITER $$
CREATE TRIGGER `addContact` BEFORE INSERT ON `mailbox` FOR EACH ROW BEGIN
INSERT into `sogo`.`contacts` (`c_uid`, `c_name`, `mail`, `c_cn`, `nsaimid`, `domain`, `active`, `givenName`, `sn`, `employeeid`,`photo`) values ( new.`username`, new.`username`, new.`username`, new.`name`, new.`name`, new.`domain`, new.`active`, SUBSTRING_INDEX(NEW.name, ' ', 1), SUBSTRING_INDEX(NEW.name, ' ', ((length(NEW.name)-length(replace(NEW.name,' ','')))*-1 )), new.employeeID, NULL);
END
$$
DELIMITER ;
DROP TRIGGER IF EXISTS `deleteContact`;
DELIMITER $$
CREATE TRIGGER `deleteContact` AFTER DELETE ON `mailbox` FOR EACH ROW BEGIN
DELETE from `sogo`.`contacts` where `sogo`.`contacts`.`domain` = old.domain and `sogo`.`contacts`.`c_uid` = old.username;
END
$$
DELIMITER ;
DROP TRIGGER IF EXISTS `updateContacts`;
DELIMITER $$
CREATE TRIGGER `updateContacts` BEFORE UPDATE ON `mailbox` FOR EACH ROW BEGIN
IF EXISTS (select `sogo`.`contacts`.`c_uid` from `sogo`.`contacts` where `sogo`.`contacts`.`c_uid` = new.`username` ) then
UPDATE `sogo`.`contacts` set `mail` = new.`username`, `domain` = new.`domain`, `active` = new.`active`, `givenName` = SUBSTRING_INDEX(NEW.name, ' ', 1), `sn` = SUBSTRING_INDEX(NEW.name, ' ', ((length(NEW.name)-length(replace(NEW.name,' ','')))*-1 )), `employeeID` = new.employeeid where `sogo`.`contacts`.`mail` = new.`username`;
ELSE
INSERT into `sogo`.`contacts` (`c_uid`, `c_name`, `mail`, `c_cn`, `nsaimid`, `domain`, `active`, `givenName`, `sn`, `employeeid`, `photo`) values ( new.`username`, new.`username`, new.`username`, new.`name`, new.`name`, new.`domain`, new.`active`, SUBSTRING_INDEX(NEW.name, ' ', 1), SUBSTRING_INDEX(NEW.name, ' ', ((length(NEW.name)-length(replace(NEW.name,' ','')))*-1 )), new.employeeID, NULL);
END IF;
END
$$
SOGo Config
The Sogo Configle located in "/etc/sogo/sogo.conf" need to be modifiled in the below sections sections for the changes in the "Domain address Book" and the "Global Address Book" to take affect:
Before making and changes backup the originla file eg;
Command: cp /etc/sogo/sogo.conf /etc/sogo/sogo.conf.yyyymmdd-hhmmss
This format allows for to tracking the changes and backup copies{no changes made above this point}..
// Authentication using SQL
SOGoUserSources = (
{
type = sql;
id = users;
viewURL = "mysql://sogo:bOLhJMp8AsFabEpnVJFePmnTKSH6x24J@127.0.0.1:3306/sogo/users2";// changed the view from the default users to the new view users2
canAuthenticate = YES;// The algorithm used for password encryption when changing
// passwords without Password Policies enabled.
// Possible values are: plain, crypt, md5-crypt, ssha, ssha512.
userPasswordAlgorithm = ssha512;
prependPasswordScheme = YES;// Use `vmail.mailbox` as per-domain address book.
isAddressBook = YES;
displayName = "Domain Address Book";
SOGoEnableDomainBasedUID = YES;
DomainFieldName = "domain";// Listing of this LDAP source is only possible when performing a
// search (respecting the SOGoSearchMinimumWordLength parameter)
// or when explicitely typing a single dot.
// Defaults to YES when unset.
//
// WARNING: if you have many accounts in this address book, it may
// reach server-side query size limit, or cause
// performance issue.
listRequiresDot = NO;
},
//this is the new section for the Global address book display users where "employeeType" <> 'employee'
{
displayName = "Global Address Book";
type = sql;
id = global_address_book;
viewURL = "mysql://sogo:bOLhJMp8AsFabEpnVJFePmnTKSH6x24J@127.0.0.1:3306/sogo/con_view";
canAuthenticate = NO;
isAddressBook = YES;
listRequiresDot = NO;
SOGoEnableDomainBasedUID = YES;
DomainFieldName = "domain";
},
// Display mailing aliases in address book.
// You need to create SQL view 'sogo.aliases' first.
//
// For MySQL:
//
// CREATE VIEW sogo.aliases (c_uid, c_name, c_password, c_cn, mail, domain)
// AS SELECT address, name, '', name, address, domain
// FROM vmail.alias WHERE active=1;
// enable tjis section ONLY if you want to use the mailing list.
// BUT to do so means you will need to creat them manually in the MySQL vmail DB.
// instructions can be found here: https://docs.iredmail.org/sql.create.mail.alias.html
{
displayName = "Mailing Lists";
type = sql;
id = mailinglist;
viewURL = "mysql://sogo:bOLhJMp8AsFabEpnVJFePmnTKSH6x24J@127.0.0.1:3306/sogo/aliases";
canAuthenticate = NO;
isAddressBook = YES;
listRequiresDot = NO;
SOGoEnableDomainBasedUID = YES;
DomainFieldName = "domain";
},
);
{no changes made bbelow this point}..
Once the SOGo config file has been updated/changed you need to restart the sogo service for the changes to take affect:
Command: service sogo restart
This to do
I still need to do a few things , like figure out how to get the 'telephoneNumber field (Work Phone) to display.
Create a few php web pages to to able to create mailing lists, add aliases
Users to edit their own information (except email address and domain)
Administrators to add new Global address
And how to add menu options to the iRedAdmin panel, or at least one option to point to another admin page with user login screen.
----
Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.