Make auth_user table (or subset of columns) available for dashboard queries

[simonw]

Consider this query:

select * from reversion_revision where comment like 'Merged locations,%%' order by date_created

Results look like this: https://vial.calltheshots.us/dashboard/?sql=select+%2A+from+reversion_revision+where+comment+like+%27Merged+locations%2C%25%25%27+order+by+date_created%3AZzkYu4UBH1kS2U1FsNiXtu5rgVo_Vt8WOYbVejupVEE&sql=select+%2A+from+reversion_revision+where+comment+like+%27Merged+locations%2C%25%25%27+order+by+date_created%3AZzkYu4UBH1kS2U1FsNiXtu5rgVo_Vt8WOYbVejupVEE&_save-title=&_save-slug=&_save-description=&_save-view_policy=private&_save-view_group=&_save-edit_policy=private&_save-edit_group=

id	date_created	comment	user_id
10562	2021-04-15 22:40:36.737875+00:00	Merged locations, winner = ldkhr, loser = reczx81U2HzIJRyQh	37
10563	2021-04-15 22:43:56.536505+00:00	Merged locations, winner = recer7TIMgaA3tY7J, loser = recWqoMmBXBUDnS4z	37
10564	2021-04-15 22:44:17.596818+00:00	Merged locations, winner = recer7TIMgaA3tY7J, loser = recMS9GHuzZ22xtdL	37

Obvious improvement is to join against auth_user to see who the user_id column corresponds to:

select * from reversion_revision join auth_user on reversion_revision.user_id = auth_user.id where comment like 'Merged locations,%%' order by date_created

https://vial.calltheshots.us/dashboard/?sql=select+%2A+from+reversion_revision+join+auth_user+on+reversion_revision.user_id+%3D+auth_user.id+where+comment+like+%27Merged+locations%2C%25%25%27+order+by+date_created%3AoCklwVVt8QmNnboJNWNVDqwMW5M6E_gFglEMFK8luUA&sql=select+%2A+from+reversion_revision+where+comment+like+%27Merged+locations%2C%25%25%27+order+by+date_created%3AZzkYu4UBH1kS2U1FsNiXtu5rgVo_Vt8WOYbVejupVEE&_save-title=&_save-slug=&_save-description=&_save-view_policy=private&_save-view_group=&_save-edit_policy=private&_save-edit_group=

The reason we don't expose auth_user is that it includes hashed passwords.

(I'm not sure why that column is populated since we outsource auth to auth0 - but our password column does seem to be populated. Best not to mess with it - especially since maybe someone gets confused and uses "change password" in the Django admin and sets their password to something they also use elsewhere)

Good news: PostgreSQL has a mechanism for GRANT to only specific columns, see simonw/django-sql-dashboard#98

GRANT SELECT(
  id, last_login, is_superuser, username, first_name, last_name, email, is_staff, is_active, date_joined
) ON auth_user TO "my-read-only-role";

[simonw]

GRANT SELECT(
  id, last_login, is_superuser, username, first_name, last_name, email, is_staff, is_active, date_joined
) ON auth_user TO "my-read-only-role";

Ran that on staging, and now https://vial-staging.calltheshots.us/dashboard/?sql=select+%2A+from+auth_user%3AuPwaIJ89y8CG8DcG2DcNiW-j6NSHkUmvq5uDgb-Nid8&sql=select+id%2C+username+from+auth_user+limit+2%3A6RifnbME7TK_E-tOgSJc4OTHw5lk23Y3M-fGJ9QGXNM returns this:

So you need to explicitly list the columns, but this does at least let you do the joins.

I ran it for datascience too, and then ran it on production.

[simonw]

GRANT SELECT(
  id,
  last_login,
  is_superuser,
  username,
  first_name,
  last_name,
  email,
  is_staff,
  is_active,
  date_joined
) ON auth_user TO "read-only-core-tables";

GRANT SELECT(
  id,
  last_login,
  is_superuser,
  username,
  first_name,
  last_name,
  email,
  is_staff,
  is_active,
  date_joined
) ON auth_user TO "datascience";

[simonw]

Works in production too: https://vial.calltheshots.us/dashboard/?sql=select+*+from+auth_user%3A2PzqKKRz4HMMqVueEu51LaXfN6hj_5k14V99T_Czaa0&sql=select+id%2C+username+from+auth_user+limit+2%3Ag3BlFf_gDE0ifuT-eix4zRH6A2ave1UyUwOhp9Tf7W0