Upload PKCS#12 Server Certificates to AWS
1. Extract RSA Private 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
|
1 |
openssl pkcs12 -in CERTIFICATE.PFX -clcerts -nokeys -out certificate.crt |
3. Extract Certificate Chains
|
1 |
openssl pkcs12 -in CERTIFICATE.PFX -cacerts -nokeys -out certificate-chain.crt |
4. Trimming Certificates for AWS
|
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
|
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 |
Using request of JSON Payload in Slim framework
How to enable JSON-Payload input in Slim Framework… SlimMiddlewareContentTypes.php
|
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
|
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(); ?> |
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 ‥‥‥
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
|
1 |
zypper install openvpn openvpn-auth-pam-plugin |
2. Install EasyRSA and create server ertificates
|
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
|
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
|
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
|
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
|
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
|
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() |
|
1 2 3 4 5 6 7 8 |
chmod 700 /etc/openvpn/update-status.py 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
|
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 ‥‥‥
Enable SFTP Logging on OpenSUSE
1. Update rsyslog(syslog) configuration
|
1 2 3 4 5 6 |
vi /etc/rsyslog.conf #sftp logging local5.* -/var/log/sftpd.log service rsyslog restart |
2. SSHD Configuration file
|
1 2 3 4 5 |
vi /etc/ssh/sshd_config Subsystem sftp /usr/lib/ssh/sftp-server -f LOCAL5 -l INFO service sshd restart |
Chroot for SFTP on OpenSUSE
1. Add chroot to SSHD Configuration file
|
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
|
1 2 3 |
vi /etc/fstab /home/www/www.mysite.com /home/uploaders/www.mysite.com none bind 0 0 |
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
|
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; 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
|
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) |
Install DKIMproxy on OpenSUSE
1. Install requirements
|
1 |
zypper install dkimproxy |
2. Create keys
|
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
|
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
|
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
|
1 2 3 |
chkconfig --add dkimproxy systemctl restart dkimproxy.service systemctl restart postfix.service |
6. Add DNS record (sender’s domain)
|
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
Install nginx + php-fpm on openSUSE
1. Download and install packages
|
1 2 |
zypper install nginx php5-fpm chmod 1733 /var/lib/php5 |
2. Configure php-fpm
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
cp -p /etc/php5/fpm/php-fpm.conf.default /etc/php5/fpm/php-fpm.conf vi /etc/php5/fpm/php-fpm.conf include=/etc/php5/fpm/vhosts.d/*.conf pm.max_children = 30 rlimit_files = 102400 catch_workers_output = yes emergency_restart_threshold 10 emergency_restart_interval 30s process_control_timeout 10s ;listen = 127.0.0.1:9000 listen = /var/run/php5-fpm.sock listen.owner = nginx error_log = /var/log/php-fpm/php-fpm.log security.limit_extensions = .php .do .json .soap |
3. Configure php-fpm
|
1 2 3 4 |
cp /etc/php5/cli/php.ini /etc/php5/fpm/ vi /etc/php5/fpm/php.ini # add below line cgi.fix_pathinfo=0 |
4. Configure nginx for reverse proxy
|
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 |
vi /etc/nginx/nginx.conf # Config for 4 Xeon CPU # Total amount of users you can serve = worker_processes * worker_connections worker_processes 8; # 2 * Number of CPUs worker_rlimit_nofile 102400; # Each connection needs a filehandle (or 2 if you are proxying) events { worker_connections 8192; # 4096 clients/second = It's the key to high performance use epoll; } http { # ............ server_tokens off; # DDoS Defense limit_conn_zone $binary_remote_addr zone=conn_limit_per_ip:20m; limit_req_zone $binary_remote_addr zone=req_limit_per_ip:20m rate=50r/s; server { # ............ # DDoS Defense limit_conn conn_limit_per_ip 100; limit_req zone=req_limit_per_ip burst=100 nodelay; # reverse proxy configuration location ~* ^.+\.(php|do|json|soap)$ { root /srv/www/htdocs; try_files $uri =404; # prevent attack fastcgi_connect_timeout 3s; fastcgi_read_timeout 10s; #fastcgi_pass 127.0.0.1:9000; fastcgi_pass unix:/var/run/php5-fpm.sock; fastcgi_index index.do; fastcgi_split_path_info ^(.+\.[^\.\?\/]+)(/.*)$; fastcgi_hide_header x-powered-by; fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name; if (!-f $document_root$fastcgi_script_name) { return 404; } include fastcgi_params; } } } |
5. Restart service
|
1 2 3 4 |
systemctl enable nginx.service systemctl restart nginx.service systemctl enable php-fpm.service systemctl restart php-fpm.service |
♦ ulimit for open files
|
1 2 3 4 5 6 |
ulimit -a vi /etc/security/limits.conf # add below line (require reboot for effect) nginx hard nofile 8192 nginx soft nofile 8192 |
♦ References http://www.howtoforge.com/perfect-server-opensuse-12.2-x86_64-nginx-dovecot-ispconfig-3-p4 https://www.linux.co.kr/home2/board/subbs/board.php?bo_table=lecture&wr_id=1685 http://stackoverflow.com/questions/7325211/tuning-nginx-worker-process-to-obtain-100k-hits-per-min http://www.codestance.com/tutorials-archive/nginx-tuning-for-best-performance-255 https://www.digitalocean.com/community/tutorials/how-to-optimize-nginx-configuration
MSSQL table to json procedure
Return json string from table data. — Author: Matthew D. Erwin (Snaptech, LLC) — Create date: May 9, 2013 — Modify: Aiden Kim, May 30, 2014
|
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 |
/****** Object: StoredProcedure [dbo].[sp_table2json] Script Date: 07/07/2014 14:11:41 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- -- Author: Matthew D. Erwin (Snaptech, LLC) -- Create date: May 9, 2013 -- Description: Returns the contents of a given table -- in JavaScript Object Notation JSON - -- -- Very notably useful for generating MOCK .json files -- for testing OR before RESTful services are completed. -- -- This implementation: -- *removed cursor (using FOR XML PATH('')) -- *properly supports NULL vs quoted values -- *supports dates in ISO 8601 - presuming UTC -- *uses Data_Type AND Is_Nullable info -- *escapes '\' -- *formats output with tabs/newlines -- *can return final results AS XML to bypass -- truncation in SSMS -- *supports schema (e.g. [dbo].[TableName] -- *includes "recordCount" field -- Options: -- @table_name: the table to execute the query -- @limit: equivalent to "SELECT top N * FROM table" -- @ssms: flag to use if executing in Sql Server Management Studio -- to bypass result truncation limits. -- -- Inspired primarily by the 2008 work of Thiago R. Santos which was influenced by Thomas Frank. -- Usage: [dbo].[GetJSON] @Table_name = 'MySchema.MyTable', @limit = 50, @ssms = 0 -- -- -- Modify: Aiden Kim (Technoblood, JPN) -- Create date: May 30, 2014 -- Description: Multilingual(unicode) data supported -- Add timezone calculation for datetime type -- Change temp table to table variable => now, session safe -- Result change to OUTPUT parameters from result-set -- Remove SSMS -- CREATE PROCEDURE [dbo].[sp_table2json] ( @table_name NVARCHAR(max ), @condition NVARCHAR(max ) = null, @limit INT = NULL, @json NVARCHAR(MAX ) OUTPUT , @row_count INT OUTPUT )AS BEGIN DECLARE @query NVARCHAR (max), @table_schema NVARCHAR( max), @tz NVARCHAR(5 ) DECLARE @tmpJsonTable AS TABLE (json NVARCHAR (max)) SET @table_schema = NULL SET @row_count = 0 SET @json = N'[]' SET @tz = CASE WHEN (GETUTCDATE () = GETDATE()) THEN N 'Z' ELSE CASE WHEN (GETUTCDATE () < GETDATE()) THEN N '+' ELSE N'-' END + REPLACE (CONVERT( VARCHAR(5 ), DATEADD (minute, ABS(DATEDIFF (minute, GETUTCDATE(), GETDATE())), 0), 108 ),N':' ,N'' ) END IF( CHARINDEX('.' , @table_name) > 0 ) BEGIN SET @table_schema = REPLACE( REPLACE( SUBSTRING(@table_name , 0, CHARINDEX ('.', @table_name)), '[', ''), ']', '') SET @table_name = REPLACE( REPLACE( SUBSTRING(@table_name , CHARINDEX('.' ,@table_name) + 1 ,LEN( @table_name)), '[', ''), ']', '') END SET @query = N 'SELECT ' + CASE WHEN ( @limit IS NOT NULL AND @limit > 0) THEN 'TOP ' + CAST(@limit AS NVARCHAR (32)) + ' ' ELSE '' END + '''{ '' + REVERSE(STUFF(REVERSE(''' + CAST((SELECT ' "' + column_name + '" : ' + CASE WHEN ( is_nullable = 'YES') THEN ''' + CASE WHEN ([' + column_name + '] IS NULL) THEN ''NULL'' ELSE ' + CASE WHEN (data_type LIKE '%char%' OR data_type LIKE '%text%' ) THEN '''"'' + ' ELSE '' END + CASE WHEN (data_type LIKE '%date%' ) THEN 'CONVERT(NVARCHAR(23),[' + column_name + '], 126) + ''' + @tz + '''' ELSE 'REPLACE(REPLACE(REPLACE(REPLACE(CAST([' + column_name + '] AS NVARCHAR(max)),''\'',''\\''),''"'',''\"''),CHAR(10),''\n''),CHAR(13),''\n'') ' END + CASE WHEN (data_type LIKE '%char%' OR data_type LIKE '%text%' ) THEN '+ ''"''' ELSE '' END + ' END + ''' ELSE CASE WHEN (data_type LIKE '%char%' OR data_type LIKE '%text%' ) THEN '"' ELSE '' END + ''' + ' + CASE WHEN (data_type LIKE '%date%' ) THEN 'CONVERT(NVARCHAR(23),[' + column_name + '], 126) + ''' + @tz + '' ELSE 'REPLACE(REPLACE(REPLACE(REPLACE(CAST([' + column_name + '] AS NVARCHAR(max)),''\'',''\\''),''"'',''\"''),CHAR(10),''\n''),CHAR(13),''\n'') + ''' END + CASE WHEN (data_type LIKE '%char%' OR data_type LIKE '%text%' ) THEN '"' ELSE '' END END + ',' AS [text()] FROM information_schema.columns WHERE table_name = @table_name AND ( @table_schema IS NULL OR table_schema = @table_schema ) FOR XML PATH( '') ) AS NVARCHAR( max)) + '''),1,1,'''')) + '' }'' AS json FROM ' + @table_name + ' WITH(NOLOCK) ' + CASE WHEN (@condition IS NOT NULL AND @condition <> '') THEN N' WHERE ' + @condition ELSE '' END INSERT INTO @tmpJsonTable EXECUTE sp_executesql @query IF ( @@ERROR <> 0) RETURN - 1 -- Row Count SELECT @row_count = COUNT(*) FROM @tmpJsonTable IF ( @row_count > 0 ) BEGIN SET @json = '[' + CHAR( 10) + REVERSE (STUFF( REVERSE(CAST ((SELECT CHAR(9 ) + json + ',' + CHAR( 10) AS [text()] FROM @tmpJsonTable FOR XML PATH ('')) AS NVARCHAR(max ))),1, 2,'' )) + CHAR (10) + ']' END RETURN 0 END |
