Removing stored role passwords when disconnecting a DB

[Sean Colsen]

Question for Brent, Pavish, Kriti, (and maybe others):

I’ve been working on implementing the new UI for disconnecting DBs. In a call just now (topic began at 1:08:43), Pavish mentioned that we ought to make it clear to the user what gets deleted in Mathesar when we disconnect a DB. For example, we should say that all explorations will be deleted.

I’ve been working on these changes since getting off the call, and I wanted to also be clear to the user about what happens to their stored role passwords.

Ok, so what does happen to those stored role passwords?? 🤔

What I expected to happen is that all stored role passwords would remain until the last DB is disconnected for a given server, in which case all stored role passwords would be deleted. I vaguely recall us having decided on this behavior during the permissions design sessions many months ago.

But what observed to happen is that all stored role passwords remain in Mathesar after all DBs are disconnected.

To me this seems like a problem. Due to the sensitive nature of passwords, I’d want to be extra careful not to give the user the impression they’ve deleted something sensitive when it actually still persists. I’d be happy to open a GitHub issue if this is actually a bug. I’m just not certain that I have the correct expectation of the behavior here.

Is it possible to fix this before beta? If so, then what I was thinking I’d do is make the UI conditional within that “Disconnect DB modal”. If the user is disconnecting the last DB for a server, then the UI would explain that all stored role passwords will be deleted. And if there are still other connected DBs with the same server, then the UI would explain that stored role passwords will not be deleted.

But if we don’t want to change the functionality, then I can write the UI to explain that the stored role passwords will remain.

​

[Brent Moran]

The correct way to fix this is to stop avoiding the "DB server" concept. That avoidance is misleading and requires the maintenance of an increasingly-brittle cathedral of UI lies. In particular, they should be managing roles, databases, etc. in the server, rather than DB, context. In this case, since the user has no real concept of a "DB Server", even any UI element mentioning that they're deleting the last DB on a server would likely be confusing. In fact, I'd argue that your expectation that "roles and passwords would be deleted" when removing the last DB on a server comes from your understanding of the actual DB structure which isn't very well represented in the UI. A naive user is just as likely to imagine that roles and passwords would be removed if they disconnect any database from Mathesar.

In the short term (i.e., for beta), there are some UI questions to answer before it's clear how to proceed:

• What's the expected behavior if a user changes the name of the only configured database, meaning they have to disconnect and reconnect it to keep using it in Mathesar? Should this also remove all their passwords?
• What if they had an example DB, and they're getting rid of that and moving to a "real" database on the same server? Should they be able to continue using their stored passwords?
  
• What about the internal database server? We already have a stored role/password for that, used when you create a database. How shall we explain the discrepancy to users?
  
• Given that we remove all Databases on a server in Mathesar, shouldn't we also remove the configured server itself from our storage? (This would solve the password problem, since the passwords would be removed when the configured server is. OTOH, it makes the internal server situation even more confusing).
• There are certainly workflows (see bullet-points 1 and 2) that would be smoothed by letting a user have a server (and its roles, etc) without any databases. How should these be prioritized in comparison to the "safety" aspect of getting rid of passwords?

My instinct is to make a super minimal UI element (maybe just a checkbox on the disconnect DB form) that lets a user know that they're disconnecting the last DB on a server, and lets them choose to remove all Configured server data as well (the URL, the port, all roles, etc.). This would map to another parameter in the `databases.configured.disconnect` function, and the back end would handle things from there. Showing the UI element would be a choice the Front end should be able to make with the information on hand. There is some confusion to iron out for the main mathesar role on the internal DB server, since that password is differently stored, but we already treat that differently so it's not a complete shock.

My 2nd option (less preferred, but time constraints exist) would be to add that parameter to the relevant RPC function, set the default to `True`, neglect any users who inadvertently delete role passwords, and sort out the UI later.

I prefer the former option since it seems like an easy UI addition that could be a longer-term solution until we get the "Server" concept ironed out for good.

The back end work for either option is feasible by our release branch cutoff date, as long as I don't get too bogged down in:

• demo data set reviews (this will require at least one more round on the data before moving on to integrating into Mathesar),
• Setting up collectors and doing back end work for the user research flow (non-trivial, if we decide to do this), or
  
• analytics docs (blocked by user research flow discussion outcomes)

I'm a little skeptical we should add more to the pile, but it's feasible if everything goes perfectly according to plan (which it obviously will).

Brent

[Sean Colsen]

• your expectation that “roles and passwords would be deleted” when removing the last DB on a server comes from your understanding of the actual DB structure which isn’t very well represented in the UI

• To be clear, my expectation comes from my recollection (perhaps incorrect!) of us having made an intentional decision as a group that this behavior would be the best way to strike a balance between various tradeoffs in user-facing goals.

• disconnect and reconnect it… Should this also remove all their passwords?

  Yes, my expectation was that all the stored passwords would be removed. I can see how this could be inconvenient. But I’m not convinced that it’s a problem worth spending a lot of time to solve right now. I’m guessing that most Mathesar installations will only have one stored role password per DB server. And renaming a database should be a rare operation. So I don’t think it’s that big of a deal.

• example DB… and moving to a “real” database on the same server? Should they be able to continue using their stored passwords?

  Sure

• What about the internal database server?… How shall we explain the discrepancy to users?

  I don’t think this discrepancy is worth attempting to explain at the moment.

• Given that we remove all Databases on a server in Mathesar, shouldn’t we also remove the configured server itself from our storage?

• Yes, this was my expectation too, albeit less important.

• make a super minimal UI element (maybe just a checkbox on the disconnect DB form) that lets a user know that they’re disconnecting the last DB on a server, and lets them choose to remove all Configured server data as well (the URL, the port, all roles, etc.). This would map to another parameter in the databases.configured.disconnect function, and the back end would handle things from there.

• I think this is a good idea! The front end work would be very minimal for me. Definitely feasible. If the backend work is feasible, then I’d be inclined to do this.

​

[Kriti Godey]

I'm on board with the checkbox for the disconnect DB form when the user is disconnecting the last DB. I just was on a call with Brent and confirmed that the backend work is minimal and Anish will prioritize it. If we can't get this done by Tuesday, we'll add the checkbox in the next release.

Let me know if you have any questions or thoughts.