Tag Archives: database

Get Austrian subnets from Ripe database

Hi
Need from the ripe database the subnets in cidr of some specific country?
Here my nooby approach to get this done.

wget ftp://ftp.ripe.net/pub/stats/ripencc/delegated-ripencc-latest
vim ripeconvert.pl

Paste the following code:

#!/usr/bin/perl

%subnet =();
$subnet{'4'} = "30";
$subnet{'8'} = "29";
$subnet{'16'} = "28";
$subnet{'32'} = "27";
$subnet{'64'} = "26";
$subnet{'128'} = "25";
$subnet{'256'} = "24";
$subnet{'512'} = "23";
$subnet{'1024'} = "22";
$subnet{'2048'} = "21";
$subnet{'4096'} = "20";
$subnet{'8192'} = "19";
$subnet{'16384'} = "18";
$subnet{'32768'} = "17";
$subnet{'65536'} = "16";
$subnet{'131072'} = "15";
$subnet{'262144'} = "14";
$subnet{'524288'} = "13";
$subnet{'1048576'} = "12";
#special ripe database
$subnet{'768'} = "22";
$subnet{'1280'} = "21";
$subnet{'1536'} = "21";
$subnet{'2560'} = "20";
$subnet{'2816'} = "20";
$subnet{'3072'} = "20";
$subnet{'9216'} = "18";
$subnet{'12288'} = "18";
$subnet{'13312'} = "18";

open(INFO,"<delegated-ripencc-latest");
        @ripe = <INFO>;
close(INFO);

sort(@ripe);

foreach $data (@ripe) {
    chomp($data);
    if ( $data =~ /ipv4/ && $data =~ /AT/ ) {
        @sdata = split('\|',$data);
        print @sdata[3]."\/".$subnet{"@sdata[4]"}."\n";
    }
    if ( $data =~ /ipv6/ && $data =~ /AT/ ) {
        @sdata = split('\|',$data);
        print @sdata[3]."\/".@sdata[4]."\n";
    }
}
perl ripeconvert.pl

Have fun!

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!