Championship #PGHACK. Platform

Already this Saturday in the Moscow office Avito will be held the championship in PostgreSQL. This is a database management competition with practical tasks. We implemented it on a platform fully assembled from open-source components. We tested it already at two domestic competitions (it turned out interesting), and now we are preparing to hold #PGHACK for all postgreses who wish (we still accept applications for participation, a link at the end of the text). In this post I will talk about how we prepared the platform for the championship.

Briefly about the game

I talked in detail about the game in this post . In short, it's like CTF, just about Postgres.

How it works?

The platform is assembled from open-source components proven by time. I collected and debugged all this myself, except for the OpenVPN setup. I'll tell you about the components in more detail.

VPN game network

To build a gaming network, Open VPN is used - a fairly standard solution for such tasks. No additional software is required on players' laptops, since they will connect to the network from virtual machines with a pre-installed VPN. The key to the game network (and all the following tasks) participants will take in the task tracker. Task execution checker checker.

Task tracker

The user is authorized in the game with the login and password given to him, after that he sees his unique VPN key to configure the virtual machine and the tracker with the list of tasks that he will need to solve. During the championship will turn on the leaderboard, where you can watch the change in the number of points from other participants.

Checker

Checker walks through the players' virtual machines and checks the results - in principle, the same way as on CTF. Under the hood, he has a Fabric, which was originally designed for parallel execution of tasks on multiple servers / computers. We did not make a new component: Fabric provides the most honest and truly parallel verification process - this is in his blood.

Virtual machines

On the virtual machines that connect to the VPN, a database and application are installed. They solve problems. Virtual machines are automatically assembled using HashiCorp Packer. The process is also automatic. Ansible is used for this, so the virtual can be rebuilt any number of times. The correctness of this decision was confirmed by the preparation process - we completely excluded routine operations and tried to minimize changes and refinements.

Preparation of tasks

Some of the tasks were invented immediately, some changed during the discussion and test runs. We entered a label in Confluence to which we could add our task and describe its solution. It was important to come up with tasks with unambiguous solutions. The check should be quick, simple and automatic: otherwise, in parallel, checking 50-60 participants (the system was calculated for such a number of players) would be problematic. Some tasks even simplified. In some cases, they rejected rare decisions in favor of standard working methods.

Initially, we planned that we would lay down the time as much as the normal fair CTF — about 8 hours. But they realized that this is quite a lot. As a result, #PGHACK will last about 4 hours, not counting the time to set up the environment. As practice has shown, the postgres-grandmaster can solve all problems in 2 hours.

Preparing a game application

The first version of the mini-Avito site was made during the New Year holidays on Flask. It has nothing to do with the technologies that are used on a real site, but trying to be like it - this is also a bulletin board.

In the process of work, the ORM was completely cut out from the application, and it began to call database functions. We took the game dataset from our fellow analysts who held their competition a couple of months ago. These are “live” ads with completely disguised personal data. It weighs about 4 GB, so we can not perform large complex queries on virtual machines. Checks for solving problems are made easy and fast.

Mistakes made

At the first stage of preparation, we planned to be in trend: to assemble a platform from microservices in different languages ​​- Go, Python, PHP. But we refused this: it would have been difficult for the participants to sort out a large number of incomprehensible microservices, and the game would not have turned into a Postgres fix, but into picking up microservices - who goes where, what he does.

There was another trend idea - to use Docker. Refused, because understood that most DBAs are not ready for its use: they work with other systems and in other planes. Came to the classic virtualka, as in most CTF.

At the battle, they found out that the checker should be a server. Initially, he went through SSH to a gaming virtual machine and executed commands on its side. In the first test game, we found out that those of our guys who had a good knowledge base on Linux, caught these commands and could have tricked the checker. Therefore, it became a server, and most of the checks were transferred there.

List of open-source components of the platform PGHACK

Packer
Ansible
Virtualbox
Openvpn
Fabric
Flask

And, of course, PostgreSQL !

What then

We do not plan to stop only on holding one #PGHACK championship. I want this to be a regular event for the postgres community. Therefore, a detailed analysis of the tasks will not be.

After the event, a photo report will appear on our page in FB . By the way, registration for the championship is still open: fill out an application until the evening of August 31 and participate in #PGHACK!