Database Connection Pool

This is a draft design document (and part implementation notes) to use a database connection pool in the web server.

Considerations

• A connection pool is simply a collection of database connection objects that are already authenticated with the DBMS
• A DBMS-authenticated connection object is immutable and is particular to the triple (server, DB, user)
• The same connection object may be used to execute different queries, but one query at a time
• A connection pool permits the application to engage a connection object currently unused, use it to execute one or more queries or transactions, and disengage the connection object
• A connection object should be engaged for as short a duration as possible: engaged just before a query is to be executed and disengaged immediately after query execution
• Because a DB-authenticated connection object is particular to the triple (server, DB, user), a pool's utility is increased if the application uses the same combination of server, DB, and user to execute all its queries
  • A different connection pool is required for each distinct combination of server, DB, and user
  • Applications typically use a proxy "application" role/user to connect to the DBMS to perform all queries, so they can maintain just a single connection pool with a larger number of connection objects in the pool.

In the DBMS

• Permissions

  • For now skip permissions, privileges, etc.
  • Let any user connect, read, write, and execute anything
  • Can later apply knowledge from ClassDB with relative ease

• Create a script initializeDB.sql

  • Create the Gradebook schema
  • Create a user (that is, a server role with LOGIN) named WebApp (tentative name)
  • Optional for now: create a server role Gradebook_User (with NOLOGIN) and make user WebApp a member of Gradebook_User

• User management

  • Gradebook users will NOT be server roles; instead they use a proxy "app user" such as WebApp to use the DB
  • Add field Email to table Instructor: make it unique and permit NULL (for now)
  • Create table User (Email VARCHAR(256) NOT NULL PRIMARY KEY, password VARCHAR, IsManager BOOLEAN DEFAULT FALSE)
  • User.password is either a password hash or an encrypted password: see module pgcrypto or chkpass
  • There are many, many ways to store passwords and authenticate users, but the scheme outlined here is a start
  • User.Email could reference Instructor.Email, but that imposes an order of row creation: highly likely user record is created after instructor record is created (via importOpenClose)
  • User.IsManager denotes if user is a manager: an instructor could additionally be a "manager"
  • Many other fields possible in table User (CanLogin, LastLogin, and so on), but ignore for now

• Add function getInstructors() to return name and ID of all instructors associated with a section

In the web server

• Create all DB connections with the user WebApp

• Use a connection pool manager such as node-pg-pool to create a pool

  • Strive to use the helper method query to keep code simple

• Receive user e-mail address and password from client and authenticate user against a row in the table User

  • If authenticated, return value from fields IsManager and InstructorID if user is also an instructor (check email against Instructor table: can optimize by using computed field, but later)

• Add REST entry point /instructor to return the result of calling the DB function getInstructors()

In the web client

• Create a login screen
  • Accept user e-mail address and password
  • If user is both manager and instructor (a numeric ID is returned), ask which role user wants to play: permit either manager or instructor
• In the main page:
  • If user is instructor (only or chose instructor role), hide instructor field: use the same instructor ID in all REST calls in this session
  • If user is manager (only or chose manager role), show an instructor drop down populated with all instructors; use ID of selected instructor in REST calls