search

DASSL / ClassDB

An open-source system to let students experiment with relational data
https://dassl.github.io/ClassDB/
Other
7 stars 2 forks source link

Tables created in the public schema by Instructors cannot be read by other roles (WM) #106

Closed afig closed 7 years ago

afig commented 7 years ago

If an Instructor creates a table in the public schema, it should automatically be able to be read by any role. However, that is not currently the case. Attempting to read a table that an Instructor created in the public schema results in a error such as: ERROR: permission denied for relation <relation>.

The probable solution to this issue is to alter the default privileges for Instructors. (Similar to how altering the default privileges for Students is performed.) Adding a line such as the following to createInstructor should solve the issue:

EXECUTE format('ALTER DEFAULT PRIVILEGES FOR ROLE %s IN SCHEMA public GRANT SELECT'
                    || ' ON TABLES TO PUBLIC', $1);
wildtayne commented 7 years ago

Will we also have to grant each Instructor to ClassDB, similar to the solution for Students?

afig commented 7 years ago

Yes, it seems necessary to do so, thanks for pointing that out.

wildtayne commented 7 years ago

As mentioned here, the following statement should be added to classdb.dropInstructor as well to revoke these default privileges. Otherwise, the instructor can't be dropped.

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