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.

How can I contact you?

For anything related to the site, mail me at pgexercises@pgexercises.com. For anything else, find details on my personal web site www.zaltys.net.

You're running raw SQL! How did you secure the server?

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:

  • User has read-only access to the exercises tables and nothing else.
  • Each statement is time limited using statement_timeout.
  • App server connects through pgbouncer, which is configured for statement-level pooling. When the connection gets returned between statements pgbouncer runs ABORT; and clears any settings, preventing the user from changing settings on the pooled connections. In particular, this prevents users changing the statement timeout to something longer :-).

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.

I want to contribute/Your CSS sucks/I have a suggestion for the site code.

You can contribute at Github. For minor corrections or more sweeping suggestions, you can mail me at pgexercises@pgexercises.com. Sorry about the CSS :-).

What about creates, updates, and deletes?

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.

What technologies were used in this site?

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.