Instructors with tables in the public schema cannot be dropped with dropInstructor (W)

[afig]

If an Instructor has created a table in the public schema, then the role cannot be dropped with dropInstructor. This is because an object owned by the Instructor has not been dropped, which prevents Postgres from dropping the role. An example error message is displayed below.

This does not affect other roles because they can only create objects within their own $user schema. Since the $user schema is manually dropped before dropping any role, this does not end up being an issue for those roles.

>>SELECT classdb.dropInstructor('ins0');
ERROR:  role "ins0" cannot be dropped because some objects depend on it
DETAIL:  owner of table testinspub
CONTEXT:  SQL statement "DROP ROLE ins0"
PL/pgSQL function dropinstructor(character varying) line 15 at EXECUTE

We can force the role being dropped by adding a DROP OWNED BY in dropInstructor. I am not 100% certain if this is something that we should do, since we could be dropping a relation that a user might not intend to be dropped. For now I am placing it into the backlog until we form a decision.

[s-aithal]

Is this issue limited only to Instructors that have created a table in public schema? Alternatively, does executing createInstructor function create any dependent object for an instructor that is not deleted when dropInstructor is executed?

I executed createInstructor and dropInstructor without doing anything else in between the two commands. dropInstructor gave the following error:

ERROR:  role "ins2" cannot be dropped because some objects depend on it
DETAIL:  owner of default privileges on new relations belonging to role ins2 in schema public
CONTEXT:  SQL statement "DROP ROLE ins2"
PL/pgSQL function dropinstructor(character varying) line 15 at EXECUTE

[smurthys]

• Yes, we should not drop any object an instructor (any user for that matter) may have created outside their own schema. We should reassign ownership, but to who?
• This makes me wonder what uninstall does in this case?
• We need to make sure that the dropXYZ functions do what uninstall does to determine if a schema is to be dropped: test ownership by ClassDB might be the only test missing.
• Need to have opinion/discussion/justification on addressing this issue in M1: chances of an instructor being dropped are low in real life, but someone evaluating this application is quite likely to try this function, especially something like what @shridharaaithal says in his comment

[wildtayne]

• Currently, the installer doesn't encounter this problem, because it doesn't drop individual Instructor/DBManager/Student roles - it only drops ClassDB_Instructor, etc. Since the objects in PUBLIC are owned by the user themselves, they are left alone by the uninstaller.
• One option would be to reassign all objects owned by said instructor to ClassDB
• This will cause issues with the current uninstaller, because it assumes the only objects that need assigning are user schemas. The uninstaller doesn't reassign other objects, so they would get dropped by the final DROP OWNED BY statement.
• Another options would be to reassign that instructors tables to the dropping user.
• The second issue Shri reported stems from the new ALTER DEFAULT PRIVILEGES statement in classdb.creteInstructor. The corresponding REVOKE statement to undo this is missing from classdb.dropInstructor.
• Adding the following line should fix the issue:

  EXECUTE format(
  'ALTER DEFAULT PRIVILEGES FOR ROLE %s IN SCHEMA PUBLIC REVOKE SELECT ON TABLES FROM PUBLIC;', $1);

[wildtayne]

If we want to go the reassign to current user route, we can use the line: EXECUTE format('REASSIGN OWNED BY %s TO %I', $1, session_user); after the instructor's schema is dropped, but before their role is. This will change the owner of all remaining objects the instructor owned to the user executing classdb.dropInstructor(). Note session_user is required here because classdb.dropInstructor() is SECURITY DEFINER.

There is one catch with this method however. This works fine if one instructor drops another, however, it will fail if a superuser executes it. This is because ClassDB is the role actually executing classdb.dropInstructor(). It has permission to assign objects to an instructor, but not a superuser.

[wildtayne]

I think the solution I outlined would be OK for M1. The superuser limitation is something we can document.

[wildtayne]

After our discussion, the solution we will implement for M1 is as follows:

• When dropping an Instructor or DBManager, all objects they own outside of their schemas will be reassigned to ClassDB_Instructor or ClassDB_DBManager, respectively.
• A function will be added to list all objects owned by the ClassDB_Instructor and ClassDB_DBManager roles, called listOrphans().
• When removeFromDB() is run, an exception will be thrown is listOrphans() returns any rows, any the user will be informed that those objects must be reassigned or dropped.