Schemaverse Championship Qualifier #1: PGCon 2013

The Schemaverse Championship will be held August 3rd at DEFCON 21 in Las Vegas, NV. This qualifier was just the first of many leading up to the championship.

We held our first qualifying round last week at PGCon 2013 in Ottawa, Ontario. This was a perfect place to start the qualifiers as PGCon sees many of the leading users and developers of PostgreSQL; meeting to discuss the past, present and future of the database we love.

We had about 15 people sign up to play in the tournament over the span of the conference and, even though they were mostly new players, they did pretty well. Anybody who has tried the Schemaverse understands that getting familiar with the game in a short period is no small feet so we really commend the effort the players put in to compete.

So what exactly was everyone competing for and who took home the gold?

Early Bird Winner

One license of Navicat for PostgreSQL - This software is the best commercial database DB interface and we were lucky enough to have another copy to give away.

This prize went to the player who was leading the pack at the end of the initial tutorial session.

Big congrats to Greg_d128 for winning the Early Bird! He was a tad shocked when his name was called but the system doesn’t lie ;)

Best Hack

The bought off zazzle official Schemaverse Best Hack Cup!

This stylish mug, which features the slick Schemaverse elephant, was awarded to the player who disregarded what little rules The Schemaverse attempted to enforce and played their version of things.

It was Foo who took home this prize. I won’t go into too much detail on his hack until the final championship is over but I will say that it involved the always fun trading system.

In the end, Foo’s hack got him that sweet mug. but it wasn’t enough to get him the title…

Schema Supremacy

The Schema Supremacy - PGCon 2013 Hoodie

One badge to DEFCON 21 in Las Vegas, NV

The Schema Supremacy is the leader of the pack, the player with the biggest, baddest fleet by the end and the most conquered space to call their home.

This year, at PGCon 2013, the title of Schema Supremacy was claimed by a Schemaverse veteran: funbuster. It was a very close battle between him and foo but, in this case, the legitimate player claimed the prize.

Side note: Unfortunately, funbuster will be unable to join us in Vegas and graciously donated his prize to an auction run by PGCon. The proceeds of which went to a local Ottawa women's shelter. We welcome him to play the Championship round remotely.



Full list of Qualifiers

These folks may not have won the titles or the prizes, but they have successfully qualified to compete at the championship. We look forward to seeing these familiar faces at DEFCON 21.

BUT WAIT!? Why was The Schemaverse down for a week after?

After everything was all said and done, and I was out of prizes to give out, I had another conference goer come up to me and suggest I take a closer look at the public SV database.

schemaverse=# select get_player_username(player_id), trophy.name from player_trophy inner join trophy on trophy.id=trophy_id where player_id=get_player_id('andres'); get_player_username | name ---------------------+--------------------- andres | SQL Injection Proof (1 row)

D:

The last person to get the SQL Injection trophy was two years ago and we suddenly had a new recipient award it to themselves!

I shouldn’t be surprised that a conference involving this many PostgreSQL folks had this happened but, in my defense, it was a really cool hack too!

So how did it happen? Andres was nice enough to point me in the right direction, informing me he used temporary schema elements (functions and views) along with the search_path. Since then I have been able to recreate the exact issue, and even implement a fix.

This is an absolutely key lesson in database security and you should be very happy that the Schemaverse got to experience it, instead of your own applications.

Let’s take a walk through the fun, starting off with one of many key functions within the Schemaverse. Any function that has the Security Definer declaration was prone to this problem, but we will look at get_player_id() as a simple example.

CREATE OR REPLACE FUNCTION get_player_username(check_player_id integer) RETURNS character varying AS $BODY$ SELECT username FROM player WHERE id=$1; $BODY$ LANGUAGE sql STABLE SECURITY DEFINER;

The above function is pretty simple, it just helps the Schemaverse hide the player table (which no player has direct access to), while still allowing players to access certain information about enemies in the game. Normally all other access to the player table would be done through the my_players view.

So what could possibly go wrong in one little line of actual code? Turns out, quite a bit. If you are unfamiliar with the hierarchy of a PostgreSQL database, it goes a little something like this:

Cluster

- - Database

- - Database2

- - - - Schema (get it.. Schemaverse. Ha!)

- - - - Schema2

- - - - - - - - Table

- - - - - - - - Table2

- - - - - - - - FunctionN

- - - - - - - - ViewN

Or, in the Schemaverse, it looks something like this:

Cluster

- - Schemaverse

- - - - $user – Users Temporary Schema

- - - - public – Where the Schemaverse lives

- - - - - - - - player (table)

- - - - - - - - my_players (view)

- - - - - - - - get_player_username(id)

Andres used his temporary schema, $user, to make a function and view, changing the hierarchy to look like this:

Cluster

- - Schemaverse

- - - - $user – Users Temporary Schema

- - - - - - - - player (view)

- - - - - - - - win_trophy()

- - - - public – Where the Schemaverse lives

- - - - - - - - player (table)

- - - - - - - - my_players (view)

- - - - - - - - get_player_username(id)

Where these new items were defined with code that likely looked similar to these:

CREATE FUNCTION pg_temp.win_trophy() RETURNS text AS $$ -- Trophy 12 is the SQL Injection Trophy -- Player 2 is my ID -- Round is given a value of 1 just to satisfy the constraint INSERT INTO player_trophy(round, trophy_id, player_id) values(1, 12, 2); select 'Owned'::text $$ language sql; CREATE TEMPORARY VIEW player as SELECT pg_temp.win_trophy() as username, 2 as id;

And the end result? When public.get_player_username(id) is called, the SQL query inside calls the player table, which should actually refer to the public.player table. Instead, when it is called, the users temporary space takes precedence, even inside the function!

This means that when public.get_player_username(id) is called, the code inside calls the $user.player view, which is then defined to call $user.win_trophy(). Finally, the kicker, this switch over to the user defined function is still all performed AS SECURITY DEFINER, in this case running as the omnipotent being known as schemaverse.

It is important to note that the order of importance for the schemas in a database can be set using the search_path but this value is client side and should not be trusted.



Some final words





The PGCon 2013 conference was a lot of fun and I am extremely grateful for the opportunity to bring the Schemaverse back for another tournament. All and all it was a much better start on the road to the championship than I could have ever imagined.

Of course, it couldn’t have happened without our sponsors. In no particular order, thank you to Navicat, The InfoBunker, PGExperts and PGCon themselves. You are all incredible for supporting our ridiculous game :)

Finally, thank you to those that played, discussed, visualized and broke the Schemaverse.

-Abstrct