Posts Tagged ‘key’

« Older Entries

How to filter dhcp traffic between two networks running separate DHCP servers to prevent IP assignment issues and MAC duplicate addresses

Tuesday, February 8th, 2022

how-to-filter-dhcp-traffic-2-networks-running-2-separate-dhcpd-servers-to-prevent-ip-assignment-conflicts-linux
Tracking the Problem of MAC duplicates on Linux routers
 

If you have two networks that see each other and they're not separated in VLANs but see each other sharing a common netmask lets say 255.255.254.0 or 255.255.252.0, it might happend that there are 2 dhcp servers for example (isc-dhcp-server running on 192.168.1.1 and dhcpd running on 192.168.0.1 can broadcast their services to both LANs 192.168.1.0.1/24 (netmask 255.255.255.0) and Local Net LAN 192.168.1.1/24. The result out of this is that some devices might pick up their IP address via DHCP from the wrong dhcp server.

Normally if you have a fully controlled little or middle class home or office network (10 – 15 electronic devices nodes) connecting to the LAN in a mixed moth some are connected via one of the Networks via connected Wifi to 192.168.1.0/22 others are LANned and using static IP adddresses and traffic is routed among two ISPs and each network can see the other network, there is always a possibility of things to go wrong. This is what happened to me so this is how this post was born.

The best practice from my experience so far is to define each and every computer / phone / laptop host joining the network and hence later easily monitor what is going on the network with something like iptraf-ng / nethogs  / iperf – described in prior  how to check internet spepeed from console and in check server internet connectivity speed with speedtest-cli) iftop / nload or for more complex stuff wireshark or even a simple tcpdump. No matter the tools network monitoring is only part on solving network issues. A very must have thing in a controlled network infrastructure is defining every machine part of it to easily monitor later with the monitoring tools. Defining each and every host on the Hybrid computer networks makes administering the network much easier task and  tracking irregularities on time is much more likely. 

Since I have such a hybrid network here hosting a couple of XEN virtual machines with Linux, Windows 7 and Windows 10, together with Mac OS X laptops as well as MacBook Air notebooks, I have followed this route and tried to define each and every host based on its MAC address to pick it up from the correct DHCP1 server  192.168.1.1 (that is distributing IPs for Internet Provider 1 (ISP 1), that is mostly few computers attached UTP LAN cables via LiteWave LS105G Gigabit Switch as well from DHCP2 – used only to assigns IPs to servers and a a single Wi-Fi Access point configured to route incoming clients via 192.168.0.1 Linux NAT gateway server.

To filter out the unwanted IPs from the DHCPD not to propagate I've so far used a little trick to  Deny DHCP MAC Address for unwanted clients and not send IP offer for them.

To give you more understanding,  I have to clear it up I don't want to have automatic IP assignments from DHCP2 / LAN2 to DHCP1 / LAN1 because (i don't want machines on DHCP1 to end up with IP like 192.168.0.50 or DHCP2 (to have 192.168.1.80), as such a wrong IP delegation could potentially lead to MAC duplicates IP conflicts. MAC Duplicate IP wrong assignments for those older or who have been part of administrating large ISP network infrastructures  makes the network communication unstable for no apparent reason and nodes partially unreachable at times or full time …

However it seems in the 21-st century which is the century of strangeness / computer madness in the 2022, technology advanced so much that it has massively started to break up some good old well known sysadmin standards well documented in the RFCs I know of my youth, such as that every electronic equipment manufactured Vendor should have a Vendor Assigned Hardware MAC Address binded to it that will never change (after all that was the idea of MAC addresses wasn't it !). 
Many mobile devices nowadays however, in the developers attempts to make more sophisticated software and Increase Anonimity on the Net and Security, use a technique called  MAC Address randomization (mostly used by hackers / script kiddies of the early days of computers) for their Wi-Fi Net Adapter OS / driver controlled interfaces for the sake of increased security (the so called Private WiFi Addresses). If a sysadmin 10-15 years ago has seen that he might probably resign his profession and turn to farming or agriculture plant growing, but in the age of digitalization and "cloud computing", this break up of common developed network standards starts to become the 'new normal' standard.

I did not suspected there might be a MAC address oddities, since I spare very little time on administering the the network. This was so till recently when I accidently checked the arp table with:

Hypervisor:~# arp -an
192.168.1.99     5c:89:b5:f2:e8:d8      (Unknown)
192.168.1.99    00:15:3e:d3:8f:76       (Unknown)

..


and consequently did a network MAC Address ARP Scan with arp-scan (if you never used this little nifty hacker tool I warmly recommend it !!!)
If you don't have it installed it is available in debian based linuces from default repos to install

Hypervisor:~# apt-get install –yes arp-scan
…


It is also available on CentOS / Fedora / Redhat and other RPM distros via:

Hypervisor:~# yum install -y arp-scan
…

 

 

Hypervisor:~# arp-scan –interface=eth1 192.168.1.0/24
…

192.168.1.19    00:16:3e:0f:48:05       Xensource, Inc.
192.168.1.22    00:16:3e:04:11:1c       Xensource, Inc.
192.168.1.31    00:15:3e:bb:45:45       Xensource, Inc.
192.168.1.38    00:15:3e:59:96:8e       Xensource, Inc.
192.168.1.34    00:15:3e:d3:8f:77       Xensource, Inc.
192.168.1.60    8c:89:b5:f2:e8:d8       Micro-Star INT'L CO., LTD
192.168.1.99     5c:89:b5:f2:e8:d8      (Unknown)
192.168.1.99    00:15:3e:d3:8f:76       (Unknown)
192.168.x.91     02:a0:xx:xx:d6:64        (Unknown)
192.168.x.91     02:a0:xx:xx:d6:64        (Unknown)  (DUP: 2)
…

N.B. !. I found it helpful to check all available interfaces on my Linux NAT router host.

As you see the scan revealed, a whole bunch of MAC address mess duplicated MAC hanging around, destroying my network topology every now and then 
So far so good, the MAC duplicates and strangely hanging around MAC addresses issue, was solved relatively easily with enabling below set of systctl kernel variables.
 

1. Fixing Linux ARP common well known Problems through disabling arp_announce / arp_ignore / send_redirects kernel variables disablement

 

Linux answers ARP requests on wrong and unassociated interfaces per default. This leads to the following two problems:

ARP requests for the loopback alias address are answered on the HW interfaces (even if NOARP on lo0:1 is set). Since loopback aliases are required for DSR (Direct Server Return) setups this problem is very common (but easy to fix fortunately).

If the machine is connected twice to the same switch (e.g. with eth0 and eth1) eth2 may answer ARP requests for the address on eth1 and vice versa in a race condition manner (confusing almost everything).

This can be prevented by specific arp kernel settings. Take a look here for additional information about the nature of the problem (and other solutions): ARP flux.

To fix that generally (and reboot safe) we  include the following lines into

 

Hypervisor:~# cp -rpf /etc/sysctl.conf /etc/sysctl.conf_bak_07-feb-2022
Hypervisor:~# cat >> /etc/sysctl.conf

# LVS tuning
net.ipv4.conf.lo.arp_ignore=1
net.ipv4.conf.lo.arp_announce=2
net.ipv4.conf.all.arp_ignore=1
net.ipv4.conf.all.arp_announce=2

net.ipv4.conf.all.send_redirects=0
net.ipv4.conf.eth0.send_redirects=0
net.ipv4.conf.eth1.send_redirects=0
net.ipv4.conf.default.send_redirects=0

Press CTRL + D simultaneusly to Write out up-pasted vars.


To read more on Load Balancer using direct routing and on LVS and the arp problem here


2. Digging further the IP conflict / dulicate MAC Problems

Even after this arp tunings (because I do have my Hypervisor 2 LAN interfaces connected to 1 switch) did not resolved the issues and still my Wireless Connected devices via network 192.168.1.1/24 (ISP2) were randomly assigned the wrong range IPs 192.168.0.XXX/24 as well as the wrong gateway 192.168.0.1 (ISP1).
After thinking thoroughfully for hours and checking the network status with various tools and thanks to the fact that my wife has a MacBook Air that was always complaining that the IP it tried to assign from the DHCP was already taken, i"ve realized, something is wrong with DHCP assignment.
Since she owns a IPhone 10 with iOS and this two devices are from the same vendor e.g. Apple Inc. And Apple's products have been having strange DHCP assignment issues from my experience for quite some time, I've thought initially problems are caused by software on Apple's devices.
I turned to be partially right after expecting the logs of DHCP server on the Linux host (ISP1) finding that the phone of my wife takes IP in 192.168.0.XXX, insetad of IP from 192.168.1.1 (which has is a combined Nokia Router with 2.4Ghz and 5Ghz Wi-Fi and LAN router provided by ISP2 in that case Vivacom). That was really puzzling since for me it was completely logical thta the iDevices must check for DHCP address directly on the Network of the router to whom, they're connecting. Guess my suprise when I realized that instead of that the iDevices does listen to the network on a wide network range scan for any DHCPs reachable baesd on the advertised (i assume via broadcast) address traffic and try to connect and take the IP to the IP of the DHCP which responds faster !!!! Of course the Vivacom Chineese produced Nokia router responded DHCP requests and advertised much slower, than my Linux NAT gateway on ISP1 and because of that the Iphone and iOS and even freshest versions of Android devices do take the IP from the DHCP that responds faster, even if that router is not on a C class network (that's invasive isn't it??). What was even more puzzling was the automatic MAC Randomization of Wifi devices trying to connect to my ISP1 configured DHCPD and this of course trespassed any static MAC addresses filtering, I already had established there.

Anyways there was also a good think out of tthat intermixed exercise 🙂 While playing around with the Gigabit network router of vivacom I found a cozy feature SCHEDULEDING TURNING OFF and ON the WIFI ACCESS POINT  – a very useful feature to adopt, to stop wasting extra energy and lower a bit of radiation is to set a swtich off WIFI AP from 12:30 – 06:30 which are the common sleeping hours or something like that.
 

3. What is MAC Randomization and where and how it is configured across different main operating systems as of year 2022?

Depending on the operating system of your device, MAC randomization will be available either by default on most modern mobile OSes or with possibility to have it switched on:

  • Android Q: Enabled by default 
  • Android P: Available as a developer option, disabled by default
  • iOS 14: Available as a user option, disabled by default
  • Windows 10: Available as an option in two ways – random for all networks or random for a specific network

Lately I don't have much time to play around with mobile devices, and I do not my own a luxury mobile phone so, the fact this ne Androids have this MAC randomization was unknown to me just until I ended a small mess, based on my poor configured networks due to my tight time constrains nowadays.

Finding out about the new security feature of MAC Randomization, on all Android based phones (my mother's Nokia smartphone and my dad's phone, disabled the feature ASAP:


4. Disable MAC Wi-Fi Ethernet device Randomization on Android

MAC Randomization creates a random MAC address when joining a Wi-Fi network for the first time or after “forgetting” and rejoining a Wi-Fi network. It Generates a new random MAC address after 24 hours of last connection.

Disabling MAC Randomization on your devices. It is done on a per SSID basis so you can turn off the randomization, but allow it to function for hotspots outside of your home.

  1. Open the Settings app
  2. Select Network and Internet
  3. Select WiFi
  4. Connect to your home wireless network
  5. Tap the gear icon next to the current WiFi connection
  6. Select Advanced
  7. Select Privacy
  8. Select "Use device MAC"
     

5. Disabling MAC Randomization on MAC iOS, iPhone, iPad, iPod

To Disable MAC Randomization on iOS Devices:

Open the Settings on your iPhone, iPad, or iPod, then tap Wi-Fi or WLAN

 

  1. Tap the information button next to your network
  2. Turn off Private Address
  3. Re-join the network


Of course next I've collected their phone Wi-Fi adapters and made sure the included dhcp MAC deny rules in /etc/dhcp/dhcpd.conf are at place.

The effect of the MAC Randomization for my Network was terrible constant and strange issues with my routings and networks, which I always thought are caused by the openxen hypervisor Virtualization VM bugs etc.

That continued for some months now, and the weird thing was the issues always started when I tried to update my Operating system to the latest packetset, do a reboot to load up the new piece of software / libraries etc. and plus it happened very occasionally and their was no obvious reason for it.

 

6. How to completely filter dhcp traffic between two network router hosts
IP 192.168.0.1 / 192.168.1.1 to stop 2 or more configured DHCP servers
on separate networks see each other

To prevent IP mess at DHCP2 server side (which btw is ISC DHCP server, taking care for IP assignment only for the Servers on the network running on Debian 11 Linux), further on I had to filter out any DHCP UDP traffic with iptables completely.
To prevent incorrect route assignments assuming that you have 2 networks and 2 routers that are configurred to do Network Address Translation (NAT)-ing Router 1: 192.168.0.1, Router 2: 192.168.1.1.

You have to filter out UDP Protocol data on Port 67 and 68 from the respective source and destination addresses.

In firewall rules configuration files on your Linux you need to have some rules as:

# filter outgoing dhcp traffic from 192.168.1.1 to 192.168.0.1
-A INPUT -p udp -m udp –dport 67:68 -s 192.168.1.1 -d 192.168.0.1 -j DROP
-A OUTPUT -p udp -m udp –dport 67:68 -s 192.168.1.1 -d 192.168.0.1 -j DROP
-A FORWARD -p udp -m udp –dport 67:68 -s 192.168.1.1 -d 192.168.0.1 -j DROP

-A INPUT -p udp -m udp –dport 67:68 -s 192.168.0.1 -d 192.168.1.1 -j DROP
-A OUTPUT -p udp -m udp –dport 67:68 -s 192.168.0.1 -d 192.168.1.1 -j DROP
-A FORWARD -p udp -m udp –dport 67:68 -s 192.168.0.1 -d 192.168.1.1 -j DROP

-A INPUT -p udp -m udp –sport 67:68 -s 192.168.1.1 -d 192.168.0.1 -j DROP
-A OUTPUT -p udp -m udp –sport 67:68 -s 192.168.1.1 -d 192.168.0.1 -j DROP
-A FORWARD -p udp -m udp –sport 67:68 -s 192.168.1.1 -d 192.168.0.1 -j DROP


You can download also filter_dhcp_traffic.sh with above rules from here


Applying this rules, any traffic of DHCP between 2 routers is prohibited and devices from Net: 192.168.1.1-255 will no longer wrongly get assinged IP addresses from Network range: 192.168.0.1-255 as it happened to me.


7. Filter out DHCP traffic based on MAC completely on Linux with arptables

If even after disabling MAC randomization on all devices on the network, and you know physically all the connecting devices on the Network, if you still see some weird MAC addresses, originating from a wrongly configured ISP traffic router host or whatever, then it is time to just filter them out with arptables.

## drop traffic prevent mac duplicates due to vivacom and bergon placed in same network – 255.255.255.252
dchp1-server:~# arptables -A INPUT –source-mac 70:e2:83:12:44:11 -j DROP


To list arptables configured on Linux host

dchp1-server:~# arptables –list -n


If you want to be paranoid sysadmin you can implement a MAC address protection with arptables by only allowing a single set of MAC Addr / IPs and dropping the rest.

dchp1-server:~# arptables -A INPUT –source-mac 70:e2:84:13:45:11 -j ACCEPT
dchp1-server:~# arptables -A INPUT  –source-mac 70:e2:84:13:45:12 -j ACCEPT


dchp1-server:~# arptables -L –line-numbers
Chain INPUT (policy ACCEPT)
1 -j DROP –src-mac 70:e2:84:13:45:11
2 -j DROP –src-mac 70:e2:84:13:45:12
3 

Once MACs you like are accepted you can set the INPUT chain policy to DROP as so:

dchp1-server:~# arptables -P INPUT DROP


If you later need to temporary, clean up the rules inside arptables on any filtered hosts flush all rules inside INPUT chain, like that
 

dchp1-server:~#  arptables -t INPUT -F

Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
Posted in Debian, Linux, Networking, System Administration, Virtual Machines | No Comments »

Zabbix rkhunter monitoring check if rootkits trojans and viruses or suspicious OS activities are detected

Wednesday, December 8th, 2021

monitor-rkhunter-with-zabbix-zabbix-rkhunter-check-logo

If you're using rkhunter to monitor for malicious activities, a binary changes, rootkits, viruses, malware, suspicious stuff and other famous security breach possible or actual issues, perhaps you have configured your machines to report to some Email.
But what if you want to have a scheduled rkhunter running on the machine and you don't want to count too much on email alerting (especially because email alerting) makes possible for emails to be tracked by sysadmin pretty late?

We have been in those situation and in this case me and my dear colleague Georgi Stoyanov developed a small rkhunter Zabbix userparameter check to track and Alert if any traces of "Warning"''s are mateched in the traditional rkhunter log file /var/log/rkhunter/rkhunter.log

To set it up and use it is pretty use you will need to have a recent version of zabbix-agent installed on the machine and connected to a Zabbix server, in my case this is:

[root@centos ~]# rpm -qa |grep -i zabbix-agent
zabbix-agent-4.0.7-1.el7.x86_64

 placed inside /etc/zabbix/zabbix_agentd.d/userparameter_rkhunter_warning_check.conf

[root@centos /etc/zabbix/zabbix_agentd.d ]# cat userparameter_rkhunter_warning_check.conf
# userparameter script to check if any Warning is inside /var/log/rkhunter/rkhunter.log and if found to trigger Zabbix alert
UserParameter=rkhunter.warning, (TODAY=$(date |awk '{ print $1" "$2" "$3 }'); if [ $(cat /var/log/rkhunter/rkhunter.log | awk “/$TODAY/,EOF” | /bin/grep -i ‘\[ Warning \]’ | /usr/bin/wc -l) != ‘0’ ]; then echo 1; else echo 0; fi)
UserParameter=rkhunter.suspected,(/bin/grep -i 'Suspect files: ' /var/log/rkhunter/rkhunter.log|tail -n 1| awk '{ print $4 }')
UserParameter=rkhunter.rootkits,(/bin/grep -i 'Possible rootkits: ' /var/log/rkhunter/rkhunter.log|tail -n 1| awk '{ print $4 }')


2. Prepare Rkhunter Template, Triggers and Items


In Zabbix Server that you access from web control interface, you will have to prepare a new template called lets say Rkhunter with the necessery Triggers and Items


2.1 Create Rkhunter Items
 

On Zabbix Server side, uou will have to configure 3 Items for the 3 configured userparameter above script keys, like so:

rkhunter-items-zabbix-screenshot.png

  • rkhunter.suspected Item configuration


rkhunter-suspected-files

  • rkhunter.warning Zabbix Item config

rkhunter-warning-found-check-zabbix

  • rkhunter.rootkits Zabbix Item config

     

     

    rkhunter-zabbix-possible-rootkits-item1

    2.2 Create Triggers
     

You need to have an overall of 3 triggers like in below shot:

zabbix-rkhunter-all-triggers-screenshot
 

  • rkhunter.rootkits Trigger config

rkhunter-rootkits-trigger-zabbix1

  • rkhunter.suspected Trigger cfg

rkhunter-suspected-trigger-zabbix1

  • rkhunter warning Trigger cfg

rkhunter-warning-trigger1

3. Reload zabbix-agent and test the keys


It is necessery to reload zabbix agent for the new userparameter to start to be sent to remote zabbix server (through a proxy if you have one configured).

[root@centos ~]# systemctl restart zabbix-agent
…


To make the zabbix-agent send the keys to the server you can use zabbix_sender to have the test tool you will have to have installed (zabbix-sender) on the server.

To trigger a manualTest if you happen to have some problems with the key which shouldn''t be the case you can sent a value to the respectve key with below command:

[root@centos ~ ]# zabbix_sender -vv -c "/etc/zabbix/zabbix_agentd.conf" -k "khunter.warning" -o "1"


Check on Zabbix Server the sent value is received, for any oddities as usual check what is inside  /var/log/zabbix/zabbix_agentd.log for any errors or warnings.

Tags: , , , , , , , , , , , ,
Posted in Linux, Monitoring, Zabbix | No Comments »

Short SSL generate new and self-signed certificates PEM, view and convert to and from PKCS12 to java key store cookbook commands cheat sheet

Tuesday, January 12th, 2021

OpenSSL-logo

Below is a short compilation of common used openssl commands (a kind of cookbook) helpful for sysadmins who has to commonly deal with OpenSSL certificates.

Lets say you have to generate new certificate / key and a PEM files, prepare self-signed certificates, show CSR / PEM or KEY ssl file contents, get information about certificate such as expiry date a type of encryption algorithm or sign certificate with self-signed authority convert PEM to PKCS12, convert from PKCS12 file format to .PEM, convert java X509 to java key store SSL encryptionor convert java key store format certificate to PKCS12, then below will be of use to you.

1. Generate Private RSA Key with 2048 bits

# openssl genrsa -out $ (hostname -f) .key 2048

2. Create CSR file

# openssl req -new -key $ (hostname -f) .key -out $ (hostname -f) .csr

3. Create a Self Certified Certificate:

# openssl x509 -req -days 30 -in $ (hostname -f) .csr -signkey $ (hostname -f) .key -out $ (hostname -f) .crt
Enter password:

# openssl rsa -in key.pem -out newkey.pem


4. Show CSR file content

# openssl req -in newcsr.csr -noout -text


5. Get Certificate version / serial number / signature algorithm / RSA key lenght / modulus / exponent etc.

# openssl x509 -in newcert.pem -noout -text


6. Server certificate as CA self signeded

# openssl ca -in newcert.csr -notext -out newcert.pem


7. Generate a certificate signing request based on an existing certificate

# openssl x509 -x509toreq -in certificate.crt -out CSR.csr -signkey privateKey.key


8. Convert .pem / .key / .crt file format to pkcs12 format
 

# openssl pkcs12 -export -in newcert.pem -inkey newkey.key -certfile ca.crt -out newcert.p12


9. Convert pkcs12 pfx to common .pem

# openssl pkcs12 -in mycert.pfx -out mycert.pem


10. The Formats available

# openssl x509 -inform the -in certificate.cer -out certificate.crt


11. Convert a pkcs # 7 certificate into PEM format

# openssl pkcs7 -in cert.p7c -inform DER -outform PEM -out certificate.p7b
# openssl pkcs7 -print_certs -in certificate.p7b -out certificate.pem


12. Convert X509 to java keystore file

# java -cp not-yet-commons-ssl-0.3.11.jar org.apache.commons.ssl.KeyStoreBuilder pass_for_new_keystore key.key certificate.crt

13. Convert java keystore file to pkcs12

# keytool -importkeystore -srckeystore keystore.jks -destkeystore intermediate.p12 -deststoretype PKCS12

Tags: , , , , , , , , ,
Posted in System Administration, Web and CMS | No Comments »

WordPress Plugins to monitor and debug WP enabled plugins – Find Errors / Warnings and Remove WP problematic plugins slowing down your Website (blog) database

Thursday, February 19th, 2015

plugins-to-monitor-debug-wordpress-enabled-plugins-how-to-track-find-errors-and-warnings-and-remove-problematic-wp-extensions-that-slow-down-your-website

Recent days, I'm spending a lot of time again trying to optimize my wordpress blog. Optimizing WP for better efficiency is becoming harder and harder task day by day as the website file content data is growing along with SQL databases. Moreover situation gets even worse because the number of plugins enabled on my blog is incrementally growing with time because, there is more and more goodies I'd like to add.
Optimizing WordPress to run for Speed on a server is a whole a lot of art and its a small universe in itself, because as of time of writting this post the count (number) of WordPress available PLUGINS is 36,197 ! 

1. Manually Tracking WordPress  Plugins causing Slow SQL Queries (MySQL bottleneck) issues directly using console / SSH

Because of its open source development and its nice modular design wordpress has turned into a standard for building small, middle sized and large websites (some WordPress based blogs and sites have from 50 000 to 100 000 unique pages!). My blog is still a small WordPress site with only 1676 posts, so I still haven't reached the high volume traffic optimization requirements but still even though I have a relatively good server hardware  8GB RAM / (2×2.70 Ghz Intel CPU) / 500 GB (7400 RPM HDD) at times I see Apache Webservers is unable to properly serve coming requests because of MySQL database (LEFT JOIN) requests being slow to serve (taking up to few seconds to complete) and creating a MySQL table lock, putting all the rest SQL queries to stay in a long unserved queues line, I've realized about this performance issue by using a a mysql cli (command) client and few commands and console command (tool) called mytop (also known as mtop). MyTop refreshes every 3 seconds, so the slow query will immediately stay on screen to view moer info about it press "f" and type the  in query ID.

mysql-top-running-on-gnu-linux-server-tracking-sql-queries-in-console-screenshot.png

mysql-top-running-on-gnu-linux-server-tracking-sql-queries-in-console-screenshot2

Finally it is very useful to run  for a while MySQL server logging to /var/log/mysql/slow-query.log:
Slow query is enabled (on my Debian 7 Wheezy host) by adding to /etc/mysql/my.cnf after conf section

 

vim /etc/mysql/my.cnf
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration

 

Paste:

 

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
log-queries-not-using-indexes

 

And then to make new mysql configuration load restarted mysql server:

 

debian-server:~# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld ..
Checking for tables which need an upgrade, are corrupt or were
not closed cleanly..

 

Leaving mysql-slow.log to be enabled for 30 minutes to an 1 hrs is a good time to track most problematic slow queries and based on this queries, I took parts of  SQL UPDATE / SELECT / INSERT etc. Db queries which was problematic and grepped throughout /var/www/blog/wp-content/plugin files in order to determine which WordPress Plugin is triggering the slow query, causing blog to hang when too many clients try to see it in browser.

My main problematic SQL query having long execution time  (about 2 to 3 seconds!!!) most commonly occuring in slow-query.log was:

 

SELECT DISTINCT post_title, ID, post_type, post_name FROM wp_posts wposts LEFT JOIN wp_postmeta wpostmeta ON wposts.ID = wpostmeta.post_id LEFT JOIN wp_term_relationships ON (wposts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE (post_type='page' OR (wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN(11))) AND post_status = 'publish' AND LENGTH(post_title)>=5 ORDER BY LENGTH(post_title) ASC LIMIT 500

Because above query uses SQL Column names and Tables which are not hard coded in PHP code, to find out which plugins is most probably to launch this complex LEFT JOIN query, I used a quick bash one-liner:

 

# cd /var/www/blog/wp-content/plugins

 

# for i in $(grep -rli 'SELECT DISTINCT' *); do grep -rli 'LEFT JOIN' $i; done 
./seo-automatic-links/seo-links.php
./wp-postviews/wp-postviews.php
./yet-another-related-posts-plugin/classes/YARPP_Cache_Tables.php

 

I wanted to put less load on CPU during grep so looked for string only in .PHP extensioned files with:

 

 # for i in $(find . -iname '*.php' -exec grep -rli 'SELECT DISTINCT' '{}' \;); do grep -rli 'LEFT JOIN' $i; done
./seo-automatic-links/seo-links.php
./wp-postviews/wp-postviews.php
./yet-another-related-posts-plugin/classes/YARPP_Cache_Tables.php


As you can see the complex query is being called from PHP file belonging to one of 3 plugins

  • SEO Automatic Links – this is SEO Smart Links WP plugin (Does internal bliog interlinking in order to boast SEA)
  • WP PostViews – WordPress Post Views plugin (Which allows me to show how many times an article was read in WP Widget menu)
  • Yet Another Related Posts – Which is WP plugin I installed / enabled to show Related posts down on each blog post


2. Basic way to optimize MySQL slow queries (EXPLAIN / SHOW CREATE TABLE)

Now as I have a basic clue on plugins locking my Database, I disabled them one by one while keeping enabled mysql slow query log and viewing queries in mytop and I figure out that actually all of the plugins were causing a short time overheat (lock) on server Database because of LEFT JOINs. Though I really like what this plugins are doing, as they boast SEO and attract prefer to disable them for now and have my blog all the time responsible light fast instead of having a little bit better Search Engine Optimization (Ranking) and loosing many of my visitors because they're annoyed to wait until my articles open …

Before disabling I tried to optimize the queries using MySQL EXPLAIN command + SHOW CREATE TABLE (2 commands often used to debug slow SQL queries and find out whether a Column needs to have added INDEX-ing to boast MySQL query).

Just in case if you decide to give them a try here is example on how they're used to debug problematic SQL query:
 

  1. mysql> explain SELECT DISTINCT post_title, ID, post_type, post_name
  2.     -> FROM wp_posts wposts LEFT JOIN wp_postmeta wpostmeta
  3.     -> ON wposts.ID = wpostmeta.post_id LEFT JOIN wp_term_relationships
  4.     -> ON (wposts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_taxonomy
  5.     -> ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
  6.     -> WHERE (post_type='page'
  7.     -> OR (wp_term_taxonomy.taxonomy = 'category'
  8.     -> AND wp_term_taxonomy.term_id IN(11,15,17)))
  9.     -> AND post_status = 'publish'
  10.     -> AND LENGTH(post_title)>=5
  11.     -> ORDER BY LENGTH(post_title) ASC
  12.     -> LIMIT 500;
  13. +—-+————-+———————–+——–+——————+———+———+———————————————+——+———————————————-+
  14. | id | select_type | table                 | type   | possible_keys    | key     | key_len | ref                                         | rows | Extra                                        |
  15. +—-+————-+———————–+——–+——————+———+———+———————————————+——+———————————————-+
  16. |  1 | SIMPLE      | wposts                | ALL    | type_status_date | NULL    | NULL    | NULL                                        | 1715 | Using where; Using temporary; Using filesort |
  17. |  1 | SIMPLE      | wpostmeta             | ref    | post_id          | post_id | 8       | blog.wposts.ID                              |   11 | Using index; Distinct                        |
  18. |  1 | SIMPLE      | wp_term_relationships | ref    | PRIMARY          | PRIMARY | 8       | blog.wposts.ID                              |   19 | Using index; Distinct                        |
  19. |  1 | SIMPLE      | wp_term_taxonomy      | eq_ref | PRIMARY          | PRIMARY | 8       | blog.wp_term_relationships.term_taxonomy_id |    1 | Using where; Distinct                        |
  20. +—-+————-+———————–+——–+——————+———+———+———————————————+——+———————————————-+
  21. 4 rows in set (0.02 sec)
  22.  
  23. mysql>

  24.  

     

     

  1. mysql> show create table wp_posts;
  2. +———-+————————–+
  3. | Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                 |
  4. +———-+————————–+
  5. | wp_posts | CREATE TABLE `wp_posts` (
  6.   `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  7.   `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
  8.   `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  9.   `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  10.   `post_content` longtext NOT NULL,
  11.   `post_title` text NOT NULL,
  12.   `post_excerpt` text NOT NULL,
  13.   `post_status` varchar(20) NOT NULL DEFAULT 'publish',
  14.   `comment_status` varchar(20) NOT NULL DEFAULT 'open',
  15.   `ping_status` varchar(20) NOT NULL DEFAULT 'open',
  16.   `post_password` varchar(20) NOT NULL DEFAULT '',
  17.   `post_name` varchar(200) NOT NULL DEFAULT '',
  18.   `to_ping` text NOT NULL,
  19.   `pinged` text NOT NULL,
  20.   `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  21.   `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  22.   `post_content_filtered` longtext NOT NULL,
  23.   `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  24.   `guid` varchar(255) NOT NULL DEFAULT '',
  25.   `menu_order` int(11) NOT NULL DEFAULT '0',
  26.   `post_type` varchar(20) NOT NULL DEFAULT 'post',
  27.   `post_mime_type` varchar(100) NOT NULL DEFAULT '',
  28.   `comment_count` bigint(20) NOT NULL DEFAULT '0',
  29.   PRIMARY KEY (`ID`),
  30.   KEY `post_name` (`post_name`),
  31.   KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  32.   KEY `post_parent` (`post_parent`),
  33.   KEY `post_author` (`post_author`),
  34.   FULLTEXT KEY `post_related` (`post_title`,`post_content`)
  35. ) ENGINE=MyISAM AUTO_INCREMENT=12033 DEFAULT CHARSET=utf8 |
  36. +———-+———————-+
  37. 1 row in set (0.00 sec)
  38.  
  39. mysql>
  40.  


By the way above output is a paste from the the new PasteBin Open Source (Stikked powered) service I started on www.pc-freak.net – paste.www.pc-freak.net (p.www.pc-freak.net) 🙂

Before I took final decision to disable slow WP plugins, I've experimented a bit trying to add INDEX to Table Column (wposts) in hope that this would speed up SQL queries with:

 

mysql> ALTER TABLE TABLE_NAME ADD INDEX (wposts);
…

 

But this didn't improve query speed even on the contrary it make execution time worse.

3. Tracking WordPress Plugin PHP Code Execution time and Plugins causing Slow SQL Queries (MySQL bottleneck) issues through WP itself

Well fine, I'm running my own hosted Blog and WordPress sites, but for people who have wordpress sites on shared hosting, there is usually no SSH (Terminal) Access to server, those people will be happy to hear there are 2 Free easy installable WordPress plugins which can be used to Debug Slow WordPress Plugins SQL Queries as well as plugin to Track which plugin takes most time to execute, this are:
 

 

a) P3 Plugin Performance Profiler  

runs a scan over your site to determine what resources your plugins are using, and when, during a standard page request. P3 PPP Can even create reports in a beatiful Excel like Pie chart sheet.

p3-plugin-performance-profiler-godaddy-screenshot-debian-gnu-linux-wordpress-website

Another useful thing to see with P3 PPP is Detailed Timeline it shows when the plugins are being loaded during new page request so you can see if there is a certain sequence in time when a plugin slows down the website.

detailed_timeline-wordpress-p3-performance-plugin-on-website-screenshot

The pictures says it all as P3 PPP is Godaddy's work, congrats to GoDaddy, they've done great job.

 

b) WordPress memory Viewer WP plugins

Is useful to check how much memory each of WordPress plugin is taking on user (visitor) request.
Memory Viewer is allows you to view WordPress’ memory utilization at several hooks during WordPress’ execution. It also shows a summary of MySQL Queries that have ran as well as CPU time.
To use it download it to plugins/ folder as usual enable it from:

Installed Plugins -> (Inactive) -> Memory Viewer (Enable)

To see statistics from Memory Viewer open any post from your blog website and scroll down to the bottom you will notice the statistics, showing up there, like on below screenshot.

wordpress-memory-viewer-plugin-debian-gnu-linux-hosted-website-show-which-plugin-component-eats-most-memory-in-wordprses-blog
 

Though WP Memory Viewer is said to work only up to WP version 3.2.1, I've tested it and it works fine on my latest stable WordPress 4.1 based blog.

c) WordPress Query Monitor

wordpress-query-monitor-plugin-to-monitor-track-and-optimize-problems-with-sql-caused-by-wp-plugins.png
 

Query Monitor is a debugging plugin for anyone developing with WordPress but also very helpful for anyone who want to track issues with plugins who use the database unefficient.
It has some advanced features not available in other debugging plugins, including automatic AJAX debugging and the ability to narrow down things by plugin or theme.
You can view plenty of precious statistics on how enabled plugins query the database server, here is a short overview on its Database Queries capabilities:

  • Shows all database queries performed on the current page
  • Shows affected rows and time for all queries
  • Show notifications for slow queries and queries with errors
  • Filter queries by query type (SELECT, UPDATE, DELETE, etc)
  • Filter queries by component (WordPress core, Plugin X, Plugin Y, theme)
  • Filter queries by calling function
  • View aggregate query information grouped by component, calling function, and type
  • Super advanced: Supports multiple instances of wpdb on one page
  • Once enabled from Plugins you will see it appear as a new menu on bottom Admin raw.

An important note to make here is latest Query Monitor extension fails when loaded on current latest Wordpress 4.1, to use it you will have to download and useolder Query Monitor plugin version 2.6.8 you can download it from here

d) Debug Bar

If you want you want a Memory Viewer like plugin for more complex used components memory debugging, reporting if (WP_DEBUG is set in wp-config.php) also check out Debug Bar .
For me Debug Bar was very useful because it show me depreciated functions some plugins used, so I substituted the obsoleted function with new one.

 

debug-bar-debug-wordpress-plugins-memory-use-screenshot-website


4. Server Hardware hungry (slow) WordPress plugins that you better not use

While spending time to Google for some fixes to WP slow query plugins – I've stumbled upon this post giving a good list with WordPress Plugins better off not to use because they will slow down your site
This is a publicly well known list of WP plugins every WordPress based site adminstrator should avoid, but until today I didn't know so my assumption is you don't know either ..

Below plugins are extremely database intensive mentioned in article that we should better (in all cases!) avoid:

  • Dynamic Related Posts
  • SEO Auto Links & Related Posts
  • Yet Another Related Posts Plugin
  • Similar Posts
  • Contextual Related Posts
  • Broken Link Checker — Overwhelms even our robust caching layer with an inordinate amount of HTTP requests.
  • MyReviewPlugin — Slams the database with a fairly significant amount of writes.
  • LinkMan — Much like the MyReviewPlugin above, LinkMan utilizes an unscalable amount of database writes.
  • Fuzzy SEO Booster — Causes MySQL issues as a site becomes more popular.
  • WP PostViews — Inefficiently writes to the database on every page load. To track traffic in a more scalable manner, both the stats module in Automattic’s Jetpack plugin and Google Analytics work wonderfully.
  • Tweet Blender — Does not play nicely with our caching layer and can cause increased server load.


A good Complete list of known WordPress slow plugins that will hammer down your wordpress performance is here

There are few alternatives to this plugins and when I have some free time I will download and test their alternatives but for now I plan the plugins to stay disabled.
 

For the absolute WP Performance Optimization Freaks, its good to check out the native way to Debug a wordpress installation through using few embedded
variables

 

define('WP_DEBUG', true);
define('WP_DEBUG', false);
define('WP_DEBUG_LOG', true);
define('WP_DEBUG_DISPLAY', false);
define('SAVEQUERIES', true);

 

An article describing how you can use native WP debug variables is here


Happy Optimizing  ! 🙂

Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
Posted in Everyday Life, MySQL, System Administration, Various, Web and CMS, Wordpress | 3 Comments »

Improve Website Apache Webserver SEO without Website source code moficitations with Google PageSpeed module on Debian, Ubuntu, CentOS, Fedora and SuSE Linux

Thursday, December 18th, 2014

Improve-website-apache-webserver-seo-without-website-source-code-modifications-with-Google-PageSpeed-Apache-module

For hosting companies and even personal website speed performance becomes increasingly important factor that gives higher and higher weight on overall PageRank and is one of the key things for Successful Site Search Engine Optimization (positioning) in Search Engines of a not specially SEO friendly crafted website.

Virtually all Google / Yahoo / Bing,  Yahoo  etc. Search Engines give better pagerank to websites which load faster and has little or no downtimes, for the reason a faster loading time of a website pages means better user experience and is indicator that the website is well maintained. 

Often websites deployed written for purpose of a business-es or just community CMS / Blog Website Open Source systems such as Joomla, Drupal and WordPress by default are not made to provide fantastic speed right after deploy without install of custom plugins and website tuning, i.e.:

  • Content size optimization (gzipping)
  • More efficient way to deliver CSS / Javascript (MinifyJS / CSS files into single ones
  • HTML optimization
  • Stripping (useful) page Comments
  • Adding <head> if missing on pages etc.

. Therefore as I said in many of my previous LAMP Optimization articles page  (opening) speed could make really Bad Users / Clients experience when the site grows too big or is badly optimized it gives degraded page speed times (often page loads 20 / 30 seconds waiting for the page to load!). Having Pages lagging on big information sites or EShos has both Ruining Company's Image on the market and quickly convinces the user to use another service from the already thosands available and thus drives out (potential) customers.

As Programming code maintainance and improvement is usually very costly, companies that want to save money or can't afford it (because of the shrinking budgets dictacted by the global economic crisis), the best thing to do is to ask your sysadmin to Squeeze the Best out of the WebService and Servers without major (Backend Code) infrastructural changes.

To  Speed up Apache and create Proper Page Caching without installing on server external PHP Caching modules such as Eaccelerator  / PHP APC caching and without
extra CMS modules such as lets say WordPress W3 Total Cache there is Google Develop Apache Webserver external module – PageSpeed.

Here is Google Pagespeed Module overview :
 

PageSpeed speeds up your site and reduces page load time. This open-source webserver module automatically applies web performance best practices to pages and associated assets (CSS, JavaScript, images) without requiring that you modify your existing content or workflow.


What does Apache Google PageSpeed actually does?
 

  • Automatic website and asset optimization
  • Latest web optimization techniques
  • 40+ configurable optimization filters
  • Free, open-source, and frequently updated
  • Deployed by individual sites, hosting providers, CDNs


1. Install PageSpeed on Debian / Ubuntu, deb derivatives) Linux

a) Download and install module 

On 64 bit deb based Linux:

cd /usr/local/src
wget https://dl-ssl.google.com/dl/linux/direct/mod-pagespeed-stable_current_amd64.deb 
dpkg -i mod-pagespeed-stable_current_amd64.deb
apt-get -f install


On 32 bit Linux:

cd /usr/local/src
wget https://dl-ssl.google.com/dl/linux/direct/mod-pagespeed-stable_current_i386.deb
dpkg -i direct/mod-pagespeed-stable_current_i386.deb
apt-get -f install


b) Restart Apache
 

sudo /etc/init.d/apache2 restart

Important files and folders placed on server by deb installer are:

/usr/bin/pagespeed_js_minify – binary that does Javascript minification
/etc/apache2/mods-available/pagespeed.conf – Pagespeed config
/etc/apache2/mods-available/pagespeed.load – Load module directives in Apache
/etc/cron.daily/mod-pagespeed – mod_pagespeed cron script for checking and installing latest updates.
/var/cache/mod_pagespeed – Mod Pagespeed cahing folder (useful to install memcached to increase even further caching performance)
/var/log/pagespeed – Directory to store pagespeed log files

 

2. Install PageSpeed on (RPM based CentOS, Fedora, RHEL / SuSE Linux)


RPM 64 bit package install:
 

rpm -Uvh https://dl-ssl.google.com/dl/linux/direct/mod-pagespeed-beta_current_x86_64.rpm

 


32 bit pack version:
 

rpm -Uvh https://dl-ssl.google.com/dl/linux/direct/mod-pagespeed-stable_current_i386.rpm


Modify pagespeed mod config 

Restart Apache

sudo /etc/init.d/httpd restart


Important config files and folders created during RPM install are:

  • /etc/cron.daily/mod-pagespeed : mod_pagespeed cron script for checking and installing latest updates.
  • /etc/httpd/conf.d/pagespeed.conf : The main configuration file for Apache.
  • /usr/lib/httpd/modules/mod_pagespeed.so : mod_pagespeed module for Apache.
  • /var/www/mod_pagespeed/cache : File caching direcotry for web sites.
  • /var/www/mod_pagespeed/files : File generate prefix for web sites.

3. Configuring Google PageSpeed module

 

To configure PageSpeed you can either edit the package installed bundled pagespeed.conf (/etc/apache2/mods-available/pagspeed.conf,  /etc/httpd/conf.d/pagespeed.conf) or insert configuration items inside Apache VirtualHosts config files or even if you need flexibility and you don't have straight access to Apache config files (on shared hosting servers where module is available) through .htaccess.
Anyways try to avoid adding pagespeed directives to .htaccess as it will be too slow and inefficient.

Configuration is managed by setting different so-called "Rewrite Levels". Default behavior is to use Level of "Corefilters.", a set of filters (module behavior configs) which according to Google is safe for use. PageSpeed Filters is a set of actions applied to Web Delivered files.

Default config setting is hence:
 

ModPagespeedRewriteLevel CoreFilters

Disabling default set of filters is done with:
 

ModPagespeedRewriteLevel PassThrough

"Corefilters" default filter set as of time of writting this article:
 

add_head
combine_css
convert_jpeg_to_progressive
convert_meta_tags
extend_cache
flatten_css_imports
inline_css
inline_import_to_link
inline_javascript
rewrite_css
rewrite_images
rewrite_javascript
rewrite_style_attributes_with_url

Complete documentation on Configuring PageSpeed Filters is here.

If caching is turned on, default PageSped caching is configured in /var/cache/mod_pagespeed/
Enabling someof the non-Corefilters that sometimes are useful for SEO (reduce of served / returned pagesize) are:
 

ModPagespeedEnableFilters pedantic,remove_comments

By default pagespeed does some things (such as inline_css, inline_javascript and rewrite_images (Optimize, removing Excess pixels).  My litle experience with pagespeed shows in some cases this could break websites), so I found for my case useful to disable some of the filters:

 

vim /etc/apache2/mods-available/pagespeed.conf

 

ModPagespeedDisableFilters rewrite_images,convert_jpeg_to_progressive,inline_css,inline_javascript

 

4. Testing if PageSpeed is Enabled pagespeed_admin

By default PageSpeed has Admin which by default is only allowed to be accessed from server localhost (127.0.0.1) to get basic statistics either install text browser like lynx / elinks or add more access IPs again in pagespeed config / vhosts pagespeed.conf include more Allow lines like below:

 

    <Location /pagespeed_admin>
        Order allow,deny
        Allow from localhost
        Allow from 127.0.0.1
        Allow from 192.168.1.1
        Allow from xxx.xxx.xxx.xxx
        #Allow from All
        SetHandler pagespeed_admin
    </Location>
    <Location /pagespeed_global_admin>
        Order allow,deny
        Allow from localhost
        Allow from 127.0.0.1
        Allow from 192.168.1.1
        Allow from xxx.xxx.xxx.xxx
        SetHandler pagespeed_global_admin
    </Location>

 

Once configured pagespeed_admin access it with favourite browser on:

http://127.0.0.1/pagespeed_admin
http://127.0.0.1/pagespeed_global_admin

improve-website-apache-webserver-seo-without-source-code-modifications-google-pagespeed_admin_panel

Other way to test it is enabled is by creating php file with good old <? phpinfo(); ?> – PHP stats enabled / disabled features code:

pagespeed-in-phpinfo-x-mod-pagespeed-output-screenshot-apache-webserver

I've also tested also pagespeed unstable release, but experienced some segmentation faults in both error.log and access.log so finally decided to keep using stable release.

PageSpeed is a great way to boost your server sites performance, however it comes on certain costs as expect your server CPU Load to jump drastically, (in my case it jumped more than twice), there are Linux servers where enabling the module could totally stone the servers, so before implementing the module on a Production system environment, always first test thouroughfully with loaded pagespeed on UAT (testing) environment with AB or Siege (Apache Benchmarking Tools).

Tags: , , , , , , , , , , , , , , , , , , ,
Posted in Everyday Life, System Administration, Various, Web and CMS | 2 Comments »

xkill for Windows – Kill hanged programs with one click like in UNIX

Thursday, September 18th, 2014

Windows Xkill by Solo Dev logo kill easily hanged, crashed windows programs - Linux xkill for Windows alternative

I’ve used Linux as I’ve used it over the last 10 years and thus I’m so used to xkill that I consider it as a normal tool every modern operating system should have.

Since I’m forced to work on a Windows platform over the last 1 year, every now and then I have crashing / hanged apps Window which sometimes is hard to kill using Task Manager or command line tools like tasklist, taskkill or pstools – Windows Sysinternal tools cause you don’t know the exact process name of the Windows crashed application.

Thanksfully a good hearted guy SuprVillain made Windows Xkill program. Windows Xkill is portable app, so you don’t need to install it but simply download and run it.

windows-7-system-tray-xkill-screenshot-xkill-for-windows

Win-Xkill runs in system tray and has “kill mode” of operation, when Kill mode it is running Windowns Xkill operates exactly like UNIX’s xkill. The mouse pointer turns into Skull and Cross Bones, you point at a Window app you wanted to kill and it gets terminated.
Windows Xkill kill mode enabled killing a windows notepad app
Win Xkill kill mode, can be invoked using also a key press Control+Alt+Backspace as well as there is an option to leave xkill running in the background background, but disabling the tiny skull icon in systray from its interface.
Enjoy 🙂

Tags: , , , , , , , , ,
Posted in Everyday Life, System Administration, Various, Windows | 1 Comment »

mod_rewrite redirect rule 80 to 443 on Apache webserver

Wednesday, April 2nd, 2014

A classic sysadmin scenario is to configure new Apache webserver with requirement to have an SSL ceriticate installed and working on port 443 and all requests coming on port 80 to be redirected to https://.
On Apache this is done with simple mod_rewrite rule:

RewriteEngine On
RewriteCond %{HTTPS} off
RewriteRule (.*) https://%{HTTP_HOST}%{REQUEST_URI}

Before applying the rule don't forget to have Apache mod_rewrite enabled usually it is not enabled on default most Linux distributions by default.
On shared hostings if you don't have access to directly modify Apache configuration but have .htaccess enabled you can add above rules also to .htaccess

Add this to respective VirtualHost configuration and restart Apache and that's it. If after configuring it for some reason it is not working debug mod_rewrite issues by enabling mod_rewrite's rewrite.log

Other useful Apache mod_rewrite redirect rule is redirect a single landing page from HTTP to HTTP

RewriteEngine On
RewriteRule ^apache-redirect-http-to-https.html$ https://www.site-url.com/apache-redirect-http-to-https.html [R=301,L]

!Note! that in case where performance is a key requirement for a website it might be better to use the standard way to redirect HTTP to HTTPS protocol in Apache through:

ServerName www.site-url.com Redirect / https://www.site-url.com/

To learn more on mod_rewrite redirecting  check out this official documentation on Apache's official site.

Tags: , , , , , , , , , , , , , , , ,
Posted in System Administration, Various, Web and CMS | No Comments »

Apache SSLCertificateChainFile adding SSL with Certificate Chain / What is Certificate Chain

Friday, January 31st, 2014

configure-apache-ssl-certificate-chain-ssl-certificate-keychain-each-signing-each-other

If you work in a big company with large network infrastructure who has to deal with SSL Certificates you will sooner or later will have to learn about existence of SSL Certificate Chains.
Its worthy thus to know what is SSL Certificate Chains and how such a chain is configured in Apache?

Personal SSL certificates (certificates issued to an individual or a company) can be used by clients to uniquely identify themselves when they are involved in starting an SSL connection.
SSL Certificate file contains X.509 certificate, which, in turn, contains a public key used for encryption.
Each personal certificate has zero or more certificate chains of certification authority certificates that extend back to the root certification authority.
 

Certificate R (Root Certification Authority)
|
| represents issuer of
V
Certificate I1 (Intermediate Certification Authority)
|
| represents issuer of
V
Certificate I2 (A subsidiary Intermediate Certification Authority)
|
| represents issuer of
V
Certificate I3 (A further subsidiary Intermediate Certification Authority)
|
| represents issuer of
V
Certificate P (A personal certificate that is used to identify its owner 
on an SSL handshake)

Certificate chains are used to verify the authenticity of each certificate in that chain, including the personal certificate. Each certificate in the chain is validated using its 'parent' certificate, which in turn is validated using the next certificate up the chain, and so on, from the personal certificate up to the root certification authority certificate.

Now after explaining thoroughfully what is SSL Certificate Chain, here is how to configure a SSL Certificate in Apache Webserver.

Open apache2.conf or httpd.conf (depending on GNU / Linux distribution) and add to it;

  SSLEngine On
   SSLCertificateFile conf/cert/webserver-host.crt
   SSLCertificateKeyFile conf/cert/webserver-host.key
   SSLCertificateChainFile conf/cert/internet-v4.crt
   # SSLCertificateChainFile conf/cert/intranet-v3.crt
   SSLOptions +StdEnvVars +OptRenegotiate +ExportCertData

SSLCertificateChainFile conf/cert/chain-cert.crt
loads a chain of separate Personal SSL certificates each signing each other on different levels, chain is leading to top ROOT CA (Certificate Authority).

Tags: , , , , , , , , , , , , , , , , , , , ,
Posted in Linux, System Administration, Web and CMS | 1 Comment »

Adding Listing and Deleting SSL Certificates in keystore Tomcat Application server / What is keystore

Thursday, December 5th, 2013

Apache Tomcat keystore delete import list logo

 I work on ongoing project where Tomtat Application servers configured to run Clustered located behind Apache with mod_proxy configured to use ReverseProxy are used. One of customers which required a java application deployment experienced issues with application's capability to connect to SAP database.

After some investigation I figured out, the application is unable to connect to the SAP db server becuse remote host webserver running some SAP related stuff was not connecting due to expired certificate in Tomcat Keystore known also as JKS / Java Keystore– (.keystore) – which is a file containing multiple remote hosts imported certificates.

The best and shortest definition of keystore is:

Keystore entry = private + public key pair = identified by an alias

The keystore protects each private key with its individual password, and also protects the integrity of the entire keystore with a (possibly different) password.

Managing Java imported certificates later used by Tomcat is done with a command line tool part of JDK (Java Development Kit) called keystore. Keystore is usually located under /opt/java/jdk/bin/keytool. My Java VM is installed in /opt/ anyways usual location of keytool is $JAVA_HOME/bin/

Keytool has capabilities to create / modify / delete or import new SSL certificates and then Java applications can access remote applications which requires Secure Socket Layer handshake . Each certificate kept in .keystore file (usually located somewhere under Tomcat web app server directory tree), lets say – /opt/tomcat/current/conf/.keystore

1. List current existing imported SSL certificates into Java's Virtual Machine

tomcat-server:~# /opt/java/jdk/bin/keytool -list -keystore /opt/tomcat/current/conf/.keystore
password:
 Command returns output similar to;

Entry type: trustedCertEntry

Owner: CN=www.yourhost.com, OU=MEMBER OF E.ON GROUP, OU=DEVICES, O=E.GP AG, C=DE
Issuer: CN=E.ON Internal Devices Sub CA V2, OU=CA, O=EGP, C=DE
Serial number: 67460001001c6aa51fd25c0e8320
Valid from: Mon Dec 27 07:05:33 GMT 2010 until: Fri Dec 27 07:05:22 GMT 2013
Certificate fingerprints:
         MD5:  D1:AA:D5:A9:A3:D2:95:28:F1:79:57:25:D3:6A:16:5E
         SHA1: 73:CE:ED:EC:CA:18:E4:E4:2E:AA:25:58:E0:2B:E4:D4:E7:6E:AD:BF
         Signature algorithm name: SHA1withRSA
         Version: 3

Extensions:

#1: ObjectId: 2.5.29.15 Criticality=true
KeyUsage [
  DigitalSignature
  Key_Encipherment
  Key_Agreement
]

#2: ObjectId: 2.5.29.19 Criticality=true
BasicConstraints:[
  CA:false
  PathLen: undefined
]

#3: ObjectId: 1.3.6.1.5.5.7.1.1 Criticality=false
AuthorityInfoAccess [
  [
   accessMethod: 1.3.6.1.5.5.7.48.2
   accessLocation: URIName: http://yourhost.com/cacerts/egp_internal_devices_sub_ca_v2.crt,
   accessMethod: 1.3.6.1.5.5.7.48.2
   accessLocation: URIName: http://www.yourhost1.com/certservices/cacerts/egp_internal_devices_sub_ca_v2.crt]
]

#4: ObjectId: 2.5.29.14 Criticality=false
SubjectKeyIdentifier [
KeyIdentifier [
0000: D3 52 C7 63 0F 98 BF 6E   FE 00 56 5C DF 35 62 22  .R.c…n..V\.5b"
0010: F2 B9 5B 8F                                        ..[.
]
…

Note that password that will be promtped has is by default changeit (in case if you don't have explicitly changed it from Tomcat's default config server.xml).

2. Delete Old expired SSL host Certificate from Java Keystore
It is good practice to always make backup of old .keystore before modifying, so I ran:

tomcat-server:~# cp -rpf /opt/tomcat/current/conf/.keystore /opt/tomcat/current/conf/.keystore-05-12-2013
In my case first I had to delete old expired SSL certificate with:

tomcat-server:~# /opt/java/jdk/bin/keytool -delete -alias "your-hostname" -v -keystore /opt/tomcat/current/conf/.keystore
Then to check certificate is no longer existent in keystore chain;
tomcat-server:~# /opt/java/jdk/bin/keytool -list -keystore /opt/tomcat/current/conf/.keystore
-keystore – option is obligitory it does specify where keystore file is located
-list – does list the certificate
-v – stands for verbose

 

3. Finally to import new SSL from already expored via a browser url in keystore

tomcat-server:~# /opt/java/jdk/bin/keytool -importcert -file /tmp/your-hostname.cer -alias your-hostname.com -keystore /opt/tomcat/current/conf/.keystore
More complete information on how to deal with keystore is available from Apache Tomcat's SSL Howto – a must read documentation for anyone managing Tomcat.

Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , ,
Posted in System Administration, Various, Web and CMS | 5 Comments »

How to generate self signed SSL certificate with openssl on Linux / BSD

Wednesday, November 6th, 2013

How to generate self signed SSL certificate with openssl on Linux / BSD

It is common solution for personal use to generate SSL certificates which are self-signed. Self-signed certificates are dangerous as no authority or company guarantees that remote site is trustable. However for private use having encrypted connection whether you need to transfer personal data is better than not having such. There are plenty of tutorials online pointing how to set-up Apache webserver to provide access via SSL port 443 with self-signed certifacate, but anyways I decided to blog here a one-liner command way, which makes generating self-signed certificate a piece of cake. Self-signed certificates on UNIX are generated with openssl command part of openssl (Secure Socket Layer cryptocgraphic tools).

On Debian Linux to install openssl (if you still don't have it):

apt-get install --yes openssl

On Fedora, RHEL, CentOS etc. same install should be done with:

yum install -y openssl

On FreeBSD to install it use ports;

cd /usr/ports/security/openssl
make install clean

Once openssl is available, here is command to generate self signed SSL certitifacate;
 

openssl req -x509 -nodes -days 3650 -newkey rsa:2048 -keyout my-sitename.key -out my-sitename.crt
Generating a 2048 bit RSA private key
............................................+++
..............................................................+++
writing new private key to 'key.pem'
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:BG
State or Province Name (full name) [Some-State]:Sofia
Locality Name (eg, city) []:Sofia
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Pc Freak
Organizational Unit Name (eg, section) []:Pc Freak
Common Name (eg, YOUR name) []:www.pc-freak.net
Email Address []:testing@www.pc-freak.net

The generated certificate Private Key file is placed in my-sitename.key
, actual certificate is located in my-sitename.crt -days option tells for how long period certificate will be valid. Regenerating certificate every year (360 days) is good security practice but it is pretty annoying to see your certificate has expered thus for private self signed SSL certificate it is more confortable to generate cert for 10 years time.
To use my-sitename.key and my-sitename.crt copy them to /etc/ssl/crt/

cp -rpf my-sitename.crt /etc/ssl/crt/
cp -rpf my-sitename.key /etc/ssl/crt/

Next what's left is to configure Apache to use new generated certs. Quickest way is to add it inside virtual host. Adding to Apache also depends on Linux distribution where SSL has to be added but in general, what should work is something like:

SSLEngine on
SSLCertificateFile /etc/ssl/crt/my-sitename.crt
SSLCertificateKeyFile /etc/ssl/crt/my-sitename.key
Note that if SSL has to be enabled for specific Virtual Host you will have to place above Apache directives into the Vhost. Though certifiate will only be trusted by your authority RSA 2048 bit encryption in transfer data between your Webserver and Browser should guarantee you extra security of data, not that it is impossible for data to be still sniffed by a skilled hacker but makes data securily transferred in probably 99% of cases 🙂

Tags: , , , , , , , , , , , ,
Posted in Computer Security, System Administration, Web and CMS | 2 Comments »

« Older Entries