Figure out robust pattern for read-only queries

[simonw]

Split from #15 (comment)_

Dashboard queries should only execute read-only. There should be zero risk of a malicious /dashboard/?sql=update+blah query ever being executed.

The best way to do this is using a dedicated read-only PostgreSQL read-only role, see https://til.simonwillison.net/postgresql/read-only-postgresql-user

There's just one catch: on Heroku you need to pay at least $50/month to gain the ability to set up additional read-only roles! So ideally I'd like a working Heroku workaround here.

[simonw]

How about using a separate database alias which sets that READ ONLY thing on every new connection? Something like this:

import psycopg2.extensions

DATABASES = {
    # ...
    'OPTIONS': {
        'isolation_level': psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE,
    },
}

https://docs.djangoproject.com/en/3.1/ref/databases/#postgresql-notes

https://nejc.saje.info/django-postgresql-readonly.html suggests:

'OPTIONS': {
    'options': '-c default_transaction_read_only=on'
}

[simonw]

After much experimentation, I think I've found a good pattern for this - at least for PostgreSQL.

Setting '-c default_transaction_read_only=on' isn't quite good enough, because an attacker can construct their SQL like this:

SET TRANSACTION READ WRITE; update auth_user set is_superuser = true where username = 'demo';

But... SET TRANSACTION READ WRITE; only works if it's executed inside a transaction before any queries have been run.

So the patter that seems to work is:

1. Operate against a connection with default_transaction_read_only=on
2. Start a new transaction with BEGIN
3. Run SELECT 1 - this prevents the user-provided SQL from running SET TRANSACTION READ WRITE;
4. Now run the user-provided SQL
5. Reset the state of the transaction with ROLLBACK at the end

I'd also like to prevent users from running multiple queries. The cheap way to do this is to ban any occurrences of a ; in the query, which I'm doing already, but I wonder if there's a more elegant way to do that?

django-sql-dashboard/django_sql_dashboard/views.py

Lines 34 to 43 in 87fc304

	if ";" in sql.rstrip(";"):
	query_results.append(
	{
	"sql": sql,
	"rows": [],
	"description": [],
	"truncated": False,
	"error": "';' not allowed in SQL queries",
	}
	)

[simonw]

I can't find a better way to forbid multiple SQL queries than banning ; so I will keep on doing that.

[simonw]

I'm pleased with this, next step is to document it in #6.