Enable Full Read-Only Mode for Connection to Read-Only DBs (Such As Production Read-Replicas) in Mathesar

[arvindamirtaa]

Problem

I am interested in connecting Mathesar to a production read replica for our data analysis needs, but I'm encountering significant barriers due to current application requirements. Primarily, Mathesar's necessity to alter the database structure just to establish a read connection is excessively intrusive. This requirement conflicts with our policies aimed at minimizing changes to the production database to ensure maximum stability and security.

Additionally, the permission level of our reporting user does not allow for the installation of new schemas, a prerequisite for using Mathesar. This limitation prevents me from trialing the tool independently, as I would need team approval for schema creation before I can even begin testing. Without the ability to trial and test these schemas personally, I'm unable to demonstrate their potential value or functionality, which is crucial for advocating for Mathesar's broader adoption within my team.

Together, these issues form a significant barrier to adopting Mathesar in our environment, as the initial setup requirements do not align with our operational and security standards.

Proposed Solution

Implement a full read-only mode in Mathesar that allows it to connect to a production read replica without needing to install or modify any schemas. I am willing to accept a slight performance hit if necessary to enable this functionality.

Additional Context

The ability to operate in a read-only mode is critical for us as it aligns with our database management and security protocols. This feature would allow us to utilize Mathesar for reporting and analysis without altering the underlying database structure or permissions.

[kgodey]

Thanks for reporting this, @arvindamirtaa. We've had several discussions within the team about enabling this exact workflow, and are in the process of overhauling our permissions for our beta release.

@pavish @ghislaineguerin Tagging you here since we probably want to consider the UX of setting up Mathesar with a default read-only role (as well as other lower privilege roles) as part of the permissions work.

[mathemancer]

I strongly suggest using Postgres's FDW functionality to enable this, with Mathesar's user DB being installed on the internal DB. I don't think we'd do this by beta.

Edit: I did a quick experiment to see what our current level of support is for FDWs. It's not great, I'm afraid. However, we're overhauling some backend pieces in a way that may let us expose tables imported via an FDW for free. If that's the case, you'd be able to achieve the setup you're describing by wiring up the FDW yourself on Mathesar's internal DB, and then just using Mathesar on the internal database.