Oneliners

Posted on | by johniax 
#delete directories older than 20 days and not older than 365 days
find /home/username/backup/ -type d -mtime +20 -mtime -365 -exec rm -rf {} \;

#delete pdf files older than 6 days but not older than 365 days
find /home/username/public_html/storage/ -type f -mtime +6 -mtime -365 -name '*.pdf' -exec rm {} \

Setup OpenVPN server on Debian 9

Posted on | by johniax

1. Installing OpenVPN and EasyRSA

On the OpenVPN server: 

sudo apt update
sudo apt install openvpn -y

# Choose the newest release
wget -P ~/server https://github.com/OpenVPN/easy-rsa/releases/download/v3.0.5/EasyRSA-nix-3.0.5.tgz
cd server
tar xvf EasyRSA-nix-3.0.5.tgz

On the CA server: 

# Choose the newest release
wget -P ~/ca https://github.com/OpenVPN/easy-rsa/releases/download/v3.0.5/EasyRSA-nix-3.0.5.tgz
cd ca
tar xvf EasyRSA-nix-3.0.5.tgz
cd ca/EasyRSA-3.0.5/

2. Configure EasyRSA and build CA

On the CA server: 

cp vars.example vars

vi vars
# uncomment and fill in your information: 
set_var EASYRSA_REQ_COUNTRY    "LT"
set_var EASYRSA_REQ_PROVINCE   "Vilnius"
set_var EASYRSA_REQ_CITY       "Vilnius"
set_var EASYRSA_REQ_ORG        "Jonas"
set_var EASYRSA_REQ_EMAIL      "admin@example.com"
set_var EASYRSA_REQ_OU         "Organisational unit"

./easyrsa init-pki
./easyrsa build-ca nopass

3. Create the Server Certificate, Key, and Encryption Files

On the OpenVPN server: 

cd server/EasyRSA-3.0.5/
./easyrsa init-pki
./easyrsa gen-req server nopass
sudo cp pki/private/server.key /etc/openvpn/

# copy server.req file to CA server
mkdir ~/ca/tmp && cp pki/reqs/server.req ~/ca/tmp/

On the CA server: 

cd ~/ca/EasyRSA-3.0.5/
./easyrsa import-req ../tmp/server.req server
./easyrsa sign-req server server
mkdir ~/server/tmp && cp pki/issued/server.crt ../../server/tmp/
cp pki/ca.crt ../../server/tmp/

On the OpenVPN server: 

cd ~/server/EasyRSA-3.0.5/
sudo cp ../tmp/server.crt /etc/openvpn/
sudo cp ../tmp/ca.crt /etc/openvpn/
./easyrsa gen-dh
sudo openvpn --genkey --secret ta.key
sudo cp ta.key /etc/openvpn/
sudo cp pki/dh.pem /etc/openvpn/

4. Generate a Client Certificate and Key Pair

On the OpenVPN server: 

mkdir -p ~/server/client-configs/keys
chmod -R 700 ~/server/client-configs
./easyrsa gen-req client1 nopass
cp pki/private/client1.key ~/server/client-configs/keys/

# copy client1.req file to CA server
cp pki/reqs/client1.req ../../ca/tmp/

On the CA server: 

cd ../../ca/EasyRSA-3.0.5/
./easyrsa import-req ../tmp/client1.req client1
./easyrsa sign-req client client1

#copy client1.crt file to OpenVPN server
cp pki/issued/client1.crt ../../server/tmp/

On the OpenVPN server: 

cd ~/server/EasyRSA-3.0.5/
cp ../tmp/client1.crt ~/server/client-configs/keys/
sudo cp ta.key ~/server/client-configs/keys/
sudo cp /etc/openvpn/ca.crt ~/server/client-configs/keys/

5. Set up the OpenVPN service

On the OpenVPN server: 

cd
sudo gzip -d /etc/openvpn/server.conf.gz

sudo vi /etc/openvpn/server.conf

# uncomment this line: 
tls-auth ta.key 0 # This file is secret

#add this line bewlow: 
key-direction 0

# uncomment this line: 
cipher AES-256-CBC

# add this line below: 
auth SHA256

#change line
dh dh2048.pem 
#to line
dh dh.pem

# uncomment these two lines: 
user nobody
group nogroup

# push DNS Changes to Redirect All Traffic Through the VPN
# uncomment these lines: 
push "redirect-gateway def1 bypass-dhcp"
push "dhcp-option DNS 8.8.8.8"
push "dhcp-option DNS 1.1.1.1"

6. Adjust network accordingly

sudo vi /etc/sysctl.conf

# uncomment this line: 
net.ipv4.ip_forward=1

# read the file and apply changes to the current session
sudo sysctl -p

7. Enable the OpenVPN Sercice

sudo systemctl start openvpn@server
sudo systemctl status openvpn@server
ip addr show tun0
sudo systemctl enable openvpn@server

8. Forward all VPN client traffic through VPN server

# Masquerade outgoing traffic
iptables -t nat -A POSTROUTING -o eth0 -j MASQUERADE
iptables -t nat -A POSTROUTING -o tun0 -j MASQUERADE

# Allow return traffic
iptables -A INPUT -i eth0 -m state --state RELATED,ESTABLISHED -j ACCEPT
iptables -A INPUT -i tun0 -m state --state RELATED,ESTABLISHED -j ACCEPT

# Forward everything
iptables -A FORWARD -j ACCEPT

# Save iptables configuration
sudo iptables-save | sudo tee /etc/iptables/rules.active

sudo vi editor /etc/network/if-pre-up.d/iptables
#!/bin/sh
/sbin/iptables-restore < /etc/iptables/rules.active

sudo chmod +x /etc/network/if-pre-up.d/iptables

9. Create client configuration infrastructure

mkdir -p server/client-configs/files
cp /usr/share/doc/openvpn/examples/sample-config-files/client.conf ~/server/client-configs/base.conf

vi server/client-configs/base.conf

remote your_server_ip 1194
proto udp

# uncomment these lines for non-windows clients
user nobody
group nogroup

# comment out these lines
#ca ca.crt
#cert client.crt
#key client.key

# make sure these lines are
cipher AES-256-CBC
auth SHA256

# add these lines as well and uncomment if yout linux client has an /etc/openvpn/update-resolv-conf file
# script-security 2
# up /etc/openvpn/update-resolv-conf
# down /etc/openvpn/update-resolv-conf
vi server/client-configs/make_config.sh

#!/bin/bash
# First argument: Client identifier

KEY_DIR=/home/jonas/server/client-configs/keys
OUTPUT_DIR=/home/jonas/server/client-configs/files
BASE_CONFIG=/home/jonas/server/client-configs/base.conf

cat ${BASE_CONFIG} \
    <(echo -e '') \
    ${KEY_DIR}/ca.crt \
    <(echo -e '\n') \
    ${KEY_DIR}/${1}.crt \
    <(echo -e '\n') \
    ${KEY_DIR}/${1}.key \
    <(echo -e '\n') \
    ${KEY_DIR}/ta.key \
    <(echo -e '') \
    > ${OUTPUT_DIR}/${1}.ovpn
chmod 700 ~/server/client-configs/make_config.sh

10. Generate client config file

cd ~/server/client-configs/
sudo ./make_config.sh client1
ls -al files/

11. Connect to OpenVPN server from Ubuntu 18

sudo apt install openvpn 
sudo openvpn --config client1.ovpn

Setting up fail2ban on debian 9

Posted on | by johniax 
sudo apt update
sudo apt install fail2ban -y
sudo vi /etc/fail2ban/jail.local
# -1 means forever
[DEFAULT]
ignoreip      = 127.0.0.1 11.22.33.44 55.66.77.88
bantime       = -1
findtime      = -1
maxentry      = 3
maxretry      = 3
sendername    = Fail2Ban
destemail     = root@localhost
mta           = sendmail
protocol      = tcp
chain         = INPUT
banaction     = iptables-multiport
action        = %(action_)s # without sending emails

# JAILS
[sshd]
enabled     = true
port        = ssh
filter      = sshd
logpath     = %(sshd_log)s
sudo systemctl enable fail2ban
sudo service fail2ban restart
sudo service fail2ban status
sudo fail2ban-client status
sudo tail -f /var/log/fail2ban.log

Email query output as HTML table – SQL Server

Posted on | by johniax

Create a stored procedure that converts query output to HTML table

USE [databasename]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[spQueryToHtmlTable] 
(
  @query nvarchar(MAX), --A query to turn into HTML format. It should not include an ORDER BY clause.
  @orderBy nvarchar(MAX) = NULL, --An optional ORDER BY clause. It should contain the words 'ORDER BY'.
  @html nvarchar(MAX) = NULL OUTPUT --The HTML output of the procedure.
)
AS
BEGIN   
    DECLARE @borderColor char(7) = '#cccccc'

  SET NOCOUNT ON;

  IF @orderBy IS NULL BEGIN
    SET @orderBy = ''  
  END

  SET @orderBy = REPLACE(@orderBy, '''', '''''');

  DECLARE @realQuery nvarchar(MAX) = '
    DECLARE @headerRow nvarchar(MAX);
    DECLARE @cols nvarchar(MAX);    

    SELECT * INTO #dynSql FROM (' + @query + ') sub;

    SELECT @cols = COALESCE(@cols + '', '''''''', '', '''') + ''['' + name + ''] AS ''''td''''''
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''    

    EXEC sys.sp_executesql @cols, N''@html nvarchar(MAX) OUTPUT'', @html=@html OUTPUT

    SELECT @headerRow = COALESCE(@headerRow + '''', '''') + '''' + name + '''' 
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @headerRow = '''' + @headerRow + '''';

    SET @html = '''' + @headerRow + @html + ''
'';    
    ';

  EXEC sys.sp_executesql @realQuery, N'@html nvarchar(MAX) OUTPUT', @html=@html OUTPUT
END

GO

Create a table that holds email recipients

USE [databasename]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[errorsRecipients](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [recipient] [varchar](512) NOT NULL,
    [enabled] [bit] NULL,
 CONSTRAINT [PK_errorsRecipients] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[errorsRecipients] ADD  DEFAULT ((1)) FOR [enabled]
GO

Insert some data in the recipients table

USE [databasename]
GO

INSERT INTO [dbo].[errorsRecipients]
           ([recipient]
           ,[enabled])
     VALUES
           ('some.emial.address@some.domain', 
            1)
GO

Execute email send

DECLARE @html            NVARCHAR(MAX)
DECLARE @emailRecipients NVARCHAR(MAX) 

SELECT @emailRecipients = COALESCE(@emailRecipients + '; ', '') + recipient 
    FROM errorsRecipients 
        WHERE enabled = 1

EXEC    [dbo].[spQueryToHtmlTable]
        @query = 'SELECT QUERY HERE',
        @html = @html OUTPUT

EXEC msdb.dbo.sp_send_dbmail  
@recipients=@emailRecipients,
@subject='The subject',
@body=@html,
@body_format='HTML',
@from_address='SENDER NAME <youraddress@yourdomain.com>',
@reply_to='noreply@somedomain.com';

Setup Let’s Encrypt SSL certificate using certbot on debian 9

Posted on | by johniax

Install certbot

"echo "deb http://ftp.debian.org/debian stretch-backports main" | sudo tee --append /etc/apt/sources.list
sudo apt-get update
sudo apt-get install python-certbot-apache -t stretch-backports

Setup certbot

# setup certificate for chosen websites, force http to https
sudo certbot --apache

Simulate certificate renewal

sudo certbot renew --dry-run

Check if certbot cron entry is created

tail -1 /etc/cron.d/certbot

Host multiple websites on LAMP server debian 9

Posted on | by johniax

Uncomment if commented in file /etc/apache2/apache2.conf

IncludeOptional sites-enabled/*.conf

Move default location to defaultsite folder

cd /etc/apache2/sites-available/
sudo cp 000-default.conf 000-default.conf.bak
sudo sed -i 's#DocumentRoot /var/www/html#DocumentRoot /var/www/html/defaultsite#g' 000-default.conf 
cd /var/www/html && sudo mkdir defaultsite
shopt -s extglob # enable extended globbing for next command
sudo mv !(defaultsite) defaultsite
sudo service apache2 restart

Create a new site config

sudo mkdir /var/www/html/anothersite.com
cd /etc/apache2/sites-available/
sudo cp 000-default.conf anothersite.com.conf
sudo sed -i 's/defaultsite/anothersite.com/g' anothersite.com.conf
sudo sed -i 's/error.log/anothersite.com_error.log/g' anothersite.com.conf
sudo sed -i 's/access.log/anothersite.com_access.log/g' anothersite.com.conf
sudo sed -i 's/#ServerName www.example.com/ServerName anothersite.com\n    ServerAlias www.anothersite.com/g' anothersite.com.conf
echo "Hello world!" | sudo tee --append /var/www/html/anothersite.com/index.html
sudo a2ensite anothersite.com
sudo service apache2 restart

Setup a wordpress website on a VPS

Posted on | by johniax

Install packages needed to run wordpress

sudo apt-get install php-gd php-xml php-mbstring php-mcrypt php-xmlrpc -y

Install wordpress

wget http://wordpress.org/latest.tar.gz
sudo mv latest.tar.gz /var/www/html/ && cd /var/www/html/
sudo tar -zxvf latest.tar.gz 
sudo mv wordpress/* . 
sudo chown -R root:www-data * 
sudo rm latest.tar.gz
  • Open your domain name with browser and follow the wizzard…
  • Manualy create file wp-config.php and put the contents when wizzard prompts
  • Now finish setting up your website from browser..
  • Delete wp-config-sample.php
sudo rm wp-config-sample.php