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. We're aided rather in this by the fact that all our work here is read-only, allowing us to easily prevent malicious writes to the DB :-). Other concerns are people running very expensive queries, changing settings, etc. I've taken 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 :-).

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.