Syed Jahanzaib Personal Blog to Share Knowledge !

July 23, 2019

RM: Delete Expired Users Record

Filed under: Radius Manager — Tags: — Syed Jahanzaib / Pinochio~:) @ 12:57 PM

expired.jpg


Following script was made for DMA Radius Manager 4.1.x. It can delete X months old Expired users record from the mysql DB.

Sharing for reference purposes …

WordPress is not letting proper pasting of the code …

delete expired uesr.PNG

#!/bin/sh
#set -x
# This script delets users who have expired 2 months ago. and then delete there records from all tables.
# Syed Jahanzaib / June 2019
SQLPASS=”SQLPASS”
export MYSQL_PWD=$SQLPASS
> /tmp/expired.users.txt

#mysql -uroot -e “use radius; select username from rm_users where expiration BETWEEN ‘2010-01-01’ AND ‘2019-04-30’;” |sort > /tmp/expired.users.txt

# Fetch users who have expired 2 months ago & before, (using expired date), BE CAREFUL WHEN USING THIS
mysql -uroot -e “use radius; select username from rm_users where expiration <= DATE_SUB(CURDATE(), INTERVAL 2 MONTH)” |sort > /tmp/expired.users.txt
num=0
cat /tmp/expired.users.txt | while read users
do
num=$[$num+1]
USERNAME=`echo $users | awk ‘{print $1}’`
echo “$USERNAME —- user record from all relevant tables”
mysql -uroot -e “use radius; DELETE FROM rm_cards WHERE cardnum = ‘$USERNAME’;”
mysql -uroot -e “use radius; DELETE FROM rm_users WHERE username = ‘$USERNAME’;”
mysql -uroot -e “use radius; DELETE FROM rm_changesrv WHERE username = ‘$USERNAME’;”
mysql -uroot -e “use radius; DELETE FROM radcheck WHERE username = ‘$USERNAME’;”
mysql -uroot -e “use radius; DELETE FROM radacct WHERE username = ‘$USERNAME’;”
mysql -uroot -e “use radius; DELETE FROM rm_radacct WHERE username = ‘$USERNAME’;”
done

 

Jz

July 17, 2019

BASH: Exporting MYSQL DB to Remote Server

Filed under: Linux Related — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 10:28 AM

mysql-export-import

Disclaimer: This post is shared just for reference & learning purposes. You must modify and add more failsafe check before using it in production.

Regards
Syed Jahanzaib


Scenario:

We are using Freeradius server which uses mySQL as its backend DB. Ideally the mysql server should have replica server so that if Primary goes down dueto any fault, the secondary replica should come in action.

For high availability purposes we we want to have a standby server. Mysql Master-Slave or Master-Master replication is ideal for real time replication. We successfully implemented this model at few sites, but yes replication requires constant monitoring, and at one place the secondary replica server backfired & caused data loss.

For one particular Remote Site we wanted to avoid the complications of REPLICATION. What we wanted is a standby server, and the DB from primary should be exported to secondary replica server daily in morning and emails for the actions taken by the script should be emailed to us.

We made custom script that is running successfully from quite some time.

The BASH script performs following function …

  • Checks secondary server PING response
  • Check secondary server SSH access
  • Checks primary server MYSQL DB access
  • Checks secondary server MYSQL DB access
  • Check if exported DB is of valid size, (I set it to min 10 KB, yes you may want to adjust it according to your setup)
  • If all OK, then export primary server DB, and import it to secondary server

Script Requirements:

https://aacable.wordpress.com/2011/11/25/howto-login-on-remote-mikrotik-linux-without-password-to-execute-commands/


BASH Script Code:

  • touch /temp/update_radius_from_10.0.0.1__TO__10.0.0.2.sh
  • chmod +x /temp/update_radius_from_10.0.0.1__TO__10.0.0.2.sh
  • nano /temp/update_radius_from_10.0.0.1__TO__110.0.0.2.sh
#!/bin/bash
clear
#set -x
# Version 1.0 / 10-July-2019
# Syed Jahanzaib / Web: https://aacable.wordpress.com / Email: aacable@hotmail.com
# This script exports mysqldb and restores it to second remote server
# Requires passwordless login on remote server using SSH keys
# Settings various VARIABLES for the script
# adding dns for resolving
echo "nameserver 8.8.8.8" > /etc/resolv.conf
#SET DATE TIME
set $(date)
time=`date |awk '{print $4}'`
YESTERDAY=`date --date='yesterday' +%Y-%m-%d`
TODAY=`date +"%d-%b-%Y__%T"`
SCRIPTST=`date +"%d-%b-%Y__%T"`
HOSTNAME=`hostname | sed 's/ //g'`
IP1=10.0.0.1
IP2=10.0.0.2
IP2ROLE="RADIUS"
IP2_SSH_PORT=22
SQL_DIR="sql_replica"
#MYSQL DETAILS
SQLUSER="root"
SQLPASS="TYPE.YOUR.SQL.ROOT.PASS"
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLUSER --skip-column-names -s -e"
DB="radius"
FILE="/$SQL_DIR/$HOSTNAME.$TODAY.IP.$IP1.sql"
GMAILID="YOUR_SENDER_GMAILID@gmail.com"
GMAILPASS="GMAIL_PASS"
ADMINMAIL1="aacableATATAThotmail.com"
COMPANY="ZAIB"
RESULT="/tmp/$IP2.$IP2ROLE.txt"
CLIENTS_FILE="/usr/local/etc/raddb/clients.conf"
PING_ATTEMPTS="2"
PING_RESULT="/tmp/$IP2.$IP2ROLE.ping.result.txt"
IP2_SSH_CHK="/tmp/$IP2.ssh.chk.txt"
touch $RESULT
touch $PING_RESULT
> $RESULT
> $PING_RESULT
rm -f /$SQL_DIR/*.sql
# Test PING to device
count=$(ping -c $PING_ATTEMPTS $IP2 | awk -F, '/received/{print $2*1}')
if [ $count -eq 0 ]; then
echo "- $COMPANY ALERT: $IP2 - $IP2ROLE is not responding to PING Attempts, cannot continue without it , Please check !"
echo "- $COMPANY ALERT: $IP2 - $IP2ROLE is not responding to PING Attempts, cannot continue without it , Please check !" > $PING_RESULT
sendemail -t $email -u "ALERT: $IP2 $IP2ROLE NOT RESPONDING!" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$PING_RESULT -o message-content-type=text
exit 1
fi
echo "- Script start time: $SCRIPTST

This report contains DB export results.

- Source Server : $HOSTNAME / $IP1
- Destination Server : $IP2

- PING Result to $IP2 : OK"

echo "- Script start time: $SCRIPTST

This report contains DB export results.

- Source Server : $HOSTNAME / $IP1
- Destination Server : $IP2
- PING Result to $IP2 : OK" >> $RESULT

#Cehck if SSH is accessible
scp -q -P $IP2_SSH_PORT root@$IP2:/etc/lsb-release $IP2_SSH_CHK
# Verify if file is downloaded from remote server via ssh
if [ ! -f $IP2_SSH_CHK ]; then
echo -e "- $COMPANY ALERT: $IP2 - $IP2ROLE is not responding to passwordless SSH ACCESS, cannot continue without it , Please check !"
exit 1
fi
echo -e "- SSH Access to $IP2 : OK"
echo -e "- SSH Access to $IP2 : OK" >> $RESULT

# Check if $DB (in this case radius )is accessible or not, if NOT, then exit the script
RESULT_DB_CHK=`$CMD "SHOW DATABASES LIKE '$DB'"`
if [ "$RESULT_DB_CHK" != "$DB" ]; then
echo "- ALERT: $IP1 - DB $DB not accessible !!!"
echo "- ALERT: $IP1 - DB $DB not accessible !!!" >> $RESULT
sendemail -t $email -u "- ALERT: $IP1 - DB $DB not accessible" -o tls=yes -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$RESULT -o message-content-type=text
exit 1
fi

echo "- $DB - Database accessed on $IP1 : OK" >> $RESULT

#############################################
######## START the BACKUP PROCESS ... #######
#############################################
# Checking if $SQL_DIR folder is previously present or not . . .
{
if [ ! -d "/$SQL_DIR" ]; then
echo -e "- ALERT: /$SQL_DIR folder not found, Creating it MYSQL EXPORT/DUMP backup should be placed there . . ."
mkdir /$SQL_DIR
else
echo -e "- INFO: $SQL_DIR folder is already present , so no need to create it, Proceeding further . . ."
fi
}

mysqldump -u$SQLUSER -p$SQLPASS --single-transaction=TRUE --ignore-table={radius.radacct} $DB > $FILE
# CHECK FILE SIZE AND COMPARE, IF ITS LESS , THEN ALERT
SIZE=`ls -lh $FILE | awk '{print $5}'`
SIZEB=`ls -l $FILE | awk '{print $5}'`
if [ $SIZEB -lt 1 ]
then
echo "- ALERT: DB export failed on $IP1 - Size = $SIZE OR $SIZEB Bytes"
echo "- ALERT: DB export failed on $IP1 - Size = $SIZE OR $SIZEB Bytes" >> $RESULT
sendemail -t $email -u "ALERT: DB export failed on $IP1 - Size = $SIZE OR $SIZEB Bytes" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$RESULT -o message-content-type=text
exit 1
fi
#ssh -p $IP2_SSH_PORT root@$IP2 mkdir /$SQL_DIR
#scp -P $IP2_SSH_PORT $FILE_FINAL root@$IP2:/$SQL_DIR
#ssh -p $IP2_SSH_PORT root@$IP2 ls -lh /$SQL_DIR
# Import file in secondary radius
#ssh -p $IP2_SSH_PORT root@$IP2 "mysql -u$SQLUSER -p$SQLPASS $DB < $FILE
#mysql -h $IP2 -u$SQLUSER -p$SQLPASS $DB < $FILE
ssh -p $IP2_SSH_PORT root@$IP2 mysql -u$SQLUSER -p$SQLPASS $DB  output
#scp -P $IP2_SSH_PORT $CLIENTS_FILE root@$IP2:/usr/local/etc/raddb/
ssh -p $IP2_SSH_PORT root@$IP2 'service freeradius restart'
SCRIPTET=`date +"%d-%b-%Y___%T"`

echo "- FILE NAME : $FILE
- FILE SIZE : $SIZE

- DONE : Backup from $IP1 to $IP2 have been Exported OK

- Script End Time: $SCRIPTET

Regard's
Syed Jahanzaib"

echo "- FILE NAME : $FILE
- FILE SIZE : $SIZE

- DONE : Backup from $IP1 to $IP2 have been Exported OK

- Script End Time: $SCRIPTET

Regard's
Syed Jahanzaib" >> $RESULT

sendemail -t $email -u "$TODAY $HOSTNAME DB Exported from $IP1 to $IP2 Report OK" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$RESULT -o message-content-type=text

#cat $RESULT
rm $IP2_SSH_CHK
rm $RESULT
rm $PING_RESULT
rm $FILE

Email Report Sample:

replica export done.PNG


Cron schedule to run the script Daily at 7am

# To run the script daily at 7 AM in morning
00 07 * * * /temp/update_radius_from_10.0.0.1__TO__10.0.0.2.sh

# To run the script every 6th hours 30 mnts
30 */6 * * * /temp/update_radius_from_10.0.0.1__TO__10.0.0.2.sh

Regard's
Syed Jahanzaib

July 2, 2019

DENIED Notes users are still able to access mails through IBM Notes Traveler

Filed under: IBM Related — Tags: , , , — Syed Jahanzaib / Pinochio~:) @ 4:30 PM

This post is about a case study regarding “Denied access Notes users are still able to access mails through IBM Notes Traveler“.

We are using IBM lotus Domino server as per following

  • – Lotus Domino – Primary Mail Server [For Lotus Notes/Webmail]
  • – Lotus Domino – Traveler Role [For Mobile Devices like Android/iPhone]

 

Case Study:

Today, It was brought to our knowledge that one of company’s employee resigned on 28th June 2019) have sent emails to HR Dept on ndex day. while his account was under DENY group, but still he was able to sent emails. We tried settings from IBm document referenced “Denied access Notes users are still able to access mails through IBM Notes Traveler” from https://www-01.ibm.com/support/docview.wss?uid=swg21634205 but still no luck. Traveler users who were under NO ACCESS GROUP under Primary LOTUS server were still able to sync emails.

Our Blocking Practice:
As per our practice , when any user resigned from the company, we add him under DENY GROUP under Lotus Domino Server for few days, which blocks the Notes/Webmail Access access for that particular user. Later if user withdraw resignation we just remove his name from this list, Else we remove his profiles and save his email in Archive for ever.

Findings:
If the user have IBM Verse installed on there mobile device, he can still access the email because his access is blocked primarily on Lotus Email Server, but since mobile devices does not communicate with the Primary server directly instead they access it via separate TRAVELER server (by proxying through LOTUS TRAVELER server), and communication between Primary Server & Lotus traveler server is being done through server to server basis thus they could access the emails.

Solution:

no access group.jpg
Adding the NO ACCESS list in the traveler server document under security DID THE TRICK !

[13FC:000A-1574] 07/01/2019 12:45:02 PM XXXXX Web Server: Access Denied Exception [/traveler?action=sync&orig=sp&deviceId=Android_a41df4vf3fe46a8e3a] CN=MY USER/O=MYCOMP

This list will be updated via Primary Lotus server after every 10 minutes (using replication connection) & it will act as additional level of permissions filtering. Now if any user will be added under DENY GROUP under Lotus Mail Server, this list will be propagated to Lotus Traveler server as well which will deny the user request if his name is under DENY GROUP.

Thanks to FB group “IBM Lotus Domino Administrators” for pointing in the right direction.


Some addition Tip:

to flush DB cache

sh nlcache reset

https://www.novell.com/coolsolutions/tip/17050.html

Regard’s
Syed Jahanzaib

 

%d bloggers like this: