How to check Client SSL cert from certain IPs only

Lately, I had a requirement to configure Apache reverse proxy in such a way to ensure any user logging through the Apache reverse proxy is presenting a valid client certificate if they are not internal users.

Internal users using local network IPs and local domain names should be allowed to access without any certificate validation.

To achieve this, we can use the below simple construct if we are using Apache 2.4.x:

<If "%{REMOTE_ADDR} !~ /^127.0.0.1$/ && %{REMOTE_ADDR} !~ /^192.168.[0-9]+.[0-9]+$/">

        SSLVerifyClient require

        SSLVerifyDepth 2

</If>

In case we are using Apache 2.2, the config needs to involve mod_rewrite as below:

SSLVerifyClient optional

SSLVerifyDepth  2

<Location / >

        Order deny,allow

        Deny from all

        Satisfy any

        Allow from ALL

        RewriteEngine on

        RewriteCond %{SSL:SSL_CLIENT_VERIFY} !^SUCCESS$

        RewriteCond %{REMOTE_ADDR} !^127.0.0.1$

        RewriteCond %{REMOTE_ADDR} !^192.168.1.[1-9]+$

        RewriteRule   ^  -  [F]

</Location>

Using SSL verifyclient optional will still try to verify clients but will not block their access.

This, allows us to test if the verification worked or not with mod_rewrite as seen above.

WebSite SSL cert. check script

There was a need to be able to check SSL certs for certain set of web services / site out of a central point without the having the actual cert.
To do this, I made use of openssl s_client functionality.

The below script takes a single parameter, the hostname:port and does the validation.
The script has 4 timing checks so it needs to be run every day to be able to catch the actual certificate expiration time.
Once it detects an expiring cert, it will send out an email given that sendmail is configured on the server that runs it.

[root@Beren check_certs]# cat check_certs.sh
#set -x

Check_CERTs ()
{
TARGET="$1"
TEST_DATE31=`date "+%b %e %T %Y %Z" -d "+31 day" |tr -s " "|cut -d" " -f1,2,4`
TEST_DATE21=`date "+%b %e %T %Y %Z" -d "+21 day" |tr -s " "|cut -d" " -f1,2,4`
TEST_DATE11=`date "+%b %e %T %Y %Z" -d "+11 day" |tr -s " "|cut -d" " -f1,2,4`
TEST_DATE03=`date "+%b %e %T %Y %Z" -d "+3 day" |tr -s " "|cut -d" " -f1,2,4`

SSL_TEST_CMD=" echo |openssl s_client -connect ${TARGET} 2>/dev/null|openssl x509 -inform pem -noout -text |grep \"Not After :\" |
cut -d\":\" -f2-|tr -s \" \"|cut -d\" \" -f2,3,5"

SSL_EXP_DATE=`bash -c "${SSL_TEST_CMD}"`

##SSL_EXP_DATE="Feb 9 2017"

echo $SSL_EXP_DATE

if [ "$SSL_EXP_DATE" == "$TEST_DATE31" ]
then
    echo "31 days left"
elif [ "$SSL_EXP_DATE" == "$TEST_DATE21" ]
then
        echo "21 days left"
elif [ "$SSL_EXP_DATE" == "$TEST_DATE11" ]
then
        echo "11 days left"
elif [ "$SSL_EXP_DATE" == "$TEST_DATE03" ]
then
        echo "3 days left"
fi
 
#End of the shell function.
}

#Main code:

CERT_TIME=`Check_CERTs "${1}"|grep left`

if [ -n "$CERT_TIME" ]
then

echo "Sending Email"

/usr/sbin/sendmail.sendmail -i -t << ENDL
From: "Script Cert Alert"
To: <sherif.abdelfattah@live.com>
Subject: CERT Expiration WaRNing

Please check the SSL Certs installed on ${1} !!
The certificate is about to expire !!
${CERT_TIME}


Please take action ASAP.

ENDL

fi[root@Beren check_certs]#

The script uses Linux gnu date, that can take a time string using -d option, also we make use of the %e which uses space padded days of the month similar to OpenSSL command date format.
Also note the use of the "echo |openssl" construct, this prevents openssl command from waiting for further input thus, we can run it from a script.

A simple script to check Redis Cluster

Below is a simple script around the redis-cli tool that aims to give info about a replica cluster and to identify the redis master node.
This can be a simple fast check for redis.

Below is the script:

[redis@feanor]$ cat check_redis.sh
echo " "
REDIS_BIN=.
echo "Getting Master Info from Sentinel"

MASTER_IP=`$REDIS_BIN/redis-cli -p 9000 info sentinel | grep "master0:name=mymaster"|cut -d"," -f3|cut -d"=" -f2|cut -d":" -f1`
MASTER_PORT=`$REDIS_BIN/redis-cli -p 9000 info sentinel | grep "master0:name=mymaster"|cut -d"," -f3|cut -d"=" -f2|cut -d":" -f2`
MASTER_STATUS=`$REDIS_BIN/redis-cli -p 9000 info sentinel | grep "master0:name=mymaster"|cut -d"," -f2|cut -d"=" -f2`
MASTER_HOSTNAME=`nslookup $MASTER_IP|grep "name = " |cut -d"=" -f2`

echo "Master IP is: $MASTER_IP"
echo "Master Port is: $MASTER_PORT"
echo "Master Hostname is: $MASTER_HOSTNAME"
echo "Master Status is:  $MASTER_STATUS"
echo " "
echo "Replication info from Master:"
$REDIS_BIN/redis-cli -h $MASTER_IP -p $MASTER_PORT info replication
[redis@feanor]$

In this case Redis-server runs on port 7000 and Redis-Sentinel on port 9000.

I have allowed the script to extract the port form sentinel output as at times we have more than one Redis instance running on the same VM, thus using same IP but different port.

Rundeck Parametric Job Definition

I have a use case where I need to run same set of shell instructions multiple times with different users and compare the results on remote hosts.

Thought about automating this using Rundeck, and since I am not allowed to use ssh keys password-less authentication in this case, Rundeck would be ideal.

below is a sample job listing (XML) for a parametric job that accepts username and a password and just prints the username in a simple embedded shell script.

<joblist>
  <job>
    <context>
      <options preserveOrder='true'>
        <option name='Password' required='true' secure='true' valueExposed='true' />
        <option name='User_Name' required='true' value='App_User' />
      </options>
    </context>
    <description></description>
    <executionEnabled>true</executionEnabled>
    <id>5e63d0fa-9bae-4d1d-bc09-a2d8429f8b19</id>
    <loglevel>INFO</loglevel>
    <name>Parametrized_job1</name>
    <scheduleEnabled>true</scheduleEnabled>
    <sequence keepgoing='false' strategy='node-first'>
      <command>
        <script><![CDATA[echo "This is a test Job"
echo 'using form $RD_OPTION_USER_NAME' $RD_OPTION_USER_NAME
echo 'using form ${option.User_name}' ${option.User_Name} #doesn't work :)
echo 'using form \@option.User_name\@' @option.User_Name@
]]></script>
        <scriptargs />
      </command>
    </sequence>
    <uuid>5e63d0fa-9bae-4d1d-bc09-a2d8429f8b19</uuid>
  </job>
</joblist>

The job parameters are better accessed as passed by Rundeck env. as:$RD_OPTION_paramname

The above job option env. variable will be passed to all rundeck executed scripts so it is much easier to use it in this form than the one between @ signs.
The form ${option.User_Name} is not working in this case as Rundeck will use it as a command-line argument if we are running a commad step rather than an embedded script.

below is an example job definition:

<joblist>
  <job>
    <context>
      <options preserveOrder='true'>
        <option name='Password' required='true' secure='true' valueExposed='true' />
        <option name='User_Name' required='true' value='App_User' />
      </options>
    </context>
    <description></description>
    <executionEnabled>true</executionEnabled>
    <id>5d1db9a0-d381-49ec-8981-c483b375751c</id>
    <loglevel>INFO</loglevel>
    <name>Parametrized_job2</name>
    <scheduleEnabled>true</scheduleEnabled>
    <sequence keepgoing='false' strategy='node-first'>
      <command>
        <exec>echo ${option.User_Name}</exec>
      </command>
    </sequence>
    <uuid>5d1db9a0-d381-49ec-8981-c483b375751c</uuid>
  </job>
</joblist>

Rundeck parametric jobs will prove very useful for doing generic tasks and generic automation.

Please check more info in Rundeck documentation at: http://rundeck.org/1.6.2/manual/job-options.html#prompting-the-user

JQuery Draggable and Droppable demo

I was exploring a way to use simple HTML UI components to offer UI drag and drop functionality.
This is a long term plan I was having to start real automation of my day to day work and avoid executing or editing shell scripts or puppet manifests.

JQuery offers a good simple API to allow drag and drop fuctionaltity with a lot of flexiability.

Since I am not UX-UI designer, it took me quite sometime to understand how it works and write down a working demo code for the functionality.

Below is a simple code for simple HTML table (TD elements) having draggable numbers from 1 to 8 and are only droppable inside the HTML table.

<html>

<head>
<style>
table, th, td {
    border: 5px solid black;
    text-align: center;
    color: blue;
    font-size: 300%;
    font-weight: bold;
    border-spacing: 10px;
}

</style>


<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>

<script>

// Make the element with id "draggable" draggable
$(function () {
   
    count = 0;

    $("#td1").draggable({revert: "invalid", containment: "document", grid: [220,210] });
    $("#td2").draggable({revert: "invalid", containment: "document", grid: [220,210] });
    $("#td3").draggable({revert: "invalid", containment: "document", grid: [220,210]});
    $("#td4").draggable({revert: "invalid", containment: "document", grid: [220,210]});
    $("#td5").draggable({revert: "invalid", containment: "document", grid: [220,210]});
    $("#td6").draggable({revert: "invalid", containment: "document", grid: [220,210]});
    $("#td7").draggable({revert: "invalid", containment: "document", grid: [220,210]});
    $("#td8").draggable({revert: "invalid", containment: "document", grid: [220,210]});
   
    $( "#tb1" ).droppable({
      drop: function(event, ui) {
      Edit_dragable (  ui.draggable );
      //$("#logger").html( "Dropped! ");
      //$("#logger2").html( "Testing" );
    }
    });
   
   
        function Edit_dragable( $item ) {
        $item
          .css( "color", "Yellow" );
          var textval = 'The number is ' + $item.html() + ' & the td id was: ' + $item.attr('id');
          $("#logger2").html( textval );
      }
   
});
</script>

</head>

<table id="tb1" >
<caption>Test arrange game </caption>
 <tr id="tr1">
    <td id="td1" style="width:200;  height:200; border-color: green"> 1 </td>
    <td id="td2" style="width:200;  height:200;"> 2 </td>
    <td id="td3" style="width:200;  height:200;"> 3 </td>
</tr>

 <tr id="tr2">
    <td id="td4" style="width:200;  height:200"> 4 </td>
    <td id="td5" style="width:200;  height:200;"> 5 </td>
    <td id="td6" style="width:200;  height:200;"> 6 </td>
</tr>

 <tr id="tr3">
    <td id="td7" style="width:200;  height:200;"> 7 </td>
    <td id="td8" style="width:200;  height:200;"> 8 </td>
   
    <!--<td id="td9" draggable="true" ondragstart="drag(event)"> 9 </td>-->
</tr>
</table>

<p id="logger"> </p>
<p id="logger2"> </p>

The code involves some HTML, CSS styles and JQuery to be able to achieve the functionality.

Next steps is to combine this with my other code components to offer an interactive control dash board for automating my day to day tasks.
Will continue to post as I progress in this work.

Haproxy CSV stats

Haproxy exposes a very useful statistics page that can be accessed over a web browser or from the command line using a tool like curl.
Haproxy allows the stats to be exposed in a CSV format that is exceedingly useful if you are going to have a script around it.

To access the CSV stats from http interface use the below:

curl  -u statsuser:statspassword 'http://haproxyhost:8001/stats/?stats;csv;' 2>/dev/null |egrep -iv "up|^#|open" |cut -d"," -f1,2,18

Where port 8001 is the statistics port as defined in the haproxy config.
The config is simple, already shown in older posts, should be something like this:

listen stats *:8001
    stats enable
    stats uri /stats
    stats hide-version
    stats auth statsuser:statspassword


The above mini script will print out only the apps and backends that show as down.
working ones will not show up.
The CVS header is also stripped as the header always starts with a "#" this makes it possible to process only the data using a grep -v as above.

The CSV result contains a big amount of info that can be used for load management and automation.
The details of the haproxy CSV stats layout can be found at:
https://cbonte.github.io/haproxy-dconv/1.5/configuration.html#9  

Updating a field of a DB table using records from same table

The below query is to update a field that is set to null in a config table from records that contain values in the same table.

I came across this scenario while working on a puppet upgrade project where we are moving the file based Yaml configs to SQL DB.


below is the query:

update config_dev c

set groupname = l.groupname

from ( select distinct (groupname) , name from config_dev where groupname is not null ) l

where c.groupname is null

and c.name = l.name

The table contains some "groupname" fields that contains null, still those should contain same group values as others given that name field is the same.

the above query will do that for us.
Not that the above query will not run in MySQL !!

Thanks to my friend Mohamed Youssef who helped me with this query.