Updates to ClassDB Uninstaller and Shelter Schema

[wildtayne]

This branch contains a few different changes:

Uninstaller:

• Updated the uninstaller, as described in #76.
• Added an additional check to the uninstaller - when it tries to reassign schema owners, it will change the owner to the user matching the name of the schema, or the current user if one is not found.
• Note that removeClassDBFromServer.sql expects shelter to have been dropped. Currently, users have to manually run dropShelter.sql to do this.

Shelter Schema:

• Added a schema called shelter to hold the shelter schema.
• ClassDB owns the schema + all default objects.
• Instructors and DBManagers have full access to the schema (read + write).
• Students have read only access to the schema.
• It uses the helper functions to check for superuser, so it does depend on those.
• It also does a SET ROLE ClassDB when creating the tables, so they are all owned by classdb. If this is not optimal, I can change to using ALTER TABLE statements.

Misc:

• Added some ClassDB_ prefixes that were missing / conflicted with my changes
• Fixed the permissions on classdb.importLog() - now instructors and dbmanagers can both run it.
• Changed the permission check in perpareClassServer.sql to superuser, which fixes #74.

Two further questions:

• Should we add shelter to students' search path?
• Should the uninstall script automatically remove shelter?

[smurthys]

Commenting only on shelter schema for now:

1. I suggest using CREATE SCHEMA IF NOT EXISTS and remove DROP SCHEMA in case the instructor has added to the schema previously. Shelter schema isn't really part of ClassDB, but is provided as a helpful starter. So, it is possible an instructor has extended. (Granted, CREATE TABLE statements will remove any revisions to table schema.)

2. I don't see a need for ClassDB to own the schema, because it is only an example schema. We do however need all the other privileges as granted to other app roles at the end of the script. At uninstall, we can leave the shelter schema as is, because creating that schema is not part of ClassDB installation.

3. I love the use of SET ROLE, but it won't be needed if we agree ClassDB won't own the schema.

4. I propose using SET LOCAL SCHEMA shelter before creating tables and inserting rows and then remove the schema name qualification from object names. This way very few changes will be needed if an instructor wants to add the objects to a different schema, etc.

5. I propose renaming the files createShelterSchema.sql and dropShelterSchema.sql.

6. If it is not too much effort, I propose putting the INSERT statements to a file such as populateShelter.sql.

[wildtayne]

Thanks for the feedback. I think all of these changes make sense. Just to confirm how the scripts will behave:

• All scripts will use SET LOCAL SCHEMA and have unqualified table names.
• createShelterSchema will create a schema called shelter, and create all the shelter tables. The schema and tables will be owned by the creating user - Students will have read access, and DBManager and Instructor will have read+write access. Currently, this script will require superuser permissions to be consistent with other ClassDB scripts, however in the future it would only need CREATE SCHEMA.
• populateShelterSchema will insert rows into the shelter tables. This can be run as any user with write permission on shelter.
• dropShelterSchema will drop all tables in the shelter schema, along with the schema itself. This script must also be run as superuser.
• I have also added additional DROP OWNED BY statements to removeClassDBFromServer for Student and DBManager. These server the purpose of dropping the permissions on any extra schemas, like shelter, while keeping the schemas.

I'll push a new commit in a minute that should address these changes.