Add new user views

[wildtayne]

This PR adds the new user views discussed for #184. A summary of the changes:

• The old StudentActivityAll and StudentActivityAnon views have been removed
• All ClassDB.getUserXYZActivity() functions now supply NULL for the default parameter
• When NULL is passed, the function returns activity records for all users
• New functions:
  • ClassDB.getStudentActivitySummary(): Like getUserActivitySummary(), but restricted to students
  • ClassDB.getStudentActivitySummaryAnon(): Anonymized version of getStudentActivitySummary()
  • ClassDB.getStudentActivity(): Like getUserActivity() but restricted to students
  • ClassDB.getStudentActivityAnon(): Anonymized version of getStudentActivity
• New views:
  • ClassDB.StudentActivitySummary: View wrapping getStudentActivitySummary()
  • ClassDB.StudentActivitySummaryAnon: View wrapping getStudentActivitySummaryAnon()
  • ClassDB.StudentActivity: View wrapping getStudentActivity()
  • ClassDB.StudentActivityAnon: View wrapping getStudentActivityAnon()

Similar functions and views limited to only connection or DDL activity have not been added, but could be easily added in the future, since all functions now take a NULL parameter.

[smurthys]

That is a lot of views and functions @srrollo. Kudos on the orchestration. 👍

I love the trick in getUserActivity (and elsewhere) to choose between one user and all users. 🥇

(I wonder if that trick has a security risk. Not saying I know there is, but that question just popped into my head.)

A few things (some minor):

• L98-100: Column names ddlcount, etc. use incorrect character case. This issue recurs in this file.
• L224: I believe the grant is unnecessary. This issue also recurs.
• L238: The second column does not have an explicit name
• L259: Column StatementStartedAt does not exist in the input table
• L332-L333: Comment should say something about column ActivityType and also state that DDL* fields can be NULL: it is easy to miss this detail in the query
• I wonder if function getStudentActivity could invoke getUserActivity and then apply a filter, as is done in function getStudentActivitySummary. In general it is not efficient to do so, but it is OK in this application due to interactive use.
• Function getStudentActivityAnon could reuse getStudentActivity

[afig]

Looks pretty good apart from the improvements that @smurthys suggested.

[wildtayne]

Thanks for the review @smurthys. I've push a commit addressing most of the observations. A couple notes:

• Thanks for pointing out the code duplication in getStudentActivity/Anon
• In my testing, I had to explicitly grant SELECT on views owned by ClassDB for other roles to be able to access them, while functions do not require an explicit grant of EXECUTE
• I've gone through the file and corrected errors in column character case / column names. I want to note that this is not strictly necessary inside Postgres functions, since they adopt the specified output schema in the RETURNS block, and the column names from the contained query itself are ignored. However, I think it's better to have any function's internal query schema match its output for clarity.
• I've tried to make as many lines as possible <80 characters

I don't think the use of COALESCE in these functions presents an issue, simply because unprivileged users can never provide an input to those functions other than the result of SESSION_USER (unless their Postgres rolename was NULL, somehow).

[smurthys]

Thanks @srrollo for the changes. Eyeballing them, they look good.

We should discuss the LIKE trick on campus. I've no particular security concern as of now.