Geek's Rhapsody

2016-04-20 System Engineering No comments

AWS CodeDeploy 설정 (S3)

The flow of a typical AWS CodeDeploy deployment 설정 목록 EC2 On-Premises(EC2가 아닌 물리서버) CodeDeploy 서비스용 IAM 권한 생성 서버용 IAM 권한 생성 CodeDeploy Application 생성 EC2 설정 CodeDeploy Agent 설치/설정 On-Premise 서버 설정 배포 파일 준비, appspec.yml 설정 [반복] 배포 [반복] CodeDeploy 서비스용 IAM 권한 생성 [AWS-Console] IAM > Roles : Create New Role Role Type : AWS CodeDeploy Attach Policy : AWSCodeDeployRole 서버용 IAM 권한 생성 [AWS-Console] IAM > Policies : Create Policy

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:Get*",
                "s3:List*"
            ],
            "Resource": [
                "*",
                "arn:aws:s3:::my_source_bucket_name/*"
            ]
        }
    ]
}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

{

"Version": "2012-10-17",

"Statement": [

{

"Effect": "Allow",

"Action": [

"s3:Get*",

"s3:List*"

],

"Resource": [

"*",

"arn:aws:s3:::my_source_bucket_name/*"

]

}

]

}

[AWS-Console] IAM > Roles : Create New Role Role Type : AWS EC2 Attach Policy : 위 Policy를 선택 CodeDeploy Application ‥‥‥

2016-03-29 System Engineering No comments

rsyslog + MySQL + LogAnalyzer on OpenSUSE

1. Install requirements

zypper install rsyslog rsyslog-module-relp rsyslog-module-mysql rsyslog-module-snmp rsyslog-module-mmnormalize

1	zypper install rsyslog rsyslog-module-relp rsyslog-module-mysql rsyslog-module-snmp rsyslog-module-mmnormalize

2. Create RuleBase for PHP log

vi /etc/rsyslog.d/phplog.rulebase

# PHP  Logs
rule=: %server:char-to:\x3a%\x3a PHP %priority:char-to:\x3a%\x3a%message:rest%
rule=: PHP %priority:char-to:\x3a%\x3a%message:rest%

1

2

3

4

5

vi /etc/rsyslog.d/phplog.rulebase

# PHP Logs

rule=: %server:char-to:\x3a%\x3a PHP %priority:char-to:\x3a%\x3a%message:rest%

rule=: PHP %priority:char-to:\x3a%\x3a%message:rest%

3. Prepare MySQL

CREATE DATABASE `syslog`;
USE `syslog`;

CREATE USER 'syslog'@'localhost' IDENTIFIED BY 'my_password';
GRANT EXECUTE, INSERT, LOCK TABLES, SELECT, SHOW VIEW, UPDATE
  ON syslog.* TO 'syslog'@'localhost';
FLUSH PRIVILEGES;

CREATE TABLE `SystemEvents` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `CustomerID` bigint(20) DEFAULT NULL,
  `ReceivedAt` datetime DEFAULT NULL,
  `DeviceReportedTime` datetime DEFAULT NULL,
  `Facility` smallint(6) DEFAULT NULL,
  `Priority` smallint(6) DEFAULT NULL,
  `FromHost` varchar(60) DEFAULT NULL,
  `Message` text,
  `NTSeverity` int(11) DEFAULT NULL,
  `Importance` int(11) DEFAULT NULL,
  `EventSource` varchar(60) DEFAULT NULL,
  `EventUser` varchar(60) DEFAULT NULL,
  `EventCategory` int(11) DEFAULT NULL,
  `EventID` int(11) DEFAULT NULL,
  `EventBinaryData` text,
  `MaxAvailable` int(11) DEFAULT NULL,
  `CurrUsage` int(11) DEFAULT NULL,
  `MinUsage` int(11) DEFAULT NULL,
  `MaxUsage` int(11) DEFAULT NULL,
  `InfoUnitID` int(11) DEFAULT NULL,
  `SysLogTag` varchar(60) DEFAULT NULL,
  `ProcessID` varchar(60) DEFAULT NULL,
  `EventLogType` varchar(60) DEFAULT NULL,
  `GenericFileName` varchar(60) DEFAULT NULL,
  `SystemID` int(11) DEFAULT NULL,
  `Checksum` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `ie1_SystemEvents` (`DeviceReportedTime`),
  KEY `ie2_SystemEvents` (`FromHost`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DELIMITER $$
ALTER DEFINER=`root`@`%` EVENT `evt_purge_old_events` 
ON SCHEDULE EVERY 1 DAY STARTS '2010-01-01 06:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
    DELETE FROM syslog.SystemEvents WHERE DeviceReportedTime < SUBDATE(CURRENT_TIMESTAMP, INTERVAL 3 MONTH);
END$$
DELIMITER;

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

36

37

38

39

40

41

42

43

44

45

46

CREATE DATABASE `syslog`;

USE `syslog`;

CREATE USER 'syslog'@'localhost' IDENTIFIED BY 'my_password';

GRANT EXECUTE, INSERT, LOCK TABLES, SELECT, SHOW VIEW, UPDATE

ON syslog.* TO 'syslog'@'localhost';

FLUSH PRIVILEGES;

CREATE TABLE `SystemEvents` (

`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`CustomerID` bigint(20) DEFAULT NULL,

`ReceivedAt` datetime DEFAULT NULL,

`DeviceReportedTime` datetime DEFAULT NULL,

`Facility` smallint(6) DEFAULT NULL,

`Priority` smallint(6) DEFAULT NULL,

`FromHost` varchar(60) DEFAULT NULL,

`Message` text,

`NTSeverity` int(11) DEFAULT NULL,

`Importance` int(11) DEFAULT NULL,

`EventSource` varchar(60) DEFAULT NULL,

`EventUser` varchar(60) DEFAULT NULL,

`EventCategory` int(11) DEFAULT NULL,

`EventID` int(11) DEFAULT NULL,

`EventBinaryData` text,

`MaxAvailable` int(11) DEFAULT NULL,

`CurrUsage` int(11) DEFAULT NULL,

`MinUsage` int(11) DEFAULT NULL,

`MaxUsage` int(11) DEFAULT NULL,

`InfoUnitID` int(11) DEFAULT NULL,

`SysLogTag` varchar(60) DEFAULT NULL,

`ProcessID` varchar(60) DEFAULT NULL,

`EventLogType` varchar(60) DEFAULT NULL,

`GenericFileName` varchar(60) DEFAULT NULL,

`SystemID` int(11) DEFAULT NULL,

`Checksum` int(11) DEFAULT NULL,

PRIMARY KEY (`ID`),

KEY `ie1_SystemEvents` (`DeviceReportedTime`),

KEY `ie2_SystemEvents` (`FromHost`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DELIMITER $$

ALTER DEFINER=`root`@`%` EVENT `evt_purge_old_events`

ON SCHEDULE EVERY 1 DAY STARTS '2010-01-01 06:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN

DELETE FROM syslog.SystemEvents WHERE DeviceReportedTime < SUBDATE(CURRENT_TIMESTAMP, INTERVAL 3 MONTH);

END$$

DELIMITER;

4. rsyslog Configuration for Log-Servers

######################
# Aiden (server)
######################

module(load="mmnormalize")
module(load="imrelp")
module(load="imtcp" keepalive="on")
module(load="imudp")
module(load="ommysql")

input(type="imrelp" port="2514" keepalive="on" ruleset="remote")
input(type="imtcp" port="1514" ruleset="remote")
input(type="imudp" port="514" ruleset="remote")
$AllowedSender TCP, 127.0.0.1, 10.0.1.0/24, 192.168.1.0/24
$AllowedSender UDP, 127.0.0.1, 10.0.1.0/24, 192.168.1.0/24

template(name="aidenSQLformat" type="string" option.sql="on" string="insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag, ProcessID) values (TRIM('%msg%'), %syslogfacility%, '%hostname%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag:R,ERE,1,FIELD:([^\\[]+)(\\[[0-9]{1,5}\\])*:--end%', '%syslogtag:R,ERE,1,BLANK:\\[([0-9]{1,5})\\]--end%')")
template(name="aidenSQL4PHPformat" type="string" option.sql="on" string="insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag, ProcessID) values ('%$!msg%', %$!facility%, '%hostname%', %$!syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%$!syslogtag%', '%syslogtag:R,ERE,1,BLANK:\\[([0-9]{1,5})\\]--end%')")

ruleset(name="php2db") {
	if not ($syslogtag contains_i 'suhosin') then {
		action(type="mmnormalize" rulebase="/etc/rsyslog.d/phplog.rulebase")
		if ($parsesuccess == 'OK') then {
			set $!syslogtag = 'php';
			set $!facility = 3;
			set $!msg = 'PHP ' & $!priority & ':' & $!message;
			if ($!priority contains_i 'error') then {
				set $!syslogpriority = 3;
				action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQL4PHPformat" serverport="3306")
			}else if ($!priority contains_i 'warning') then  {
				set $!syslogpriority = 4;
				action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQL4PHPformat" serverport="3306")
			}else if ($!priority contains_i 'notice') then {
				set $!syslogpriority = 5;
				action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQL4PHPformat" serverport="3306")
			}
		}
	}
	if ($fromhost-ip == '127.0.0.1') then action(type="omfile" file="/var/log/php.log")
}
ruleset(name="remote") {
	if ($syslogtag contains_i 'php' or $syslogtag contains_i 'httpd2-prefork' or $syslogtag contains_i 'httpd-prefork' or \
			$syslogtag contains_i 'ool' or $syslogtag contains_i 'suhosin') then {
		call php2db
	}else{
		action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQLformat" serverport="3306")
	}
	if ($fromhost-ip != '127.0.0.1') then action(type="omfile" file="/var/log/remote")
	stop
}

#--------------

if ($syslogpriority <= 4 and ($msg contains_i 'kernel does not support IPv6' or $msg contains_i 'Failed to parse address value')) then { # bug
	-/var/log/messages
	stop
}
if ($syslogpriority <= 4 and ($msg contains_i 'Failed to open private bus connection' or $msg contains_i 'protocol dhcp covered by a ipv4')) then { # bug
	-/var/log/messages
	stop
}
if ($syslogpriority <= 4 and ($msg contains_i 'Could not load host key' or $msg contains_i 'audit_printk_skb')) then { # bug
	-/var/log/auth.log
	stop
}

if  ($syslogtag contains_i 'php' or $syslogtag contains_i 'httpd2-prefork' or $syslogtag contains_i 'httpd-prefork' or \
	   $syslogtag contains_i 'ool' or $syslogtag contains_i 'suhosin') then {
	call php2db
	stop
}

*.=warning;*.=err;*.=crit   action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQLformat" serverport="3306")
if ($syslogpriority == 5 and ($msg contains_i 'error' or $msg contains_i 'exception' or $msg contains_i 'warning')) then \
	action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQLformat" serverport="3306")

if ($syslogtag startswith 'kernel' and $msg contains_i 'audit:') then {
	-/var/log/auth.log
	stop
}
auth,authpriv.*				-/var/log/auth.log
& stop
cron.*					-/var/log/cron.log
& stop
if ($msg contains_i 'pam_unix(crond:session)') then {
	-/var/log/cron.log
	stop
}
local5.*				-/var/log/sftpd.log
& stop
if ($syslogtag contains_i 'internal-sftp') then {
	-/var/log/sftpd.log
	stop
}
if ($syslogtag contains_i 'tomcat') then {
	-/var/log/tomcat.log
	stop
}

######################

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

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

######################

# Aiden (server)

######################

module(load="mmnormalize")

module(load="imrelp")

module(load="imtcp" keepalive="on")

module(load="imudp")

module(load="ommysql")

input(type="imrelp" port="2514" keepalive="on" ruleset="remote")

input(type="imtcp" port="1514" ruleset="remote")

input(type="imudp" port="514" ruleset="remote")

$AllowedSender TCP, 127.0.0.1, 10.0.1.0/24, 192.168.1.0/24

$AllowedSender UDP, 127.0.0.1, 10.0.1.0/24, 192.168.1.0/24

template(name="aidenSQLformat" type="string" option.sql="on" string="insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag, ProcessID) values (TRIM('%msg%'), %syslogfacility%, '%hostname%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag:R,ERE,1,FIELD:([^\\[]+)(\\[[0-9]{1,5}\\])*:--end%', '%syslogtag:R,ERE,1,BLANK:\\[([0-9]{1,5})\\]--end%')")

template(name="aidenSQL4PHPformat" type="string" option.sql="on" string="insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag, ProcessID) values ('%$!msg%', %$!facility%, '%hostname%', %$!syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%$!syslogtag%', '%syslogtag:R,ERE,1,BLANK:\\[([0-9]{1,5})\\]--end%')")

ruleset(name="php2db") {

if not ($syslogtag contains_i 'suhosin') then {

action(type="mmnormalize" rulebase="/etc/rsyslog.d/phplog.rulebase")

if ($parsesuccess == 'OK') then {

set $!syslogtag = 'php';

set $!facility = 3;

set $!msg = 'PHP ' & $!priority & ':' & $!message;

if ($!priority contains_i 'error') then {

set $!syslogpriority = 3;

action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQL4PHPformat" serverport="3306")

}else if ($!priority contains_i 'warning') then {

set $!syslogpriority = 4;

action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQL4PHPformat" serverport="3306")

}else if ($!priority contains_i 'notice') then {

set $!syslogpriority = 5;

action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQL4PHPformat" serverport="3306")

}

if ($fromhost-ip == '127.0.0.1') then action(type="omfile" file="/var/log/php.log")

}

ruleset(name="remote") {

if ($syslogtag contains_i 'php' or $syslogtag contains_i 'httpd2-prefork' or $syslogtag contains_i 'httpd-prefork' or \

$syslogtag contains_i 'ool' or $syslogtag contains_i 'suhosin') then {

call php2db

}else{

action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQLformat" serverport="3306")

}

if ($fromhost-ip != '127.0.0.1') then action(type="omfile" file="/var/log/remote")

stop

}

#--------------

if ($syslogpriority <= 4 and ($msg contains_i 'kernel does not support IPv6' or $msg contains_i 'Failed to parse address value')) then { # bug

-/var/log/messages

stop

}

if ($syslogpriority <= 4 and ($msg contains_i 'Failed to open private bus connection' or $msg contains_i 'protocol dhcp covered by a ipv4')) then { # bug

-/var/log/messages

stop

}

if ($syslogpriority <= 4 and ($msg contains_i 'Could not load host key' or $msg contains_i 'audit_printk_skb')) then { # bug

-/var/log/auth.log

stop

}

if ($syslogtag contains_i 'php' or $syslogtag contains_i 'httpd2-prefork' or $syslogtag contains_i 'httpd-prefork' or \

$syslogtag contains_i 'ool' or $syslogtag contains_i 'suhosin') then {

call php2db

stop

}

*.=warning;*.=err;*.=crit action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQLformat" serverport="3306")

if ($syslogpriority == 5 and ($msg contains_i 'error' or $msg contains_i 'exception' or $msg contains_i 'warning')) then \

action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQLformat" serverport="3306")

if ($syslogtag startswith 'kernel' and $msg contains_i 'audit:') then {

-/var/log/auth.log

stop

}

auth,authpriv.* -/var/log/auth.log

& stop

cron.* -/var/log/cron.log

& stop

if ($msg contains_i 'pam_unix(crond:session)') then {

-/var/log/cron.log

stop

}

local5.* -/var/log/sftpd.log

& stop

if ($syslogtag contains_i 'internal-sftp') then {

-/var/log/sftpd.log

stop

}

if ($syslogtag contains_i 'tomcat') then {

-/var/log/tomcat.log

stop

}

######################

5. rsyslog Configuration for Log-Client

######################
# Aiden (client)
######################

module(load="mmnormalize")
module(load="omrelp")

ruleset(name="php2remote") {
	if not ($syslogtag contains_i 'suhosin') then {
		action(type="mmnormalize" rulebase="/etc/rsyslog.d/phplog.rulebase")
		if ($parsesuccess == 'OK') then {
			if ($!priority contains_i 'error' or $!priority contains_i 'warning' or $!priority contains_i 'notice') then  {
				action(type="omrelp" target="logserver_ip_or_name" port="2514")
			}
		}
	}
	action(type="omfile" file="/var/log/php.log")
}

#--------------

if ($syslogpriority <= 4 and ($msg contains_i 'kernel does not support IPv6' or $msg contains_i 'Failed to parse address value')) then { # bug
	-/var/log/messages
	stop
}
if ($syslogpriority <= 4 and ($msg contains_i 'Failed to open private bus connection' or $msg contains_i 'protocol dhcp covered by a ipv4')) then { # bug
	-/var/log/messages
	stop
}
if ($syslogpriority <= 4 and ($msg contains_i 'Could not load host key' or $msg contains_i 'audit_printk_skb')) then { # bug
	-/var/log/auth.log
	stop
}

if  ($syslogtag contains_i 'php' or $syslogtag contains_i 'httpd2-prefork' or $syslogtag contains_i 'httpd-prefork' or \
	   $syslogtag contains_i 'ool' or $syslogtag contains_i 'suhosin') then {
	call php2remote
	stop
}

*.=warning;*.=err;*.=crit	action(type="omrelp" target="logserver_ip_or_name" port="2514")
if ($syslogpriority == 5 and ($msg contains_i 'error' or $msg contains_i 'exception' or $msg contains_i 'warning')) then \
	action(type="omrelp" target="logserver_ip_or_name" port="2514")

if ($syslogtag startswith 'kernel' and $msg contains_i 'audit:') then {
	-/var/log/auth.log
	stop
}
auth,authpriv.*				-/var/log/auth.log
& stop
cron.*					-/var/log/cron.log
& stop
if ($msg contains_i 'pam_unix(crond:session)') then {
	-/var/log/cron.log
	stop
}
local5.*				-/var/log/sftpd.log
& stop
if ($syslogtag contains_i 'internal-sftp') then {
	-/var/log/sftpd.log
	stop
}
if ($syslogtag contains_i 'tomcat') then {
	-/var/log/tomcat.log
	stop
}

######################

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

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

######################

# Aiden (client)

######################

module(load="mmnormalize")

module(load="omrelp")

ruleset(name="php2remote") {

if not ($syslogtag contains_i 'suhosin') then {

action(type="mmnormalize" rulebase="/etc/rsyslog.d/phplog.rulebase")

if ($parsesuccess == 'OK') then {

if ($!priority contains_i 'error' or $!priority contains_i 'warning' or $!priority contains_i 'notice') then {

action(type="omrelp" target="logserver_ip_or_name" port="2514")

}

action(type="omfile" file="/var/log/php.log")

}

#--------------

if ($syslogpriority <= 4 and ($msg contains_i 'kernel does not support IPv6' or $msg contains_i 'Failed to parse address value')) then { # bug

-/var/log/messages

stop

}

if ($syslogpriority <= 4 and ($msg contains_i 'Failed to open private bus connection' or $msg contains_i 'protocol dhcp covered by a ipv4')) then { # bug

-/var/log/messages

stop

}

if ($syslogpriority <= 4 and ($msg contains_i 'Could not load host key' or $msg contains_i 'audit_printk_skb')) then { # bug

-/var/log/auth.log

stop

}

if ($syslogtag contains_i 'php' or $syslogtag contains_i 'httpd2-prefork' or $syslogtag contains_i 'httpd-prefork' or \

$syslogtag contains_i 'ool' or $syslogtag contains_i 'suhosin') then {

call php2remote

stop

}

*.=warning;*.=err;*.=crit action(type="omrelp" target="logserver_ip_or_name" port="2514")

if ($syslogpriority == 5 and ($msg contains_i 'error' or $msg contains_i 'exception' or $msg contains_i 'warning')) then \

action(type="omrelp" target="logserver_ip_or_name" port="2514")

if ($syslogtag startswith 'kernel' and $msg contains_i 'audit:') then {

-/var/log/auth.log

stop

}

auth,authpriv.* -/var/log/auth.log

& stop

cron.* -/var/log/cron.log

& stop

if ($msg contains_i 'pam_unix(crond:session)') then {

-/var/log/cron.log

stop

}

local5.* -/var/log/sftpd.log

& stop

if ($syslogtag contains_i 'internal-sftp') then {

-/var/log/sftpd.log

stop

}

if ($syslogtag contains_i 'tomcat') then {

-/var/log/tomcat.log

stop

}

######################

6. Install LogAnalyzer

cd /usr/local/src
wget http://download.adiscon.com/loganalyzer/loganalyzer-4.1.3.tar.gz
tar -zxvf loganalyzer-4.1.3.tar.gz
mkdir -p /srv/www/htdocs/loganalyzer
cp /usr/local/src/loganalyzer-4.1.3/src/* /srv/www/htdocs/loganalyzer/

1

2

3

4

5

cd /usr/local/src

wget http://download.adiscon.com/loganalyzer/loganalyzer-4.1.3.tar.gz

tar -zxvf loganalyzer-4.1.3.tar.gz

mkdir -p /srv/www/htdocs/loganalyzer

cp /usr/local/src/loganalyzer-4.1.3/src/* /srv/www/htdocs/loganalyzer/

7. LogAnalyzer Configuration http://localhost/loganalyzer ♦ References http://www.the-art-of-web.com/system/rsyslog-config/ http://www.liblognorm.com/files/manual/configuration.html

2016-01-13 System Engineering No comments

Upload PKCS#12 Server Certificates to AWS

1. Extract RSA Private key

openssl pkcs12 -in CERTIFICATE.PFX -nocerts -nodes -out private.key
openssl rsa -in private.key -out private-rsa.key

1 2	openssl pkcs12 -in CERTIFICATE.PFX -nocerts -nodes -out private.key openssl rsa -in private.key -out private-rsa.key

2. Extract Certificate

openssl pkcs12 -in CERTIFICATE.PFX -clcerts -nokeys -out certificate.crt

1	openssl pkcs12 -in CERTIFICATE.PFX -clcerts -nokeys -out certificate.crt

3. Extract Certificate Chains

openssl pkcs12 -in CERTIFICATE.PFX -cacerts -nokeys -out certificate-chain.crt

1	openssl pkcs12 -in CERTIFICATE.PFX -cacerts -nokeys -out certificate-chain.crt

4. Trimming Certificates for AWS

sed -ne '/-----BEGIN CERTIFICATE-----/,/-----END CERTIFICATE-----/ p' certificate.crt > certificate-trim.crt
sed -ne '/-----BEGIN CERTIFICATE-----/,/-----END CERTIFICATE-----/ p' certificate-chain.crt > certificate-chain-trim.crt

1 2	sed -ne '/-----BEGIN CERTIFICATE-----/,/-----END CERTIFICATE-----/ p' certificate.crt > certificate-trim.crt sed -ne '/-----BEGIN CERTIFICATE-----/,/-----END CERTIFICATE-----/ p' certificate-chain.crt > certificate-chain-trim.crt

5. Upload Key & Certificates to AWS via CLI

aws iam upload-server-certificate --profile profile --server-certificate-name domain.com --certificate-body file://certificate-trim.crt --private-key file://private-rsa.key --certificate-chain file://certificate-chain-trim.crt

1	aws iam upload-server-certificate --profile profile --server-certificate-name domain.com --certificate-body file://certificate-trim.crt --private-key file://private-rsa.key --certificate-chain file://certificate-chain-trim.crt

2015-03-12 Developments No comments

Using request of JSON Payload in Slim framework

How to enable JSON-Payload input in Slim Framework… SlimMiddlewareContentTypes.php

<?php
/**
 * Middleware SlimMiddlewareContentTypes (for JSON Payload requests)
 * 2015/03/12
 * Aiden Kim
 */
class SlimMiddlewareContentTypes extends \Slim\Middleware\ContentTypes {
	
	public function call() {
		$mediaType = $this->app->request->getMediaType();
		if ( isset($this->contentTypes[$mediaType]) && !$this->app->request->isGet() && !$this->app->request->isHead() ) {
			$env = $this->app->environment();
			// Parse request data
			$form_hash = $this->parse($env['slim.input'], $mediaType);
			if (is_array($form_hash)) {
				// Backup original form-hash
				$env['slim.request.form_hash_original'] = $env['slim.request.form_hash'];
				// Replace new form-hash with parsed Array
				$env['slim.request.form_hash'] = $form_hash;
			}
		}
		$this->next->call();
	}

}
?>

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

<?php

/**

* Middleware SlimMiddlewareContentTypes (for JSON Payload requests)

* 2015/03/12

* Aiden Kim

*/

class SlimMiddlewareContentTypes extends \Slim\Middleware\ContentTypes {

public function call() {

$mediaType = $this->app->request->getMediaType();

if ( isset($this->contentTypes[$mediaType]) && !$this->app->request->isGet() && !$this->app->request->isHead() ) {

$env = $this->app->environment();

// Parse request data

$form_hash = $this->parse($env['slim.input'], $mediaType);

if (is_array($form_hash)) {

// Backup original form-hash

$env['slim.request.form_hash_original'] = $env['slim.request.form_hash'];

// Replace new form-hash with parsed Array

$env['slim.request.form_hash'] = $form_hash;

}

$this->next->call();

}

?>

index.asp

<?php
require_once('SlimMiddlewareContentTypes.php');

/**
 * Loading
 */
\Slim\Slim::registerAutoloader();

/**
 * Default conditions
 */
$app = new \Slim\Slim();

/**
 * Route Middleware
 */
$app->add(new SlimMiddlewareContentTypes());

/**
 * Route config
 */
$app->post('/sample', 
	function () use ($app) {
		var_dump($app->request()->params());
		var_dump($app->request()->post());
	}
);
 
/**
 * Run
 */
$app->run();

?>

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

<?php

require_once('SlimMiddlewareContentTypes.php');

/**

* Loading

*/

\Slim\Slim::registerAutoloader();

/**

* Default conditions

*/

$app = new \Slim\Slim();

/**

* Route Middleware

*/

$app->add(new SlimMiddlewareContentTypes());

/**

* Route config

*/

$app->post('/sample',

function () use ($app) {

var_dump($app->request()->params());

var_dump($app->request()->post());

}

);

/**

* Run

*/

$app->run();

?>

2015-01-10 Developments 1 Comment

MySQL Procedure Pattern for Nested Transaction

The MySQL dose not supports ‘Nested Transaction’. Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms. (https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html) But I want to use ‘Stored Procedure’ what work well with a transaction independently and with nested calling. This pattern isn’t correct nested transactions, but enables the use of nested procedures. The basic idea is to use the @@ AUTOCOMMIT environment variable. The procedure will determine whether the transaction is started from the ‥‥‥

2014-12-15 System Engineering No comments

Install OpenVPN on openSUSE

0. Plan Public IF : eth1 (210.1.1.1) Private IF : eth0 (10.1.0.0/16) Virtual Tunneling IF : tun0 (172.16.1.0/24) 1. Download and install packages

zypper install openvpn openvpn-auth-pam-plugin

1	zypper install openvpn openvpn-auth-pam-plugin

2. Install EasyRSA and create server ertificates

cd /usr/local/src
wget -O easy-rsa-2.x.tar.gz https://github.com/OpenVPN/easy-rsa/archive/release/2.x.tar.gz
tar xvf easy-rsa-2.x.tar.gz
cp -r easy-rsa-release-2.x/easy-rsa /etc/openvpn/

cd /etc/openvpn/easy-rsa/2.0/
vi /etc/openvpn/easy-rsa/2.0/vars
# Fill KEY_COUNTRY, KEY_PROVINCE, KEY_CITY, KEY_ORG, and KEY_EMAIL 

source ./vars
./clean-all 
# Don't run clean-all after this time...

./build-ca
./build-key-server server
./build-dh

# Certificate create & revoke & create CRL
./build-key client1
./revoke-full client1

cd /etc/openvpn/easy-rsa/2.0/keys
cp ca.crt ca.key dh2048.pem server.crt server.key crl.pem /etc/openvpn

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

cd /usr/local/src

wget -O easy-rsa-2.x.tar.gz https://github.com/OpenVPN/easy-rsa/archive/release/2.x.tar.gz

tar xvf easy-rsa-2.x.tar.gz

cp -r easy-rsa-release-2.x/easy-rsa /etc/openvpn/

cd /etc/openvpn/easy-rsa/2.0/

vi /etc/openvpn/easy-rsa/2.0/vars

# Fill KEY_COUNTRY, KEY_PROVINCE, KEY_CITY, KEY_ORG, and KEY_EMAIL

source ./vars

./clean-all

# Don't run clean-all after this time...

./build-ca

./build-key-server server

./build-dh

# Certificate create & revoke & create CRL

./build-key client1

./revoke-full client1

cd /etc/openvpn/easy-rsa/2.0/keys

cp ca.crt ca.key dh2048.pem server.crt server.key crl.pem /etc/openvpn

3. Configuration for PAM-plugin

groupadd -g 180 openvpn
vi /etc/pam.d/openvpn

auth    required        pam_env.so
auth    required        pam_unix.so        shadow    nodelay
auth    requisite       pam_succeed_if.so  uid >= 1000 quiet
auth    requisite       pam_succeed_if.so  user ingroup openvpn quiet
auth    required        pam_tally2.so      deny=5 lock_time=2 unlock_time=1200 even_deny_root audit
account required        pam_unix.so
account required        pam_tally2.so

1

2

3

4

5

6

7

8

9

10

groupadd -g 180 openvpn

vi /etc/pam.d/openvpn

auth required pam_env.so

auth required pam_unix.so shadow nodelay

auth requisite pam_succeed_if.so uid >= 1000 quiet

auth requisite pam_succeed_if.so user ingroup openvpn quiet

auth required pam_tally2.so deny=5 lock_time=2 unlock_time=1200 even_deny_root audit

account required pam_unix.so

account required pam_tally2.so

4. Configuration for rsyslog

mkdir /var/log/openvpn && chown root:admin /var/log/openvpn && chmod 750 /var/log/openvpn
vi /etc/rsyslog.d/openvpn.conf

if      (($programname == 'openvpn') and ($msg contains 'authentication succeeded')) or \
        (($programname == 'openvpn') and ($msg contains 'pool returned IPv4')) or \
        (($programname == 'openvpn') and ($msg contains 'Inactivity timeout')) or \
        (($syslogfacility-text == 'kern') and ($msg contains 'IN=tun')) \
then    -/var/log/openvpn/openvpn-user.log

if      ($programname == 'openvpn')
then    -/var/log/openvpn/openvpn.log
&       ~

service rsyslog restart

1

2

3

4

5

6

7

8

9

10

11

12

13

14

mkdir /var/log/openvpn && chown root:admin /var/log/openvpn && chmod 750 /var/log/openvpn

vi /etc/rsyslog.d/openvpn.conf

if (($programname == 'openvpn') and ($msg contains 'authentication succeeded')) or \

(($programname == 'openvpn') and ($msg contains 'pool returned IPv4')) or \

(($programname == 'openvpn') and ($msg contains 'Inactivity timeout')) or \

(($syslogfacility-text == 'kern') and ($msg contains 'IN=tun')) \

then -/var/log/openvpn/openvpn-user.log

if ($programname == 'openvpn')

then -/var/log/openvpn/openvpn.log

& ~

service rsyslog restart

5. Configure SuSEfirewall

vi /etc/sysconfig/SuSEfirewall2

FW_CUSTOMRULES="/etc/sysconfig/scripts/SuSEfirewall2-custom


vi /etc/sysconfig/scripts/SuSEfirewall2-custom

fw_custom_after_finished() {

    # openvpn config
    # 2014-12 Aiden
    
    /usr/sbin/iptables -t nat -D POSTROUTING -o tun0 -j MASQUERADE
    /usr/sbin/iptables -t nat -A POSTROUTING -s 172.16.1.0/24 -o eth0 -j MASQUERADE

    /usr/sbin/iptables -N forward_openvpn
    /usr/sbin/iptables -A forward_openvpn -m conntrack --ctstate NEW -m limit --limit 3/min -j LOG --log-prefix "SFW2-FWDvpn-REJECT_DEFLT " --log-tcp-options --log-ip-options
    /usr/sbin/iptables -A forward_openvpn -j reject_func

    /usr/sbin/iptables -D FORWARD -i tun0 -j forward_ext
    /usr/sbin/iptables -I FORWARD 4 -i tun0 -o eth0 -j forward_openvpn    # before LOG


    /usr/sbin/iptables -I INPUT -i tun0 -p tcp -m tcp --dport 22 -j ACCEPT
    /usr/sbin/iptables -I INPUT 2 -i tun0 -j DROP
    /usr/sbin/iptables -D forward_ext -i tun0 -o eth0 -m conntrack --ctstate RELATED,ESTABLISHED -j ACCEPT
    /usr/sbin/iptables -I forward_ext 1 -i eth1 -m conntrack --ctstate NEW -j DROP
    /usr/sbin/iptables -D forward_int -i eth0 -o tun0 -m conntrack --ctstate NEW,RELATED,ESTABLISHED -j ACCEPT
    /usr/sbin/iptables -I forward_int 10 -i eth0 -o tun0 -m conntrack --ctstate RELATED,ESTABLISHED -j ACCEPT    # before DROP
    
    /usr/sbin/iptables -N openvpn_subnet
    /usr/sbin/iptables -A openvpn_subnet -p icmp -m conntrack --ctstate RELATED,ESTABLISHED -m icmp --icmp-type 0 -j ACCEPT
    /usr/sbin/iptables -A openvpn_subnet -p icmp -m conntrack --ctstate RELATED,ESTABLISHED -m icmp --icmp-type 3 -j ACCEPT
    /usr/sbin/iptables -A openvpn_subnet -p icmp -m conntrack --ctstate RELATED,ESTABLISHED -m icmp --icmp-type 11 -j ACCEPT
    /usr/sbin/iptables -A openvpn_subnet -p icmp -m conntrack --ctstate RELATED,ESTABLISHED -m icmp --icmp-type 12 -j ACCEPT
    /usr/sbin/iptables -A openvpn_subnet -p icmp -m conntrack --ctstate RELATED,ESTABLISHED -m icmp --icmp-type 14 -j ACCEPT
    /usr/sbin/iptables -A openvpn_subnet -p icmp -m conntrack --ctstate RELATED,ESTABLISHED -m icmp --icmp-type 18 -j ACCEPT
    /usr/sbin/iptables -A openvpn_subnet -p icmp -m conntrack --ctstate RELATED,ESTABLISHED -m icmp --icmp-type 3/2 -j ACCEPT
    /usr/sbin/iptables -A openvpn_subnet -p icmp -m conntrack --ctstate RELATED,ESTABLISHED -m icmp --icmp-type 5 -j ACCEPT
    /usr/sbin/iptables -A openvpn_subnet -i tun0 -o eth0 -m conntrack --ctstate NEW -m limit --limit 10/min -j LOG --log-prefix "SFW2-FWDvpn-ACC " --log-tcp-options --log-ip-options
    /usr/sbin/iptables -A openvpn_subnet -i tun0 -o eth0 -m conntrack --ctstate NEW,RELATED,ESTABLISHED -j ACCEPT
    /usr/sbin/iptables -A openvpn_subnet -m pkttype --pkt-type multicast -j DROP
    /usr/sbin/iptables -A openvpn_subnet -m pkttype --pkt-type broadcast -j DROP
    /usr/sbin/iptables -A openvpn_subnet -p tcp -m limit --limit 3/min -m tcp --tcp-flags FIN,SYN,RST,ACK SYN -j LOG --log-prefix "SFW2-FWDvpn-DROP-DEFLT " --log-tcp-options --log-ip-options
    /usr/sbin/iptables -A openvpn_subnet -p icmp -m limit --limit 3/min -j LOG --log-prefix "SFW2-FWDvpn-DROP-DEFLT " --log-tcp-options --log-ip-options
    /usr/sbin/iptables -A openvpn_subnet -p udp -m limit --limit 3/min -m conntrack --ctstate NEW -j LOG --log-prefix "SFW2-FWDvpn-DROP-DEFLT " --log-tcp-options --log-ip-options
    /usr/sbin/iptables -A openvpn_subnet -j reject_func

    if [[ -f /etc/openvpn/openvpn-status.log ]]; then
        conns=`/usr/bin/egrep -e "^CLIENT_LIST,.+$" /etc/openvpn/openvpn-status.log`
        while read -r line; do
            if [[ $line =~ ^CLIENT_LIST,([^,]+),[0-9.]+:[0-9]+,([0-9.]+),[0-9]+,[0-9]+,.+$ ]]; then
                /bin/bash /etc/openvpn/learn-address.sh add ${BASH_REMATCH[2]} ${BASH_REMATCH[1]}
            fi
        done <<< "$conns"
    fi
    
    true
}

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

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

vi /etc/sysconfig/SuSEfirewall2

FW_CUSTOMRULES="/etc/sysconfig/scripts/SuSEfirewall2-custom

vi /etc/sysconfig/scripts/SuSEfirewall2-custom

fw_custom_after_finished() {

# openvpn config

# 2014-12 Aiden

/usr/sbin/iptables -t nat -D POSTROUTING -o tun0 -j MASQUERADE

/usr/sbin/iptables -t nat -A POSTROUTING -s 172.16.1.0/24 -o eth0 -j MASQUERADE

/usr/sbin/iptables -N forward_openvpn

/usr/sbin/iptables -A forward_openvpn -m conntrack --ctstate NEW -m limit --limit 3/min -j LOG --log-prefix "SFW2-FWDvpn-REJECT_DEFLT " --log-tcp-options --log-ip-options

/usr/sbin/iptables -A forward_openvpn -j reject_func

/usr/sbin/iptables -D FORWARD -i tun0 -j forward_ext

/usr/sbin/iptables -I FORWARD 4 -i tun0 -o eth0 -j forward_openvpn # before LOG

/usr/sbin/iptables -I INPUT -i tun0 -p tcp -m tcp --dport 22 -j ACCEPT

/usr/sbin/iptables -I INPUT 2 -i tun0 -j DROP

/usr/sbin/iptables -D forward_ext -i tun0 -o eth0 -m conntrack --ctstate RELATED,ESTABLISHED -j ACCEPT

/usr/sbin/iptables -I forward_ext 1 -i eth1 -m conntrack --ctstate NEW -j DROP

/usr/sbin/iptables -D forward_int -i eth0 -o tun0 -m conntrack --ctstate NEW,RELATED,ESTABLISHED -j ACCEPT

/usr/sbin/iptables -I forward_int 10 -i eth0 -o tun0 -m conntrack --ctstate RELATED,ESTABLISHED -j ACCEPT # before DROP

/usr/sbin/iptables -N openvpn_subnet

/usr/sbin/iptables -A openvpn_subnet -p icmp -m conntrack --ctstate RELATED,ESTABLISHED -m icmp --icmp-type 0 -j ACCEPT

/usr/sbin/iptables -A openvpn_subnet -p icmp -m conntrack --ctstate RELATED,ESTABLISHED -m icmp --icmp-type 3 -j ACCEPT

/usr/sbin/iptables -A openvpn_subnet -p icmp -m conntrack --ctstate RELATED,ESTABLISHED -m icmp --icmp-type 11 -j ACCEPT

/usr/sbin/iptables -A openvpn_subnet -p icmp -m conntrack --ctstate RELATED,ESTABLISHED -m icmp --icmp-type 12 -j ACCEPT

/usr/sbin/iptables -A openvpn_subnet -p icmp -m conntrack --ctstate RELATED,ESTABLISHED -m icmp --icmp-type 14 -j ACCEPT

/usr/sbin/iptables -A openvpn_subnet -p icmp -m conntrack --ctstate RELATED,ESTABLISHED -m icmp --icmp-type 18 -j ACCEPT

/usr/sbin/iptables -A openvpn_subnet -p icmp -m conntrack --ctstate RELATED,ESTABLISHED -m icmp --icmp-type 3/2 -j ACCEPT

/usr/sbin/iptables -A openvpn_subnet -p icmp -m conntrack --ctstate RELATED,ESTABLISHED -m icmp --icmp-type 5 -j ACCEPT

/usr/sbin/iptables -A openvpn_subnet -i tun0 -o eth0 -m conntrack --ctstate NEW -m limit --limit 10/min -j LOG --log-prefix "SFW2-FWDvpn-ACC " --log-tcp-options --log-ip-options

/usr/sbin/iptables -A openvpn_subnet -i tun0 -o eth0 -m conntrack --ctstate NEW,RELATED,ESTABLISHED -j ACCEPT

/usr/sbin/iptables -A openvpn_subnet -m pkttype --pkt-type multicast -j DROP

/usr/sbin/iptables -A openvpn_subnet -m pkttype --pkt-type broadcast -j DROP

/usr/sbin/iptables -A openvpn_subnet -p tcp -m limit --limit 3/min -m tcp --tcp-flags FIN,SYN,RST,ACK SYN -j LOG --log-prefix "SFW2-FWDvpn-DROP-DEFLT " --log-tcp-options --log-ip-options

/usr/sbin/iptables -A openvpn_subnet -p icmp -m limit --limit 3/min -j LOG --log-prefix "SFW2-FWDvpn-DROP-DEFLT " --log-tcp-options --log-ip-options

/usr/sbin/iptables -A openvpn_subnet -p udp -m limit --limit 3/min -m conntrack --ctstate NEW -j LOG --log-prefix "SFW2-FWDvpn-DROP-DEFLT " --log-tcp-options --log-ip-options

/usr/sbin/iptables -A openvpn_subnet -j reject_func

if [[ -f /etc/openvpn/openvpn-status.log ]]; then

conns=`/usr/bin/egrep -e "^CLIENT_LIST,.+$" /etc/openvpn/openvpn-status.log`

while read -r line; do

if [[ $line =~ ^CLIENT_LIST,([^,]+),[0-9.]+:[0-9]+,([0-9.]+),[0-9]+,[0-9]+,.+$ ]]; then

/bin/bash /etc/openvpn/learn-address.sh add ${BASH_REMATCH[2]} ${BASH_REMATCH[1]}

fi

done <<< "$conns"

fi

true

}

6. Configure IP setting shell script

vi /etc/openvpn/learn-address.sh

#!/bin/bash
# <author>Aiden</author>
# <modified>2014/12/08</modified>

function update_current_status
{
  /usr/bin/env python /etc/openvpn/update-status.py
}

function get_next_matching_firewall_rule
{
  ip_address=$1
  RULE=`/usr/sbin/iptables -L forward_openvpn -n --line-numbers | grep $ip_address | head -n 1`
}

function drop_rule_from_iptables
{
  rule=$1
  echo "  Drop rule [$rule]"
  line_number=`echo "$rule" | awk '{print $1}'`
  /usr/sbin/iptables -D forward_openvpn $line_number
}

function close_firewall
{
  echo "Closing firewall for [$CLIENTIP]"
  get_next_matching_firewall_rule $CLIENTIP

  while [ -n "$RULE" ]
  do
    drop_rule_from_iptables "$RULE"
    get_next_matching_firewall_rule $ip_address
  done
}

function open_firewall
{
  echo "Opening firewall for $CLIENTCERT @ [$CLIENTIP]"
  SERVICE=`echo $CLIENTCERT| cut -d'-' -f 2`
  case $SERVICE in
    "admin")
      /usr/sbin/iptables -I forward_openvpn -s $CLIENTIP -d 10.1.0.0/16 -j openvpn_subnet
      return 0
      ;;
    "subadmin")
      /usr/sbin/iptables -I forward_openvpn -s $CLIENTIP -d 10.1.1.0/24 -j openvpn_subnet
      /usr/sbin/iptables -I forward_openvpn -s $CLIENTIP -d 10.1.2.0/24 -j openvpn_subnet
      /usr/sbin/iptables -I forward_openvpn -s $CLIENTIP -d 10.1.3.0/24 -j openvpn_subnet
      return 0
      ;;
    "member01")
      /usr/sbin/iptables -I forward_openvpn -s $CLIENTIP -d 10.1.1.0/24 -j openvpn_subnet
      return 0
      ;;
    "member02")
      /usr/sbin/iptables -I forward_openvpn -s $CLIENTIP -d 10.1.2.0/24 -j openvpn_subnet
      return 0
      ;;
    "member03")
      /usr/sbin/iptables -I forward_openvpn -s $CLIENTIP -d 10.1.3.0/24 -j openvpn_subnet
      return 0
      ;;
    *)
      echo "  Error, Unknown certification [$CLIENTCERT]"
      return 1
      ;;
  esac
}

# Main
RET=-1
OPERATION=$1
CLIENTIP=$2
CLIENTCERT=$3

if [[ ! $CLIENTIP =~ ^[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}$ ]]; then
  echo "Error, Illegal ip address [$CLIENTIP]"
  exit 1
fi
if [[ ! $CLIENTCERT =~ ^[0-9A-Za-z]{1,10}-[0-9A-Za-z]{1,10}-[0-9]{2}$ ]] && [ $OPERATION != "delete" ]; then
  echo "Error, Illegal certification [$CLIENTCERT]"
  exit 1
fi

case $OPERATION in
  add)
    close_firewall
    open_firewall
    RET=$?
    update_current_status    
    ;;
  update)
    close_firewall
    open_firewall
    RET=$?
    update_current_status    
    ;;
  delete)
    close_firewall
    RET=$?
    update_current_status    
    ;;
  *)
    echo "Error, Unknown operation"
    RET=1
esac

exit $RET


chmod 700 /etc/openvpn/learn-address.sh

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

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

vi /etc/openvpn/learn-address.sh

#!/bin/bash

# <author>Aiden</author>

# <modified>2014/12/08</modified>

function update_current_status

{

/usr/bin/env python /etc/openvpn/update-status.py

}

function get_next_matching_firewall_rule

{

ip_address=$1

RULE=`/usr/sbin/iptables -L forward_openvpn -n --line-numbers | grep $ip_address | head -n 1`

}

function drop_rule_from_iptables

{

rule=$1

echo " Drop rule [$rule]"

line_number=`echo "$rule" | awk '{print $1}'`

/usr/sbin/iptables -D forward_openvpn $line_number

}

function close_firewall

{

echo "Closing firewall for [$CLIENTIP]"

get_next_matching_firewall_rule $CLIENTIP

while [ -n "$RULE" ]

do

drop_rule_from_iptables "$RULE"

get_next_matching_firewall_rule $ip_address

done

}

function open_firewall

{

echo "Opening firewall for $CLIENTCERT @ [$CLIENTIP]"

SERVICE=`echo $CLIENTCERT| cut -d'-' -f 2`

case $SERVICE in

"admin")

/usr/sbin/iptables -I forward_openvpn -s $CLIENTIP -d 10.1.0.0/16 -j openvpn_subnet

return 0

;;

"subadmin")

/usr/sbin/iptables -I forward_openvpn -s $CLIENTIP -d 10.1.1.0/24 -j openvpn_subnet

/usr/sbin/iptables -I forward_openvpn -s $CLIENTIP -d 10.1.2.0/24 -j openvpn_subnet

/usr/sbin/iptables -I forward_openvpn -s $CLIENTIP -d 10.1.3.0/24 -j openvpn_subnet

return 0

;;

"member01")

/usr/sbin/iptables -I forward_openvpn -s $CLIENTIP -d 10.1.1.0/24 -j openvpn_subnet

return 0

;;

"member02")

/usr/sbin/iptables -I forward_openvpn -s $CLIENTIP -d 10.1.2.0/24 -j openvpn_subnet

return 0

;;

"member03")

/usr/sbin/iptables -I forward_openvpn -s $CLIENTIP -d 10.1.3.0/24 -j openvpn_subnet

return 0

;;

*)

echo " Error, Unknown certification [$CLIENTCERT]"

return 1

;;

esac

}

# Main

RET=-1

OPERATION=$1

CLIENTIP=$2

CLIENTCERT=$3

if [[ ! $CLIENTIP =~ ^[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}$ ]]; then

echo "Error, Illegal ip address [$CLIENTIP]"

exit 1

fi

if [[ ! $CLIENTCERT =~ ^[0-9A-Za-z]{1,10}-[0-9A-Za-z]{1,10}-[0-9]{2}$ ]] && [ $OPERATION != "delete" ]; then

echo "Error, Illegal certification [$CLIENTCERT]"

exit 1

fi

case $OPERATION in

add)

close_firewall

open_firewall

RET=$?

update_current_status

;;

update)

close_firewall

open_firewall

RET=$?

update_current_status

;;

delete)

close_firewall

RET=$?

update_current_status

;;

*)

echo "Error, Unknown operation"

RET=1

esac

exit $RET

chmod 700 /etc/openvpn/learn-address.sh

7. Status file script and add to cron job

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# vim: set noexpandtab:ts=4

# <author>Aiden</author>
# <modified>2014/12/17</modified>

import os, os.path
import sys
import re
import time
import datetime
import sqlite3 as lite


# Constants

sqlite_file = 'openvpn-client_log'
pid_path = '/var/run/openvpn/server.pid'
status_path = '/etc/openvpn/openvpn-status.log'
sqlite_path = '/var/log/openvpn'
summary_path = '/etc/openvpn/openvpn-status'


# Function definition

def ts2str(timestamp):
	return datetime.datetime.fromtimestamp(int(timestamp)).strftime('%Y-%m-%d %H:%M:%S')
def str2ts(timestr):
	return time.mktime(datetime.datetime.strptime(timestr,'%Y-%m-%d %H:%M:%S').timetuple())


# Main

cur_time = int(time.time())
sqlite_full_path = '{0}-{1}.sqlite'.format(os.path.join(sqlite_path,sqlite_file), datetime.datetime.fromtimestamp(cur_time).strftime('%Y%m'))
client_pat = re.compile(r'^CLIENT_LIST,([^,]+),([0-9.]+):([0-9]+),([0-9.]+),([0-9]+),([0-9]+),([^,]+),([0-9]+),(\w+)$')
route_pat = re.compile(r'^ROUTING_TABLE,([0-9.]+),([^,]+),([0-9.]+):([0-9]+),([^,]+),([0-9]+)$')
global_pat = re.compile(r'^GLOBAL_STATS,[^,]+,([0-9]+)$')

conn = None
client_cnt = 0
route_cnt = 0
bmcast_queue = 0

try:
	
	conn = lite.connect(sqlite_full_path)
	cur = conn.cursor()
	
	cur.executescript("""
		CREATE TABLE IF NOT EXISTS "client_log" ("start_time" DATETIME NOT NULL , "certificate" TEXT NOT NULL , "virtual_ip" TEXT NOT NULL , "account" TEXT, "remote_ip" TEXT, "remote_port" INTEGER, "received" INTEGER NOT NULL  DEFAULT 0, "sent" INTEGER NOT NULL  DEFAULT 0, "end_time" DATETIME, "update_time" DATETIME NOT NULL  DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("start_time", "certificate", "virtual_ip"));
		CREATE TABLE IF NOT EXISTS "status_log" ("time" DATETIME PRIMARY KEY  NOT NULL  UNIQUE  DEFAULT CURRENT_TIMESTAMP, "client_cnt" INTEGER NOT NULL  DEFAULT 0, "route_cnt" INTEGER NOT NULL  DEFAULT 0, "bmcast_queue" INTEGER NOT NULL  DEFAULT 0, "update_time" DATETIME NOT NULL  DEFAULT CURRENT_TIMESTAMP);
		""")
	conn.commit()

	if (os.path.exists(pid_path) and os.path.isfile(pid_path)):

		with open(status_path,'rt') as fd:
			for line in fd:
				client_mat = client_pat.match(line)
				global_mat = global_pat.match(line)
				if client_mat:
					client_cnt += 1
					(cert_name,r_address,r_port,v_address,received,sent,c_time,c_timeval,username) = client_mat.groups()
					cur.execute("INSERT OR REPLACE INTO client_log (start_time,certificate,virtual_ip,account,remote_ip,remote_port,received,sent,end_time,update_time) VALUES ('{0}','{1}','{2}','{3}','{4}',{5},{6},{7},NULL,'{8}')"
								.format(ts2str(c_timeval),cert_name,v_address,username,r_address,r_port,received,sent,ts2str(cur_time)))
				elif route_pat.match(line):
					route_cnt += 1
				elif global_mat:
					bmcast_queue = int(global_mat.group(1))

		cur.execute("UPDATE client_log SET end_time='{0}', update_time='{1}' WHERE end_time IS NULL AND update_time<'{2}'".format(ts2str(cur_time),ts2str(cur_time),ts2str(cur_time)))
		cur.execute("SELECT time, client_cnt,route_cnt,bmcast_queue FROM status_log ORDER BY time DESC LIMIT 1")
		row = cur.fetchone()
		if (row == None) or ((str2ts(row[0])+1800) <= cur_time) or (int(row[1]) != client_cnt) or (int(row[2]) != route_cnt) or (int(row[3]) != bmcast_queue):
			cur.execute("INSERT OR REPLACE INTO status_log (time,client_cnt,route_cnt,bmcast_queue,update_time) VALUES ('{0}',{1},{2},{3},'{4}')".format(ts2str(cur_time),client_cnt,route_cnt,bmcast_queue,ts2str(cur_time)))
		else:
			cur.execute("UPDATE status_log SET update_time='{0}' WHERE time='{1}'".format(ts2str(cur_time),row[0]))
		conn.commit()			

		with open(summary_path,'wt') as sfd:
			sfd.write('connections:{0}\nroutes:{1}\nmaxqueue:{2}'.format(client_cnt,route_cnt,bmcast_queue))

	else:

		os.remove(summary_path) 

except lite.Error, e:
    
    print "Error %s:" % e.args[0]
    if conn:
    	conn.rollback()    
    sys.exit(1)

finally:
    
    if conn:
        conn.close()

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

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

#!/usr/bin/env python

# -*- coding: utf-8 -*-

# vim: set noexpandtab:ts=4

# <author>Aiden</author>

# <modified>2014/12/17</modified>

import os, os.path

import sys

import re

import time

import datetime

import sqlite3 as lite

# Constants

sqlite_file = 'openvpn-client_log'

pid_path = '/var/run/openvpn/server.pid'

status_path = '/etc/openvpn/openvpn-status.log'

sqlite_path = '/var/log/openvpn'

summary_path = '/etc/openvpn/openvpn-status'

# Function definition

def ts2str(timestamp):

return datetime.datetime.fromtimestamp(int(timestamp)).strftime('%Y-%m-%d %H:%M:%S')

def str2ts(timestr):

return time.mktime(datetime.datetime.strptime(timestr,'%Y-%m-%d %H:%M:%S').timetuple())

# Main

cur_time = int(time.time())

sqlite_full_path = '{0}-{1}.sqlite'.format(os.path.join(sqlite_path,sqlite_file), datetime.datetime.fromtimestamp(cur_time).strftime('%Y%m'))

client_pat = re.compile(r'^CLIENT_LIST,([^,]+),([0-9.]+):([0-9]+),([0-9.]+),([0-9]+),([0-9]+),([^,]+),([0-9]+),(\w+)$')

route_pat = re.compile(r'^ROUTING_TABLE,([0-9.]+),([^,]+),([0-9.]+):([0-9]+),([^,]+),([0-9]+)$')

global_pat = re.compile(r'^GLOBAL_STATS,[^,]+,([0-9]+)$')

conn = None

client_cnt = 0

route_cnt = 0

bmcast_queue = 0

try:

conn = lite.connect(sqlite_full_path)

cur = conn.cursor()

cur.executescript("""

CREATE TABLE IF NOT EXISTS "client_log" ("start_time" DATETIME NOT NULL , "certificate" TEXT NOT NULL , "virtual_ip" TEXT NOT NULL , "account" TEXT, "remote_ip" TEXT, "remote_port" INTEGER, "received" INTEGER NOT NULL DEFAULT 0, "sent" INTEGER NOT NULL DEFAULT 0, "end_time" DATETIME, "update_time" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("start_time", "certificate", "virtual_ip"));

CREATE TABLE IF NOT EXISTS "status_log" ("time" DATETIME PRIMARY KEY NOT NULL UNIQUE DEFAULT CURRENT_TIMESTAMP, "client_cnt" INTEGER NOT NULL DEFAULT 0, "route_cnt" INTEGER NOT NULL DEFAULT 0, "bmcast_queue" INTEGER NOT NULL DEFAULT 0, "update_time" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);

""")

conn.commit()

if (os.path.exists(pid_path) and os.path.isfile(pid_path)):

with open(status_path,'rt') as fd:

for line in fd:

client_mat = client_pat.match(line)

global_mat = global_pat.match(line)

if client_mat:

client_cnt += 1

(cert_name,r_address,r_port,v_address,received,sent,c_time,c_timeval,username) = client_mat.groups()

cur.execute("INSERT OR REPLACE INTO client_log (start_time,certificate,virtual_ip,account,remote_ip,remote_port,received,sent,end_time,update_time) VALUES ('{0}','{1}','{2}','{3}','{4}',{5},{6},{7},NULL,'{8}')"

.format(ts2str(c_timeval),cert_name,v_address,username,r_address,r_port,received,sent,ts2str(cur_time)))

elif route_pat.match(line):

route_cnt += 1

elif global_mat:

bmcast_queue = int(global_mat.group(1))

cur.execute("UPDATE client_log SET end_time='{0}', update_time='{1}' WHERE end_time IS NULL AND update_time<'{2}'".format(ts2str(cur_time),ts2str(cur_time),ts2str(cur_time)))

cur.execute("SELECT time, client_cnt,route_cnt,bmcast_queue FROM status_log ORDER BY time DESC LIMIT 1")

row = cur.fetchone()

if (row == None) or ((str2ts(row[0])+1800) <= cur_time) or (int(row[1]) != client_cnt) or (int(row[2]) != route_cnt) or (int(row[3]) != bmcast_queue):

cur.execute("INSERT OR REPLACE INTO status_log (time,client_cnt,route_cnt,bmcast_queue,update_time) VALUES ('{0}',{1},{2},{3},'{4}')".format(ts2str(cur_time),client_cnt,route_cnt,bmcast_queue,ts2str(cur_time)))

else:

cur.execute("UPDATE status_log SET update_time='{0}' WHERE time='{1}'".format(ts2str(cur_time),row[0]))

conn.commit()

with open(summary_path,'wt') as sfd:

sfd.write('connections:{0}\nroutes:{1}\nmaxqueue:{2}'.format(client_cnt,route_cnt,bmcast_queue))

else:

os.remove(summary_path)

except lite.Error, e:

print "Error %s:" % e.args[0]

if conn:

conn.rollback()

sys.exit(1)

finally:

if conn:

conn.close()

chmod 700 /etc/openvpn/update-status.py
mkdir -p /var/log/openvpn


vi /etc/cron.d/update-openvpn-status 

*/1 * * * * root /usr/bin/env python /etc/openvpn/update-status.py 2>&1

chmod 644 /etc/cron.d/update-openvpn-status
service cron restart

1

2

3

4

5

6

7

8

9

10

chmod 700 /etc/openvpn/update-status.py

mkdir -p /var/log/openvpn

vi /etc/cron.d/update-openvpn-status

*/1 * * * * root /usr/bin/env python /etc/openvpn/update-status.py 2>&1

chmod 644 /etc/cron.d/update-openvpn-status

service cron restart

8. Configure OpenVPN and Start service Run yast Add network device “tun0” type TUN Assign tun0 to External Network at Firewall 9. Configure OpenVPN and Start service

cp /usr/share/doc/packages/openvpn/sample-config-files/server.conf /etc/openvpn/
vi /etc/openvpn/server.conf

local 210.1.1.1
port 1194
proto udp
;dev tap
dev tun0

ca /etc/openvpn/ca.crt
crl-verify /etc/openvpn/crl.pem
cert /etc/openvpn/server.crt
key /etc/openvpn/server.key
dh /etc/openvpn/dh2048.pem

server 172.16.1.0 255.255.255.0
ifconfig-pool-persist ipp.txt
push "route 10.1.0.0 255.255.0.0"
learn-address /etc/openvpn/learn-address.sh
script-security 2
;push "redirect-gateway def1 bypass-dhcp"

comp_lzo
max-clients 20
;user nobody
;group nobody

persist-key
persist-tun

status /etc/openvpn/openvpn-status.log 20
status-version 2

verb 2

# Aiden
syslog openvpn
plugin /usr/lib64/openvpn/plugins/openvpn-plugin-auth-pam.so openvpn
;client-connect /etc/openvpn/client-connect.sh
;client-disconnect /etc/openvpn/client-disconnect.sh


systemctl start openvpn@server.service
systemctl enable openvpn@server.service
chmod o+x /var/run/openvpn

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

36

37

38

39

40

41

42

43

44

45

cp /usr/share/doc/packages/openvpn/sample-config-files/server.conf /etc/openvpn/

vi /etc/openvpn/server.conf

local 210.1.1.1

port 1194

proto udp

;dev tap

dev tun0

ca /etc/openvpn/ca.crt

crl-verify /etc/openvpn/crl.pem

cert /etc/openvpn/server.crt

key /etc/openvpn/server.key

dh /etc/openvpn/dh2048.pem

server 172.16.1.0 255.255.255.0

ifconfig-pool-persist ipp.txt

push "route 10.1.0.0 255.255.0.0"

learn-address /etc/openvpn/learn-address.sh

script-security 2

;push "redirect-gateway def1 bypass-dhcp"

comp_lzo

max-clients 20

;user nobody

;group nobody

persist-key

persist-tun

status /etc/openvpn/openvpn-status.log 20

status-version 2

verb 2

# Aiden

syslog openvpn

plugin /usr/lib64/openvpn/plugins/openvpn-plugin-auth-pam.so openvpn

;client-connect /etc/openvpn/client-connect.sh

;client-disconnect /etc/openvpn/client-disconnect.sh

systemctl start openvpn@server.service

systemctl enable openvpn@server.service

chmod o+x /var/run/openvpn

∗ Create new client certificate ‥‥‥

2014-12-02 System Engineering No comments

Enable SFTP Logging on OpenSUSE

1. Update rsyslog(syslog) configuration

vi /etc/rsyslog.conf

#sftp logging
local5.*                                -/var/log/sftpd.log

service rsyslog restart

1

2

3

4

5

6

vi /etc/rsyslog.conf

#sftp logging

local5.* -/var/log/sftpd.log

service rsyslog restart

2. SSHD Configuration file

vi /etc/ssh/sshd_config 

Subsystem sftp /usr/lib/ssh/sftp-server -f LOCAL5 -l INFO

service sshd restart

1

2

3

4

5

vi /etc/ssh/sshd_config

Subsystem sftp /usr/lib/ssh/sftp-server -f LOCAL5 -l INFO

service sshd restart

2014-12-02 System Engineering No comments

Chroot for SFTP on OpenSUSE

1. Add chroot to SSHD Configuration file

vi /etc/ssh/sshd_config

Match Group uploaders,!admin
  X11Forwarding no
  AllowTcpForwarding no
  ForceCommand internal-sftp
  ChrootDirectory /home/uploaders

Match User cacti Address *,!10.10.1.0/24
  ForceCommand /bin/false

1

2

3

4

5

6

7

8

9

10

vi /etc/ssh/sshd_config

Match Group uploaders,!admin

X11Forwarding no

AllowTcpForwarding no

ForceCommand internal-sftp

ChrootDirectory /home/uploaders

Match User cacti Address *,!10.10.1.0/24

ForceCommand /bin/false

2. Mount directory to home directory

vi /etc/fstab

/home/www/www.mysite.com /home/uploaders/www.mysite.com none bind 0 0

1

2

3

vi /etc/fstab

/home/www/www.mysite.com /home/uploaders/www.mysite.com none bind 0 0

2014-09-30 Developments No comments

MySQL, Checking the IP address in a range

fn_ipv4_match( needle, haystack ) Returns 1 if haystack contains needle. needle : IPv4 address haystack : IPv4 address, IPv4 address with subnet mask, IPv4 address with netmask bits

DELIMITER $$

DROP FUNCTION IF EXISTS `fn_ipv4_match`$$

CREATE FUNCTION `fn_ipv4_match`(
  needle    VARCHAR(15),
  haystack  VARCHAR(31)
) RETURNS TINYINT
BEGIN
/*
 * Checking the IP address in the range
 * 2014-09-30
 * Aiden Kim
 */
	
	DECLARE start_ip VARCHAR(15) DEFAULT NULL;
	DECLARE netmask VARCHAR(15) DEFAULT NULL;
	DECLARE netmask_bits TINYINT DEFAULT NULL;
	DECLARE search_ip_val INT UNSIGNED DEFAULT NULL;
	DECLARE start_ip_val INT UNSIGNED DEFAULT NULL;
	DECLARE end_ip_val INT UNSIGNED DEFAULT NULL;
	DECLARE result TINYINT DEFAULT 0;
	
	-- Split Network & Netmask
	SET start_ip = SUBSTRING_INDEX(haystack, '/', 1);
	SET netmask = SUBSTRING_INDEX(haystack, '/', -1);
	SET start_ip_val = INET_ATON(start_ip);
	
	-- Calculate the range
	IF (netmask = '') OR (start_ip = netmask) THEN
		SET end_ip_val = start_ip_val;
	ELSE
		IF (INSTR(netmask,'.') > 0) THEN
			-- Subnet mask
			SET netmask_bits = 32-LOG2((4294967296-INET_ATON(netmask)));
			SET start_ip_val = (-1 << (32-netmask_bits)) & start_ip_val;
			SET end_ip_val = start_ip_val + 4294967295 - INET_ATON(netmask);
		ELSE
			-- Subnet bits
			SET netmask_bits = CONVERT(netmask, UNSIGNED);
			SET start_ip_val = (-1 << (32-netmask_bits)) & start_ip_val;
			SET end_ip_val = start_ip_val + 4294967295 - (((POWER(2,32)-1)<<(32-netmask_bits)) & (POWER(2,32)-1));
		END IF;
	END IF;
	
	-- Compare 
	IF (INET_ATON(needle) BETWEEN start_ip_val AND end_ip_val) THEN
		SET result = 1;
	END IF;
	
	RETURN result;
	
END$$

DELIMITER ;

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

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

DELIMITER $$

DROP FUNCTION IF EXISTS `fn_ipv4_match`$$

CREATE FUNCTION `fn_ipv4_match`(

needle VARCHAR(15),

haystack VARCHAR(31)

) RETURNS TINYINT

BEGIN

/*

* Checking the IP address in the range

* 2014-09-30

* Aiden Kim

*/

DECLARE start_ip VARCHAR(15) DEFAULT NULL;

DECLARE netmask VARCHAR(15) DEFAULT NULL;

DECLARE netmask_bits TINYINT DEFAULT NULL;

DECLARE search_ip_val INT UNSIGNED DEFAULT NULL;

DECLARE start_ip_val INT UNSIGNED DEFAULT NULL;

DECLARE end_ip_val INT UNSIGNED DEFAULT NULL;

DECLARE result TINYINT DEFAULT 0;

-- Split Network & Netmask

SET start_ip = SUBSTRING_INDEX(haystack, '/', 1);

SET netmask = SUBSTRING_INDEX(haystack, '/', -1);

SET start_ip_val = INET_ATON(start_ip);

-- Calculate the range

IF (netmask = '') OR (start_ip = netmask) THEN

SET end_ip_val = start_ip_val;

ELSE

IF (INSTR(netmask,'.') > 0) THEN

-- Subnet mask

SET netmask_bits = 32-LOG2((4294967296-INET_ATON(netmask)));

SET start_ip_val = (-1 << (32-netmask_bits)) & start_ip_val;

SET end_ip_val = start_ip_val + 4294967295 - INET_ATON(netmask);

ELSE

-- Subnet bits

SET netmask_bits = CONVERT(netmask, UNSIGNED);

SET start_ip_val = (-1 << (32-netmask_bits)) & start_ip_val;

SET end_ip_val = start_ip_val + 4294967295 - (((POWER(2,32)-1)<<(32-netmask_bits)) & (POWER(2,32)-1));

END IF;

-- Compare

IF (INET_ATON(needle) BETWEEN start_ip_val AND end_ip_val) THEN

SET result = 1;

END IF;

RETURN result;

END$$

DELIMITER ;

Results

mysql> SELECT fn_ipv4_match('1.2.3.88','1.2.3.88');
+--------------------------------------+
| fn_ipv4_match('1.2.3.88','1.2.3.88') |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT fn_ipv4_match('1.2.3.21','1.2.3.22');
+--------------------------------------+
| fn_ipv4_match('1.2.3.21','1.2.3.22') |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT fn_ipv4_match('1.2.3.139','1.2.3.160/255.255.255.192');
+--------------------------------------------------------+
| fn_ipv4_match('1.2.3.139','1.2.3.130/255.255.255.192') |
+--------------------------------------------------------+
|                                                      1 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT fn_ipv4_match('1.2.3.127','1.2.3.160/25');
+-------------------------------------------+
| fn_ipv4_match('1.2.3.127','1.2.3.130/25') |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT fn_ipv4_match('2.3.4.5','1.2.3.0/8');
+--------------------------------------+
| fn_ipv4_match('2.3.4.5','1.2.3.0/8') |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.00 sec)

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

36

37

38

39

mysql> SELECT fn_ipv4_match('1.2.3.88','1.2.3.88');

+--------------------------------------+

| fn_ipv4_match('1.2.3.88','1.2.3.88') |

+--------------------------------------+

| 1 |

+--------------------------------------+

1 row in set (0.00 sec)

mysql> SELECT fn_ipv4_match('1.2.3.21','1.2.3.22');

+--------------------------------------+

| fn_ipv4_match('1.2.3.21','1.2.3.22') |

+--------------------------------------+

| 0 |

+--------------------------------------+

1 row in set (0.00 sec)

mysql> SELECT fn_ipv4_match('1.2.3.139','1.2.3.160/255.255.255.192');

+--------------------------------------------------------+

| fn_ipv4_match('1.2.3.139','1.2.3.130/255.255.255.192') |

+--------------------------------------------------------+

| 1 |

+--------------------------------------------------------+

1 row in set (0.00 sec)

mysql> SELECT fn_ipv4_match('1.2.3.127','1.2.3.160/25');

+-------------------------------------------+

| fn_ipv4_match('1.2.3.127','1.2.3.130/25') |

+-------------------------------------------+

| 0 |

+-------------------------------------------+

1 row in set (0.00 sec)

mysql> SELECT fn_ipv4_match('2.3.4.5','1.2.3.0/8');

+--------------------------------------+

| fn_ipv4_match('2.3.4.5','1.2.3.0/8') |

+--------------------------------------+

| 0 |

+--------------------------------------+

1 row in set (0.00 sec)

2014-09-01 System Engineering No comments

Install DKIMproxy on OpenSUSE

1. Install requirements

zypper install dkimproxy

1	zypper install dkimproxy

2. Create keys

cd /usr/share/dkimproxy/etc
openssl genrsa -out dkim-private.key 1024
openssl rsa -in dkim-private.key -pubout -out dkim-public.key
chown dkim dkim-private.key
chmod 400 dkim-private.key

1

2

3

4

5

cd /usr/share/dkimproxy/etc

openssl genrsa -out dkim-private.key 1024

openssl rsa -in dkim-private.key -pubout -out dkim-public.key

chown dkim dkim-private.key

chmod 400 dkim-private.key

3. Configuration file

cp dkimproxy_in.conf.example dkimproxy_in.conf
cp dkimproxy_out.conf.example dkimproxy_out.conf
vi dkimproxy_out.conf

# add your domain of sender ...
domain    domain1.com,domain2.com
# locate private-key file ...
keyfile   /usr/share/dkimproxy/etc/dkim-private.key
# define selector of DNS record ...
selector  default

1

2

3

4

5

6

7

8

9

10

cp dkimproxy_in.conf.example dkimproxy_in.conf

cp dkimproxy_out.conf.example dkimproxy_out.conf

vi dkimproxy_out.conf

# add your domain of sender ...

domain domain1.com,domain2.com

# locate private-key file ...

keyfile /usr/share/dkimproxy/etc/dkim-private.key

# define selector of DNS record ...

selector default

4. Setting up postfix

vi /etc/postfix/master.cf:

#
# modify the default submission service to specify a content filter
# and restrict it to local clients and SASL authenticated clients only
#
submission  inet  n     -       n       -       -       smtpd
    -o smtpd_etrn_restrictions=reject
    -o smtpd_sasl_auth_enable=yes
    -o content_filter=dksign:[127.0.0.1]:10027
    -o receive_override_options=no_address_mappings
    -o smtpd_recipient_restrictions=permit_mynetworks,permit_sasl_authenticated,reject

#
# specify the location of the DKIM signing proxy
# Note: we allow "4" simultaneous deliveries here; high-volume sites may
#   want a number higher than 4.
# Note: the smtp_discard_ehlo_keywords option requires Postfix 2.2 or
#   better. Leave it off if your version does not support it.
#
dksign    unix  -       -       n       -       4       smtp
    -o smtp_send_xforward_command=yes
    -o smtp_discard_ehlo_keywords=8bitmime,starttls

#
# service for accepting messages FROM the DKIM signing proxy
#
127.0.0.1:10028 inet  n  -      n       -       10      smtpd
    -o content_filter=
    -o receive_override_options=no_unknown_recipient_checks,no_header_body_checks
    -o smtpd_helo_restrictions=
    -o smtpd_client_restrictions=
    -o smtpd_sender_restrictions=
    -o smtpd_recipient_restrictions=permit_mynetworks,reject
    -o mynetworks=127.0.0.0/8
    -o smtpd_authorized_xforward_hosts=127.0.0.0/8

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

36

vi /etc/postfix/master.cf:

#

# modify the default submission service to specify a content filter

# and restrict it to local clients and SASL authenticated clients only

#

submission inet n - n - - smtpd

-o smtpd_etrn_restrictions=reject

-o smtpd_sasl_auth_enable=yes

-o content_filter=dksign:[127.0.0.1]:10027

-o receive_override_options=no_address_mappings

-o smtpd_recipient_restrictions=permit_mynetworks,permit_sasl_authenticated,reject

#

# specify the location of the DKIM signing proxy

# Note: we allow "4" simultaneous deliveries here; high-volume sites may

# want a number higher than 4.

# Note: the smtp_discard_ehlo_keywords option requires Postfix 2.2 or

# better. Leave it off if your version does not support it.

#

dksign unix - - n - 4 smtp

-o smtp_send_xforward_command=yes

-o smtp_discard_ehlo_keywords=8bitmime,starttls

#

# service for accepting messages FROM the DKIM signing proxy

#

127.0.0.1:10028 inet n - n - 10 smtpd

-o content_filter=

-o receive_override_options=no_unknown_recipient_checks,no_header_body_checks

-o smtpd_helo_restrictions=

-o smtpd_client_restrictions=

-o smtpd_sender_restrictions=

-o smtpd_recipient_restrictions=permit_mynetworks,reject

-o mynetworks=127.0.0.0/8

-o smtpd_authorized_xforward_hosts=127.0.0.0/8

5. Restart services

chkconfig --add dkimproxy
systemctl restart dkimproxy.service
systemctl restart postfix.service

1

2

3

chkconfig --add dkimproxy

systemctl restart dkimproxy.service

systemctl restart postfix.service

6. Add DNS record (sender’s domain)

# add your public-key to p= ...
default._domainkey  IN  TXT  "v=DKIM1; k=rsa; g=*; t=s; p=MHwwDQYJK ... OprwIDAQAB"

# if error in bind, split key like below ...
default._domainkey  IN  TXT  ("v=DKIM1; k=rsa; g=*; t=s; p="
                             "MHwwDQYJKasdE324asAHTDFDSADDAFGffdsdASDsasdOprwIDAQAB"
                             "ADDAFGffdsdASDsasdOprwIDAQABMHwwDQYJKasdE324asAHTDFDS"
                             ..............
                             "AHTDFDSADDAFGffdsdASDsasdOprwIMHwwDQYJKasdE324asDAQAB")

1

2

3

4

5

6

7

8

9

# add your public-key to p= ...

default._domainkey IN TXT "v=DKIM1; k=rsa; g=*; t=s; p=MHwwDQYJK ... OprwIDAQAB"

# if error in bind, split key like below ...

default._domainkey IN TXT ("v=DKIM1; k=rsa; g=*; t=s; p="

"MHwwDQYJKasdE324asAHTDFDSADDAFGffdsdASDsasdOprwIDAQAB"

"ADDAFGffdsdASDsasdOprwIDAQABMHwwDQYJKasdE324asAHTDFDS"

..............

"AHTDFDSADDAFGffdsdASDsasdOprwIMHwwDQYJKasdE324asDAQAB")

♦ References http://dkimproxy.sourceforge.net/usage.html

Geek's Rhapsody

에이든의 기술정보 블로그 (Aiden's blog)

AWS CodeDeploy 설정 (S3)

rsyslog + MySQL + LogAnalyzer on OpenSUSE

Upload PKCS#12 Server Certificates to AWS

Using request of JSON Payload in Slim framework

MySQL Procedure Pattern for Nested Transaction

Install OpenVPN on openSUSE

Enable SFTP Logging on OpenSUSE

Chroot for SFTP on OpenSUSE

MySQL, Checking the IP address in a range

Install DKIMproxy on OpenSUSE

Recent Posts

Archives

Categories

About Aiden Kim

Meta

Banner

에이든의 기술정보 블로그 (Aiden's blog)

Recent Posts

Archives

Categories

Tags

About Aiden Kim

Meta

Banner