createRole() User Schema checks and actions

This wiki page contains a flowchart representing the proposed checks and corresponding actions that will be done during the creation of a ClassDB user/role in M2.

Flow chart

Exported HTML version

Checks - Outdated

• Does role 'usr' exist in pg_catalog.pg_roles? - Implemented in M1 by calling classdb.isRoleDefined(roleName)

  • Sidenote: This function name is now somewhat unclear, may want to rename to something such as isServerRoleDefined(roleName)

• Does role 'usr' exist in classdb.RoleBase? - N/A in M1, not yet implemented in M2

  • Options: Checking result of an EXISTS statement within classdb.createRole, or same statement, but in a helper function

• Does schema 'usr' exist in pg_catalog.pg_namespace? - Implemented in M1 by checking the result of an EXISTS statement, not yet modified in M2

• Do role 'usr' and ClassDB roles have proper privileges to schema 'usr'? - Not implemented in M1, not yet implemented in M2

  • ClassDB should be the owner of the schema, 'usr' should have ALL PRIVILEGES (In practice, this results having 'Usage' and 'Create' privileges.)
  • Should probably be implemented as a helper function, due to needing to check several properties
  • has_schema_privilege(user, schema, privilege) will likely be useful. Checking for ownership may require checking the pg_catalog.pg_namespace view (see below)

Output of SELECT * FROM pg_catalog.pg_namespace; on a clean installation of ClassDB on PG 9.6 with one student (teststu):

classdb_0102=# SELECT * FROM pg_catalog.pg_namespace;
      nspname       | nspowner |                                                 nspacl
--------------------+----------+---------------------------------------------------------------------------------------------------------
 pg_toast           |       10 |
 pg_temp_1          |       10 |
 pg_toast_temp_1    |       10 |
 pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
 information_schema |       10 | {postgres=UC/postgres,=U/postgres}
 public             |       10 | {postgres=UC/postgres,=U/postgres,classdb_instructor=C/postgres}
 classdb            |       10 | {postgres=UC/postgres,classdb=UC/postgres,classdb_instructor=UC/postgres,classdb_dbmanager=UC/postgres}
 teststu            |    26416 | {classdb=UC/classdb,teststu=UC/classdb,classdb_instructor=U/classdb}
(8 rows)

See documentation for pg_namespace, and pg_authid

Actions - Outdated

• createRole('usr'...) called - Sample call, 'usr' is the role that is currently being attempted to be added to ClassDB. This is called whenever an Instructor, DBManager, Student is to be created

• Create role 'usr', add to classdb.RoleBase - Only role is created in M1, Adding to RoleBase not yet implemented in M2

• Create schema 'usr' and assign schema to role 'usr' - Implemented in M1, No changes needed for M2 (currently)

Messages - Outdated

In M1, only NOTICEs are raised in _some _non-default scenarios, and the conditions in which they are raised in are not strictly defined. M2 seeks to define when these messages are raised, and document the conditions that led up to them.

• Raise EXCEPTION: "Role 'usr' already exists" - okIfRoleExists was false, so an exception should be raised if the role already exists, regardless of whether it is known to this instance of ClassDB

• Raise NOTICE: "Previously existing role 'usr' added to ClassDB" - The role being created already exists on the server, but is not known to this instance of ClassDB. This registers the role with ClassDB (adds it to the RoleBase table), but raises a NOTICE in case this wasn't planned

  • This NOTICE may not be necessary

• Raise EXCEPTION: "Schema 'usr' already exists" - The schema that should correspond to the newly created user already existed, since okIfSchemaExists was false, an exception should be raised

• Raise EXCEPTION: "Schema 'usr' already exists, but is not managed by ClassDB" - The schema that should correspond to the newly created user already existed, but did not have appropriate privileges. It should have been owned by the classdb role, and the user being created should have 'Usage' and 'Create' privileges on the schema.

  • If classdb was the owner of the schema, then it would be possible to assign privileges to the newly created user, but this may not be appropriate.