The Schemaverse Dev Blog

The Schemaverse was hacked!

Well, that didn’t take long :) I wanted to leave the public database unpatched for a couple days to see how long it would take somebody to compromise it after the announcement of a serious vulnerability. About 24 hours after the release, this little file showed up in my /data directory. 

-bash-4.1$ ls -l
total 108
drwx------. 8 postgres postgres  4096 Oct  9 14:08 base
drwx------. 2 postgres postgres  4096 Apr  6 17:30 global
drwx------. 2 postgres postgres  4096 Apr  5 16:25 pg_clog
-rw-------. 1 postgres postgres  3982 Oct 29 11:10 pg_hba.conf
-rw-------. 1 postgres postgres  1636 Oct  9 10:10 pg_ident.conf
drwx------. 2 postgres postgres  4096 Oct 15 00:00 pg_log
drwx------. 4 postgres postgres  4096 Oct  9 10:10 pg_multixact
drwx------. 2 postgres postgres  4096 Apr  6 17:30 pg_notify
drwx------. 2 postgres postgres  4096 Oct  9 10:10 pg_serial
drwx------. 2 postgres postgres  4096 Oct  9 10:10 pg_snapshots
drwx------. 2 postgres postgres  4096 Apr  6 17:30 pg_stat_tmp
drwx------. 2 postgres postgres  4096 Apr  6 15:50 pg_subtrans
drwx------. 2 postgres postgres  4096 Oct  9 10:10 pg_tblspc
drwx------. 2 postgres postgres  4096 Oct  9 10:10 pg_twophase
-rw-------. 1 postgres postgres     4 Oct  9 10:10 PG_VERSION
drwx------. 3 postgres postgres  4096 Apr  6 17:26 pg_xlog
-rw-------. 1 postgres postgres 19660 Apr  4 08:59 postgresql.conf
-rw-------. 1 postgres postgres    71 Apr  4 08:59 postmaster.opts
-rw-------. 1 postgres postgres    72 Apr  4 08:59 postmaster.pid
-rw-------. 1 postgres postgres   535 Apr  5 05:33 SECURITY_RISK_PLEASE_UPGRADE_TO_9.2.4_NOW
-bash-4.1$ cat SECURITY_RISK_PLEASE_UPGRADE_TO_9.2.4_NOW 
?otFATAL:  no pg_hba.conf entry for host "***", user "***", database "-rSECURITY_RISK_PLEASE_UPGRADE_TO_9.2.4_NOW", SSL on
DETAIL:  Client IP address resolved to "c-***", forward lookup not checked.
?otFATAL:  no pg_hba.conf entry for host "***", user "***", database "-rSECURITY_RISK_PLEASE_UPGRADE_TO_9.2.4_NOW", SSL off
DETAIL:  Client IP address resolved to "c-***", forward lookup not checked.
-bash-4.1$ 

This kind ‘attacker’ could have easily destroyed the entire database. Instead, they just wrote me a nice note on my file system.

Now, as a good security professional, I get to rebuild a server. 

Please, please, please take this as a warning for anybody else currently running anything less than PostgreSQL 9.2.4, 9.1.9, 9.0.13 or 8.4.17. Vulnerability CVE-2013-1899 is serious and needs to be addressed immediately. Hiding behind a firewall will make this far less of a threat but please don’t rely on that, just patch your damn box!

For more details on the security release that resolves this issue, follow these links and get your servers patched!

http://www.postgresql.org/about/news/1456/

http://www.postgresql.org/support/security/faq/2013-04-04/

http://www.postgresql.org/download/

Playing with PostgreSQL 9.3

This version is still under heavy development but there are some really great features making their way in that I couldn’t wait to try. 

Here is a view I created demonstrating two new feature points including better JSON support and the LATERAL keyword.

 
CREATE OR REPLACE VIEW game_variables AS 
SELECT 
    json_get_as_text(rules->'vars'->generate_series, 'name') as name, 
    json_get_as_text(rules->'vars'->generate_series, 'private')::boolean as private, 
    json_get_as_text(rules->'vars'->generate_series, 'val')::numeric as val, 
    json_get_as_text(rules->'vars'->generate_series, 'description') as description 
FROM
    galaxy.game,
	LATERAL generate_series(0, json_array_length(rules->'vars') - 1)
WHERE 
	game.schema_name = current_schema(); 	
  
  

The ‘rules’ column is currently storing something like this:

   
{
    "vars":
	[
		{
			"name":"MINE_BASE_FUEL",
			"private":"false",
			"val":"15",
			"description":""
		},
		{
			"name":"UNIVERSE_CREATOR",
			"private":"true",
			"val":"9702000",
			"description":"The answer which creates the universe"
		},
		{
			"name":"EXPLODED",
			"private":"false",
			"val":"3",
			"description":"Tics taken for explosion"
		},
		{
			...
		}
	],		
	"round_end":{"type":"time","length":"1 day"},
	"map":{"type":"random", "size":"relative", "quantity":"1.05"},
	"tic":{"sleep":"60"}
}
    

The JSON support in 9.3 brings new functions, like json_get_as_text() used above, and also brings an in-line way of traversing through the tree where, as an example, json_column->vars->0->name would return “MINE_BASE_FUEL”.

This JSON support, when used in conjunction with a generate_series() call, and a join, allowed me to convert any JSON array of objects into a great looking view. The real key here was the second feature I mentioned, the LATERAL keyword.

Normally, using a column from one table, as part of the FROM clause would cause an error like so: ERROR: function expression in FROM cannot refer to other relations of same query level

With the new LATERAL ability, this is a thing of the past. In the VIEW example above, we use the size of the array, found by json_array_length on our rules column, and pipe that value right into the generate_series() function so limit the rows being returned by that number.

Minor note on the LATERAL command, you will not actually need to even specify this in the syntax. The magical PostgreSQL planner will figure it out on it’s own and do it regardless. I have just added it to show where said magic is taking place.  

Edit: The JSON feature used above is still under development and may not be in the 9.3 codebase yet. The code branch I am currently using that does have it can be found here https://bitbucket.org/adunstan/pgdevel/commits/branch/jsonapi

DEFCON 20 Tournament Results

DEFCON 20 Tournament Results

The DEFCON 20 Schemaverse Tournament

Now the second year in a row, we are pretty excited to be bringing The Schemaverse Tournament back to Las Vegas this year for DEFCON 20. We learned a lot running the contest last year and we have put those lessons to good use to make this year’s competition even better.

The Schemaverse is, and always will be, a game for database geeks. The core of the game is proudly a PostgreSQL database server which all players have an account on. This year though, we realize that database geeks should not be the only DEFCON attendees that we recommend play. Any developers, programmers and scripters who want to try their hands at this space battle game are welcome to come join in on the fun.

One great example of how a player can get involved, while still staying out of the database, is a Ruby bot that was developed by a player by the name of Sailias. He has posted the story on his blog and the code on github if you want to fork it and play around yourself.

The advantage to the Ruby bot is that it can even be deployed straight to Heroku as a free worker. So, if you don’t plan on bringing a laptop to DC20, you can prepare your bot now by playing on the public Schemaverse server and then point the bot at the DEFCON database once the tournament starts. Now you are competing and not even missing a step at the con! 

New to The Schemaverse? There is still more than enough time to start getting yourself prepared to compete in the competition! There are lots of resources available to help get you started:

• The Schemaverse Tutorial
• GettingStarted.sql
• The Schemaverse Ruby Bot
• #Schemaverse on irc.freenode.net 

Registration
Where: Contest Area
When: All day, every day while the Contest Area is open

Prize: First 100 registrants will “win” a sweet DC20 Schemaverse button

There will be no deadline for entering the competition but realistically speaking you should be signed up by Friday morning if you are playing to win. 


Pre-tournament Round
Starts: Thursday at Noon
Ends: Friday at 11:30AM

Prize: Bragging Rights?

You will want to use this round to get all your scripts loaded and configured for the tournament. This way it is all ready to go when the real round starts up. 


Actual Tournament
Starts: Friday at Noon
Ends: Sunday 11:00AM

Prizes: 

Schema Supremacy: DC20 Schemaverse Championship hoodie
Get the highest final score and you can win an awesome looking hoodie :D

Best Hack: 1 License of Navicat for PostgreSQL
This one is completely judged by me. Keep in mind, if I don’t know about your hack then you can’t win the prize! I will keep the details to myself so any advantage is kept but you need to come gloat to me about it.


Game Settings
As anybody who has played the game tends to know, some of the settings change pretty frequently while we try to balance it out. For the tournament, I will likely keep the settings as they currently are in the public database. These settings are as follows:

Map Size=~3000 Planets

MAX_SHIP_SKILL=500
MAX_SHIP_HEALTH=1000
MAX_SHIP_RANGE=5000
MAX_SHIP_FUEL=200000
MAX_SHIP_SPEED=800000

MAX_SHIPS=2000

EXPLODED=3

If you don’t know what these settings are, you can do a select * from public_variable; to see an explanation of each (or run over to the tutorial:https://schemaverse.com/tutorial/tutorial.php ).


Our Contest Booth
Last year our Contest Area booth ended up being a great place to sit around and hack at the game and I am definitely looking forward to this happening again. I have requested a couple more chairs and maybe even an extra table but this may depend on space available in the contest area. 

Regardless though, please feel free to come by to learn more about the game, submit bugs, social engineer some strategy or just drink a beer with us. 

Of course, we will also have the visualizer up showing off the the leader board, the Schemaverse overall map and close-ups of battles taking place. So far this thing looks awesome :D

Sponsors
Our sponsor, Navicat, deserves a big thank you from us. They have been very kind in giving us licenses to use as prizes for both our DEFCON tournaments and our PGCon tournament. If you are in the market for some database software (regardless of the flavour), make sure to check them out! 

Questions and Comments
If you want anything cleared up, please feel free to contact me over on the twitter: @schemaverse

Optimizing my_ships, again.

So far the best part of this blog has been having people read through my steps and logic and further tell me how wrong I am. While this sounds super sarcastic (and coming from me it normally would be), the results speak for themselves. 

Last post I discussed how I used the WITH command to optimize some really painfully long running queries. While the results were great, I had somebody point out that this method takes away planners ability to decide how best to run the query. 

In the case of my_ships, the planner wasn’t having a great time trying to figure out how to best utilize any indexes that were available which is why the WITH method helped out a lot. Instead of letting the planner figure out the best route, we chose a pretty good way using WITH, separating pieces out to increase speed and then glueing them back together. 

Our query plan was certainly a lot better then the planner was deciding on but that didn’t mean it was the best option. It turns out that the planner doing an awful job of optimizing my_ships was just a symptom of another problem elsewhere in the Schemaverse.

First culprit:
CREATE OR REPLACE FUNCTION GET_PLAYER_ID(check_username name) RETURNS integer AS $get_player_id$

$get_player_id$ LANGUAGE plpgsql SECURITY DEFINER; 

The code above always seemed innocent enough but there were two ways to help speed it, and everything that calls it, pretty drastically. 

CREATE OR REPLACE FUNCTION GET_PLAYER_ID(check_username name) RETURNS integer AS $get_player_id$
SELECT id FROM player WHERE username=$1;
$get_player_id$ LANGUAGE sql STABLE SECURITY DEFINER;

First, the languages was switched to SQL rather than pl/pgSQL. It isn’t a complex piece of code so this isn’t a huge change. The most important change was outside the function definition, changing it from a VOLATILE function to STABLE.

What the hell does that mean? The PostgreSQL manual gives a great explanation so I will just steal it (source: http://www.postgresql.org/docs/9.1/static/xfunc-volatility.html ):

• A VOLATILE function can do anything, including modifying the database. It can return different results on successive calls with the same arguments. The optimizer makes no assumptions about the behavior of such functions. A query using a volatile function will re-evaluate the function at every row where its value is needed.

• A STABLE function cannot modify the database and is guaranteed to return the same results given the same arguments for all rows within a single statement. This category allows the optimizer to optimize multiple calls of the function to a single call. In particular, it is safe to use an expression containing such a function in an index scan condition. (Since an index scan will evaluate the comparison value only once, not once at each row, it is not valid to use a VOLATILE function in an index scan condition.)

• An IMMUTABLE function cannot modify the database and is guaranteed to return the same results given the same arguments forever. This category allows the optimizer to pre-evaluate the function when a query calls it with constant arguments. For example, a query like SELECT … WHERE x = 2 + 2 can be simplified on sight to SELECT … WHERE x = 4, because the function underlying the integer addition operator is marked IMMUTABLE.

This is generally the trouble with learning something like this on your own.  This was one of those cases where you just need to be shown what you don’t know and the rest tends to fall nicely into place. 

Once this function was fixed, the planner suddenly knew it only needed to run the function once and then it could use the results to perform index scans. Yay the black magic of the PostgreSQL planner is suddenly back and performing wonders for all :D

-Abstrct

Optimizing my_ships

Generally, my passions are data and keeping data secure. Just as a lot of non InfoSec minded individuals tend to miss certain obvious security practices, I tend to make the mistake of missing basic chances for optimization when designing portions of The Schemaverse. This is going to be my first post where I start to take a look at finding spots in The Schemaverse that are running slow and it’s all my fault. 

Most of my PostgreSQL knowledge tends to be self taught, most specifically from online resources like The PostgreSQL official manual (which is incredible) and other random sources that happen to have the correct keywords and page rank to get them noticed. I know my blog certainly won’t replace any of these resources, but I hope it can add something new to the mix. That being the point of view of somebody who has no idea what they are doing. 

Today I will be looking at the ship related views, taking suggestions from @cbbrowne on where some optimization can likely take place and hopefully figuring it out. 

Starting with my_ships, let’s take a look at what the view currently looks like:

CREATE VIEW my_ships AS 

SELECT 

ship.id as id,

…(shortened to make this less obnoxious)

ship_control.action_target_id as action_target_id

FROM ship, ship_control 

WHERE

ship.player_id=GET_PLAYER_ID(SESSION_USER)

AND ship.id=ship_control.ship_id

AND destroyed=’f’;

In short what is happening here is there are two tables that are being queried, ship and ship_control. This is a one-to-one relationship where they meet on ship.id=ship_control.ship_id. This view is also then filtered down by two more predicates;

• ship.player_id must be the same as the user accessing the database; and
• ship.destroyed must be false.

When I wrote the above, I was really just very happy with myself that I was using the players database credentials to filter the actual ship tables down to only what the player should be seeing (i.e.: their own ships!). Again, I am a security guy so the data Confidentiality and Integrity was my initial goal. All was fine for a while when nobody was playing but then it quickly became pretty slow when you were dealing with about 30-40k ships.

As far as I really cared to know, the ship table had an index on id and the ship_control table had an index on ship_id (since these were both primary and foreign keys already). They are there so it must be using them right? Same with ship.player_id, it was a foreign key to player.id so that must be indexed too.

So why was it slow? Well @CBBrowne took the logical step of running explain on a SELECT * FROM my_ships:

“Hash Join  (cost=7750.54..8611.02 rows=1185 width=187)”
“  Hash Cond: (ship_control.ship_id = ship.id)”
“  ->  Seq Scan on ship_control  (cost=0.00..783.82 rows=17282 width=83)”
“  ->  Hash  (cost=7735.73..7735.73 rows=1185 width=108)”
”    ->  Seq Scan on ship  (cost=0.00..7735.73 rows=1185 width=108)”
”    Filter: ((NOT destroyed) AND (player_id =get_player_id(“session_user”())))”

I’m going to be honest, these things generally scared the crap out of me at first. No clue what any of the mystical hieroglyphics meant. It turns out though that if, instead of not reading it due to fright, you simply read it, it is pretty helpful (who knew?). 

So what is the above saying? It turns out that the most important part is actually what it isn’t saying. Where is the mention of the player_id index? Why isn’t the ship_control.ship_id index being used? The indexes that I knew existed hadn’t even been touched. 

While it seems too obvious now, at the time I just hadn’t thought about it really. PostgreSQL isn’t going to have any use the index if there is a bunch of other details it needs to check as well either as an additional AND or OR. Instead, its only option is to do a sequential scan through every row, comparing these WHERE conditions individually each time. 

But I really needed all of these details in my my_ships definition. Otherwise the game doesn’t work anymore. Luckily, PostgreSQL has a wonderful command called WITH. In a unfortunately short description, WITH is going to allow us to run a couple queries separately before beginning the actual SELECT to allow us to narrow down our results using the index and then join and filter it later on in the query. 

First off we need to figure out what and how we can structure these initial WITH queries so that they properly take advantage of the indexes they have.

1. 1) We want all ships that belong to the player, which means first we should know who the player is! We are putting this in a sub query so the function is ever only called once. 
   current_player AS (SELECT GET_PLAYER_ID(SESSION_USER) as player_id)
    
2. 2) We want all ships from the ship table where the player_id matches the results above:
   ships AS (SELECT ship.* FROM ship, current_player WHERE ship.player_id=current_player.player_id)
    
3. 3) Finally we want to also grab all the ship_control rows that belong to the same player. Since grabbing all the potential ship.id values to compare would require a sequential scan through the entire table, it made sense to add the player_id column to this table as well, allowing this to be possible:
   ship_controls AS (SELECT ship_control.* FROM ship_control, current_player WHERE ship_control.player_id=current_player.player_id) 

CREATE OR REPLACE VIEW my_ships AS 

WITH 

current_player AS (SELECT GET_PLAYER_ID(SESSION_USER) as player_id ),

ships AS (SELECT ship.* FROM ship, current_player WHERE ship.player_id=current_player.player_id), 

ship_controls AS (SELECT ship_control.* FROM ship_control, current_player WHERE ship_control.player_id=current_player.player_id)

SELECT 

ship.id as id,

…(shortened to make this less obnoxious)

ship_control.action_target_id as action_target_id

FROM ships INNER JOIN ship_controls ON ships.id=ship_controls.ship_id 

WHERE ships.destroyed =’f’

What does our EXPLAIN statement look like after these changes? Certainly looks way more terrifying. I am learning that the more terrifying your EXPLAIN statement looks, the better things are going. 

“Hash Join  (cost=2989.45..3173.12 rows=4412 width=292)”

“  Hash Cond: (ship_controls.ship_id = ships.id)”

“  CTE ships”

“    ->  Nested Loop  (cost=234.84..2713.84 rows=1329 width=113)”

“          CTE player”

“            ->  Result  (cost=0.00..0.26 rows=1 width=0)”

“          ->  CTE Scan on player  (cost=0.00..0.02 rows=1 width=4)”

“          ->  Bitmap Heap Scan on ship  (cost=234.58..2696.94 rows=1329 width=113)”

“                Recheck Cond: (player_id = player.player_id)”

”            ->  Bitmap Index Scan on ship_player  (cost=0.00..234.25 rows=1329 width=0)”

“                      Index Cond: (player_id = player.player_id)”

“  CTE ship_controls”

“    ->  Nested Loop  (cost=26.81..240.74 rows=1329 width=87)”

“          CTE player”

“            ->  Result  (cost=0.00..0.26 rows=1 width=0)”

“          ->  CTE Scan on player  (cost=0.00..0.02 rows=1 width=4)”

“          ->  Bitmap Heap Scan on ship_control  (cost=26.55..223.84 rows=1329 width=87)”

“                Recheck Cond: (player_id = player.player_id)”

“                ->  Bitmap Index Scan on fki_ship_control_player_id  (cost=0.00..26.22 rows=1329 width=0)”

“                      Index Cond: (player_id = player.player_id)”

“  ->  CTE Scan on ship_controls  (cost=0.00..26.58 rows=1329 width=176)”

“  ->  Hash  (cost=26.58..26.58 rows=664 width=120)”

“        ->  CTE Scan on ships  (cost=0.00..26.58 rows=664 width=120)”

“              Filter: (NOT destroyed)”

So, it’s certainly longer. Is that good? It would appear the answer is yes. A SELECT on my_ships no longer has to look over the entire 40k rows of the ship table, nor does it then have to look over the entire 40k rows of the ship_control table right after. Once it has both tables down to a manageable size via the player_id index, it then does the further filtering where destroyed=false on the much smaller virtual data set. 

Alright, a longer EXPLAIN statement is exciting but let’s look at the numbers that matter. Execution time. 

Original my_ships view: 13223ms
New my_ships view: 49ms

D:

This increase makes me feel a tad embarrassed in all honesty. It also has me excited though! If I can figure this out in a night, what else can be sped up using the same basic changes. Turns out, quite a few other tables. Here are the results of the other optimization I did, following the same steps for each.

select * from ships_in_range
Original ships_in_range view: 27090ms
New ships_in_range view: 152ms

select * from planets_in_range
Original planets_in_range view: 15347ms
New planets_in_range view: 78ms

Fantastic improvements on each one. I’m sure I am just scratching the surface on how to optimize these queries but this is still al learning process for me and results like this are encouraging to say the least. 

Turns out that PostgreSQL is just a tad less about magic and slightly more about knowing how to use it. My bad. 

If you found anything helpful here but still a little short on facts, here is some addition reading that is hopefully going to fill in the gaps I have left:

WITH Queries: http://www.postgresql.org/docs/9.1/static/queries-with.html
EXPLAIN: http://www.postgresql.org/docs/9.1/static/sql-explain.html

Next time I will cover taking some similar steps to optimize my_events. If anything here is wrong or if you can think of better ways to improve what has been done, let me know in the google group (https://groups.google.com/forum/?fromgroups#!forum/schemaverse) or in the GitHub repo (https://github.com/Abstrct/Schemaverse)

Security Barrier Views (without waiting for 9.2)

While making my rounds through the Internet today I came across a great article by Robert Haas on the topic of Security Barrier Views. Robert pointed out that simply by using a quick boolean function, one can use it to leak data from views that are normally used for row-level security. As you can imagine, this is a fairly serious topic for the Schemaverse as being able to leak data on other players and their scripts, ships and events can certainly help you gain a serious advantage. 

You can take a look at Robert’s article for his own example of this but I will show what is happening in terms of The Schemaverse. 

First off a player logs in and creates a new function (this part is care of Robert)

CREATE OR REPLACE FUNCTION pg_temp.leak(text) returns bool as
$$begin
raise notice ‘%’, $1; return true; 
end$$ language plpgsql cost 0.0000000000000001; 

This function will take whatever is passed to it and return it as a notice along with the query results.

A player using this function along with, say, the my_fleets view would run the following query:

select * from my_fleets where pg_temp.leak(script);

and they would receive:

NOTICE:<other players script>
NOTICE:<other players script>
…
NOTICE:<other players script> 

fleet_id | player_id | script | script_declarations
1 | 1 | <players own script> | <etc>

The view still works as expected, only listing the players own fleets but all the scripts have also been sent one-by-one. 

For reference, here is the my_fleets view definition:

CREATE OR REPLACE VIEW my_fleets AS 

 SELECT fleet.id, fleet.name, fleet.script, fleet.script_declarations, fleet.last_script_update_tic, fleet.enabled, fleet.runtime

   FROM fleet

  WHERE fleet.player_id = get_player_id(“session_user”());

Given that row-level security using views is how The Schemaverse works, seeing this work was pretty depressing but also yet another fun challenge to try and overcome. My initial thoughts where to remove the ability for players to create temporary functions. This is apparently not possible (please correct me if this is the case).

Then I had another idea though: Limit the results before the player’s where clauses is applied.

Keeping the fleet scripts secure tends to be my biggest priority as this is where all the players efforts go to. For that reason, I tried to tackle securing my_fleets against this attack first.

In PostgreSQL you can create functions that return result sets so first I created a new type that my function will reference named my_fleets_type.

create type my_fleets_type as
(
id integer,
name character varying,
script text,
script_declarations text, 
last_script_update integer,
enabled boolean,
runtime interval
);

 I then moved the my_fleeets view query into a function my_fleets_security():

CREATE OR REPLACE FUNCTION my_fleets_security() RETURNS setof my_fleets_type as $my_fleets_security$
DECLARE
r record;
BEGIN
FOR r IN SELECT fleet.id, fleet.name, fleet.script, fleet.script_declarations, fleet.last_script_update_tic, fleet.enabled,fleet.runtime
FROM fleet
WHERE fleet.player_id = get_player_id(“session_user”())
LOOP
RETURN next r;
END LOOP;
END
$my_fleets_security$ language ‘plpgsql’ SECURITY DEFINER; 

Finally, to finish everything up, I changed the my_fleets view to call the function rather then the query:

CREATE OR REPLACE VIEW my_fleets AS SELECT * FROM my_fleets_security();

Done and done. If you try the same trick on this, you only get your own scripts noticed back since the player’s view where-clause is being performed on the result set returned by the function. 

This change is fairly time consuming to pull off so this round is fair game for playing around with the bug. Your fleet scripts are safe (for now) but some spies may know you are coming during your big attack. 

Also, potentially welcome to The Schemaverse’s dev blog. I know there is a lot of places to discuss this game already but I wanted one central point where I can make announcements that are larger than 140 characters like this one. Please keep using the group and github for discussion though.