Need a stop-gap web app to change password

[KevinKelly25]

While pgweb is used a front-end, a stop-gap app is needed for users to change their passwords for the underlying PostgreSQL database. Information about proposed solution can be found here.

EDIT: Changed wording to show stop-gap nature of the app. Also changed the URL due to Wiki changes.

[smurthys]

I'd like feedback on the following extension which also requires some work in ClassDB:

It seems like logging should/could also be done in the ClassDB database so it can be included in activity history. This feature will require a new table in ClassDB, perhaps a generic "Activity" table with "Password change" as the value in a column that represents "activity". This table's content can be unioned in ClassDB's "user activity" functions and views.

Seeing a "password change" activity is extremely helpful for instructors to know if a student has actually change password. Of course, password change entries would be added only through apps such as LearnDB (and not from generic clients such as psql), and that is OK.

The activity table could also be used to log user-selected details. E.g., an instructor might require students to log when they run their table-creation scripts in a project.

This proposal to log in an activity table is in addition to logging to a disk file as already proposed.

[KevinKelly25]

I believe you are correct that this would be a valuable extension to ClassDB and creating an generic activity table can be useful for future extensions to logging as well.

If we did this extension the activity table would need to protected from student's being able to alter it. In the proposed route to change password we are using the student's own role to update their password. This means we cannot also insert into the proposed activity table with that role/connection. Since passwords are stored in system tables such as pg_authid I do not believe it is possible to make a trigger to monitor password change. We could create a secondary connection with a more privledged role via the web server that updates the activity table after a successful password change. However, this does add some other issues such as needing new role that can do that insert row and also creating a second connection that may be unnecessary.

[smurthys]

Thinking further, it seems useful to have a table ClassDB.PasswordChangeActivity permitting connections and a role ClassDB_ActivityLogger which LearnDB can use to log activities.

If a general activity table is provided, any classdb-created role should be able to add generic long entries, but via a function owned by the ClassDb role. That is, the actual activity table should be accessible only to the ClassDB role. This is similar to the situation with DDL and connection activity management.

I think we need to study the complexity to determine if a password-change activity logging is needed right now. Perhaps not, I recommend thinking further, without slowing down the app development.