I have a Fly application that uses their PostgreSQL service. I wanted to run Django SQL Dashboard with a read-only user against that database.
Here's how I did it.
The application running my Fly PostgreSQL database is called
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
Then paste in the password (if you don't have your
postgres password to hand, I spotted it in
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
I created a random password for my role first, which I used as
RANDOM_PASSWORD in this script:
-- Create the role - will be read-only and have access to most tables CREATE ROLE dashboardrole; 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:
Now to grant access to current and future tables in that schema:
-- Grant access to the schema and all tables GRANT USAGE ON SCHEMA public TO dashboardrole; GRANT SELECT ON ALL TABLES IN SCHEMA public TO dashboardrole; -- Grant access to future tables as well 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
-- Revoke access to django_session 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:
-- Grant access to the auth_user table to just the columns we need REVOKE SELECT ON auth_user FROM dashboardrole; GRANT SELECT( id, last_login, is_superuser, username, first_name, last_name, email, is_staff, is_active, date_joined ) ON auth_user TO dashboardrole;
Finally I set a password so my
dashboardrole could connect from Django with one:
-- Set a password for the role ALTER ROLE dashboardrole WITH LOGIN PASSWORD 'RANDOM_PASSWORD';
I then exited
psql and ran this to test the new role:
psql postgres://dashboardrole@localhost/datasettecloud -W < PASTE RANDOM_PASSWORD >
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:
The rest of the configuration is described in the Django SQL Dashboard documentation.
More on Django SQL Dashboard.
Created 2023-08-21T21:51:03-07:00 · Edit