Cygal.net.pl

Systemy, Sieci i takie tam :)

OpenVPN i autentykacja user/password na podstawie bazy danych




OpenVPN autentykacja user/password na podstawie bazy danych

Dzisiaj napisze jak zrobić prosty system autentykacji dla openvpn. System będzie się składał Openvpn, bazy mysql oraz skryptów perl. Na początek zainstalujmy potrzebne pakiety.

Co będziemy potrzebować:
OpenVPN
Mysql
Perl scripts

Zacznijmy od założenia schematu bazy danych:

Mysql –uroot –p	
CREATE DATABASE openvpn;
GRANT ALL ON openvpn.* TO 'USERNAME'@"%" IDENTIFIED BY 'PASSWORD';
exit;
mysql -uUSERNAME -pPASSWORD
USE openvpn;

Stwórzmy teraz tabele dla użytkowników

CREATE TABLE IF NOT EXISTS `user` (
    `user_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
    `user_pass` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '1234',
    `user_mail` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `user_phone` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
    `user_online` tinyint(1) NOT NULL DEFAULT '0',
    `user_enable` tinyint(1) NOT NULL DEFAULT '1',
    `user_start_date` date NOT NULL,
    `user_end_date` date NOT NULL,
PRIMARY KEY (`user_id`),
KEY `user_pass` (`user_pass`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

teraz tabele dla naszych logów ;

CREATE TABLE IF NOT EXISTS `log` (
    `log_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
    `log_trusted_ip` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
    `log_trusted_port` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
    `log_remote_ip` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
    `log_remote_port` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
    `log_start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `log_end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `log_received` float NOT NULL DEFAULT '0',
    `log_send` float NOT NULL DEFAULT '0',
PRIMARY KEY (`log_id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Dodajmy jakiegoś testowego usera do naszej bazy:

INSERT INTO `user` (
    `user_id`, `user_pass`, `user_mail`, `user_phone`,
    `user_online`, `user_enable`, `user_start_date`, `user_end_date`
)
VALUES (
    'test', '1234', 'test@example.com',
    '+48501043032', 0, 1, '2012-10-01', '0000-00-00'
);

zainstalujmy teraz openvpn:
CentOS:
yum install openvpn
lub
SUSE:
zypper install openvpn

Stwórzmy wymagane certyfikaty:

cp -R /usr/share/doc/openvpn/examples/easy-rsa /etc/openvpn/.
cd /etc/openvpn/easy-rsa/2.0/

teraz edytujemy następujący plik:

vi vars

export KEY_COUNTRY="PL"
export KEY_PROVINCE="MAŁOPOLSKA"
export KEY_CITY="KRAKÓW"
export KEY_ORG="MYVPN"
export KEY_EMAIL="support@myvpn.com"

zapisujemy plik a następnie wydajmy komendę:

source ./vars
./clean-all

teraz generujemy już właściwe certyfikaty publiczny i prywatny
./build-ca
./build-key-server server
./build-key client
./build-dh
mv keys /etc/openvpn/.

Stwórzmy teraz katalog dla naszych skryptów.
mkdir scripts
a nastepnie :

vim config.cfg

HOST=localhost
PORT=3306
USERNAME=openvpn
PASSWORD=jakispassword
DB_NAME=openvpn

vim test.connect_db.pl

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
#!/usr/bin/perl
use DBI;
use Config::General qw(ParseConfig);

%config = ParseConfig(-ConfigFile => "config.cfg");

$host = $config{HOST};
$user = $config{USERNAME};
$pass = $config{PASSWORD};
$db = $config{DB_NAME};

print "$host : $user : $pass : $dbna";
print "n";

$dbh = DBI->connect("DBI:mysql:$db", $user, $pass) or die "connection to database FAILED: $DBI->errstr() unless $dbh";
print "SUCCESS connected to database n";

$username=$ARGV[0];
$password=$ARGV[1];

$sth=$dbh->prepare("select user_id from user where user_id='$username' and user_pass='$password' and user_enable=1 and user_start_date != user_end_date and TO_DAYS(now()) >= TO_DAYS(user_start_date) and (TO_DAYS(now()) <= TO_DAYS(user_end_date) or user_end_date='0000-00-00')");

$sth->execute();


if (($sth != '') && ($sth == "$username")){
        print  "user : $username n" ;
        print "authentication ok. n";
        exit 0 ;
}
        else
{
                        print "authentication failed. n";
                        exit 1;
}

vim login.pl

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
#!/usr/bin/perl
use DBI;
use Config::General qw(ParseConfig);

%config = ParseConfig(-ConfigFile => "/etc/openvpn/scripts/config.cfg");

$host = $config{HOST};
$user = $config{USERNAME};
$pass = $config{PASSWORD};
$db = $config{DB_NAME};

$username=$ENV{'username'};
$password=$ENV{'password'};

$dbh = DBI->connect("DBI:mysql:$db", $user, $pass) or die "connection to database FAILED: $DBI->errstr() unless $dbh";

$sth=$dbh->prepare("select user_id from user where user_id='$username' and user_pass='$password' and user_enable=1 and user_start_date != user_end_date and TO_DAYS(now()) >= TO_DAYS(user_start_date) and (TO_DAYS(now()) <= TO_DAYS(user_end_date) or user_end_date='0000-00-00')");

$sth->execute();

if (($sth != '') && ($sth = "$username")){
                print  "user : $username n" ;
                print "authentication ok. n";
                exit 0 ;
}
                else    {
                        print "authentication failed. n";
                        exit 1;
}

Vim connect.pl

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#!/usr/bin/perl
use DBI;
use Config::General qw(ParseConfig);

%config = ParseConfig(-ConfigFile => "/etc/openvpn/scripts/config.cfg");

$host = $config{HOST};
$user = $config{USERNAME};
$pass = $config{PASSWORD};
$db = $config{DB_NAME};

$common_name=$ENV{'common_name'};
$trusted_ip=$ENV{'trusted_ip'};
$trusted_port=$ENV{'trusted_port'};
$ifconfig_pool_remote_ip=$ENV{'ifconfig_pool_remote_ip'};
$remote_port_1=$ENV{'remote_port_1'};
$bytes_received=$ENV{'bytes_received'};
$bytes_sent=$ENV{'bytes_sent'};

$dbh = DBI->connect("DBI:mysql:$db", $user, $pass) or die "connection to databas                                                                                                 e FAILED: $DBI->errstr() unless $dbh";

$query = "INSERT INTO log (log_id,user_id,log_trusted_ip,log_trusted_port,log_re                                                                                                 mote_ip,log_remote_port,log_start_time,log_end_time,log_received,log_send) VALUE                                                                                                 S(NULL,'$common_name','$trusted_ip','$trusted_port','$ifconfig_pool_remot                                                                                                 e_ip','$remote_port_1',now(),'0000-00-00 00:00:00','$bytes_received','$b                                                                                                 ytes_sent')";

$dbh->do($query);

$query = "UPDATE user SET user_online=1 WHERE user_id='$common_name'";
$dbh->do($query);

vim disconnect.pl

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#!/usr/bin/perl
use DBI;
use Config::General qw(ParseConfig);

%config = ParseConfig(-ConfigFile => "/etc/openvpn/scripts/config.cfg");

$host = $config{HOST};
$user = $config{USERNAME};
$pass = $config{PASSWORD};
$db = $config{DB_NAME};

$common_name=$ENV{'common_name'};
$trusted_ip=$ENV{'trusted_ip'};
$trusted_port=$ENV{'trusted_port'};
$bytes_received=$ENV{'bytes_received'};
$bytes_sent=$ENV{'bytes_sent'};


$dbh = DBI->connect("DBI:mysql:$db", $user, $pass) or die "connection to database FAILED: $DBI->errstr() unless $dbh";

$query = "UPDATE user SET user_online=0 WHERE user_id='$common_name'";
$dbh->do($query);

$query = "UPDATE log SET log_end_time=now(),log_received='$bytes_received',log_send='$bytes_sent' WHERE log_trusted_ip='$trusted_ip' AND log_trusted_port='$trusted_port' AND user_id='$common_name' AND log_end_time='0000-00-00 00:00:00'";
$dbh->do($query);

stwórzmy plik konfiguracyjny openvpn o nazwie server.conf

vim server.conf

port 443
proto tcp
dev tun

server 10.4.0.0 255.255.255.0
 
ca /etc/openvpn/keys/ca.crt
cert /etc/openvpn/keys/server.crt
key /etc/openvpn/keys/server.key
dh /etc/openvpn/keys/dh1024.pem
 
persist-key
persist-tun
keepalive 5 60
reneg-sec 432000
 
comp-lzo
user nobody
client-to-client
username-as-common-name
client-cert-not-required
auth-user-pass-verify /etc/openvpn/script/login.pl via-env
 
max-clients 50
push "persist-key"
push "persist-tun"
push "redirect-gateway def1"

push "dhcp-option DNS 8.8.8.8"
push "dhcp-option DNS 8.8.4.4"
 
script-security 3 system
client-connect /etc/openvpn/script/connect.pl
client-disconnect /etc/openvpn/script/disconnect.pl
 
status /etc/openvpn/log/tcp_443.log
log-append /etc/openvpn/log/openvpn.log
verb 3


mkdir /etc/openvpn/log
touch /etc/openvpn/log/openvpn.log
touch /etc/openvpn/log/tcp_443.log
touch /etc/openvpn/log/udp_53.log

chmod -R 755 /etc/openvpn

teraz szybki test

/etc/openvpn/script/test_connect_db.sh test 1234

/etc/init.d/openvpn start

vim /etc/sysctl.conf

net.ipv4.ip_forward=1

vim /etc/rc.local

echo "1" > /proc/sys/net/ipv4/ip_forward
echo "1" > /proc/sys/net/ipv4/ip_dynaddr
 
iptables -A INPUT -i tun0 -j ACCEPT
iptables -A FORWARD -i tun0 -j ACCEPT
iptables -t nat -A POSTROUTING -s 10.4.0.0/24 -o eth0 -j MASQUERADE


/etc/rc.local
iptables-save

teraz tworzymy plik konfiguracyjny klienta:

vim clinet.conf

client
dev tun
 
proto tcp
remote 1.1.1.1 443
 
nobind
auth-user-pass
reneg-sec 432000
resolv-retry infinite

ca ca.crt
comp-lzo
verb 1

kopiujemy ca.crt z /etc/openvpn/keys/ca.crt do takiej samej ścieżki na kliencie

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *


Time limit is exhausted. Please reload CAPTCHA.