Users/Permissions Assumptions to move forward on architecture

[Brent Moran]

Hello!

My current assumptions are:

• A single Mathesar user should be able to access multiple databases, including across multiple Postgres servers or clusters.
• A single Mathesar user should not have access to multiple connections to the same database (at least until after Beta).
  
• Multiple Mathesar users can use the same connection to a given database.

@Kriti Godey This is slightly different from what we discussed, due to the first bullet point.

These assumptions are as minimal as I can make them, subject to being complete enough to move forward on some back end architecture decisions that are blocking performance improvements.

What do we think? Should I take these assumptions and run with them?

Brent

P.S. Not discussed is how we will input or define these mappings between users, databases, and connections. I'd need to know that at some point, but I can get at least some work done with just the three assumptions above.

[Pavish Kumar Ramani Gopal]

Hey Brent,

Assumptions 1 and 3 make sense to me. I have some questions on the second one.

> A single Mathesar user should not have access to multiple connections to the same database (at least until after Beta)

• Can the user (or admin) change the connection they are using?
• Would it no longer be possible to configure multiple connections to the same database? (We currently allow that)

I would like to understand all the restrictions this assumption brings w.r.t user flows.

[Brent Moran]

Good questions, I should have been clearer.

When I said "access to multiple connections", this is completely aside from managing connections, but rather about using a connection to connect to the database. "Access" was probably the wrong word. It would be possible for a user or admin to change which connection a user is attached to at the moment of actually connecting, but that would be a different call in the setup I'm imagining. The main thing would be that when a user calls for records from a table, we don't accept a "connection to use" at that point, but rather look up which connection to use based on the (user, database) pair.

Regarding configuring multiple connections to the same database, It's not disallowed, but I'm leaving aside how we'd handle setting a given user up with a given connection right now.

I don't think we have enough agreement on how to actually assign users to connections to proceed at this moment, so I'm trying to start from an assumption that we have a user who has some defined connection that they use when querying a given database so I can work on that part of things, while leaving the assignment step for later.

I hope that's clearer. Please let me know if I need to add more context. I'm trying to keep these assumptions as minimal as possible (i.e., I'm avoiding specifying anything that doesn't affect how this part of things will work in the back end) so there's a minimal set of things we might need to change later.

Brent

Brent

[Pavish Kumar Ramani Gopal]

Thanks for the explanation. This makes sense to me.

I'd like a clarification on this line.

> The main thing would be that when a user calls for records from a table, we don't accept a "connection to use" at that point, but rather look up which connection to use based on the (user, database) pair.

I assume that it'd be easy to modify the (user, database) pair to (user, project) or something similar. Mainly, the `database` should be swappable with a new object/concept we might come up with in our upcoming discussions. Is my assumption correct?

[Brent Moran]

 > I assume that it'd be easy to modify the (user, database) pair to (user, project) or something similar. Mainly, the `database` should be swappable with a new object/concept we might come up with in our upcoming discussions. Is my assumption correct?

In theory, yes. But I'd be inclined to add that as a layer on top of the (user, database) identifier (though an advantage of the RPC style is composability; any function involving a (user, project) pair could call a function using a (user, database) pair as long as there's one DB per project). I suppose any such concept would work as long as we can determine the connection to use based on the passed information.

Also, keep in mind that I'm only looking as far as the beta for this spec, while trying to maintain an extensible setup that we can adapt as we refine the product vision.

Brent

[Pavish Kumar Ramani Gopal]

Would the following setup be feasible with your proposed structure?

Each project has one DB. So, a (user, project) pair will reference a (user, database) pair. However, another project can also reference the same user and database. The credentials are tied to the project.

(user, projectA) -> (user, database): should utilize connection credentials A

(user, projectB) -> (user, database): should utilize connection credentials B

[Anish Umale]

> The main thing would be that when a user calls for records from a table, we don't accept a "connection to use" at that point, but rather look up which connection to use based on the (user, database) pair.

Assuming we are talking about a Mathesar user here, and not db user/role, it’s quite unclear to me how the (user, database) pair would help us identify which connection to use. 

With our current setup if we have multiple connections to the same db we can identify the connection to use using the nickname which is unique across the app. 

So, (msar_user, nickname) or Pavish’s proposed (msar_user, project name) makes more sense to me in terms of identifying a connection to use.

[Brent Moran]

@Anish Umale points out that I need to clarify some definitions. I'll try to use these moving forward (I think I have been so far):

• user: A Mathesar user, represented by some Mathesar-managed model.
• database: An actual database on a PostgreSQL server. Not the model, though we might represent it with a model.
• role: A role created on a database using "CREATE ROLE" or "CREATE USER".
• connection: A connection specification. This involves at least a role, a database, and a credential giving access to use that role on the server containing that database.
  

Please let me know if there are other definitions I need to clarify.

@Anish Umale Recall that I'm trying to check assumptions for the new architecture design, not slightly tune our current setup. In particular, don't assume we'll keep the same models. These assumptions are at the level of abstract mapping between users, databases, and connections, and aren't referring to any particular implementation. So being able to look up a connection from a (user, database) pair follows directly from the predicate "a given user has at most one connection available for a given database. 

@Pavish Kumar Ramani Gopal It would be possible to extend to being able to have different connections, one per project, or even a full many-to-many mapping between users and connections, but it's outside of the assumptions above. Do you realistically think it would be relevant by the time we put out the beta?

For context, one outcome that would logically follow from the 3 assumptions in the initial email (hopefully clarified by my second email), and the outcome I'd like, is that we would completely detach CRUD on connections from actually using those connections as credentials for a database. We could probably accomplish that goal through other sets of assumptions, but the 3 I listed are what (I had thought) we were closing in on, at least until the beta.

To refocus the discussion, I'll restate the assumptions, clarified as per other emails in the chain. These use the definitions from this email:

• A single Mathesar user could access multiple databases, including across multiple Postgres servers or clusters.
• A single Mathesar user will not choose between multiple connections to the same database when querying a database object (at least until after Beta).
  

• Multiple Mathesar users can use the same connection to a given database.

So far, I haven't actually seen anyone disagreeing with those assumptions at this point, assuming we don't implement projects, worksheets, etc. until after Beta. Is that correct?

Brent

[Anish Umale]

That makes sense Brent, thanks for clarifying. Also, I agree with your stated assumptions.

[Pavish Kumar Ramani Gopal]

> but the 3 I listed are what (I had thought) we were closing in on, at least until the beta.

> So far, I haven't actually seen anyone disagreeing with those assumptions at this point, assuming we don't implement projects, worksheets, etc. until after Beta. Is that correct?

I'm in disagreement with assumption 2.

We would have to solve the problem of permission handling in the UI and differentiating db roles with Mathesar access controls for the Beta. For this purpose, we might end up having to implement a leaner version of projects (or a different solution).

I assume that whatever solution we end up with will require us to have the option of a single Mathesar user using multiple connections to the same database when querying a database object.

[Brent Moran]

@Pavish Kumar Ramani Gopal

I see. Can we put that on the agenda for the meeting Tuesday, then? Maybe with example user flows that would involve a user having multiple connections they need to choose when querying?

[Kriti Godey]

I agree we should talk about this Tuesday, but for the sake of trying to make some progress before then: 

I assume @Pavish Kumar Ramani Gopal's concern is related to the conversation we had in a previous meeting (a few weeks ago) about potentially using database roles to implement role-based permissions. e.g. a user could have "marketing coordinator" role that give them access to the Blog Posts table and "office admin" role which would give them access to the Janitorial Supplies table. So if they're trying to access the Blog Posts table, we need to use the "marketing coordinator" DB role for that connection and if they're trying to access Janitorial Supplies, we need to use the "office admin" DB role.

Do I have this right? And if so, how do you see this working with the assumptions you're proposing, Brent?

[Brent Moran]

I'll state a couple of "product-level" principles w.r.t. permissions:

1. The permissions layer in Mathesar should manage access to Mathesar objects only.

2. The role a user uses on a database is an attribute of that user, not something they pick up at runtime.

These two principles minimize Mathesar-managed state, simplify implementation, and (crucially) simplify the thinking a human at the computer has to do when conducting their work. From that human's perspective, they just have some set of permissions, and they don't have to think about them while doing any other work (i.e., when they're not specifically modifying their permissions or having them modified by an admin).

As for the example scenario @Kriti Godey mentioned, that privilege inheritance can (and I believe should) be managed on the database using role inheritance. Insofar as we want to offer features in the Mathesar UI to enable that scenario without resorting to any other DB client for setting up the roles, I think those features should work on database roles rather than add a layer of permission inheritance and wrangling in the webservice. Otherwise, we're reimplementing features that already exist in PostgreSQL.

Brent

[Pavish Kumar Ramani Gopal]

> Maybe with example user flows that would involve a user having multiple connections they need to choose when querying?

In one of our previous discussions, I proposed the concept of being able to share connections and maintaining Mathesar objects at the same level of connections. The mail is here.

This concept would not be feasible with the product-level principle 2 mentioned above, i.e.: "The role a user uses on a database is an attribute of that user, not something they pick up at runtime".

We may or may not end up not proceeding with this concept after more discussions. My concern is that this principle would restrict us from considering such concepts in the first place.

> Scenario mentioned by Kriti

I agree with Brent on using role inheritance in the scenario mentioned by Kriti. My concern is not related to this specific scenario.

> Can we put that on the agenda for the meeting Tuesday, then?

Yes, I'll add it to the agenda.

[Brent Moran]

 @Pavish Kumar Ramani Gopal I'm taking Kriti's suggestion to try to make some progress between now and the meeting. As such, I've moved on from assumptions to "big-picture" product principles, and my most recent email is in that light. I'm not saying that I assume we are all in agreement on these principles, I'm proposing them.

Another definition I'll (try to) use going forward is:

• human: This is the actual person sitting at the computer using Mathesar. I want to avoid using user for this to enhance clarity.
• PG server: This is a PostgreSQL server (sometimes called a cluster in their documentation). It has databases and roles as top-level objects.
  

Regarding the linked email: While the notes are a bit sparse, I recall that in our most recent big-picture product meeting, there seemed to be an agreement that we in fact don't think we should guarantee that a user who has an exploration (or any other Mathesar-managed object) shared with them should have the exact same view of the underlying data supporting that object. That breaks a fundamental assumption of your linked email, and seems to render it obsolete. In particular, the idea of needing to be able to run a query with a particular connection attached to that query is a feature driven by that assumption (rather than a use-case scenario) and I don't see why it would be otherwise useful. I also don't recall having discussed why it would be otherwise useful. I think any use-case that we could support via features that blur the line between managing a connection as a Mathesar object, and using that connection to connect to a PG server should be instead supported through other means. 

My goal would be for the human to have a Mathesar user that has a particular role on a PG server, with the potential that their role could inherit privileges from other roles on that PG server. This is aligned with how PostgreSQL manages this concept, and any deviation should be carefully (and skeptically) considered. Otherwise, we basically force the human to think about the Mathesar connection inheritance and sharing layer as it controls privileges on database objects, as well as the PG server layer roles and their management. It's unnecessarily complicated for them and for us.

Brent

[Sean Colsen]

Here’s where I’m at on this thread:

• Generally, Brent’s assumptions sound good to me.

• Brent seems to be using these assumptions to construct a rough plan in his head for the backend architecture. I find this discussion of assumptions to be a little too abstract for me to confidently critique — so I’d prefer to critique that actual plan, even if somewhat nascent. A more concrete plan would be something I’d have an easier time trying to poke holes into. I’d be able to more easily form examples scenarios in real-world business-oriented terms. We’re all going to need to understand that backend architecture plan at some point, so I think now (or soon) might be a good time.

​

[Pavish Kumar Ramani Gopal]

Brent and I had a call to discuss this further on Monday.

TLDR: I'm good with moving forward with Brent's proposal.

Here's a brief summary:

• > there seemed to be an agreement that we in fact don't think we should guarantee that a user who has an exploration (or any other Mathesar-managed object) shared with them should have the exact same view of the underlying data supporting that object. That breaks a fundamental assumption of your linked email, and seems to render it obsolete.

• I offered more explanation on this response from Brent's previous mail:
• The shareable connections idea doesn't imply that each connection needs to be tied to a query. We could allow the human to choose the connection they already have, enter the connection credentials manually, or request them from the human that shared the query.
• This way of sharing connections and specifying a connection with each query is quite flexible, however, this flexibility comes with the cost of UX and architectural complexity. Mainly from the UX standpoint, the human would have to understand how connections tie to each object they create.
• While both of us didn't want to go in this direction now, my argument was that the architecture proposed by Brent would prevent us from moving in this direction now and in the future.
• Brent mentioned that the architecture is simplified, and it's also safer from a DBA point of view when connections are attributes of the user, rather than objects the user utilizes.
• Brent also mentioned that we would probably never require sharing connections and switching between them while running a query, for our use cases.
• I expressed a slight uneasiness that this might limit us in the long run, however, I'm okay with moving ahead with Brent's proposal.
• Further conversation from this point assumed that we'd be following Brent's proposal.
• We talked a bit more on possible scenarios where a human might have to utilize multiple connections in the same session.
  
• What if the human wants to view a table as a different role while sharing it?
• It's possible to use role inheritance on the DB to assume roles to do this.
• We might never require such a feature.
• How can we reasonably configure a multi-tenant setup in Mathesar, where the same human would access the same DB with different roles?
• We talked about various options and the project concept seemed the most reasonable way to do this.
• Multiple projects can utilize the same DB, and the roles with which the user utilizes them would vary based on the project.
• This would be feasible to do in the same user session.
• Each query could run in a transaction which specifies the role to utilize.
• Brent proposed that each project could have a role associated with it and this role could be inherited by the roles of the humans that have access to the project.
• I don't have a concern with the backend implementation, however, this will limit us on the UX.
• Only the admin will be able to create projects in this structure, since the user creating the project needs to have a role that has the 'CREATE ROLE' attribute on the DB.
  
• This will limit individual users from being able to create projects of their own.
  
• I did not agree with this specific proposal.
• Brent proposed more ideas such as allowing everyone to have a CREATE ROLE attribute.
• I was not in agreement with these.
• We also talked a bit about how/if the UX might get complicated with introducing concepts like role inheritance and moving a bunch of permission handling onto the DB.
• We still have to figure out how transparent the UI needs to be with respect to such actions, and how much internal functionality the user should have to know.
• I expressed that as long as the UX is straightforward and we're able to do these common operations from the Mathesar UI, I'm fine with any architectural decisions.

Brent, please feel free to mention anything I might have missed or misunderstood from our call.