Tag Archives: sogo

sogo auth with mysql and mysql(view) against ispconfig database

hi

fist step let mysql listen on: 0.0.0.0
therefore verify your /etc/mysql/my.cnf

cat /etc/mysql/my.cnf | grep bind
#bind-address		= 127.0.0.1

(should be done @ispconfig install)

create a mysql database and a user and grant all privileges to that user

CREATE USER 'sogo'@'1.1.1.1' IDENTIFIED BY  '***';
GRANT USAGE ON * . * TO  'sogo'@'1.1.1.1' IDENTIFIED BY  '***' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
CREATE DATABASE IF NOT EXISTS  `sogo` ;
GRANT ALL PRIVILEGES ON  `sogo` . * TO  'sogo'@'1.1.1.1;

use mysql database as root and create special view

use sogo;
CREATE VIEW sogo_auth_view AS
SELECT  email AS c_uid,
             email AS c_name,
             password AS c_password,
             name AS c_cn,
             email AS mail
FROM dbispconfig.mail_user
WHERE disableimap='n' AND postfix ='y';

in your sogo config set the following lines:

<key>userPasswordAlgorithm</key>
<string>crypt</string>
<key>viewURL</key>
<string>mysql://sogo:yourpass@1.1.1.2:3306/sogo/sogo_auth_view</string>

Have Fun!

simple sogo backup in perl with mysql

Hi

Need to export(backup) your sogo users contacts calendar etc?

Here some simple approach to get this done.

#!/usr/bin/perl
use warnings;
use DBI;

my $db ="databasename";
my $user = "username";
my $pass = "mypassword";
my $host = "localhost";
my $query = "SELECT mail FROM sogo_users";
my $bkppath = "/path/to/backup";
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);

my $dbh = DBI->connect("DBI:mysql:$db", "$user", "$pass")
	or die "Could not connect to database: " . DBI->errstr;

my $sth = $dbh->prepare("$query")
	or die "Couldn't prepare statement: " . $dbh->errstr;

$sth->execute()
	or die "Couldn't execute statement: " . $sth->errstr;

while ($data = $sth->fetchrow_array()) {
	my $mail = $data;
	my $bkp = `/usr/sbin/sogo-tool backup $bkppath $mail > /dev/null`;
	sleep 2;
	my $mv = `/bin/mv $bkppath/$mail $bkppath/$mail.$wday`;
	}

$sth->finish;
$dbh->disconnect;

Have fun!