 Well, thank you for coming to my talk. Talk today is one Space Elephant's Attack, and it's about a new game that I'm bringing, a new contest that I'm bringing to DEF CON today, this weekend, which is called the Schemaverse, and it's just basically a game within a database. So, first quickly, who am I? I'm an expert in designing video game UI. I'm specialized in computer-human interaction. I'm a graphic designer, and I'm also a very skilled technical writer. Except I'm actually none of those things whatsoever. I made a game in a database. That wouldn't make any sense. So, who am I really? Just a database geek. So, Schemaverse 101. It's a space battle game played entirely within a PostgreSQL database. There's actually no UI whatsoever, just glorious SQL queries constantly. The game itself is a tick-based game, so basically everyone has a chance to do one move, and then the tick finishes, some things are calculated, and then the next tick goes on. The database itself is completely open to the internet. Everyone who gets the address for it will be able to connect, so long as you come to me first at the contest booth to sign up. Security is implemented within the database itself. There's also a couple of dirty little pro scripts here and there, but pro's always dirty. Surprisingly, this game is actually a pretty good time, so I hope a couple people come, sign up and give it a try. The game itself does also play after the con, there's just a constant running server you can join in. So, why did I do this? Great question. Every geek needs a project, this just happens to be mine. I'm sure everyone can respect that. I also wanted to be educated myself on database security, learn a lot more, and learn about even just optimization and non-security elements. And I wanted to do the same and educate others on the matter. I also wanted to push database systems a little bit further. When I started creating this system, a lot of people told me, well, I asked the internet because the internet is all knowing. I said, what would you do to secure an open database on the internet? The only answer I got was don't. Well, why? What's wrong with that? It's just another application. What's missing from this system that would make this a bad idea? So, this is what I wanted to find out. And I might find out this weekend. And finally, it's also pretty funny. It's a game in a database, come on. So, why bring it to DEF CON? Clearly, the last DEF CON that I've been at have just been too relaxing. Just drinking and having a good time. I felt I needed a little bit more stressful here. The well still wasn't enough to break here, especially for the database geeks. So, I've brought this here in hopes that you have a good time. So, getting into the actual gameplay. As I've mentioned, it's a tick-based game. Everyone goes, finished, new tick starts up. Basically, you command an army of ships who go out, conquer planets, and fight each other and blow things up. Sounds pretty good. So, in the game, there's a bunch of different goals you can actually have. You can be a space jerk, running around, just blowing people up, not really actually playing. You can be the emperor, just going and conquering every planet that you can find. You can be the planet pillager, and just rate the crap out of all the land's resources that you can find, using those resources to then build bigger and better things. Or, size matters, build the biggest and best ships constantly, and you'll also win. But of course, there's one overall goal, which is to do all of those things and become the schema supremacy. So, how do you actually play this game? As I mentioned, you come to me, or anyone who's at the contest booth, and you ask to register. When you register, you get inserted into the player table, and then you get an actual user account on the database. Yes, you are a user on the database. You also get two things. You get a row in the player's table, and you get a row, or you get a planet assigned to you as your home planet. So, you can see when you start out in the player table, you got some money there. You got, it's like 10 million, I don't really remember. And then you got a fuel reserve to get you started to. You also have something called an air channel, which I'll get to later on. And finally, on your planet, you can see your planet has a location, has a name, which it's your planet. You can update the name if you'd like. It also has a mine limit. That limit is only that number of ships can actually mine that planet per tick. I'm gonna get into that a little bit more later. And finally, the conqueror ID, which is you. Great work. So the planet you get off the beginning is somewhere off the far center of the universe. Center of the universe is where most of the battle will happen. So you get this planet off nowhere, and it's kind of your own little home that no one's likely to find. So now you've got your player, you've got a planet. Great, what's next? Well, I said it's a space battle game, so you need some ships. So I wasn't kidding when I said this is a sequel game. So what you wanna do to create some ships, which will cost you $1,000, virtual dollars, is insert into your ships table. Now there's a couple ways you can do that. It's fairly dynamic. You can either just give it a name and it'll be created at the center of the universe. You can give it some skill. Every ship gets 20 skill to start with and you can kind of disperse that however you want. You can upgrade those more later on, but it's good just to have that initial go. And you can also specify the location of where you wanna build. Now there's a little bit of extra rules there. Basically if you're building within the center, 3,000 in every direction from zero to zero, you can build wherever you want within that. That's fine. Outside of that range, you can only build on planets that you've already conquered. So you've got your home planet where no one can get to it. You go there, mine some resources, and then hit center earth for the actual battle. So what can ships actually do? Each of your ships have the ability to do three different actions. They can only do one of those actions per tick. So you have to decide what it's doing. When performing that action, both objects have to be within range of each other. Range is just another skill that your ship has. So what's the first action? Attacking, of course. So again, this is a sequel game. So there's a ships in range view to find all your enemy ships because you can't normally see them. But if they're within range of your ships, then you can. So using the attack function, you can actually attack from one of your ships to an enemy ship that is within range of you. Makes sense? Actual attacking uses your attack skill and their defense skill plus a little bit of luck and then calculates that to your future health that doesn't get committed until the end of the tick. Of course, if there's attacking, there's also repair. Repair is the same thing. You can actually repair anything within range of you. Repairing uses the engineering skill. No luck is really involved in that one, it's just engineering. And finally, there's mining. Mining is how you get all the resources in the game. Mining is how you continue on and build up your fleets and improve the. So when you mine a planet, nothing happens instantly. You get added to a table called, don't remember. Anyways, it's added to a mining table where it's just temporary for that tick that says this ship is mining this planet. At the end of the tick, all ships that tried to mine each planet try and do so. Now keep in mind there's that mine limit. So only a certain number of ships can mine a planet per tick. If you mine a planet more than anyone else, that tick, you now conquer that planet. Oh, and you also get the resources. You don't have to conquer it to get the resources though. So obviously ships can also move around. And this is the most finicky part of the game. It's the thing that people complain of the most about. But you can write a better move function in SQL. The game is open source. Please do so. So there's really two ways you can use the move function. And keep in mind, this is a 2D game. A 3D game in a database would just be weird. So the first way you can do it is actually give a speed and then a destination. If you give a speed and a destination, your ship must have enough fuel to start and then slow down. If you want to start or if you want to speed up to 100, then it'll take 100 fuel. When you get to your destination, it's going to take another 100 fuel to slow down. You also require fuel for changing direction while moving. If you don't have enough fuel to slow down, but you have enough fuel to speed up, you're not going to be able to use that first methodology. It's going to stop you and say, this isn't going to work because I can't stop you and you have a destination. But it will at least return back the direction that it was trying to go to your air channel, which I'll get to later on. So the second way you can do it, if you don't have that much fuel and you don't actually care about stopping anywhere, is just specifying the speed and then the direction and then just null and null for the actual location. Your ship is just going to go off into wherever. Doesn't really matter. You can refuel it later and stop it when you feel like it. So that's pretty much moving around. It's not too hard, but for some reason it is difficult. Nice little graph there on how moving actually goes as far as the direction. Not too complicated. So you got these awesome ships, but you want them to be better. You want that size matters award maybe. Well, there are a lot of upgrades you can do. You can upgrade the health, the fuel, the speed, range, range is a good one. Attack, defense, engineering and prospecting. Prospecting is your mining, should have mentioned that. Every skill does have a limit that you can upgrade it to. Attack, defense, engineering and prospecting have a limit of 500 total. They can't surpass that. Everything else has its own limit. It's right there, it's also on the CD if you want to reference it. So just kind of a recap of what's going on in the game. What happens in a tick? Every ship can move once in the tick. The amount it moves is based on its speed. Every ship performs a single action or they might do nothing, that's really up to you. After that, the perform mining function takes action and actually calculates who is successful in mining that planet, gives out resources and awards new planet conqueror to anyone who's successfully done it. Planets will run out of fuel. So if that happens, some planets randomly will receive more fuel after each tick. Afterwards, damage and repair is committed. As I mentioned, attacking and repair happens to the future health. Future health becomes current health, pretty simple. Any ship that has been at zero health for 60 ticks is going to explode and you can no longer use that ship. All your upgrades are gone, but you do get a thousand virtual dollars of currency back so that way you can build new ships somewhere. And finally, tickundersourcesequence.nextval. Tick goes to the next, pretty standard. So general strategy, as I talked about earlier, basically you wanna go build on your home planet that you've been given, build up to your mine limit and just mine the crap out of that planet. That's gonna give you all the resources you need to then build some great ships for the center where everyone else is gonna converge and try and branch out to get all the other planets. You're gonna wanna make sure you actually defend any planet you conquer. If you conquer a new planet, build some new ships around it to attack anything and to defend. It's pretty much the game. So anything that's happening in the game does enter your myEvents view that you have. Most things that happen successfully, at least. Some events are public, some are private. You don't necessarily care when someone upgrades a ship or you don't necessarily should know when someone upgrades a ship. The events, myEvents view itself is designed to be read very programmatically because this is a game where you wanna be able to pull details and actually figure out what you're gonna do based on those details. But if you want to read it in a more human readable way of what's actually happening, if you just use the read underscore event function with the ID, it'll actually concatenate it into a nice little string that you can read. So there's a couple other little helper functions like that. Refuel ship, obviously is fairly helpful. Every time you mine a planet, that fuel goes to your player's fuel reserve and then you can give that fuel to your ship. Doesn't go directly to the ship that mined it. Convert resource is important because you mine fuel but you want money. You need money to build things. It's actually a one-to-one relationship where originally it had some crazy system where it would fluctuate but the only thing you ever do is convert fuel to money. You never convert money to fuel. So it was kinda tanked. Whoops. There's a couple other get character variable, get numeric variable. There's a variables table that holds like the maximums and things that you can upgrade to. So it's just an easy way to get that value. And get player ID and get player username. You're gonna use those a lot if you write some queries based on the events table or if you wanna figure out who someone is that's attacking you. Other key views that you might find interesting, I talked about ships in range. Again, you can't see other ships in your my ships table because it's not the your ships table, it's the my ships table. So if you use the ships in range, then you can actually see other ships around you. The my ships flight recorder is just kinda neat really. It's actually completely useless but if you wanted to graph out and see where all your ships went, then it's kinda fun. Finally there's also online players, obviously what it is. Current stats just gives how many planets have been conquered, how many ships total are in the system, how many players are online, how many players are in the system, et cetera, et cetera. So it just, how much money is in the system too. So it just kinda gives you a benchmark to see how you're doing. You can't really see very much about how anyone else is doing in the game but you can see the overall stats. You can kinda guess how well you're doing off of that. And then there's the public variable table again that I mentioned, which you can just use those functions to graph. So finally, fleet scripts. This is where the game actually gets super cool. At least in my opinion, I might have a weird sense of cool. Basically, a fleet script allows you to automate all the gameplay for you because I love SQL. If you're sitting in this room, you're either my friends or you like SQL as well, but you don't wanna be writing SQL for the next 72 hours to win a contest because we're at DEF CON. DEF CON is a little bit more fun than doing that constantly. So how do you actually do this and how does it work? Well, if you insert into your flat MyFleets table and give it a name, let's first start. If you update your MyFleets table with the script declarations column and the script column and then enable it, what's going to happen is it's going to take those sections there, which you can see for this one, I'm taking all of the ships within range and attacking the crap out of them. The system will take that data you just updated into those columns and then create a function out of it. So you can see this will be called fleet underscore script underscore nine because we updated fleet script ID equals nine. The declarations go under declare and the scripts go under begin. Just in case nothing does get returned, got a quick little return one at the end there. If you wanted to return some things, it makes you feel better, it's fine, do so. So now you've just got this function, which isn't really doing a lot. It makes it easier because now you don't have to rewrite the scripts every time, you just call them. But if you want that to happen automatically for you every tick, you can do that. The script that controls all the ticks is tick.pl. Tick.pl is just one of my dirty little pro scripts and it will actually connect as your user and run that function on your behalf every single tick. To do so, you just have to buy a minimum of one minute of processing time for it, which is 10 million. They're expensive because time is money. And you have to set it to enabled. The enabled is really just for your own little reference. So if you wanted something to stop, then you could do so. If your script runs past that one minute, everything's canceled, gets reverted back and you just lose what you did. So you have to keep it within that one minute or buy another minute onto it. To buy any time for it, you just use the upgrade function again. You pass it the fleet ID and you pass it fleet runtime as the code and then you can specify the quantity. So this would buy one more minute for my fleet nine. So obviously you don't necessarily see what's happening in the fleet because you're not running it. So if there's been any problems, you just notice I'm not getting any money. This kind of sucks. So I have actually made a way where you can see what's going on too. PostgreSQL has a notify system built into it and I utilize that to use that error channel that I threw in my players. Any errors that your script will have will be piped to that. So if you type listen and add your error channel, every time you run a SQL query, within the results payload, it's going to include any new errors that you've had in that queue. I also wrote a super insecure Python script which you shouldn't run from the DEF CON network because you will be on the wall of sheep. There's no SSL on it. If you want to download the script, it's on schemaverse.com. If you have some server off DEF CON that you feel more comfortable using, then it's fine to use it, it's not a big deal. But please don't use it here. Here's a little example of what it looks like. Dead simple, it's just, it's the schemaverse output streamer SOS and it'll just list anything that's going on. There's also a couple other things in the system that will notify here, not just fleet scripts, but anytime move fails or things like that, it just gives it here. I did have an error table at first, but after 10 million records in a couple of days, it didn't seem like a good idea to continue. So, this is DEF CON, security in this game. As I mentioned earlier, most people's response was don't. But hopefully, I think it might last. It lasted the first night, so I'm pretty happy about that. Architecture-wise in the game, as I said, you are users. So the players are users in the system. Then you've got the database and then you've got the two Perl scripts. And I didn't really talk about the one Perl script yet, but I'll get to it. And that's really all the system is. So, what was I worried about when writing this game? Well, query limits was the first one. All it takes is one person to run a couple queries of generate series one to infinity and then no one could ever have another query actually run on the system. So I needed to find a way to stop that. Obviously, authentication is a bit of an issue. Luckily, I've got super secure MD5 going on. That was the best I could do. SQL injections, obviously a problem, although admittedly at first I thought, well, a user can only do what they have the ability to do on the system. And I kind of forgot a key fact of there was another higher power user controlling some things. And finally, Postgres exploits and quirks. I don't really know what the next zero day is for Postgres. If it happens this weekend, I have things logging so I hope I find it. As I mentioned earlier, most communities were less than helpful on pretty much all of this. The only, I think the one response I got was use SSL. So someone connects securely to my box and then destroy it. So I was really happy about that. So the basics that I kind of went on basically as far as the actual gameplay goes, almost everything in the game is based off of player ID equals get player ID session user. Session user is just a system variable in Postgres that's who's connecting. So because you're a user and it connects to your MyPlayers table, that's how I figure out what you're allowed to do. And all the views, all the triggers, everything's based off that. Which gets me to the next thing. How do I control data? It's basically just as I would hope you would develop your regular application security within your database. I use roles to make sure that users should only do what users can do and can only see what they should be able to see. Triggers and functions control most of the data changes that users don't have direct access to update. Rules do the same, I should say. And then views just make sure you can only see what you're supposed to see. Again, you shouldn't be able to see where other players ships are. So that's just, I did enable SSL because I do want people to connect securely and destroy my box. Well, I'd rather they win, but I'd rather someone not find the username. And finally availability, which was those query limits that generate series. Postgres has a very, very helpful, helpful variable within the configuration called set query timeout or statement timeout. Perfect, I thought this is exactly what I needed. I know query should last longer than a minute was my original thought. So I set that up and then I played around and I found out that every single user who connects to Postgres has the ability to type set statement timeout equals zero. This is one of the things where I'm talking about pushing database systems a little bit. How is that acceptable? When you have something that you configure as a security measure and a user, any user can connect and run a statement to set it to infinity. Didn't make any sense? That's where ref.pl comes into play. It's just another Perl script that monitors all the processes running and will kill anything that surpassed its time limit. It's actually a lot more dynamic than the statement would have been because now I can let people buy more processing time if they actually need it. And make less, I don't like them. So here's a little fun example of SQL injections that I thought, again, oh, it's fine. They're a player, they can only do so many things. That was really dumb. So in that fleet creation script, where you take what you've updated the fleets table to be and then thrown it into the actual table itself, or then that gets taken out, put into the function. You see, script declaration gets put there and then the new script. I hope you can see the problem with this. Not only can someone privilege escalate their script, the function that they're creating to be security definer. Security definer in Postgres means the administrator is now running this basically or the person who created the function which only Schemaverse itself has the ability to do. Now your fleet script is going to run at Schemaverse every time. So I've got a nice little update player set balance equals zero. I may want to specify except me, but you get the gist of it. But not only that, after that, by inserting more nice code, I actually rewrite the move function as well. Because like, why not? I certainly have the ability so I can send every player backwards if I want or I can make the move function more convoluted. Great. So this was a bit of a problem. And I wasn't really sure how to fix it. I still don't know if I have fixed it, but I like my solution. I just added a secret. So let me go back to that quickly. Right underneath the initial create and replace, you have fleet under source script. It's just a quick string declaration to say this is where it starts, this is where it ends. To fix this, I just made that random. If you don't know how to end the script, then you can't forcefully stop it. The actual creation will just air out. Now I have had people with ideas come to me and say, well what if I find a way to just make it so that I throw an exception and then my exception does something. I have no idea if this works. If someone here wants to play around with it, again, it's DEF CON. It certainly can. So some final words on security. I hope that if someone does find a bug in this that can destroy it, instead of destroying it, they use it to win. Because if you can win this game through fairly unnecessarily good ways, it's DEF CON. Full respect to you. You win. I have no problem with that. So my only deterrent is the fact that this year at DEF CON, running the first ever Schemaverse tournament, I do have prizes. So at the end of it, if someone does drop database Schemaverse, then no one wins my prizes and they're actually pretty good. I have backups, but I don't... Once it goes down, I'm not sure if there's going to be time to fix it. I might be too drunk. But we'll see how it goes. So the tournament did start yesterday. Although in everyone's manual, I think it says the tournament starts tomorrow. But the tick actually, like the game can take a while to play. Usually it's played over a week. I've tried to tweak it a little bit to be a three day game. So here we are. It does end at Sunday at noon. Registration, if you want to come play and I really hope that some people here do, it's going to be in the contest area. Just come find us, register, and then you're good to go. I have no idea how many users this will support. I've played with probably about 15 different players and it's gone pretty good. I don't know if there's going to be more than that here. I don't know if there's going to be more power users. We'll see how it goes. At some point I may have to limit it if I see it starting to slow down because as I mentioned, the tick won't end until everyone's scripting is done and a tick will never start until that first one is finished. So if I get 100 players, then that's 140 minutes worth of processing time if everyone takes up their limit that they get off the beginning. So it can be a fairly long time which normally a tick would last one minute. So my prizes. To the person who gets first blood, the first person to attack in this game, PostgreSQL themselves have donated some nice swag. They've given me a t-shirt to give out and some posters commemorating the release of 9.1. Navicat has given me one license of Navicat PostgreSQL which is a pretty sweet piece of software for Mac, Windows, et cetera. They're not gonna be a salesman here. And I have one free admission to PostgreSQL West or PG West I should say. It's at least happening end of September 27th to 30th. I thought that was a great prize too. I realized that one's gonna be a little bit more location specific. Not everyone might have the ability to travel again in a month. That's why some of the prizes might be a little bit kind of decisive once we see some results. And I've also got an extra $50 American from an anonymous donor who will basically, whoever he sees fit in the game will get it. So those are my prizes. So finally, how do you actually connect to this game? However the hell you want. It's a database on the net. Anything that can connect to the database, be it any language you want, Python, Perl, PHP, Ruby, .NET, Java, TCL, I super don't care. Use it, it's fine. Any database client you feel comfortable with. I'm gonna say PG Admin might be your best bet because I know PG Admin has SSL. So I would suggest you use that. But if you're more comfortable with something else, I know NaviCat will automatically pipe it through some SSH for you. So that could be useful too. Again, we are at Defcon. A wall of sheep will be plentiful with schema versus accounts if we're not careful and I'd rather that not happen. So as you can see, under my host db.schemaverse.com was the super secret host name that you used to connect and it's just on a regular port of five, four, three, two. So if you want more information, I've got website defcon.schemaverse.com and then I've got some project homes that said it's open source. Now my talk has ended up pretty fast. I apologize for that. I thought it was gonna be too long from yesterday. So I'll kind of talk a little bit more about strategy and how to win if you actually want to. I do know some people who instead of using my tix or the actual fleet system that I've developed, instead of spending waste of money on that, they actually just write Python scripts instead to run everything for them every minute. Roughly the same thing. You don't necessarily know when the tic ends, but you can always pull the system and find out if there's a new tic. So it can be fairly creative without you play this game. Since I've got time, I'd love to answer questions if anyone has it. Yeah. Nearby planets, you can see every planet in the game using the planets table, lots of view. Oh, sorry. The question was how can you see planets within range? And originally there was a planets in range view and then I realized, well, we can kind of see most things. So I guess it makes sense that you can kind of see most things too. So basically any planet that you want to see is in the view and you can also see who's conquered it. So if you want to be a jerk and target one of your friends, then you can go find what he's got and go attack it. Other questions? Over there? Sorry? Yes. You can conquer another player's home system if it's within range. Now keep in mind, most of the planets were developed very close to the center and then as you get out of the kind of center area of the map, then it becomes more and more sparse. So your home planet, it might actually be impossible to find if you upgrade the ship speed completely, it might still be impossible to get to another planet. So it might be protected, but if you can find a sequel injection that gives you their home planet, then that could work too, right? Any other questions? More questions? I've been working on it for about a year and a half. It's 2,300, oh sorry, how long have I been working on it? 2,300 lines of sequel code at this point. Couple hundred lines of pearl. You can see my Git repository history, which is like activity down, activity down. Yeah, so it's been a fun pet project. It actually, no one knew about it for ages and then it was Merlin on PostgresQL on the mailing list, found it and he was like, hey, this is awesome, but it wasn't overly tested at that point, this is last December, which is great for testing because people basically just connected and just brutally destroyed it within a couple of days, which is great. Basically took it down, rewrote everything, most things at least. And now it's in a very more stable, actually playable time. It was basically beforehand, it was just, you've got some chips, you can go around and blow some things up, but now there's actually like gameplay to it. Other interesting things, if you wanted, you could actually add a goal in the game. The trophies table, the trophy table I should say, is something you can insert into. Every trophy that I showed earlier, you got Space Jerk, et cetera. Space Jerk is just the best one clearly. Pillage is pretty good. Anyways, any of those are just SQL statements again, just like the fleet system, except these ones have a little bit more control because they can actually go into all the tables. Your player can only see the views most of the time, but the trophies can see every table there is. So if you wanna make up a trophy, you can actually add it into the game. And if I allow it, because there's nothing ridiculous in the code, then it will become a trophy that will get calculated out at the end of the round. Pretty fun. There's also items in the game. I didn't really talk about it because I haven't had time to create any items. But just like the trophies and the fleets, it's just SQL code that you throw into a table and you're allowed to insert into the items table. So if you can insert into that table, create an item that you want, I'll see who made it, I'll see which user created this, and I might be nice and give you this item if I think it's really cool. Then I'm gonna scatter them around the universe and you can find them. So the game can be a little bit more complex, or you can take it how you wish. Any other questions? Good question. The question was, what happens after the game ends basically? Users stay, players stay. All ships are destroyed. Planets don't move. You get a new planet, though. Kind of change things up. All the fuel is redone on all the planets and all the events and everything is destroyed basically. Originally I wanted to just kind of be an ongoing game, but after playing it for a while, it didn't really make sense because at some point a new user just can't compete. So. The whole circle of planets in case you're a simple planet and people are now just going around the outside and carrying about the board. That would be kind of interesting in its own way. The question was, what happens at the end of a game when basically planets are re-given out, but you do lose your original home planet, which I didn't specify. Yeah. Question? All right, well, I guess I'm done early, but if you do have any further questions that you wanted a more intimate space. QA room four, I believe is my room. I'll be happy to answer any questions you have. Talk strategy, or if you wanna complain about move to me, that's fine too. I hope you come to the contest area and sign up for the game. I'd love for this to become successful and I'd love to bring it back to DEF CON 20. I think that would be fantastic. So, anyways, thank you for coming.