Document how to grant access to individual columns

[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";

This is a useful pattern for allowing joins against the users table without exposing password hashes.

[simonw]

One limitation of this approach is that select * won't work against tables - you have to explicitly list each column.

[simonw]

Good news though: the technique we use to find the columns available for a table appears to take column permissions into account:

That's this code here:

django-sql-dashboard/django_sql_dashboard/views.py

Lines 139 to 165 in eefa5fb

	tables_cursor.execute(
	"""
	with visible_tables as (
	select table_name
	from information_schema.tables
	where table_schema = 'public'
	order by table_name
	)
	select
	information_schema.columns.table_name,
	string_agg(column_name, ', ' order by ordinal_position) as columns
	from
	information_schema.columns
	join
	visible_tables on
	information_schema.columns.table_name = visible_tables.table_name
	where
	information_schema.columns.table_schema = 'public'
	group by
	information_schema.columns.table_name
	order by
	information_schema.columns.table_name
	"""
	)
	available_tables = [
	{"name": t[0], "columns": t[1]} for t in tables_cursor.fetchall()
	]

So rather than generate select * we should generate explicit column selects, to avoid this issue cropping up in queries generated by clicking on links.