Big picture meeting 2023-01-18: Permission handling by Shareable connections and Browsable DB objects

[Pavish Kumar Ramani Gopal]

Hello team,

In our next product big-picture meeting on 2023-01-18, I'd like to put forward an approach for permission handling: Shareable connections and Browsable DB objects.

The problem we intend to solve:

• We need to figure out a way to handle permissions in our app for both DB objects and Mathesar specific objects.
• All permissions for DB objects have to be handled by the database.
• All permissions for Mathesar specific objects have to be handled by our app.
• I'm not going into more detail since we've discussed this problem before and intend to solve it for the beta.

Previous discussions on the same problem:

• We've talked about the 'Projects/Workspaces' concept as a solution to deal with this problem.
• The outcome of our last discussion made it clear that the Projects concept was too broad and does not fully take granular permission handling of Mathesar specific objects into account.
• This discussion focuses solely on solving the Permissions problem. If this is agreed upon, we can think of whether it would make sense to integrate it with the Projects approach or to keep it separate.

An assumption this email makes:

• When a Mathesar user (A) shares an exploration to another Mathesar user (B), by default, the user viewing it (B) should get the same view as the user sharing it (A), even if (B) does not have explicit access to the underlying table.
• This also applies to when the user shares the object publicly.
• The user should also be able to modify this behaviour if needed.
• We'd first have to discuss this assumption before discussing the concept.

Proposed concept:

What do I mean by a connection in this proposal?

• A connection refers to details of a singular DB connection containing host, port, username, and password. It does not hold any other items like explorations.
• The connection object would be considered as a Mathesar specific object.
  
• A connection can contain only one DB username and password. If the user wants to add more, even if it's for the same DB, they will have to create new connection objects.
• All users can create connections. A user can only view and utilize a connection that they have access to.
• These connections can be shared to other users, granting them access to utilize them.

How would the UI be organized and object hierarchy work for Mathesar objects?

• All Mathesar specific objects (connections, explorations etc.,) would be at the same level and fall directly under the user. 
• For contrast, currently:
• Connections are at the highest level, and all Mathesar objects are placed under it.
• Users have a weird link to individual objects which decide the connections the user has access to.
• In this proposed concept:
• A user is at the highest level.
• Everything falls under the user including connections and Mathesar specific objects that the user creates.
• Mathesar objects will make use of connections instead of being under a connection.
• Since all these Mathesar objects are at the same level, the user can organize them by the DBs they use and sub-folders of their creation.
• We could attempt to introduce the Projects concept to help with organizing these objects.

How would the user view and use the DB objects?

• When the user clicks on a connection, they "open" it, and start browsing the database.
• We will make this visually distinct to the user in the UI.
• They will be presented with schemas, tables, and views.
• They can open the table and view it in a grid format, as we do now.
• The UX design, when we have it, will attempt to make it easier to switch connections while browsing the DB.
• If the user is viewing a table, they can switch the connections to view the same table with a different credential.
• The listed connections for switching would all belong to the same DB that the table belongs to.
• The user would only view the connections they have access to.
• Whether we organize these connections under the DB (or use the Project concept for it) is beyond the scope of this discussion.

How would the user create explorations?

• (For the sake of this discussion, we'll call them explorations. This concept can be adapted to work with new concepts we decide to introduce. eg., worksheets.)
• The user can choose to create an exploration while browsing the database. They can make changes to the table they're viewing and save it as an exploration.
  
• The user can also create an exploration at the top level, where they'll "choose" the connection and then choose the base table.
• The UX design would attempt to make accessing related explorations easier, but it will be made clear to the user that the explorations are saved at the top level, under the user.

How would Permission handling work?

• Permissions for all Mathesar specific objects (connections, explorations etc.,) will be handled by our app.
• Each object will have an owner.
• Whomever creates the object becomes its owner.
• Only the owner can edit them or delete them.
• The owner can move ownership of the object to another Mathesar user.
• All Mathesar users can create them, and share them to other users.
• When shared to another user, that user obtains 'usage' access.
• Users with 'usage' access level can make use of the object but cannot edit or delete it.
• We can introduce 'manage' or 'edit' access to allow more granular control.
• Ofcourse, this logic is entirely decided by us, and we can extend or modify it as needed.
  
• Permissions for DB objects will be based on which connection the user is opening inorder to browse the database.
• There would be clear visual distinction between browsing DB objects by opening connections, and working with Mathesar specific objects that utilize connections.
• This would make it easier for the user to understand that permissions work differently for both.

How would sharing work?

• When a user shares a connection to another user, the other user can open it and browse the DB.
• They will have all the access to the DB that the connection allows.
• They can then create explorations utilizing the connection which will be private to them.
• Sharing a connection will not share any exploration or other Mathesar specific object that the user has created.
• The user can choose to share a run-only exploration to another user without sharing the connection object.
• When they do this, we will internally utilize the connection to run the exploration.
• The second user will not see the connection object in the UI.
• The user can choose a different connection than the one they used while creating the exploration, while sharing.
• This will allow sharing the exploration to another user, only allowing a smaller set of DB privileges.
• The exploration object itself cannot be edited, but the result records can be edited.
• The user can still perform DML operations if the DB privileges of the connection allows it.
• This is assuming we allow DML in explorations.
• If the user wants to share the exploration and make it editable (as in editing the exploration definition), they will have to also share the connection object the exploration utilizes.
• The user can choose a different connection than the one they used while creating the exploration.
• All that's additionally required is that the user also shares a connection to the DB that the exploration makes use of.
• The user can also share DB objects like tables and views to other users.
• Internally, we will have to create an exploration (or query) and then share that exploration to the other users. This will work similar to the above points.
• We do not have to showcase this in the same way to the user. We can make such explorations visually distinct and call them 'shares' or by another name.
• On the backend, deciding the connection to use for handling permissions for Mathesar specific objects would be based on the user sharing the objects.
• Since this is explicitly made clear to the user, we do not have to "guess" what connection to use.
• The result view to the shared user will also not have any surprises.
• Public sharing will work in a similar manner, except we'll generate a permalink which will contain the shared object info and the connection info to use.

What about table settings?

• Table settings are a weird middle state. These are essentially properties of the table, but configured for Mathesar within Mathesar.
• My proposal is to let it work the same way it works now. They are tied to the connection+table.
• If the user has access to the table, they have access to these settings.
• The access level to these settings will depend on the DB access level that the connection provides.
• As we implement this concept, we will have to rethink what DB objects and Mathesar objects mean. I'm proposing that:
• We call all objects whose permissions are handled the DB as DB objects.
• All objects whose permissions are handled by our app as Mathesar objects.
• By this definition, table settings will be considered as DB objects.

In the upcoming meeting, we'll try to poke holes into this concept, see if it'll work well with other concepts we've been discussing, and try to brainstorm more on it.

- Pavish

[Sean Colsen]

This is all very interesting stuff!!

• I agree with your assumption here:

• When a Mathesar user (A) shares an exploration to another Mathesar user (B), by default, the user viewing it (B) should get the same view as the user sharing it (A), even if (B) does not have explicit access to the underlying table.

• Lifting explorations up to be stored alongside connections does not seem intuitive to me, but maybe I’m too ingrained in our current approach.

  What do we gain by putting explorations alongside connections? What problems to you foresee with keeping explorations within schemas (within connections)?

  In theory, would it be possible for a user to create an exploration that utilizes multiple connections simultaneously? If so, then putting explorations alongside connections would seem more intuitive.

• In your vision, would it possible for a team of users to “own” connections (and explorations)? I think features that facilitate shared ownership is important because they serve to mirror business structures. Pretend we moved our CRM into our internal Mathesar installation. What user would own the connection to that DB? If we are forced to pick one of us, then we have a bus factor problem.

​

[Pavish Kumar Ramani Gopal]

> What do we gain by putting explorations alongside connections? What problems to you foresee with keeping explorations within schemas (within connections)?

The permissions for all the items hierarchically viewed within connections (i.e. DB objects including schemas, tables, views etc.,) are handled by the database. If the pg role used in the connection has access to a schema, the user can view the schema when opening said connection.

The permissions for explorations however, are handled by our application's service layer. Keeping it at the same level as connections provides a clear separation of concerns, since the permissions for the connection object itself are also handled by our service layer.

If we keep them within schemas inside a connection, it introduces complexity and user confusion such as:

• Is the access control for exploration handled by the connection's pg role or by Mathesar? If the connection does not have access to the schema, can the Mathesar user still have access to the exploration?
• Individual DB objects are not shared directly by the app. Access control is handled by the DB and we only share connections. Explorations, however, need to be shared independently.

The core idea is that the hierarchy should be based on how permissions are handled. All objects that check DB level permissions are viewed while opening a connection are hierarchically placed within a connection, and all objects that check our service level permissions are placed at the same top level as the connections.

 > In theory, would it be possible for a user to create an exploration that utilizes multiple connections simultaneously? 

Not simultaneously, but yes, a single exploration can utilize multiple connections. The user can choose to run an exploration using different connections that have different DB level privileges.

Also, while sharing an exploration, the user can choose which connection the share should utilize.

For eg.,

• I create an exploration to view the patents table. The connection I use has superuser privileges to the database, so I can view all rows and columns.
• On the DB, there exists a different role that prevents access to sensitive columns, and has RLS implemented to prevent access to sensitive rows.
• There is a different connection configured in Mathesar that utilizes this DB role.
• While sharing the exploration to another user, I can choose to share it such that the shared user will view it with the restricted DB role, while I can continue to use the exploration with my superuser role.

> In your vision, would it be possible for a team of users to “own” connections (and explorations)?

Yes.

I've limited my email to discuss only the permission handling part between DB objects and Mathesar objects. I'd like to focus on this root problem first.

Once we solve that, we will discuss the team aspect (or Projects) to solve object organization and collaboration.