Posts from 2015-07

FTP: view files from within client

posted on 2015-07-23 00:58:40

To have a look at a file on an ftp server, without downloading it and opening it in a pager/editor/cat?

Well, you have to download, but just try appending a dash:

get <filename> -

That way the file gets transferred directly to stdout. :D

PHP / MySQL Tuning

posted on 2015-07-22 05:42:35

For sqeezing a little bit more performance out of a php page backed with mysql, try:

/etc/mysql/my.cnf:

#max_execution_time = 30
max_execution_time  = 60

#memory_limit       = 128M
memory_limit        = 512M

php.ini:

#key_buffer         = 16M
key_buffer          = 32M
#query_cache_limit  = 8M
query_cache_limit   = 8M
#query_cache_size   = 16M
query_cache_size    = 64M

Commented lines are the default values for comparison.

OpenSSL: Create a TLS certificate

posted on 2015-07-18 01:13:32

This posting is solely about handling certificates, no cryptographic background or deeper technical digging.

background and grey theory

Basically creating a certificate can be done via two ways:

  • create a private key and a certificate from it
  • create a private key, a certificate signing-request out of it, and with another certificate-authority private-key and certificate-authority certificate create an own certificate

The first approach is to be done for CA's (certificate authorities), and their (CA) certificates are shipped with the common browsers.

Website (or whatever type of service uses SSL/TLS) let their domain be secured with an own certificate, which the certificate authority created from a certificate signing request the owner sent them using their private key and certificate.

The user using the browser to access the secured domain gets the ca cert and the site owner's cert, and can check if the site owner's cert can be validated against the root CA cert.

So these are the artifacts in play: (CA = certificate authority, SRV = server owner)

  • CA privkey (CA_PK)
  • CA cert (CA_C)
  • SRV privkey (SRV_PK)
  • SRV CSR (SRV_CSR)
  • SRV cert (SRV_C)

installing them server-side

In this order they are created and installed:

         SRV                                         CA
        -----                                       ----
          |                                          |
          |                                     1. create CA_PK
          |                                     2. create CA_C
          |                                          |
    3. create SRV_PK                                 |
    4. create SRV_CSR                                |
          |                                          |
    5.    |--------------- email SRV_CSR ----------->|
          |                                          |
          |                                     6. create SRV_C
          |                                     (from CA_PK,CA_C,SRV_CSR)
          |                                          |
    7.    |<--------------  email SRV_C  ------------|
          |                                          |
    8. configure server (web,ftp,...)                |
       (to provide CA_C,SRV_PK,SRV_C)                |
          |                                          |

This is all which is needed to set TLS encryption up.

Note: CA_C and SRV_C differ in having the CA flag set to TRUE or not. Besides that, they are the same.

using them client-wise

To keep it simple, clients negotiate the connection with the server, and use the th SRV_C of aforementioned artefacts from the server. The CA_C is already part of the web browser.

practice and its complexities

To prevent some misunderstandings, even more basics:

  • CA's are just companies printing money, as these certificates tend to be very expensive but are not saver than ones you create on your own and validate these against a self-created self-signed one (With you being basically your own CA.). The only difference is, the CA's get their certificates shipped with every browser, whereas you do not get that luxury.
  • CA_PK and SRV_PK do not differ in how they were created.
  • self-signed certificate = no higher-level cert is needed for validation (for CA's)
  • messing up which one is which is the third biggest source of problems revolving around certificates
  • the second biggest problem is the unwieldiness of the tools to handle these things
  • the biggest source of FAIL when dealing with certificates is the amount of different ones being in different formats (single file or container 'containing' several 'files') and encodings (ascii/base64 vs. binary) and simple copy-paste errors when creating some of them

This sounds complexer than it really is. Haha, I know.

You can import the self-created self-signed CA cert into your browser by hand, so you don't get the warning about visiting unsecure sites anymore, but the rest of the world will still get the error. Plus your google ranking for your website should be lower, as far as I can remember.

a script for automating things

When having to have to create certificates from time to time, this may come handy:

openssl genrsa 2048 > ca-key.pem && \
openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca-cert.pem && \
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-req.pem && \
openssl rsa -in server-key.pem -out server-key.pem && \
openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

For automated generation, the openssl tool is way more nicer than certtool from GnuTLS. For checking contents or creating a .p12 / pkcs #12 container, the latter is better:

# check contents
certtool --certificate-info --infile C.pem

# create .p12
certtool --to-p12 --load-ca-certificate CA_C.pem --load-privkey PK.key --load-certificate C.pem --outfile CONTAINER.p12 --outder

show certificate information

certtool --certificate-info --infile C.pem

proxmox: manual cluster migration

posted on 2015-07-13 18:10:53

  1. change drbd on current node to 'active', if needed
  2. service pve-cluster stop
  3. pmxcfs -l
  4. start vm again (qm start <vm-id>)

To rebuild the cluster again:

  • unmount /etc/pve
  • service pve-cluster start

MySQL: Check used storage engine

posted on 2015-07-13 15:32:01

Something to copy paste, in case you already have a .my.cnf for your root user with his password.

This only for tables you created:

less < <({ for i in $(mysql -e "show databases;" | cat | grep -v -e Database -e information_schema -e mysql -e performance_schema); do echo "--------------------$i--------------------";  mysql -e "use $i; show table status;"; done } | column -t)

This will show all tables, including the mysql ones:

less < <({ for i in $(mysql -e "show databases;" | cat | grep -v -e Database); do echo "--------------------$i--------------------";  mysql -e "use $i; show table status;"; done } | column -t)

To make it a little more readable, hitting -S in less turns or wordwrapping in less. Thus the lines which are too long are simply cut.

In a little more detail, this cannot be copy pasted in this form as it's missing the line break escapes, sorry this time not:

less < <(
            { 
                for i in $(mysql -e "show databases;" | 
                cat | 
                grep -v -e Database -e information_schema -e mysql -e performance_schema);
                do echo "--------------------$i--------------------"; 
                    mysql -e "use $i; show table status;";
                done 
            } | 
            column -t
        )

The cat piping is needed so the output will be without borders. I honestly have no idea why this cat here works the way it does. :)

VMWare: .vmx file locations

posted on 2015-07-13 07:20:50

When having to edit VMWare virtual machine settings, you need to find the proper .vmx file.

Connect to the hypervisor and do:

cat /etc/vmware/hostd/vmInventory.xml

This will output the xml contents, each VM is named <vmname>.xml, so you will get the full path to each vm config file.

SSH: tunnel and port-forwarding howto

posted on 2015-07-10 07:56:07

To create ssh tunnels there are a lot of explanations out there, and the most are not worth much. Let's see if I can do better.

some facts against common misconceptions

one

A tunnel involves only two endpoints.

Ok, fair enough. But you need to specify minimum three host locations for a working tunnel.

Where two can point to the same machine, just from different views.

Which is your local host (or at least it's port), the gateway (the machine which will be the other tunnel endpoint) and the machine you are targetting. localhost, if the target/destination host is the same machine as the gateway host.

More on that later, if this does not make sense yet.

two

Another misconception which is often prevalent: "How do I get the server port so I can access it locally?"

Actually the direction may seem unnatural:
Things depend on the source host, where the request (of whichever protocol being used) will originate.

three

There exist directions, which is what the -L and -R flags are for.

four

The order in which the ssh arguments are specified can actually be changed. And changed it is quite easier to grok.

tunnel 101

This is basic tunnelling knowledge, where SSH tunnels differ from SSL/IPSEC VPNs comments will indicate so.

Tunnelling connects non-routable networks with each other. (This is the case when one or both sites are behind a NAT.)

A tunnel is created between two enpoints, often called gateways. Encrypted pipes are created for securing traffic by crypting packets between the endpoints.

On each side, other hosts can be reached. Depending on the tunnel type, you may or may not have access to the remote gateway. (SSH lets you access the remote gateway, with an IPSEC VPN (virtual private network) where application and endpoint run on the same box you are in for some trouble. It works, but is ugly to do so.)

You also have to specify the hosts behind the endpoint. This can happen via subnets, or you can specify single hosts. (With SSH we will specify only single hosts here, no networks. Further only one side behind the tunnel has to be specified, the other side's host 'behind' the tunnel endpoint, is always located on the same machine as the gateway in question. The tunnel, it being of local or remotely forwarded port type, lets you specify the host not being locate on the gateway. Don't worry, this will come later with a better explanation.)

On general VPN's:
If you would not specify the local and remote network, how could the remote party possibly know to which ip packets should have to be directed, after the data packets exit the tunnel? (For SSH as already stated, only one host, either remote or local, which is not located on a gateway, can be specified. The other 'end' outside of the tunnel endpoint, lies always on the the gateway.)

ssh tunneling howtos

preface

A regular ssh tunnel is like the above mentioned tunnels, except that the gatways and the networks after the ends (/32 networks to be exact) reside on the same host (read: the gateway). This guide assumes that you already know how to do this, its the basic ssh <hostname-or-ip> stuff.

chained tunnels

To connect to a remote host, but hopping over a few other hosts in the process, simply chain the tunnels:

ssh <host1> ssh <host2> ssh <host3>

Since you will want proper terminals, use the -t flag when doing so. And use -A if you need agent forwarding, when wanting to copy files between hosts directly.

ssh -t -A <host1> ssh -t -A <host2> ssh -A <host3>

This chaining stuff will also work for port forwardings described below, but you really have to watch your ports, so things fit together.

local tunnelling / port forwarding

-L will forward a port on your side of the tunnel to a host on the other one. That way you can reach over into the remote network.

The first use case here will be 'local' tunneling with the -L flag. The port specified on the local site will be forwarded to the remote site. This will be done so the webinterface of a remote NAS behind a router with NAT will be made externally accessible. NAS means Network Attached Storage, a small data server consuming not much energy providing file-level data.

For this to work, the router has to be configured such that it does port forwarding of requests on its port 12345 to the ssh host you want to connect to, by knowing its IP and the port on which the ssh server on this machine runs. (Usually on port 22.)

Usually you see specifications like this one:

ssh -L 1337:192.168.0.33:443 <user>@<domain-or-ip> -p12435

Easier to grasp should be this:

ssh <domain-or-ip> -l <user> -p 12345 -L localhost:1337:192.168.0.33:443

You ssh to the host at <domain-or-ip>, with the user specified by -l as <user> on port specified with -p which is 12345. The port only has to be specified if SSH is not running on standard port 22. This is the gateway part.

Then you pass the information from on the local and the remote host, connected via a :.

localhost is the bind address, on which the SSH server instance is running, and 1337 is the port which will be used for accessing the webinterface. Which is what you have to type into your browser. (https://localhost:1337) If it were running with a different bind address, you'd have to use this one here, but then I likely would not have to tell you that. :) localhost does not have to be specified, this is done just for illustration purposes.

What another bindaddress does, is allowing others to use the tunnel if GatewayPorts is enabled on the local SSH server. See man sshd_config for more info.

192.168.0.33:443 is the ip of the NAS system on the remote network behind the remote gateway and the port where the webserver is running on there.

remote tunneling / port forwarding

-R will forward a port from the remote site to your side of the tunnel. That way hosts from your network can be reached remotely.

Going along with the example above, from within the LAN where the NAS is located:

ssh <domain-or-ip> -l <user> -p 12345 -R localhost:1337:192.168.0.33:443

Here <domain-or-ip> -l <user> -p 12345 is again the gateway information for the remote machine. Depending on -L or -R the local or remote port (and bindaddress!) are specified.

localhost here talks about the bindaddress on the remote server. If it is explicitly set, ssh's GatewayPorts directive/option has to be enabled on the server's /etc/ssh/sshd_config.

192.168.0.33:443 is just the location of the NAS again.

tunnel chains with port forwardings

A local example:

ssh -t <host1> -L 1337:localhost:1337 ssh -t <host2> -L 1337:localhost:1337 ssh <host3> -L 1337:192.168.0.33:443

Local browser can reach the far far away NAS via https://localhost:1337, which is on the same network as <host3>. If the NAS were SSH accessible, the complete path could be encrypted. Since we can't (at least in my made up example), we will hop from <host3> to it at its IP 192.168.0.33, and this is the only part of the connection, that cannot be encrypted. (This is just provided for educational purposes, such complex setups are usually unlikely in sane reality.)

Use -t for all hops prior to the last one.

a tunnel in a tunnel - port forwarding for ssh to reach locally bound services

This is for services bound to the loopback / 127.0.0.1 interface, and which are thus only locally available:

ssh <host1> -L 1336:<host2>:22
ssh localhost -p 1336 -L 1337:localhost:3306

NAS is again a bad example here, as usually these boxes do not have ssh daemons installed/running.

What we did above was simply building a tunnel to the host we want to hop onto, and then creating the port forward by connecting to the locally existing SSH tunnel. This may be useful for remote connections to mysql instances that usually can just be reached locally.

Usually I have no use for this, but it might come in handy some day.

dynamic tunnelling

To create a SOCKS proxy via SSH:

ssh <domain-or-ip> -l <user> -p 12345 -D 192.168.0.2:1337

Here a specific bindaddress was used (192.168.0.2, which is our local ip within our LAN. Do you remember the Gatewayports thing?). Any host connecing to our ssh tunnel running on port 1337 will straight be forwarded to the remote gateway.

The application has to know how to handle SOCKS connections, else this will not work.

To keep up with our NAS example, I'd do:

ssh <domain-or-ip> -l <user> -p 12345 -D 1337

Then set up my web browser to use a SOCKS proxy, with address localhost (since no bindaddress was given, unlike in the prior example) and port 1337.

Afterwards https://192.168.0.33:433 can be entered into the adressbar and the NAS is reachable. Just keep in mind, that other Websites will not work.

PPP-over-SSH

When having to use software which is unaware of SOCKS proxies, the Point-to-Point Protocol (PPP) comes to help.

Also this is a poor man's VPN, when used to transfer all traffic through it and not just a sole host or network.

Since I have not had this put to use yet, I cannot write much about it.

So far:

  • Routing may be an issue and thus reaching DNS servers, when its just used to partially tunnel network connections.
  • When tunnelling everything, OSPF (open-shortest-path-first, a routing protocol) can be used to fix this, as I read, see the second link for more info.
  • Well, here are the links.

One link was on BSD, but I guess this helps with enlightenment. The shortest howto is the last one from the Arch wiki. Best may be the second one.

mysql: show grants for database

posted on 2015-07-03 15:37:44

To show all grants for a single database in a proper overview:

select db 'DATABASE', host HOST, user USER from mysql.db where db = '<databasename>';

To quickly check all databases and all permissions:

select db,host,user from mysql.db;

This blog covers .csv, .htaccess, .pfx, .vmx, /etc/crypttab, /etc/network/interfaces, /etc/sudoers, /proc, 10.04, 14.04, 16.04, AS, ASA, ControlPanel, DS1054Z, GPT, HWR, Hyper-V, IPSEC, KVM, LSI, LVM, LXC, MBR, MTU, MegaCli, PHP, PKI, PS1, R, RAID, S.M.A.R.T., SNMP, SSD, SSL, TLS, TRIM, VEEAM, VMware, VServer, VirtualBox, Virtuozzo, XenServer, acpi, adaptec, algorithm, ansible, apache, apache2.4, apachebench, apple, applet, arcconf, arch, architecture, areca, arping, asa, asdm, autoconf, awk, backup, bandit, bar, bash, benchmarking, binding, bitrate, blackarmor, blockdev, blowfish, bochs, bond, bonding, booknotes, bootable, bsd, btrfs, buffer, c-states, cache, caching, ccl, centos, certificate, certtool, cgdisk, cheatsheet, chrome, chroot, cisco, clamav, cli, clp, clush, cluster, cmd, coleslaw, colorscheme, common lisp, configuration management, console, container, containers, controller, cron, cryptsetup, csync2, cu, cups, cygwin, d-states, database, date, db2, dcfldd, dcim, dd, debian, debug, debugger, debugging, decimal, desktop, df, dhclient, dhcp, diff, dig, display manager, dm-crypt, dmesg, dmidecode, dns, docker, dos, drivers, dtrace, dtrace4linux, du, dynamictracing, e2fsck, eBPF, ebook, efi, egrep, emacs, encoding, env, error, ess, esx, esxcli, esxi, ethtool, evil, expect, exportfs, factory reset, factory_reset, factoryreset, fail2ban, fakeroot, fbsd, fdisk, fedora, file, files, filesystem, find, fio, firewall, firmware, fish, flashrom, forensics, free, freebsd, freedos, fritzbox, fsck, fstrim, ftp, ftps, g-states, gentoo, ghostscript, git, git-filter-branch, gitbucket, github, gitolite, global, gnutls, gradle, grep, grml, grub, grub2, guacamole, hardware, haskell, hdd, hdparm, hellowor, hex, hexdump, history, howto, htop, htpasswd, http, httpd, https, i3, icmp, ifenslave, iftop, iis, imagemagick, imap, imaps, init, innoDB, innodb, inodes, intel, ioncube, ios, iostat, ip, iperf, iphone, ipmi, ipmitool, iproute2, ipsec, iptables, ipv6, irc, irssi, iw, iwconfig, iwlist, iwlwifi, jailbreak, jails, java, javascript, javaws, js, juniper, junit, kali, kde, kemp, kernel, keyremap, kill, kpartx, krypton, lacp, lamp, languages, ldap, ldapsearch, less, leviathan, liero, lightning, links, linux, linuxin3months, lisp, list, livedisk, lmctfy, loadbalancing, locale, log, logrotate, looback, loopback, losetup, lsblk, lsi, lsof, lsusb, lsyncd, luks, lvextend, lvm, lvm2, lvreduce, lxc, lxde, macbook, macro, magento, mailclient, mailing, mailq, make-jpkg, manpages, markdown, mbr, mdadm, megacli, micro sd, microsoft, minicom, mkfs, mktemp, mod_pagespeed, mod_proxy, modbus, modprobe, mount, mouse, movement, mpstat, multitasking, myISAM, mysql, mysql 5.7, mysql workbench, mysqlcheck, mysqldump, nagios, nas, nat, nc, netfilter, networking, nfs, nginx, nmap, nocaps, nodejs, numberingsystem, numbers, od, onyx, opcode-cache, openVZ, openlierox, openssl, openvpn, openvswitch, openwrt, oracle linux, org-mode, os, oscilloscope, overview, parallel, parameter expansion, parted, partitioning, passwd, patch, pct, pdf, performance, pfsense, php, php7, phpmyadmin, pi, pidgin, pidstat, pins, pkill, plasma, plesk, plugin, posix, postfix, postfixadmin, postgres, postgresql, poudriere, powershell, preview, profiling, prompt, proxmox, ps, puppet, pv, pveam, pvecm, pvesm, pvresize, python, python3, qemu, qemu-img, qm, qmrestore, quicklisp, quickshare, r, racktables, raid, raspberry pi, raspberrypi, raspbian, rbpi, rdp, redhat, redirect, registry, requirements, resize2fs, rewrite, rewrites, rhel, rigol, roccat, routing, rs0485, rs232, rsync, s-states, s_client, samba, sar, sata, sbcl, scite, scp, screen, scripting, seafile, seagate, security, sed, serial, serial port, setup, sftp, sg300, shell, shopware, shortcuts, showmount, signals, slattach, slip, slow-query-log, smbclient, snmpget, snmpwalk, software RAID, software raid, softwareraid, sophos, spacemacs, spam, specification, speedport, spi, sqlite, squid, ssd, ssh, ssh-add, sshd, ssl, stats, storage, strace, stronswan, su, submodules, subzone, sudo, sudoers, sup, swaks, swap, switch, switching, synaptics, synergy, sysfs, systemd, systemtap, tar, tcpdump, tcsh, tee, telnet, terminal, terminator, testdisk, testing, throughput, tmux, todo, tomcat, top, tput, trafficshaping, ttl, tuning, tunnel, tunneling, typo3, uboot, ubuntu, ubuntu 16.04, ubuntu16.04, udev, uefi, ulimit, uname, unetbootin, unit testing, upstart, uptime, usb, usbstick, utf8, utm, utm 220, ux305, vcs, vgchange, vim, vimdiff, virtualbox, virtualization, visual studio code, vlan, vmstat, vmware, vnc, vncviewer, voltage, vpn, vsphere, vzdump, w, w701, wakeonlan, wargames, web, webdav, weechat, wget, whois, wicd, wifi, windowmanager, windows, wine, wireshark, wpa, wpa_passphrase, wpa_supplicant, x11vnc, x2x, xfce, xfreerdp, xmodem, xterm, xxd, yum, zones, zsh


Unless otherwise credited all material Creative Commons License by sjas