PostgreSQL Exercises was made by Alisdair Owens. I noticed that while there was a lot of information out there on database systems, there didn't seem to be a lot of places to learn by doing in a structured manner. I hope this site helps people out.
Good question! Generally speaking, allowing raw SQL to run against your database is, of course, a big no-no. Historically, the site was purely read-only. This allowed us a lot of leeway, because (bugs in Postgres aside) you couldn't run any damaging SQL against the database. Other concerns were people running very expensive queries, changing settings, etc. These can be mitigated with the following security measures:
More recently, I've added some questions around data modification. This adds more complexity, as the user has to be able to alter the database! In order to secure this, for questions that require DML I've given the user access to alter tables under the 'cd' schema. The 'cd' schema gets deleted after every operation, and an admin user recreates it. To ensure reasonable behaviour when it comes to concurrency, there's a rotating set of databases underlying this, and each database can only be used by one user at a time.
This should at least secure the DB from casual attempts at taking it down. If you know of vulnerabilities in these measures, please email me.
These are harder to do safely on a site like this, as having a bunch of users concurrently modifying the same data just wouldn't work - quite apart from the security risks. At some point in the distant future I hope to make a similar site that takes you through DDL and CRUD operations against a database you set up yourself.
I've used the following technologies as part of this site. Many thanks to all of their contributors!
I also used the following tooling/sites:
And finally, thanks to user Nemo on Pixabay for producing public domain artwork used on this site.