firewing1's blog

Customizing the DNS Servers used for specific clients with Unifi Security Gateway

One of the neat and relatively undocumented feature of Unifi Security Gateway (USG) is the ability to specify alternate DNS servers sent with DHCP replies for specific clients, permitting you to do things like setup pihole for only a few specific devices on your LAN (e.g. the Smart TV or a streaming stick).

This is perfect as I didn't want to point my whole network at the pihole, as that would mean any technical issues with my pihole host (config errors, docker failing, etc) means the whole home Internet connection effectively going offline.

You can test out this feature interactively by SSHing into the USG and running these commands (replace capitals as appropriate):

set service dhcp-server shared-network-name net_LANNAME_eth1_SUBNET-MASK subnet SUBNET/MASK static-mapping DASH-SEPARATED-MAC-ADDR ip-address LAN_STATIC_IP
set service dhcp-server shared-network-name net_LANNAME_eth1_SUBNET-MASK subnet SUBNET/MASK static-mapping DASH-SEPARATED-MAC-ADDR mac-address COLON:SEPARATED:MAC:ADDRESS
set service dhcp-server shared-network-name net_LANNAME_eth1_SUBNET-MASK subnet SUBNET/MASK static-mapping DASH-SEPARATED-MAC-ADDR static-mapping-parameters  "option domain-name-servers DNS_IP_FOR_OVERRIDE;"

This will edit the running configuration but rebooting or re-provisioning will lose these changes. To persist the configuration, create/edit your config.gateway.json with a snippet like this:

        "service": {
                "dhcp-server": {
                        "shared-network-name": {
                                "net_LANNAME_eth1_SUBNET-MASK": {
                                        "subnet": {
                                                "SUBNET/MASK": {
                                                        "static-mapping": {
                                                                "DASH-SEPARATED-MAC-ADDRESS": {
                                                                        "host-record": "disable",
                                                                        "ip-address": "LAN_STATIC_IP",
                                                                        "mac-address": "COLON:SEPARATED:MAC:ADDRESS",
                                                                        "static-mapping-parameters": "option domain-name-servers DNS_IP_FOR_OVERRIDE;"

Credit for discovering the syntax: tachyonforce on the Unifi forums

For those curious about the pihole setup specifically, I used docker-compose with the pihole/pihole image on a home server to get it running:

version: "2.2"

    image: pihole/pihole
    container_name: pihole
    restart: unless-stopped
      - TZ=America/Los_Angeles
      - ServerIP=
      - WEBPASSWORD=arandompw
      - VIRTUAL_HOST=externalhostname
      - ""
      - ""
      - /srv/docker-vols/pihole/etc/pihole:/etc/pihole/
      - /srv/docker-vols/pihole/etc/dnsmasq.d:/etc/dnsmasq.d

Here I used the IP address of the server in the port mapping to as the server has multiple interfaces, and 53 is already used elsewhere. Specifying the IP ensures that Docker attempts to port map on the correct interface.

VIRTUAL_HOST is required because I use a reverse proxy to expose internal services, so the hostname must be provided to ensure dashboard URLs resolve correctly.


Using Azure CLI 2.0 behind a web proxy with mitmproxy or Fiddler

The Azure CLI is a wonderful tool to manage Azure resources but at times, you'll run into a bizarre error (or want to reverse engineer what API call is being made for a given comment) and need more information. HTTP session capture tools like Fiddler or mitmproxy are excellent for tracing HTTP calls, but the since the Azure CLI constructs requests directly using the requests Python library, it ignores the Windows or macOS default proxy settings.

Here's how you can call the Azure CLI forcing it to use the HTTP web proxy:

export HTTP_PROXY="http://localhost:8080" HTTPS_PROXY="http://localhost:8080"
az rest --debug --method put --uri "$URL" --body "$BODY"

Note that unless you just want to use a HTTP proxy, mitmproxy or Fiddler will also be intercepting HTTPS requests and presenting its own certificate. Even if you it trusted in the system certificate store, again - Python's requests uses its own resulting in something like this error message: : HTTPSConnectionPool(host='', port=443): Max retries exceeded with url: /subscriptions/subid/resourceGroups/vmname/providers/microsoft.Security/locations/westus2/jitNetworkAccessPolicies/default/Initiate?api-version=2015-06-01-preview (Caused by SSLError(SSLError("bad handshake: Error([('SSL routines', 'ssl3_get_server_certificate', 'certificate verify failed')])")))

HTTPSConnectionPool(host='', port=443): Max retries exceeded with url: /subscriptions/subid/resourceGroups/vmname/providers/microsoft.Security/locations/westus2/jitNetworkAccessPolicies/default/Initiate?api-version=2015-06-01-preview (Caused by SSLError(SSLError("bad handshake: Error([('SSL routines', 'ssl3_get_server_certificate', 'certificate verify failed')])")))

Set AZURE_CLI_DISABLE_CONNECTION_VERIFICATION=1 to also disable SSL certificate verification for the Azure CLI:


Good to go!


Automating database restores with SQL Managed Instance

Provisioning if an often overlooked aspect when architecting a SaaS solution, but it's one of the most important ones. How do you make sure new users and tenants can be automatically created and ready to use?

Recently, I worked on a provisioning API for Azure SQL Managed Instance and ran into some interesting quirks.

Database restores

When restoring databases, SQL Managed Instance will not provide any STATS or restore completion events to the user; this is because internally, the SQL Managed Instance has a background worker that does the actual restore - this is well documented by Brent Ozar on his blog. You can see this in action by restoring a large backup (say, >2GB) and then querying active restores:

-- Query active restores
SELECT query = a.text, start_time, percent_complete, eta = dateadd(second,estimated_completion_time/1000, getdate())
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command IN ('RESTORE DATABASE', 'DROP DATABASE');

for example:

name database_id query percent_complete
master 1 RESTORE DATABASE [9f288db4-12af-49a6-ab9d-b414afaf467a] FROM URL = N'' WITH STATS=10, BUFFERCOUNT=8, MAXTRANSFERSIZE=3145728, NORECOVERY, REPLACE, MOVE N'v3starterdb_data' TO N'C:\WFRoot\DB16C.2\Fabric\work\Applications\Worker.CL_App15\work\data\9f288db4-12af-49a6-ab9d-b414afaf467a.mdf' , [truncated for brevity] 37.5055

In short, SQL MI accepts a restore command and begins a restore to a database named after a random GUID. Once completed, it renames it to the user-requested database name.

If you need to map the physical database name to the logical (user-facing) database names, check the physical_database_name column value from sys.databases:

SELECT name, physical_database_name FROM sys.databases

Note that this value only becomes available after restore is complete as a result, detecting restore progress programmatically is impractical (theoretically possible if OK with throwing a bunch of string matching-kludges into the query above... but I'm not).

Detecting restoring completion

As a result of the physical vs logical DB name mappings, detecting database restore progress or completion is non-trivial and can be a blocker if you have downstream tasks to perform, e.g. running some T-SQL against your newly restored DB to prepare it for use.

When a restore is completed and the physical (GUID) database is renamed to the user-requested one, it appears to be online:

    DATABASEPROPERTYEX(name,'Updatability') AS 'Updatability',
    DATABASEPROPERTYEX(name,'Status') AS 'Status'
FROM dbo.sysdatabases;
name Updatability Status

However attempting to query that database (either read or write) will fail with Unable to access database 'mydb' because it lacks a quorum of nodes for high availability. Try the operation again later. So what gives?

SQL MI's Business Critical tier uses something similar to Always On to prevent data loss. Those availability groups are configured for you automatically - but while initial replication occurs and until quorum is established, the database is present on the master but not queriable!

You can use the following query to determine if a DB is still under replication, and therefore ready or not on the Business Critical tier:

-- Query synchronization state
SELECT sdb.database_id,, sdb.physical_database_name, t.is_ready FROM sys.databases sdb
    SELECT r.database_id, is_ready = CASE WHEN COUNT(CASE WHEN r.synchronization_state = 2 THEN 1 ELSE NULL END) > 1 THEN 1 ELSE 0 END
    FROM sys.dm_hadr_database_replica_states r
    GROUP BY r.database_id
) t ON t.database_id = sdb.database_id
WHERE name LIKE 'Works_tid%'

Once is_ready=1 your database is good to go, and you can safely execute additional queries against it. Note that this is during normal operation - if you are doing something fancy like resizing your instance, the ready detection not work as expected.

One last word of caution regarding SqlConnection and SMO

This is not specific to SQL Managed Instance, but a word of caution for those automating query execution with the C# SqlConnection class from System.Data.SqlClient: it does not necessarily open a new physical connection (see connection pooling for details) and it can only support one command at a time.

Attempting to re-use a SqlConnection (e.g. via multiple RestoreAsync() in SMO) before it is ready can result in very bizarre behavior (the SqlConnection closes itself and the active thread may stop executing). Best practice is:

  1. Avoid SMO altogether in favor of T-SQL, which is what SMO produces anyways
  2. Create a new SqlConnection instance for every T-SQL command you wish to execute, and close the connection after. Wrapping the creation in a using statement will do this for you.

Testing Flask applications (code, database, views, flask config, and app context) with pytest

I love writing tests for my code but whenever starting in a new language or framework it's such a pain since getting the mocks and fixtures just right tends to be very language & framework specific. I searched and searched for a good pytest + flask configuration that would let me do unit and integration tests for the app and found some good pieces but nothing holistic.

Thorough testing of Flask apps

I wanted a pytest configuration that would give no side-effects between tests and provide a solid foundation for writing everything from unit to integration to database tests:

  • Mocked (monkey-patched) methods and classes for unit testing
  • Per-test Flask application context, letting you test things like oauth-filter-for-python-flask
  • Fast, in-memory SQLite database that is torn down between each test
  • REST client to test Flask APIs or views

pytest configuration

The code samples below are pretty generic but may require minor customization for your Flask application. I highly recommend you take a look at the flask-bones sample, which contains many best practices and this sample will work with it out of the box.

It assumes the use of the following modules available via pip: pytest, pytest-flask and pytest-mock


import pytest

from yourflaskmodule import create_app
from yourflaskmodule.config import test_config
from yourflaskmodule import db as _db

def app(request):
    """Test session-wide test `Flask` application."""
    app = create_app(test_config)
    return app

def _setup_app_context_for_test(request, app):
    Given app is session-wide, sets up a app context per test to ensure that
    app and request stack is not shared between tests.
    ctx = app.app_context()
    yield  # tests will run here

def db(app, request):
    """Returns session-wide initialized database"""
    with app.app_context():
        yield _db

def session(app, db, request):
    """Creates a new database session for each test, rolling back changes afterwards"""
    connection = _db.engine.connect()
    transaction = connection.begin()

    options = dict(bind=connection, binds={})
    session = _db.create_scoped_session(options=options)

    _db.session = session

    yield session


Here's an example of a base config class with the SQLite in-memory override:

class test_config(base_config):
    """Testing configuration options."""

    TESTING = True
    SQLALCHEMY_DATABASE_URI = 'sqlite:///memory'

Here's an example of a test making use of all the different features:

import pytest

    from flask import _app_ctx_stack as ctx_stack
except ImportError:
    from flask import _request_ctx_stack as ctx_stack

class TestCase:
    # set a Flask app config value using a pytest mark
    def test_foo(self, client, session):
        # set user identity in app context = {'sub': 'user1', 'tid': 'expected-audience'}

        # mock a class
        mocked_batch_client = mocker.patch('backend_class.BackendClient')
        assert(mocked_batch_client.return_value.list.return_value = ['a', 'b'])

        # test a view - it uses BackendClient (mocked now)
        resp = client.get('/api/items')
        data = json.loads(
        assert(len(data['results']) > 0)

        # insert data into the database - will get rolled back after test completion
        item = YourModel() = "bar"

Routing packets from a specific Docker container through a specific outgoing interface

My home server is multi-homed (multiple outgoing network interfaces) which a lot of the times is more trouble than it's worth... This time around I had a need to route a specific Docker container's traffic through a non-default outgoing interface (i.e. an OpenVPN interface to access secured resources). Below I'll show you how I made that happen.

Primer on multi-homed networking

Controlling incoming connections is generally easier than outgoing. Listening connections can be setup on all interfaces or on a specific IP, which will bind it to a specific incoming interface.

Outgoing connections, on the other hand, are a routing decision performed by the kernel. So regardless of the incoming connection, data flows out (generally) through the default route.

Policy-based routing customizes these routing decisions so that the route -- that is, the outgoing interface -- can be determined by a set of rules like source IP or marked packet by IP tables.

A bit about Docker networking

Docker is a marvel of technology but at times feels very user-hostile due to its rigidity - it makes a lot of assumptions and doesn't often communicate them well in documentation.

So, to the point: Docker supports adding multiple network interfaces to containers, great! I can have my specific container continue to join the default Docker network and talk to my other containers, and create a new network specifically for this container that maps to my target outgoing interface on the host.

However, the user-hostility: Docker doesn't let you customize which network is the container's default route. Normally I wouldn't care and we'd just use policy-based routing to fix that, but remember how it's the kernel's routing decision? Well containers don't have their own kernel. Docker is all NAT magic under the hood, and the actual routing decision is done on the host.

Turns out, you can influence the default route in a container... It's just that Docker uses the first network added to a container as the default, and from testing it appears to also add networks to containers alphabetically. OK then.

Putting it all together

Our recipe will leverage three key components:
1. A custom Docker network named such that Docker adds it to the container first, making it the default route
2. An IP tables rule to mark packets coming out of that Docker network
3. Policy-based routing on the host to route marked packets through the non-default interface

Here we go:

# create a new Docker-managed, bridged connection
# 'avpn' because docker chooses the default route alphabetically
docker network create --subnet=$DOCKER_SUBNET -d bridge -o avpn

# mark packets from the docker_vpn interface during prerouting, to destine them for non-default routing decisions
# 0x25 is arbitrary, any hex (int mask) should work
firewall-cmd --permanent --direct --add-rule ipv4 mangle PREROUTING 0  -i docker_vpn ! -d $DOCKER_SUBNET -j MARK --set-mark 0x25

# alternatively, for regular iptables:
#iptables -t mangle -I PREROUTING 0  -i docker_vpn ! -d $DOCKER_SUBNET -j MARK --set-mark 0x25`

# create new routing table - 100 is arbitrary, any integer 1-252
echo "100 vpn" >> /etc/iproute2/rt_tables

# configure rules for when to route packets using the new table
ip rule add from all fwmark 0x25 lookup vpn

# setup a different default route on the new routing table
# this route can differ from the normal routing table's default route
ip route add default via dev tun0 

# connect the docker_vpn
docker network connect docker_vpn mycontainer

That's it! You should now observe that outgoing traffic from mycontainer going through tun0 with gateway To get this all setup programmatically on boot, I recommend looking into docker-compose to automatically attach the docker_vpn network and create files route-docker_vpn and rule-docker_vpn in /etc/sysconfig/network-scripts to configure the routing rules.

Note that you may need an add-on package for NetworkManager to trigger the network-scripts - on Fedora, it's called NetworkManager-dispatcher-routing-rules.