UX question about DB connections

[Brent Moran]

Hello,

This is probably most relevant to Ghislaine and Kriti. We have a concept of "connecting to a database" in our UI that is not quite precise.

The current UI makes it look like a Mathesar user (i.e., one that a user logs into via the Mathesar interface) connects to a given database (e.g., mathesar_tables in our default dev setup). However, what actually happens is that a given Mathesar user uses a selected database user to connect to the chosen database. This is relevant because different database users may have different permissions, and if a given Mathesar user has access to more than one database user for a given database, confusion may ensue. In fact, I suspect this exposition has already been a bit confusing.

I've spoken with a couple of devs about how to handle this, but I think most of the actual difficult problems and questions are product and UX related.

Should a Mathesar user have access to multiple DB users for the same database?

I think yes, since it may be that an admin wants to downgrade their own permissions on a given DB for daily use. Or, an admin may want to temporarily grant a Mathesar user access to a DB user with higher privileges for some task or another. It may be that some database user wants to share their DB credentials with a Mathesar user to facilitate access to some table or another. Etc.

If a Mathesar user has access to more than one DB user, how should we model that?

My instinct is to go with the tried and true brent@mydb style. I could be logged into Mathesar's UI as alice , but see brent@mydb and bob@mydb in the "connections" panel. This may be kind of irritating or confusing, however, since the access that a given Mathesar user has to a table will be dependent on which connection they're using. Our current model of tables being under schemas being under databases doesn't quite translate. Changing the user you're using to connect to the database is a different concept than changing the database entirely. For example, I'd like to be able to change the database user I'm using for connecting from within a table page when the table won't let me write, rather than navigating up to choose a new database and then back down to the table as if it were a totally separate thing.

What if they have access to more than one user on more than one DB?

Ugh. This gets ugly, and probably confusing. Design help needed.

What if we only want a Mathesar user to have a single DB user available?

We'd still need to think a bit about design for admins.

The big picture is that if we let a Mathesar user access more than one DB connection string for a given database, we need to figure out a UX concept for that and also work out the relevant design. Even if we don't want to allow that, we need some design work to help admins diagnose permissions issues without having the relevant DB user visible from the UI (with our current setup).

[Ghislaine Guerin]

I agree that we should let Mathesar users connect to multiple database users within the same database. If we don't, we'd be limiting a key feature related to database access control. However, this could also make managing permissions in Mathesar almost too complex for implementation, wouldn't it?

I assume we're not considering the option to add different users as separate connections because that would disassociate any explorations and settings from the database, correct? In the case that we do want to implement this, we could include a navigation control in the top bar for global access. This control could list all users, and if a user belongs to a different database than the current one, clicking on it would navigate to that database's page. However, we can resolve all of this once we determine how to integrate permissions management with the concept of multiple database users.

[Kriti Godey]

This concern makes sense to me, although I'm somewhat cautious about putting a bunch of effort into a problem that no one has complained about yet.

[Brent Moran]

I'm far more worried about the complexity of the user presentation than the implementation in this case. For example, we could implement this by having a DBConnection model, with each instance being a connection string. Then a given Mathesar user would have to pass an access check to use a given connection string. I don't see any real huge problems there; It's actually simpler than our current access policies for the Database model.

I assume we're not considering the option to add different users as separate connections because that would disassociate any explorations and settings from the database, correct?

I don't understand what you mean by this. Can you clarify?

[Ghislaine Guerin]

Say I connect to a database with a user and credentials. Then I add the same database but with a different user. So I have two connections. Even if it’s the same DB, whatever explorations I’ve created would exist under one specific db connection. 

[Brent Moran]

Say I connect to a database with a user and credentials. Then I add the same database but with a different user. So I have two connections. Even if it’s the same DB, whatever explorations I’ve created would exist under one specific db connection.

Now I understand. Great question.

I've been thinking of explorations as being owned by ("under") a Mathesar user (rather than a DB connection or DB user). If a DB connection or user is attached to an exploration, it would be as an attribute. I.e., the DB connection is an attribute of an exploration and can be changed by modifying that exploration. This gets at the central point of why I think the current concept we're using is insufficient, and how I think we need to modify it. Schemas, tables, explorations, etc. can be modeled as "under" a database in some hierarchy, but that breaks down when considering a connection to a database, since the objects (or at least accessible objects) for a database change based on who's asking.

Three options are readily apparent (please try to think of these as conceptual options for a user to understand rather than implementations):

1. Attach a connection string to an exploration, with permission inheritance

This would be done by a user with access to that connection string when they create or modify an exploration. This means anyone with "viewer" or higher permissions on an exploration would be allowed to inherit the use of the underlying connection string. This is an extension of our current "shareable links" concept.

2. Attach a connection string to an exploration, without permission inheritance

This would also be done when creating or modifying an exploration. This means that someone else with "viewer" permissions to the exploration would need to also have access to the exploration's connection string in order to run it. If they have "editor" or "manager" permissions for that exploration, they could modify it to use a different connection string to which they have access.

3. Don't attach a connection string to an exploration

In this case, when a user tries to run an exploration, it would just run against their currently configured DB connection. This is, of course, the easiest to implement, but seems like it would be irritating since you'd always have to remember (or figure out) which of the connection strings you can access works for running the exploration in question.

I'm currently leaning towards 3 as a first iteration and 2 after that for security reasons. Shareable links seem sufficient to handle most use cases that 1 would specifically enable.

[Pavish Kumar Ramani Gopal]

We have a call to discuss this today, and I wanted to send a mail with my initial set of opinions before the call. 

Here's some terminologies I'm using:

DB object:

• Refers to actual objects created on the user database. (eg., schemas, tables, columns etc.,)
• They may be represented in the UI or completely hidden from the user.

Internal object:

• Refers to objects created in our internal Django DB (eg., explorations).

UI object:

• Refers to objects being shown on the UI (eg., database connections, the schemas, record page etc.,).
• They are usually linked to underlying DB or Internal objects.
• These are very real to the user and affect the way the user sees the app, even if they do not accurately represent what happens underneath.
• They have additional properties that affect the UI and UX (eg., disabled states for DB connections, schemas that are locked etc.,)

> The current UI makes it look like a Mathesar user (i.e., one that a user logs into via the Mathesar interface) connects to a given database (e.g., mathesar_tables in our default dev setup). However, what actually happens is that a given Mathesar user uses a selected database user to connect to the chosen database. This is relevant because different database users may have different permissions, and if a given Mathesar user has access to more than one database user for a given database, confusion may ensue. In fact, I suspect this exposition has already been a bit confusing.

I'm curious to hear further thoughts on how the current UI would confuse the user.

I find the current UX pretty straight forward, where the Mathesar user enters the database information and the DB credentials, inorder to create a connection. From the user perspective, each connection is a different UI object (even if the user connects to the same DB with another DB user), and each have their own set of user defined Internal objects such as explorations.

I understand this is not ideal when it comes to use cases where people may want to use the same Database connection UI object containing all the user defined Internal objects (explorations, forms etc.,), but with different DB users. For eg., an admin creates a table and explorations and wants other users to be able to login to Mathesar but only be able to view them. They want to restrict this behaviour at the DB level, not just the UI level.

However, I think the current UX makes it clear that this feature is not supported via the UI at the moment. I don't see the exact problems we are trying to solve for the user from your questions, that are caused by the "connecting to the database" UI.

I do think we should be figuring out permissions and how we integrate Postgres users with Mathesar users, in a more holistic way. I consider this a product wide problem that needs to be discussed separately from the "connecting to the database" UI/feature.

[Brent Moran]

To start, because it may not have been clear: This email thread isn't intended to make decisions about the UI we need to implement for 0.1.4, other than I'd like it if we were moving towards, rather than away from, a coherent end-goal vision of how users should think about connections and permissions. This email thread is rather about that end-goal architectural vision, and trying to figure out a coherent end-goal UX / conceptual vision that makes sense and is sustainable in the long-term.

To answer your question, Pavish, the confusion would come in based on the fact that our current conceptual model and the resulting UX/UI make it seem like there are multiple copies of DB objects when there's actually one, since in reality a DB object is an attribute of a database, not a database connection. Even worse, depending on what we do about showing (or not showing) DB objects that a user can't select from or otherwise access, you might simultaneously have some tables that are in a given database, but only show up if you happen to be using the correct underlying connection. So, the conceptual framework of databases containing schemas containing tables containing columns breaks down even in our current UI and can't be maintained consistently. Regarding explorations and other internal objects, attaching these as attributes of connections (via inheritance) rather than having connections attached to them as attributes is basically upside-down in a confusing way. To put a point on it, an exploration uses a connection to run. A connection doesn't "have" explorations; that doesn't make conceptual sense.

Further, since an exploration isn't actually in a database at all, modeling it as though it is sets up a conceptual framework that constrains UX options (e.g., switching which connection you're using for an exploration when you realize you need a role with access to some other table or schema).

[Dominykas Mostauskis]

I'll try and distill the problem as I understand it.

# We never know what database we're connecting to

"Connection strings/credentials" can connect you to arbitrary databases: we don't control or know what db we get connected to: an SQL proxy or connection pooler might cause multiple connection strings to connect to the same database.

And, what db a given connection string connects us to can change at any time: a database is renamed, the port is changed, the host is changed, etc.

# We want Mathesar-stored data and state to be associated with a database, not connection string

Above contrasts with our Mathesar-stored data (display options, explorations, etc) and low-level state: we want it associated with actual databases, not connection strings/credentials.

# User needs to have ultimate control over what database a given connection string is connected to

We can make guesses, but we can't infer those associations reliably.

# We could improve inference by storing a UID on each database.

But, this wouldn't be totally reliable either, because the UID would survive cloning of databases, or could be arbitrarily modified by a third-party. This could just provide better defaults/suggestions.

Am I missing anything?

[Dominykas Mostauskis]

Sorry if this is the wrong thread, there's a few similar threads.