Configuring Django SQL Dashboard for Fly PostgreSQL#

Connect to psql on the Fly PostgreSQL service#

The application running my Fly PostgreSQL database is called my-app-postgresql.

1
flyctl ssh console -a my-app-postgresql

This gives me a shell on the machine running PostgreSQL. I can then connect to the database with psql.

1
psql postgres://postgres@localhost

Then paste in the password (if you don’t have your postgres password to hand, I spotted it in ps aux).

Create a role with read-only permissions#

I decided to create a role called dashboardrole. In PostgreSQL roles and users are technically the same thing - a user is really just a role that can be signed into with a password.

My PostgreSQL database schema here is called appdatabase.

I created a random password for my role first, which I used as RANDOM_PASSWORD in this script:

1
-- Create the role - will be read-only and have access to most tables
2
CREATE ROLE dashboardrole;
3
GRANT CONNECT ON DATABASE appdatabase TO dashboardrole;

I connected to the database, so the references to public coming up would refer to the right place:

1
\c appdatabase

Now to grant access to current and future tables in that schema:

1
-- Grant access to the schema and all tables
2
GRANT USAGE ON SCHEMA public TO dashboardrole;
3
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dashboardrole;
4
-- Grant access to future tables as well
5
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO dashboardrole;

Since I’m running a Django application I wanted to lock things down a little here. I revoked access entirely to the django_session table:

1
-- Revoke access to django_session
2
REVOKE SELECT ON TABLE django_session FROM dashboardrole;

I want to be able to join against the auth_user table, but I didn’t want to make the password hashes visible to dashboard users. In PostgreSQL you can’t subtract permissions, but you can revoke the entire table and then selectively add them back for the other columns:

1
-- Grant access to the auth_user table to just the columns we need
2
REVOKE SELECT ON auth_user FROM dashboardrole;
3
GRANT SELECT(
4
  id, last_login, is_superuser, username, first_name,
5
  last_name, email, is_staff, is_active, date_joined
6
) ON auth_user TO dashboardrole;

Finally I set a password so my dashboardrole could connect from Django with one:

1
-- Set a password for the role
2
ALTER ROLE dashboardrole WITH LOGIN PASSWORD 'RANDOM_PASSWORD';

I then exited psql and ran this to test the new role:

1
psql postgres://dashboardrole@localhost/datasettecloud -W
2
< PASTE RANDOM_PASSWORD >

I tried select * from auth_user and got an error, but select id, username from auth_user worked fine - as I had intended.

The last step was to construct a connection URL for Django. I used this:

Set up the Django connection string#

1
postgres://dashboardrole:RANDOM_PASSWORD@my-app-postgresql.internal/appdatabase

The rest of the configuration is described in the Django SQL Dashboard documentation.

More on Django SQL Dashboard.