Instructors and DB Managers have all privileges on schema ClassDB (E)

[smurthys]

Roles ClassDB_Instructor and ClassDB_DBManager unnecessarily have all privileges on ClassDB schema.

Also, it is unclear why the creating user owns schema ClassDB instead of role ClassDB.

The corresponding code is here.

The correct code seems to be the following, but the reasons for instructors and managers having all rights to the schema needs to analyzed.

GRANT ClassDB TO CURRENT_USER;
CREATE SCHEMA IF NOT EXISTS ClassDB AUTHORIZATION ClassDB;

[smurthys]

I wonder if we can revoke all privileges from all users on schema ClassDB soon after the schema is created and yet not interfere with the ability of instructors and dbmans to access specific objects as permissions are granted to them. For example, instructors will have explicit execution privilege on function ClassDB.createStudent.

[wildtayne]

IIRC a role cannot access objects in a schema at all, regardless of their privileges, if they do not have USAGE on the containing schema (this appears to be confirmed in the docs). Otherwise, for example, we could have given ClassDB_Student EXECUTE on FoldPgID() without giving them USAGE on ClassDB.

I agree that ClassDB should be owned by ClassDB, and not the creating user, especially since we grant the ClassDB role to the creating user. Additionally, it seems sufficient to grant USAGE only on the ClassDB schema to instructors and dbmanagers (based on these docs) since we are already explicitly setting permissions on each object in ClassDB. That way, instructors and dbmanagers could still read tables and execute functions we grant them, but nothing more.