simonw / django-sql-dashboard

Django app for building dashboards using raw SQL queries

django-sql-dashboard

Django SQL Dashboard provides an authenticated interface for executing read-only SQL queries directly against your PostgreSQL database, bringing a useful subset of Datasette to Django.

Applications include ad-hoc analysis and debugging, plus the creation of reporting dashboards that can be shared with team members or published online.

See my blog for more about this project, including a video demo.

Features include:

• Safely run read-only one or more SQL queries against your database and view the results in your browser
• Bookmark queries and share those links with other members of your team
• Create saved dashboards from your queries, with full control over who can view and edit them
• Named parameters such as select * from entries where id = %(id)s will be turned into form fields, allowing quick creation of interactive dashboards
• Produce bar charts, progress bars and more from SQL queries, with the ability to easily create new custom dashboard widgets using the Django template system
• Write SQL queries that safely construct and render markdown and HTML
• Export the full results of a SQL query as a downloadable CSV or TSV file, using a combination of Django's streaming HTTP response mechanism and PostgreSQL server-side cursors to efficiently stream large amounts of data without running out of resources
• Copy and paste the results of SQL queries directly into tools such as Google Sheets or Excel
• Uses Django's authentication system, so dashboard accounts can be granted using Django's Admin tools

Documentation

Full documentation is at django-sql-dashboard.datasette.io

Screenshot

Alternatives

• django-sql-explorer provides a related set of functionality that also works against database backends other than PostgreSQL

django-sql-dashboard's Issues

Inline end-user dashboard documentation

Since this is a tool for building dashboards, having inline user documentation would be good. It could even include useful copy-and-paste SQL snippets.

Support named parameters as form fields

Like Datasette, except they will apply to all of the queries on the page.

Pip installation missing urls.py

Hi @simonw this looks cool. Ive run into a hiccup with the install.

pip installed django-sql-dashboard
pip freeze says django-sql-dashboard==0.1a3

path("dashboard/", include(django_sql_dashboard.urls)),
AttributeError: module 'django_sql_dashboard' has no attribute 'urls'

Got it working with manually creating urls.py.

Saved dashboards currently available to anyone to view

Until full permissions are implemented in #27 this should be considered an urgent bug to fix.

Protect against GET SQL injection

I'm really nervous about allowing attackers to trick my into visiting /dashboard/?sql=... with some hitherto unexpected evil query that somehow bypasses read-only protections or executed an XSS of some sort.

But I still want to be able to bookmark and share queries.

Maybe a solution can involve signatures? Execute queries with an authenticated CSRF protected POST, it then redirects and adds a signed parameter to verify that it's not from any untrusted source.

Dashboard edit admin should link to dashboard

Probably using .get_absolute_url()

Implement big number dashboard widget

Proposed design:

select 'Signups today' as big_number_label,
count(*) as big_number
from signups where created > now() - interval '1 day';

General mechanism for custom widgets

Needed by #10 and #11 and #12.

Optional audit log

At some point it might be good to add an optional audit log, recording exactly which SQL queries were executed and by whom. More of a far-future idea.

Make it easier to implement custom templates

Maybe extract most of the bundled templates out into fragments that can be easily included in custom parent templates.

Version history for saved dashboards

Since dashboards are each a simple list of SQL queries storing a full history of versions for each one should be really inexpensive.

This could provide a full "undo" stack, which would reduce the risk involved in allowing large teams of inexperienced SQL users to learn how to maintain dashboards together.

Tool for creating read-only credentials

This may be a bit of scope creep, but a super-user only view tool you can optionally enable that helps you create read-only credentials (including restricting which database tables they can see) could really help people use this tool as securely as possible.

Upgrade to Read the Docs and Sphinx for documentation

What with widgets and security configuration and suchlike this is already growing beyond what makes sense to put in a README.

Support databases other than PostgreSQL

• MySQL
• SQLite

Not sure when I'll get to this!

Users without permission may enter a redirect loop

Need to verify this.

Split interactive dashboard and saved dashboard template logic into separate templates

I need to split the interactive dashboard and saved dashboard template logic into separate templates to implement this neatly.

Originally posted by @simonw in #42 (comment)

Installation by include()

Urgent instructions are to do this:

from django.urls import path
from django_sql_dashboard.views import dashboard, dashboard_index

urlpatterns = [
    path("dashboard/", dashboard_index, name="django_sql_dashboard-index"),
    path("dashboard/<slug>/", dashboard),
    # ...
]

Using include() here would be better, especially since the paths need to have fixed names that can be referenced in the templates.

Document how to enforce a statement timeout

This seems to work:

    DATABASES["dashboard"]["OPTIONS"] = {
        "options": "-c default_transaction_read_only=on -c statement_timeout=100"
    }

Test this with:

SELECT pg_sleep(0.2);

It returns " canceling statement due to statement timeout "

I disabled the ; check and tried executing this, but it still timed out so it looks like SET statement_timeout=4000000 was ignored:

SET statement_timeout=4000000; SELECT pg_sleep(0.2);

Really basic charting support

I could use that trick where if a query returns columns with specific names a charting mechanism kicks in. Could use Vega-Lite for this. Will be a little like datasette-vega but less flexible, at least for the first version. Probably implemented entirely in JavaScript.

Markdown and HTML widgets

select '# hello there' as markdown

Export ALL results for a query as CSV/TSV

For any given SQL query the ability to export the entire resultset as CSV would be incredibly useful.

It could be expensive, so we would want to restrict it to only specific trusted users.

Count link doesn't persist the current parameters set in the querystring

Related: #31

Got a 500 error on a like % query

I tried this:

select * from reporter where auth0_role_name like '%Trainee%'

And got a 500 error. Should have had an inline pink error instead. The problem was that those % needed to be %% - should have a hint about that.

Private (or anything other than public) dashboards should not be cached

I noticed that a private dashboard running behind Cloudflare (on my blog) got cached in the public Cloudflare cache when I loaded it as an authenticated user - and could then be seen by logged-out users. This is bad.

Copy and paste TSV for table default template

Copy and paste as TSV - as seen in datasette-copyable - is really useful. Have that as a expand/hide option in the default.html template.

Interface for creating and editing dashboards

This is currently only possible in the Django Admin. This will also implement edit permissions, taking over from #27.

Still todo:

• Form to save a dashboard as a saved dashboard
• If dashboard reloads with form errors, scroll down to the form
• "Edit dashboard" link that links to the Django admin
• Custom Django admin code to respect edit policies
• Saved dashboard pages should show their visibility and edit policies

CSV and JSON export

Ala Datasette

"Duration <blank>ms" shows on error cells

Explore keyset pagination

Efficient pagination based on sorting by a unique key (and potentially using multiple columns with a tie-breaker for other sort orders) is a really neat trick in Datasette that could be interesting to explore here too. Could even unlock larger CSV exports, see #8.

Run tests against a read-only "dashboard" database connection

Related to #16. I want to encourage using a separate "dashboard" database alias which is configured something like this:

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql_psycopg2",
        "NAME": "mydb",
    },
    "dashboard": {
        "ENGINE": "django.db.backends.postgresql_psycopg2",
        "NAME": "mydb",
        "OPTIONS": {"options": "-c default_transaction_read_only=on -c statement_timeout=100"},
    },
}

I want to write the tests against this - but I'm running into some trouble because the test framework isn't designed to handle read-only database connections like this. I'm seeing errors like this:

Got an error creating the test database: cannot execute CREATE DATABASE in a read-only transaction

Unlisted dashboards should use noindex meta tags

Refs #27. If a saved dashboard has view permission of unlisted it should include this in the header of the page:

<meta name="robots" content="noindex">

Command-line tool for starting a server?

Would it be possible to depend on gunicorn and support this:

sql-dashboard $DATABASE_URL

Maybe as a separate PyPI package, to avoid gunicorn as a core dependency.

Add a screenshot to the README

Document configuring a read-only connection, explain security issues

Best practice for this tool will be configuring a read-only role with access to an allow-list of tables, as described here: https://til.simonwillison.net/postgresql/read-only-postgresql-user

I'm going to continue supporting read-only transactions against a regular connection purely because Heroku charge $50/month minimum for the ability to add extra read-only users to a database.

Allow dashboards to be saved to the database

Needed for permissions work in #1

OSError: [Errno 63] File name too long

Got this error for a SQL query that returned a large number of columns. Here's the culprit:

django-sql-dashboard/django_sql_dashboard/views.py

Line 119 in d8a1f7d

template_name = "-".join(sorted(c.name for c in cursor.description))

Add "features" section to README

The README has to explain what the thing is and convince people they want to use it. The documentation then tells them how.

Use ?sql=xxx:signature instead of signed values

URLs to SQL queries currently look like this:

https://simonwillison.net/dashboard/?sql=InNlbGVjdCAlKG5hbWUpcyBhcyBuYW1lLCB0b19jaGFyKGRhdGVfdHJ1bmMoJ21vbnRoJywgY3JlYXRlZCksICdZWVlZLU1NJykgYXMgYmFyX2xhYmVsLFxyXG5jb3VudCgqKSBhcyBiYXJfcXVhbnRpdHkgZnJvbSBibG9nX2VudHJ5IGdyb3VwIGJ5IGJhcl9sYWJlbCBvcmRlciBieSBjb3VudCgqKSBkZXNjIg%3A1lLfRD%3AvFP_m0s3BxRS2qyiWtlMlE1KRa2qoKItofP1vvK7hdY&sql=InNlbGVjdCBib2R5IGFzIGh0bWwgZnJvbSBibG9nX2VudHJ5IGxpbWl0IDEi%3A1lLfRD%3AEK0KOXcGgYdgD4Yzglbodf806GnbmrdtPridp8m0hlY

The problem here is that if the Django secret is reset these become broken links - there's no easy way to recover the SQL.

Instead, if the signatures do not match, how about populating the forms but NOT executing the SQL queries, and showing a warning message at the top of the page?

The ?sql= parameters could then become ?sql=SELECT ...::oKItofP1vvK7hdY where oKItofP1vvK7hdY is a signature but the rest of the query is in plain text.

pip install fails on macOS due to psycopg2

pip install django-sql-dashboard==0.1a fails on macOS.

There I want to install psycopg2-binary instead.

Count links break if SQL query contains double quotes

The double quotes get turned into HTML entities and those contain a semicolon which causes an error message to be shown.

/dashboard/?edit=slug as opposed to /dashboard/slug/

I think /dashboard/slug/ should always be read-only. It can provide a link to /dashboard/?edit=slug(visible only to users with the right permissions) that provides the edit interface.

This will also help deal with the problem that the new Markdown and HTML widgets encourage dashboard SQL queries long enough that they might run into query string length restrictions. Those widgets are mainly intended to work with saved dashboards, where the edit would take place entirely via POST requests with much less restrictive size limits.

Control access using a Django permission

I was going to change this to superuser-only, but having it use a permission would both enable superuser access and allow other users to be granted access too.

https://docs.djangoproject.com/en/3.1/topics/auth/default/

I'm going to call the first new permission django_sql_dashboard.execute_sql.

Bug: templatetags not included in PyPI release

View and edit permissions for dashboards

The ability to control who can view a dashboard, and who can edit a dashboard at the individual dashboard level.

Auto-refreshing cells

Since a cell has a SQL query, and is tied to a server-side template, it wouldn't be too hard to provide a mechanism whereby the cell periodically polls the server to refresh itself.

pip installed package doesn't include templates, migrations

After downloading and extracting:

/tmp % find django-sql-dashboard-0.1a2 
django-sql-dashboard-0.1a2
django-sql-dashboard-0.1a2/PKG-INFO
django-sql-dashboard-0.1a2/django_sql_dashboard
django-sql-dashboard-0.1a2/django_sql_dashboard/models.py
django-sql-dashboard-0.1a2/django_sql_dashboard/__init__.py
django-sql-dashboard-0.1a2/django_sql_dashboard/apps.py
django-sql-dashboard-0.1a2/django_sql_dashboard/admin.py
django-sql-dashboard-0.1a2/django_sql_dashboard/views.py
django-sql-dashboard-0.1a2/pyproject.toml
django-sql-dashboard-0.1a2/README.md
django-sql-dashboard-0.1a2/django_sql_dashboard.egg-info
django-sql-dashboard-0.1a2/django_sql_dashboard.egg-info/PKG-INFO
django-sql-dashboard-0.1a2/django_sql_dashboard.egg-info/SOURCES.txt
django-sql-dashboard-0.1a2/django_sql_dashboard.egg-info/requires.txt
django-sql-dashboard-0.1a2/django_sql_dashboard.egg-info/top_level.txt
django-sql-dashboard-0.1a2/django_sql_dashboard.egg-info/dependency_links.txt
django-sql-dashboard-0.1a2/setup.py
django-sql-dashboard-0.1a2/setup.cfg

Django 3.1 migration dependency

I noticed an error when I installed this with Django 3.0 because you have a migration that's tied to 0012_alter_user_first_name_max_length which is a 3.1 feature.

Maybe this is intentional, but an updated trove classifier and/or a setup requirement of 3.1 might at least let people know.

https://github.com/simonw/django-sql-dashboard/blob/main/django_sql_dashboard/migrations/0002_dashboard_permissions.py#L12

Ability to configure dashboards in code that lives in source control

Dashboard configurations that live in the database could get out of sync with the database design - especially if it is being actively iterated on.

An option where you can define your dashboards in source code - probably YAML - would enable people to keep the dashboards synchronized with changes made to their models, enforced by code review.

Could even have a testing mechanism which can attempt to render every configured dashboard and check that no SQL errors (e.g. from missing columns) are returned during the renders.

Given this mechanism, users could opt-out entirely of database-driven dashboards - or they could use both.

Figure out robust pattern for read-only queries

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.

Ability to make a saved dashboard public

So you can expose dashboards to the public