oHbEt.. configuration backup, how to, sample config and reminder..

Friday, June 24, 2016

Tunnel RDP via ssh

I have a ssh server serve as proxy and i want to tunnel the rdp on it

SSH Server (example only)
Public: 11.11.22.22
Internal: 10.10.1.1

Windows server that I need to RDP via SSH Server
Internal IP: 10.10.1.2

IP of my workstation:
Public: 9.9.9.9 (example only)

So I am going to ssh with below parameters:

ssh -L 12389:10.10.1.2:3389 user@11.11.22.22

Once connected, I can now RDP to 127.0.0.1:12389

So as long as you are connected to 11.11.22.22, RDP is tunneled.

Monday, February 29, 2016

Enabling Transparent Data Encryption on Postgresql 9.3

Well, this is actually copied from this link.

https://translate.google.com/translate?sl=auto&tl=en&js=y&prev=_t&hl=en&ie=UTF-8&u=http%3A%2F%2Fpgsqldeepdive.blogspot.com%2F2015%2F06%2Fpostgresql-nec-tde.html&edit-text=&act=url

But then, below are my steps.

Specs: Current posstgresql server is 9.3.10

yum install --enablerepo=pgdg93 postgresql93-devel gmake make gcc gcc-c++ postgresql93-test yum-utils rpm-build bison flex perl-ExtUtils-Embed "perl(ExtUtils::MakeMaker)" python-devel tcl-devel readline-devel zlib-devel openssl-devel krb5-devel e2fsprogs-devel gettext libxml2-devel libxslt-devel pam-devel uuid-devel openldap-devel openjade opensp docbook-dtds docbook-style-dsssl rpm-build git

It will update the current postgres-9.3 so be sure you have a backup of you current database.

cd /root
mkdir src
cd src
yumdownloader --source postgresql93
rpm -ivh postgresql93-9.3.11-1PGDG.rhel6.src.rpm
cd /root/rpmbuild
rpmbuild -bp SPECS/postgresql-9.3.spec

mkdir /usr/local/src/
cd /usr/local/src/
git clone https://github.com/nec-postgres/tdeforpg.git
ll /root/rpmbuild/BUILD/postgresql-9.3.11/
export PGSRC=/root/rpmbuild/BUILD/postgresql-9.3.11/
export PGHOME=/usr/pgsql-9.3
export TDEHOME=/usr/local/src/tdeforpg
export PATH=/usr/pgsql-9.3/bin/:$PATH

ln -s /usr/pgsql-9.3/lib/pgcrypto.so /usr/lib64/libpgcrypto.so

cd $PGSRC
./configure

cd /usr/local/src/tdeforpg/SOURCES/data_encryption/

ln -s /root/rpmbuild/BUILD/postgresql-9.3.11/src/backend/utils/errcodes.h /root/rpmbuild/BUILD/postgresql-9.3.11/src/include/utils/errcodes.h

sh makedencryption.sh 93 $PGSRC

ln -s /usr/local/src/tdeforpg/SOURCES/data_encryption/93/data_encryption93.so.1.1.1.1 /usr/lib64/data_encryption.so
chown postgres:postgres -R -v /usr/local/src/tdeforpg

Apped the below line at current postgresql.conf

shared_preload_libraries='/usr/lib64/data_encryption.so'
encrypt.enable = on

Then connect to the database that you want the encryption to be enabled.

as super user.

psql -U pgadminuser -h 127.0.0.1 mycompanydb

then execute

create extension pgcrypto;

then exit.

Follow the ff:

cd $TDEHOME/SOURCES
sh bin/cipher_setup.sh $PGHOME

Transparent data encryption feature setup script
Please select from the setup menu below
Transparent data encryption feature setup menu
1: activate the transparent data encryption feature
2: inactivate the transparent data encryption feature
select menu [1 - 2] > 1
Please enter database server port to connect : 5432
Please enter database user name to connect : pgadminuser
Please enter password for authentication :
Please enter database name to connect : mycompanydb

CREATE LANGUAGE
INFO: Transparent data encryption feature has been activated

cd $TDEHOME/SOURCES
sh bin/cipher_key_regist.sh /usr/pgsql-9.3
=== Database connection information ===
Please enter database server port to connect : 5432
Please enter database user name to connect : pgadminuser
Please enter password for authentication :
Please enter database name to connect : mycompanydb
=== Regist new cipher key ===
Please enter the new cipher key : "make sure you remember this"
Please retype the new cipher key :
Please enter the algorithm for new cipher key : aes

Are you sure to register new cipher key(y/n) : y

Try to check cipherkey table

[root@MyServer SOURCES]# psql -U pgadminuser -h 127.0.0.1 mycompanydb
Password for user pgadminuser:
psql (9.3.11)
Type "help" for help.

mycompanydb=# select * from cipher_key_table;
key
| algorithm
-------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------+-----------
\xc38f3e4f1305863e66e2ac3e4eaffe7617e09c7c6f8f3e4f1305863e66e
2ac3e4eaffe7617e09c7c6f8f3e4f1305863e66e2ac3e4eaffe7617e
09c7c6f8f3e4f1305863e66e2ac3e4eaffe7617e09c7c6f

7dacc2290d1ce491c620e0b43702a1f8509656c6 | aes
(1 row)

mycompanydb=#

Then developers will be the one to continue.

Sunday, November 15, 2015

Deleting a message per subject via CLI on zimbra

Deleting a message per subject via CLI on zimbra

The scenario:

You want to delete a message that has been accidentally sent to a bunch of zimbra users and you need to delete those message with out accessing the mailbox of those who received one by one on the web. So you might be ending doing it on a CLI mode.

Assuming that you able to open one of the message that you need to delete. If you view the full headers, you will find that it contain inside the header a msgid:

sample:

msgid:609947668.476624.1447370486564.JavaMail.zimbra@my.company.com

Assuming the the senders zimbra domain is my.company.company

the contents of the message ID is common to all that received the common message.

assuming that you have a list file at /tmp/userlists.txt containing

user1@my.company.com
user2@my.company.com
user3@my.company.com
user4@my.company.com
user5@my.company.com

I used this command to generate first the user with message ID for deletion. Make sure to run this under zimbra user.

for i in `cat /tmp/userlists.txt`; do echo $i,`zmmailbox -z -m $i s -t message -l 1 "msgid:609947668.476624.1447370486564.JavaMail.zimbra@my.company.com" |grep mess |awk '{print $2,$3,$4,$5}'`; done |tee -a /tmp/outputlist.txt

I pipe the output at /tmp/outputlist.txt

Sample output that will fill the file is below:

user1@my.company.com,57 mess Userx MYSPAM:
user2@my.company.com,1705 mess Userx MYSPAM:
user3@my.company.com,505 mess Userx MYSPAM:
user4@my.company.com,4705 mess Userx MYSPAM:
user5@my.company.com,9715 mess Userx MYSPAM:

The Numbers are the MSG ID
UserX is the sender
MYSPAM is the Subject.

I intended to do it to display the Sender and Subject so to double check that the message ID is the exact ID i need to delete.

The awk part is the one that will display the tab needed.

Now, I need to generate the email and the message ID.

cat /tmp/outputlist.txt |awk '{priont $1}' > /tmp/fordeletelist.txt

Outbut is now a csv.

user1@my.company.com,57
user2@my.company.com,1705
user3@my.company.com,505
user4@my.company.com,4705
user5@my.company.com,9715

Then use bash IFS for the little script.

while IFS=, read USER ID; do echo deleting message id $ID from $USER; zmmailbox -z -m $USER dm $ID; done < /tmp/fordeletelist.txt

It will search the user and the message ID which is the number on the file and will delete it. If you have a thousand to delete, it will be helpfull.

By the way, google and there are also scripts provided already, just modified it for my need.

Wednesday, November 4, 2015

Joining CentOS and authenticate to ACtive Directory using winbind

Joining CentOS and authenticate to ACtive Directory using winbind

- Make sure that you have a working DNS that can resolve the domain you are going to join and authenticate the CentOS server. Check /etc/resolv.conf

Install the ff:
yum install authconfig krb5-workstation pam_krb5 samba-common

Execute the command:

Assuming the domain is MYCOMPANY.COM

authconfig --disablecache --enablewinbind --enablewinbindauth --smbsecurity=ads --smbworkgroup=MYCOMPANY --smbrealm=MYCOMPANY.COM --enablewinbindusedefaultdomain --enablekrb5 --krb5realm=MYCOMPANY.COM --enablekrb5kdcdns --enablekrb5realmdns --enablelocauthorize --enablepamaccess --smbidmapuid=16777216-16777300 --krb5kdc=srv001.mycompany.com --krb5adminserver=srv001.mycompany.com --winbindtemplateshell=/bin/bash --updateall

The command above will change the /etc/samba/smb.conf and /etc/krb5.conf

Once done on the authconfig command, issue the command below:

kinit admin.user@MYCOMPANY.COM #This will ask for your password that you use on your AD domain to login, and will tell you if the server was joined successfully.

Once accepted, you may join to domain.

net join -w MYCOMPANY.COM -U admin.user #Again will ask for a password.
/etc/init.d/winbind restart
chkconfig winbind on

Thursday, September 3, 2015

Some Backup script for postgresql 9.3 database - using parallel run

#!/bin/bash
#set-x
#Original Script Owner#
# #backuppostgresql.sh
#by #CraigSanders
#this script is public domain. feel free to use or modify as you like.
## Modified by: yongitz and ohbet - 2015 of September
#Note
# to restore database from this backup
# Command is: /usr/pgsql-9.3/bin/pg_restore -j -Fd $DATA
# where data is the path of the backup /db/BACKUP/Date-of-backup/Database_Name-folder
# /usr/pgsql-9.3/bin/pg_restore -j -Fd /db/BACKUP/2015-09-04/database_folder
# See postgresql 9.3 manual page for details
#
PGDUMP="/usr/pgsql-9.3/bin/pg_dump"
PSQL="/usr/pgsql-9.3/bin/psql"
#
# directory to save backups in, must be owned by postgres user
BASE_DIR="/db/BACKUP"
#Make some checking if backup folder is available
#
if ! ls $BASE_DIR > /dev/null 2>&1; then
        echo "Backup directory $BASE_DIR does not exist. Backup processes terminated." | mail -s "!!! ALERT - BACKUP PROCESS FAILED @ DBSERVERXYZ !!!" db.admin@mycompany.com
exit 1
fi
#Create the Base Directory
YMD=$(date "+%Y-%m-%d")
DIR="$BASE_DIR/$YMD"
ls -l $BASE_DIR
mkdir -p $DIR
cd $DIR
#
# get list of databases in system , exclude the tempate db and other db not needed, db below are example only
DBS=$($PSQL -l -t | egrep -v 'template[01]|dbxyz|db2|db_warehouse' | awk '{print $1}' | grep -v '|'|grep -v '^$')
#
# now loop through each individual database
for database in $DBS; do
    DATA=$DIR/$database
    # dump data
    if [ $database = db_live_final ]; then
    # Remarks: j8 = 8 parallel process to run: at 9.3 and above
    # Remarks: -N backup_tables and -N DBX_* are schema that are excluded on backup
     $PGDUMP -v -j8 -N backup_tables -N DBX_* -Fd -f $DATA $database
    else
     $PGDUMP -v -j8 -Fd -f $DATA $database
    fi
done

#copy conf files currently used.
cp /db/pgsql/9.3/data/*.conf $DIR/
#

#Create readme for restore
cat < $DIR/Restore_readme.txt
To restore database from this backup
Use Command:
/usr/pgsql-9.3/bin/pg_restore -j -Fd /path/to/backup/folder/per/db/name
where data is the path of the backup /db/BACKUP/Date-of-backup/Database_Name-folder
Sample Below:
/usr/pgsql-9.3/bin/pg_restore -j -Fd /db/BACKUP/2015-09-04/database_folder
See postgresql 9.3 manual page for further details
EOF
#

# delete backup files older than 30 days
OLD=$(find $BASE_DIR -type d -mtime +30)
if [ -n "$OLD" ] ; then
        echo deleting old backup files: $OLD
        echo $OLD | xargs rm -rf
fi

Then save this as bash script and put on a cron to run daily.

Sunday, November 16, 2014

Redirect http to https with multiple VirtualHost on apache

Goal: Redirect all http to https, domain are config.example.com and configure.example.com.

When first try on it, I encountered redirect problem, when you dont place carefully the virtual host, redirection will got problem, so on my example, I need to place carefully, config http then https, and configure http then https. It should also redirect properly when you have a sub folder.

Note: I changed the tags to |

|VirtualHost *:80|
ServerName config.example.com/
RedirectMatch 301 /(.*)$ https://config.example.com/$1

|/VirtualHost|

|VirtualHost *:443|
        SSLEngine on
        SSLProtocol -ALL -SSLv3 +TLSv1
        SSLCipherSuite ALL:!aNULL:!ADH:!eNULL:!LOW:!EXP:RC4+RSA:+HIGH:+MEDIUM
        SSLCertificateFile /etc/pki/example_cert_2017/example.com.crt
        SSLCertificateKeyFile /etc/pki/example_cert_2017/example.key
        SSLCertificateChainFile /etc/pki/example_cert_2017/gd_bundle.crt
        ServerName config.example.com
        ServerAlias config.example.com
        DocumentRoot "/data/www/config"
        ErrorLog logs/ssl-config-error_log
        CustomLog logs/ssl-config-access.log common
|/VirtualHost|

|VirtualHost *:80|
ServerName configure.example.com
RedirectMatch 301 /(.*)$ https://configure.example.com/$1
|/VirtualHost|

|VirtualHost *:443|
        SSLEngine on
        SSLProtocol -ALL -SSLv3 +TLSv1
        SSLCipherSuite ALL:!aNULL:!ADH:!eNULL:!LOW:!EXP:RC4+RSA:+HIGH:+MEDIUM
        SSLCertificateFile /etc/pki/example_cert_2017/example.com.crt
        SSLCertificateKeyFile /etc/pki/example_cert_2017/example.key
        SSLCertificateChainFile /etc/pki/example_cert_2017/gd_bundle.crt
        ServerName configure.example.com
        ServerAlias configure.example.com
        DocumentRoot "/data/www/configure"
        ErrorLog logs/ssl-configure-error_log
        CustomLog logs/ssl-configure-access.log common
|/VirtualHost|

Wednesday, November 12, 2014

Zimbra email retention under 30 days

Normally, zimbra COS can allocate only at least 30 Days and more of email retention, and if lower than 30 days is required, COS is not capable. (Well, you may correct my if I am wrong)

Below is a sample script to do the Job.

1. Create a zimbra distro list that will become the bases of user list that have a threshold. For example, 7days_users@list.example.com is the distro, member of this will have a retention of 7 days.

2. The script.

#!/bin/bash
#Generate the members of 7days_users and pipe it on a temp file.
zmprov gdlm 7days_users@list.example.com |egrep -v '^#|members' > /tmp/_TMP7days
#
for USER in `cat /tmp/_TMP7days`
do
zmmailbox -z -m $USER s -t message -l 1000 "before:`date +%x --date="7 days ago"`" |grep mess |awk '{print $2}' > /tmp/7days_$USER
done
##delete ID
for UserName in `cat /tmp/_TMP7days`
do
for MAILID in `cat /tmp/7days_$UserName`
do
zmmailbox -z -m $UserName dm $MAILID
done
done
#end of script