Setup SSH Tunnel for SytemD

The what:

There are several cases when you just need some SSH tunnel to another system with some port redirections.
If the tunnel fails for whatever reason, it should reset itself. Setting up a SSH tunnel with port redirection as a service is the answer!

The How:

Creating SystemD config

The setup is a “user defined” config. When the user is logged on to the system, the service will become active.
You need to setup SSH password less login setup (with keys) to be able to use this.

First you need to created some directories (if not present)

mkdir -p ~/.config/systemd/user

Then create a SystemD service definition file inside this location
Name the file:

ssh_tunnel@.service

The content….:

# Author: VDV-IT Consultancy
# URL: https://www.vdv-it.nl
[Unit]
Description=Setup a secure tunnel to %I
After=network-online.target

[Service]
ExecStart=/usr/bin/ssh -NT -o ServerAliveInterval=60 -o ExitOnForwardFailure=yes %i

# Restart every >2 seconds to avoid StartLimitInterval failure
RestartSec=5
Restart=always

[Install]
WantedBy=default.target

After creating the file, SystemD needs to know that this file is present. You need to do a reload of SystemD

systemctl --user daemon-reload

Almost done…..

Define the tunnels

To define a tunnel, add the following lines to your ~/.ssh/config file. If not present, create the file.

Host [Your tunnel name]
    HostName [ip or url to remote computer]
    Port     [ssh port, usually 22]
    User     [username for login]
    IdentityFile ~/.ssh/[the private key to be used].key
    LocalForward [local port to listen] localhost:[remote port to connect to]
    RemoteForward [remote port to listen] localhost:[local port to connect to]

More tunnels? just duplicate the block with other params.
If you need more option for SSH, take a look at man ssh_config

Running the tunnels

Lets get your hard work up and running!
Use the systemctl command to start/stop/status you’re tunnel

systemctl --user start ssh_tunnel@[your tunnel name]

To set-up autostart, use the same command but replace start with enable
This will start the tunnel when you log-on to your system.

systemctl --user enable ssh_tunnel@[your tunnel name]

Thats it! you’re up and running!

Checking / Stopping the tunnel

For status checking, use the systemctl command

systemctl --user status ssh_tunnel@[Your tunnel name]

To stop the tunnel

systemctl --user stop ssh_tunnel@[Your tunnel name]

 

 

MySQL Search and Replace Query

You need to change some data or whatever inside a table, but you don’t want to edit every row by row. Just use this query to search for a string in a table column and change it!

UPDATE  `MySQL_Table` SET  `MySQL_Table_Column` = REPLACE(`MySQL_Table_Column`, 'oldString', 'newString') WHERE  `MySQL_Table_Column` LIKE 'oldString%';

When Should I use MySQL Search and Replace?

Doing a Search and Replace via SQL could be dangerous if you have links that you’re unaware of which shouldn’t be changed. You should Search and Replace only when you’re convinced that you have to change the text in your Database. You should always perform a Database backup before proceeding with any manual changes. This can easily be done through phpMyAdmin:

Search Puppet file bucket for a changed file(s)

When you use Puppet to manage your infrastructure, it’s common to manage several files on a system with a template or something. The default setting when a file is changed, it gets a backup on the client system in a filebucket

You can use to command ‘puppet filebucket’ with a lot of options to hopefully get what you want…. Or…. just search the filesystem 🙂

The location below holds the directory structure were the files are physically stored! with a lot of hashes added 😐

'/opt/puppetlabs/puppet/cache/clientbucket/'

A directory structure could look like this:

f
f/8
f/8/6
f/8/6/3
f/8/6/3/1
f/8/6/3/1/b
f/8/6/3/1/b/a
f/8/6/3/1/b/a/a
f/8/6/3/1/b/a/a/f8631baa8f83d5067af7eedcd7bdf641
f/8/6/3/1/b/a/a/f8631baa8f83d5067af7eedcd7bdf641/contents
f/8/6/3/1/b/a/a/f8631baa8f83d5067af7eedcd7bdf641/paths

The files content and paths hold the magic information about original file and its location. YES!!

Use the following command the search your filebucket to get you’re long lost files back!!

find /opt/puppetlabs/puppet/cache/clientbucket/ -name paths -print -exec grep <file to look for> {} \;

This only works when the file is managed through the ‘file’ resource type.

Files changed by augeas or not to be found here…. Bummer!

Rename all files in directory from $file to $newfile

The what?

Dead simple.
How do I rename

hello_kitty2.png
hello_kitty3.png

to

byebye_kitty2.png
byebye_kitty3.png

I think it’s simple, but it’s hard to Google for this kind of thing unless you already know.

The how…..!

You can do this with a simple one-liner with bash

for file in $(ls hello_ki*); do mv "$file" "${file/hello/byebye}"; done

 

Apache serves the first SSL host vhost found on a none-SSL domain.

Your hosting two name based domains deurbellen.nl and fietsbellen.nl. For the domain fietsbellen.nl you have a SSL and non-SSL vhost configuration .
The domain deurbellen.nl has only a none-SSL vhost config plane and simple.

When a user connects to the site https://deurbellen.nl, there will be no error message telling the vhost (domain) doesn’t exist. Instead apache serves the site fietsbellen.nl in SSL……. Pretty weird!
This is default behavior for apache. It serves the first available SSL vhost when the requested one is not found.

Solution:

For many reasons you just won’t want this to happen. This behavior can be changed by adding a vhost configuration that listens to *:443 and serve one of the apache error codes back to the client. For a complete list of error codes look here.

  1. Create a file named 10-localhost.localdomain-ssl.conf at the location apache reads your configuration files. On a RHEL/CentOS based os, the default location is /etc/httpd/conf.d
  2. opy the stuff below into your file
    <VirtualHost *:443>
      ServerName localhost.localdomain
    
      ## Vhost docroot
      DocumentRoot "/var/www/html/localhost.localdomain"
    
      ## Directories, there should at least be a declaration for /var/www/html/localhost.localdomain
    
      <Directory "/var/www/html/localhost.localdomain">
        Options None
        AllowOverride None
        Require all granted
      </Directory>
    
      ## Logging
      ErrorLog "/var/log/httpd/localhost.localdomain/error-ssl_log.%Y.%m.%d 86400"
      ServerSignature Off
      CustomLog "/var/log/httpd/localhost.localdomain/access-ssl_log.%Y.%m.%d 86400" combined
    
      ## Redirect rules
      Redirect  404 /
    
      ## Server aliases
      ServerAlias localhost.localdomain
    
      ## SSL directives
      SSLEngine on
      SSLCertificateFile      "/etc/ssl/certs/localhost.localdomain.crt"
      SSLCertificateKeyFile   "/etc/ssl/certs/localhost.localdomain.key"
      SSLOptions +StdEnvVars +ExportCertData
    </VirtualHost>
    

    Checkout the config section Redirect rules

      ## Redirect rules
      Redirect  404 /
    

    This tells apache which error code/page the client receives. So tweak it with the message you want to return

  3. Create a SSL cert and key for localhost.localdomain
    Check this how to for creating certificates and stuff…
  4. Copy the new cert and key file to
    /etc/ssl/certs/
  5. Restart apache and go test!

How To Generate SSL Key, CSR and Self Signed Certificate For Apache

If you want to convert your website from HTTP to HTTPS, you need to get a SSL certificate from a valid organization like Verisign, Quovadis, Thawte, etc. You can also generate self signed SSL certificate for testing purpose.

In this article, let us review how to generate private key file (server.key), certificate signing request file (server.csr) and webserver certificate file (server.crt) that can be used on Apache server with mod_ssl.

Key, CSR and CRT File Naming Convention

I typically like to name the files with the domain name of the HTTPS URL that will be using this certificate. This makes it easier to identify and maintain.

  • Instead of server.key, I use localhost.localdomain.key
  • Instead of server.crt, I use localhost.localdomain.crt

Generate Private Key on the Server Running Apache + mod_ssl

First, generate a private key on the Linux server that runs Apache webserver using openssl command as shown below.

[root@hercules ~]# openssl genrsa -des3 -out localhost.localdomain.key 2048
Generating RSA private key, 2048 bit long modulus
......................................+++
............................................................................................................................................................+++
e is 65537 (0x10001)
Enter pass phrase for localhost.localdomain.key:
Verifying - Enter pass phrase for localhost.localdomain.key:

That generates a 2048-bit RSA key pair, encrypts them with a password you provide, and writes them to a file. You need to next extract the public key file. You will use this, for instance, on your web server to encrypt content so that it can only be read with the private key..

Export the RSA Public Key to a File

The command to do that is

[root@hercules ~]# openssl rsa -in localhost.localdomain.key -outform PEM -pubout -out public.crt
Enter pass phrase for localhost.localdomain.key:
writing RSA key

The -pubout flag is really important. Be sure to include it.

Next open the public.crt and ensure that it starts with a -----BEGIN PUBLIC KEY-----. This is how you know that this file is the public key of the pair and not a private key.
To check the file from the command line you can use the less command, like this:

[root@hercules ~]# less public.crt

Visually Inspect Your Key Files

It is important to visually inspect you private and public key files to make sure that they are what you expect. OpenSSL will clearly explain the nature of the key block with a -----BEGIN RSA PRIVATE KEY----- or -----BEGIN PUBLIC KEY-----

You can use less to inspect each of your two files in turn:

  • less localhost.localdomain.key to verify that it starts with a -----BEGIN RSA PRIVATE KEY-----
  • less localhost.localdomain.crt to verify that it starts with a -----BEGIN PUBLIC KEY-----

The Generated Key Files

The generated files are base64-encoded encryption keys in plain text format. If you select a password for your private key, its file will be encrypted with your password. Be sure to remember this password or the key pair becomes useless.

The private key file looks something like this

-----BEGIN RSA PRIVATE KEY-----
Proc-Type: 4,ENCRYPTED
DEK-Info: DES-EDE3-CBC,32495A90F3FF199D
lrMAsSjjkKiRxGdgR8p5kZJj0AFgdWYa3OT2snIXnN5+/p7j13PSkseUcrAFyokc
V9pgeDfitAhb9lpdjxjjuxRcuQjBfmNVLPF9MFyNOvhrprGNukUh/12oSKO9dFEt
s39F/2h6Ld5IQrGt3gZaBB1aGO+tw3ill1VBy2zGPIDeuSz6DS3GG/oQ2gLSSMP4
OVfQ32Oajo496iHRkdIh/7Hho7BNzMYr1GxrYTcE9/Znr6xgeSdNT37CCeCH8cmP
aEAUgSMTeIMVSpILwkKeNvBURic1EWaqXRgPRIWK0vNyOCs/+jNoFISnV4pu1ROF
92vayHDNSVw9wHcdSQ75XSE4Msawqv5U1iI7e2lD64uo1qhmJdrPcXDJQCiDbh+F
hQhF+wAoLRvMNwwhg+LttL8vXqMDQl3olsWSvWPs6b/MZpB0qwd1bklzA6P+PeAU
sfOvTqi9edIOfKqvXqTXEhBP8qC7ZtOKLGnryZb7W04SSVrNtuJUFRcLiqu+w/F/
MSxGSGalYpzIZ1B5HLQqISgWMXdbt39uMeeooeZjkuI3VIllFjtybecjPR9ZYQPt
FFEP1XqNXjLFmGh84TXtvGLWretWM1OZmN8UKKUeATqrr7zuh5AYGAIbXd8BvweL
Pigl9ei0hTculPqohvkoc5x1srPBvzHrirGlxOYjW3fc4kDgZpy+6ik5k5g7JWQD
lbXCRz3HGazgUPeiwUr06a52vhgT7QuNIUZqdHb4IfCYs2pQTLHzQjAqvVk1mm2D
kh4myIcTtf69BFcu/Wuptm3NaKd1nwk1squR6psvcTXOWII81pstnxNYkrokx4r2
7YVllNruOD+cMDNZbIG2CwT6V9ukIS8tl9EJp8eyb0a1uAEc22BNOjYHPF50beWF
ukf3uc0SA+G3zhmXCM5sMf5OxVjKr5jgcir7kySY5KbmG71omYhczgr4H0qgxYo9
Zyj2wMKrTHLfFOpd4OOEun9Gi3srqlKZep7Hj7gNyUwZu1qiBvElmBVmp0HJxT0N
mktuaVbaFgBsTS0/us1EqWvCA4REh1Ut/NoA9oG3JFt0lGDstTw1j+orDmIHOmSu
7FKYzr0uCz14AkLMSOixdPD1F0YyED1NMVnRVXw77HiAFGmb0CDi2KEg70pEKpn3
ksa8oe0MQi6oEwlMsAxVTXOB1wblTBuSBeaECzTzWE+/DHF+QQfQi8kAjjSdmmMJ
yN+shdBWHYRGYnxRkTatONhcDBIY7sZV7wolYHz/rf7dpYUZf37vdQnYV8FpO1um
Ya0GslyRJ5GqMBfDS1cQKne+FvVHxEE2YqEGBcOYhx/JI2soE8aA8W4XffN+DoEy
ZkinJ/+BOwJ/zUI9GZtwB4JXqbNEE+j7r7/fJO9KxfPp4MPK4YWu0H0EUWONpVwe
TWtbRhQUCOe4PVSC/Vv1pstvMD/D+E/0L4GQNHxr+xyFxuvILty5lvFTxoAVYpqD
u8gNhk3NWefTrlSkhY4N+tPP6o7E4t3y40nOA/d9qaqiid+lYcIDB0cJTpZvgeeQ
ijohxY3PHruU4vVZa37ITQnco9az6lsy18vbU0bOyK2fEZ2R9XVO8fH11jiV8oGH
-----END RSA PRIVATE KEY-----

The public key file looks somthing like

-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAxzYuc22QSst/dS7geYYK
5l5kLxU0tayNdixkEQ17ix+CUcUbKIsnyftZxaCYT46rQtXgCaYRdJcbB3hmyrOa
vkhTpX79xJZnQmfuamMbZBqitvscxW9zRR9tBUL6vdi/0rpoUwPMEh8+Bw7CgYR0
FK0DhWYBNDfe9HKcyZEv3max8Cdq18htxjEsdYO0iwzhtKRXomBWTdhD5ykd/fAC
VTr4+KEY+IeLvubHVmLUhbE5NgWXxrRpGasDqzKhCTmsa2Ysf712rl57SlH0Wz/M
r3F7aM9YpErzeYLrl0GhQr9BVJxOvXcVd4kmY+XkiCcrkyS1cnghnllh+LCwQu1s
YwIDAQAB
-----END PUBLIC KEY-----

Protecting Your Keys

Depending on the nature of the information you will protect, it’s important to keep the private key backed up and secret. The public key can be distributed anywhere or embedded in your web application scripts, such as in your PHP, Ruby, or other scripts. Again, backup your keys!

Remember, if the key goes away the data encrypted to it is gone.Keeping a printed copy of the key material in a sealed envelope in a bank safety deposit box is a good way to protect important keys against loss due to fire or hard drive failure

WordPress with SSL (https) behind a Reverse Proxy (Apache)

As you can see, this blog is accessible through SSL (https) encryption only. Normally this is not a huge problem but WordPress is a little bit clunky if it comes to a setup that also includes a reverse proxy.

The following text is a sum up some pages which can be found on the internet but often lacks information. This WordPress blog that you are currently reading is running on an Apache httpd on localhost. In front of it, there is a second Apache httpd which acts as reverse proxy for different tasks. One of these tasks is to offload SSL (https) encryption.

WordPress installation

In the described setup you should first install the WordPress software on http (port 80) without SSL. If you enable SSL at this time chances are good that you end up in a redirect loop.

Configure SSL (https)

On the reverse proxy configure SSL as usual but be aware, that you have to set RequestHeader set X-Forwarded-Proto "https" inside the SSL virtual host! This information is important as otherwise the URL’s generated by WordPress will be http links and therefore you will get browser warnings later. Do not force a permanent redirect from http to https at this point or you will not be able to install the necessary WordPress plugin which take care on your URL’s.

After you have enabled basic https support install the WordPress extension SSL Insecure Content Fixer and configure it to use the X-Forwarded-Proto header. Afterwards you have to modify the wp-config.php to reflect this settings. If you want use Jetpack, you also have to specify SERVER_PORT otherwise you will receive a error message on wordpress.com during the configuration of your social media connections (There was an error retrieving your site settings.). You also have to force admin SSL usage.

Hopefully this will help some people out there to get this up and running. If this config does not help you, leave a comment!

Apache http reverse proxy config

# ************************************
# Vhost template in module puppetlabs-apache
# Managed by Puppet
# ************************************

<VirtualHost *:443>
 ServerName www.wittedeurbellen.nl
 ServerAdmin webmaster@wittedeurbellen.nl

 ## Vhost docroot
 DocumentRoot "/var/www/html/www.wittedeurbellen.nl"

 ## Directories, there should at least be a declaration for /var/www/html/www.wittedeurbellen.nl

 <Location "/">
 Options None
 Require all granted
 </Location>

 <Location "/wp-admin">
 Options None
 Require all granted
 </Location>

 ## Logging
 ErrorLog "|/usr/sbin/rotatelogs -l -f /var/log/httpd/external/www.wittedeurbellen.nl/error-ssl_log.%Y.%m.%d 86400"
 ServerSignature Off
 CustomLog "|/usr/sbin/rotatelogs -l -f /var/log/httpd/external/www.wittedeurbellen.nl/access-ssl_log.%Y.%m.%d 86400" combined

 ## Request header rules
 ## as per http://httpd.apache.org/docs/2.2/mod/mod_headers.html#requestheader
 RequestHeader set X-Forwarded-Proto "https"

 ## Proxy rules
 ProxyRequests Off
 ProxyPreserveHost On
 ProxyPass / http://blog.wittedeurbellen.lan/ timeout=120
 ProxyPassReverse / http://blog.wittedeurbellen.lan/

 ## Server aliases
 ServerAlias *.wittedeurbellen.nl

 ## SSL directives
 SSLEngine on
 SSLCertificateFile "xxxxx.pem"
 SSLCertificateKeyFile "xxxxx.pem"
 SSLCertificateChainFile "xxxxxchain.pem"
 SSLCACertificatePath "/etc/pki/tls/certs"
 SSLOptions +StdEnvVars +ExportCertData

 # SSL Proxy directives
 SSLProxyEngine On
</VirtualHost>

Nginx reverse proxy

I dont use Nginx at the moment, but it should work in the same manner. Just be shure that the X-Forwarded-Proto header is submitted by the reverse proxy to the backend.

WordPress wp-config.php

define('FORCE_SSL_ADMIN', true);
$_SERVER['SERVER_PORT'] = 443;