Does Mathesar support views or row level security

[sai-sy]

While judging Mathesar's viability for our use case, we need to provision access to certain subsets of data. Does Mathesar work with PG row level security naturally or with views? I don't see any way to manage RLS from within Mathesar but if we can set RLS through PG and then "collaborators" who have db connect, shchema read, and table read permissions woudl then have only certain row read/change persmissions would be fine. If not then does Mathesar work with views?

[zackkrida]

Does Mathesar work with PG row level security naturally?

Hi @sai-sy! Yes, Mathesar automatically respects row-level security. A Mathesar user can only access data if:

• They’re a collaborator on the database, and
• The database role assigned to the collaborator has permission to view the relevant rows.

One thing to watch out for: superusers are always exempt from row-level security. That’s tripped me up a few times in the past. 😊

As for views, we don’t support them yet. There are some related issues (#4235, #2281) where we’ve started exploring how they might be displayed, but we don’t have concrete plans at the moment. Would you find views useful beyond security/access control? I’d love to hear more about your use case.

[sai-sy]

I think what could be helpful is making that more clear in the documentation. The tables page does say "ability to alter tables may be limited by access control" but that doesn't mean the ability to view will also be limited.

I'm more technical but assumed (maybe a bit stupidly) that provisioning database, schema, and table access to a role would then provide the specific permissions to that user. It's kind of obvious when I think about the layers of mathesar but it's also the kind of thing you don't need to think about when normally managing permissions for a software and a non technical person wouldn't figure out. Explicitly saying in the "User" guide that to give someone access to a table you need to share the database, schema, and table to them through both mathesar and then also through the database feels like it'd be a big improvement for users.

[sai-sy]

I think with RLS view's aren't super necessary since you can give someone access to specific subsets of the tables anyways. I think more explicit documentation and examples and guides on roles and access management would be nice in the docs (maybe a thorough example) but from a technical feature perspective I think RLS fills the rolls of views well enough.

Once place where views might be helpful is in tabular data analysis. If I want a query to join a bunch of tables then aggregate that data into a table that's saved so it can act as a BI style data summary. I can definitely see see us using that. I think airtable has something similar

[sai-sy]

Another possible usecase of ours (and maybe others) for views I just stumbled on right now is that we use certain technical names for columns that aren't super helpful to front end users. e.g. we use "division" for address data to generalize "state", "province", and "prefecture". But for a front end user a view could let them see the columns in more useful name

[sai-sy]

View's could also just be the underlying arch for data explorer