Getting connection URL from (Flask) SQLAlchemy

While working with Flask‘s SQLAlchemy extension I ran into the case where I wanted to obtain the host I was connected to combined with the username and password.

As I didn’t want to parse the URL I provided earlier to SQLAlchemy (and I didn’t have it available at that part of the code) I looked for a method to obtain it from SQLAlchemy.

It turns out this is not (or poorly) documented, but the Engine object has a attribute called url.

>>> db.engine.url
mysql+mysqlconnector://sqlmanager:***@localhost/sqlmanager?charset=utf8
>>>

This is actually a sqlalchemy.engine.url.URL object which contains attributes with the connection information:

>>> url = db.engine.url
>>> url.host
'localhost'
>>> url.username
'sqlmanager'
>>> url.password
'supersecretpassword'
>>> url.database
'sqlmanager'
>>> 

This was the information I needed and allowed me to use this information elsewhere.

Calculating DS record from DNSKEY with Python 3

While working on DNSSEC for PCextreme’s Aurora DNS I had to convert a DNSKEY to a DS-record which could be set in the parent zone for proper delegation.

The foundation for Aurora DNS is PowerDNS together with Python 3.

The API for Aurora DNS has to return the DS-records so that a end-user can use these in the parent zone. I had the DNSKEY, but I didn’t have the DS-record so I had to calculate it using Python 3.

I eventually ended up with this Python code which you can find on my Github Gists page.

"""
Generate a DNSSEC DS record based on the incoming DNSKEY record

The DNSKEY can be found using for example 'dig':

$ dig DNSKEY secure.widodh.nl

The output can then be parsed with the following code to generate a DS record
for in the parent DNS zone

Author: Wido den Hollander 

Many thanks to this blogpost: https://www.v13.gr/blog/?p=239
"""

import struct
import base64
import hashlib


DOMAIN = 'secure.widodh.nl'
DNSKEY = '257 3 8 AwEAAckZ+lfb0j6aHBW5AanV5A0V0IfF99vAKFZd6+fJfEChpZtjnItWDnJLPa3/LAFec/tUhLZ4jgmzaoEuX3EQQgI1V4kp9SYf8HMlFPP014eO+AnjkYFGLE2uqHPx/Tu7/pO3EyKwTXi5fMadROKuo/mfat5AEIhGjteGGO93DhnOa6kcqj5RHYJBh5OZ/GoZfbeYHK6Muur1T16hHiI12rYGoqJ6ZW5+njYprG6qwp6TZXxJyE7wF1JdD+Zhbjhf0Md4zMEysP22wBLghBaX6eDIBh/7jU7dw1Ob+I42YWk+X4NSiU3sRYPaq1R13JEK4zVqQtL++UVtgRPEbfj5RQ8='


def _calc_keyid(flags, protocol, algorithm, dnskey):
    st = struct.pack('!HBB', int(flags), int(protocol), int(algorithm))
    st += base64.b64decode(dnskey)

    cnt = 0
    for idx in range(len(st)):
        s = struct.unpack('B', st[idx:idx+1])[0]
        if (idx % 2) == 0:
            cnt += s << 8
        else:
            cnt += s

    return ((cnt & 0xFFFF) + (cnt >> 16)) & 0xFFFF


def _calc_ds(domain, flags, protocol, algorithm, dnskey):
    if domain.endswith('.') is False:
        domain += '.'

    signature = bytes()
    for i in domain.split('.'):
        signature += struct.pack('B', len(i)) + i.encode()

    signature += struct.pack('!HBB', int(flags), int(protocol), int(algorithm))
    signature += base64.b64decode(dnskey)

    return {
        'sha1':    hashlib.sha1(signature).hexdigest().upper(),
        'sha256':  hashlib.sha256(signature).hexdigest().upper(),
    }


def dnskey_to_ds(domain, dnskey):
    dnskeylist = dnskey.split(' ', 3)

    flags = dnskeylist[0]
    protocol = dnskeylist[1]
    algorithm = dnskeylist[2]
    key = dnskeylist[3].replace(' ', '')

    keyid = _calc_keyid(flags, protocol, algorithm, key)
    ds = _calc_ds(domain, flags, protocol, algorithm, key)

    ret = list()
    ret.append(str(keyid) + ' ' + str(algorithm) + ' ' + str(1) + ' '
               + ds['sha1'].lower())
    ret.append(str(keyid) + ' ' + str(algorithm) + ' ' + str(2) + ' '
               + ds['sha256'].lower())
    return ret


print(dnskey_to_ds(DOMAIN, DNSKEY))

The Ceph Trafficlight

At PCextreme we have a 700TB Ceph cluster which is used behind our public cloud Aurora Compute which runs Apache CloudStack.

Ceph health

One of the things we monitor of the Ceph cluster is it’s health. This can be OK, WARN or ERR. It speaks for itself that you always want to see OK, but things do go wrong. Disks fail, machines die, kernel panics happen. Stuff goes wrong.

I thought it was a cool idea to buy a used real traffic light which I could install at the office. OK would be green, WARN would be orange/amber and ERR would be red.

2nd hand Trafficlight

Some searching on the internet brought me to trafficlightshop.com. They sell used (Dutch) traffic lights. I bought a Vialis 2230 (The largest on the picture below).

Vialis trafficlight overview

For EUR 75,00 I got my hands on a original trafficlight!

Controlling the lights

When I got the trafficlight it was already equipped with LED lights which work on 230V. A 30cm cable (cut off) was sticking out with 4 wires in it:

  • Blue: Neutral
  • Green: Phase/Positive for Green
  • Yellow: Phase/Positive for Orange/Amber
  • Red: Phase/Positive for Red

It was easy. All I had to do was buy a add-on board for a Raspberry Pi so I could control the lights.

Solid State Relay

My search for a add-on board brought me to BitWizard.nl, they make all kinds of add-on boards for the Raspberry Pi.

One of them is a SSR (Solid State Relay) board which has 4 outputs. Their wiki explained that it was very simple to control the Relays using Python.

Solid State Relay board

A quick test at my desk at home brought be to a working setup.

Addition components

After writing the code which controls the light it was time to buy some housing where I could install it in.

At Conrad I found the things I needed. A housing, some connectors and some cabling. A overview of my order:

Conrad order

This was needed since I would install it at the office and it needed to be safe. You don’t want somebody to get shocked by 230V. That’s kind of dangerous.

Bringing it together

It was time to start drilling and soldering! In my shed it looked like this:

My shed

And a few more pictures of building it. Took me about 3 hours to complete.

ssr-board-and-connector

drilling-holes

connectors-installed-1

connectors-installed-2

box-installed

box-installed-with-cables

At the office

The next day it was time to install it at the office! Some drilling and the result:

Health OK: Green

light-on-green

Health WARN: Amber/Orange

light-on-orange

Health ERR: Red

No picture! We can trigger a WARN state in Ceph without service interruptions, but not a ERR state.

The code

The Python code I wrote is all on Github. It’s just some Python code which polls our Ceph dashboard every second. If the status changes it also changes the traffic light.

Calculating RADOS objects for RBD images

Ceph’s RBD (RADOS Block Device) is just a thin wrapper on top of RADOS, the object store of Ceph.

It stripes (by default) over 4MB objects in RADOS. It’s very simple to calculate which RADOS object corresponds with which sector on your RBD image/block device.

First you have to find out the block device’s object prefix name and the stripe size:

ceph@daisy:~$ sudo rbd info test
rbd image 'test':
	size 128 MB in 32 objects
	order 22 (4096 KB objects)
	block_name_prefix: rb.0.1066.2ae8944a
	format: 1
ceph@daisy:~$

In this case the stripe size is 4MB (order 2^22) and the object name prefix is rb.0.1066.2ae8944a

With one line of Perl we can calculate the object name in RADOS:

perl -e 'printf "BLOCK_NAME_PREFIX.%012x\n", ((SECTOR_OFFSET * 512) / (4 * 1024 * 1024))'

Let’s say that we want the object for sector 1 of our block device:

perl -e 'printf "rb.0.1066.2ae8944a.%012x\n", ((0 * 512) / (4 * 1024 * 1024))'

This tells us that we need to fetch object rb.0.1066.2ae8944a.000000000000 from RADOS. This can be done using the ‘rados’ command:

sudo rados -p rbd get rb.0.1066.2ae8944a.000000000000 rb.0.1066.2ae8944a.000000000000

Voila, you just fetched 4MB of your drive. Might be useful if you want to do some data recovery or such.