The #PGHACK championship. The platform

This coming Saturday, the Avito Moscow office will host a PostgreSQL championship. It’s a database administration competition built around hands-on tasks. We built it on a platform assembled entirely from open source components. We’ve already tested it in two internal competitions (turned out to be interesting), and now we’re getting ready to run #PGHACK for anyone eager to test their Postgres chops (we’re still accepting entries – link at the end of this post). In this post I’ll talk about how we prepared the platform for the championship.

A few words about the game

I already went into detail about the game in this post. Short version: it’s like a CTF, just about Postgres.

How does this work?

The platform is built from time-tested open source components. I assembled and debugged all of it myself, except for the OpenVPN setup. Let me go through the components in more detail.

VPN game network

We use OpenVPN to build the game network – a fairly standard solution for this kind of task. Players don’t need any extra software on their laptops, since they’ll connect to the network from virtual machines with the VPN preconfigured. Participants will get the game network key (and every subsequent task) from the task tracker. A checker verifies task completion.

Task tracker

A user logs into the game with the login and password they’ve been issued, and then sees their unique VPN key for setting up the virtual machine, plus a tracker with the list of tasks they’ll need to solve. During the championship, a leaderboard will go live, where you can watch other participants’ scores change.

Checker

The checker walks through players’ virtual machines and verifies the results – basically the same way it works at a CTF. Under the hood it runs on Fabric, which was originally built for running tasks in parallel across a bunch of servers or computers. We didn’t build a new component for this: Fabric guarantees a completely fair, genuinely parallel checking process – that’s just in its blood.

Virtual machines

The virtual machines that connect to the VPN have the database and the application installed on them. That’s where the tasks get solved. The virtual machines are built automatically with Packer from HashiCorp. Provisioning is automatic too, using Ansible, so we can rebuild a virtual machine as many times as we like. The preparation process proved this was the right call – we cut out routine operations entirely and tried to minimize changes and clarifications.

Preparing the tasks

Some tasks were nailed down right away, others changed during discussion and test runs. We set up a table in Confluence where anyone could add their own task and describe its solution. It was important to come up with tasks that had unambiguous solutions. Checking had to be fast, simple, and automatic: otherwise checking 50-60 participants in parallel (the system was sized for that many players) would have been a problem. Some tasks we even simplified. For others, we dropped uncommon solutions in favor of standard methods.

Initially we planned to allot as much time as a proper honest CTF gets – around 8 hours. But we realized that was quite a lot. In the end, #PGHACK will run for about 4 hours, not counting the time spent setting up the environment. Practice has shown that a Postgres grandmaster can solve all the tasks in 2 hours.

Preparing the game application

The first version of the mini-Avito site was built over the New Year holidays on Flask. It has nothing in common with the technologies used on the real site, but it tries to resemble it – it’s a classifieds board too.

Along the way we ripped the ORM out of the application completely, so it now calls database functions directly. We took the game dataset from analyst colleagues who’d run their own contest a couple of months earlier. It’s “live” listings with all personal data fully scrubbed. It weighs in at around 4 GB, so we can’t run big, complex queries on the virtual machines. Task-solution checks are built to be light and fast.

Mistakes we made

At the first stage of preparation we planned to be trendy: build the platform out of microservices in different languages – Go, Python, PHP. But we backed off from that idea: it would have been hard for participants to make sense of a pile of confusing microservices, and the game would have turned into poking around in microservices – who goes where, who does what – instead of fixing Postgres.

There was another trendy idea – use Docker. We dropped it once we realized most DBAs weren’t ready to work with it: they work with different systems, on a different plane entirely. We settled on the classic virtual machine, like most CTFs do.

Once we ran it for real, we discovered the checker needed to be server-side. Originally it connected to the game virtual machine over SSH and ran commands there. During our first test game, we found that some of our guys with solid Linux knowledge were intercepting these commands and could trick the checker. So it became server-side, and most of the checks moved there.

Open source components of the PGHACK platform

Packer
Ansible
VirtualBox
OpenVPN
Fabric
Flask

And, of course, PostgreSQL!

What’s next

We don’t plan to stop at running just one #PGHACK championship. We want this to become a regular event for the Postgres community. So there won’t be a detailed breakdown of the tasks.

After the event, a photo recap will go up on our Facebook page. By the way, registration for the championship is still open: fill out the application by the evening of August 31 and join #PGHACK!