3 ms·
Ask HN: Handle customer DB access in SaaS
when providing SaaS how do you handle (big) customers that requests access to their data for BI applications?
most BI solutions like power bi struggles hard to obtain data from HTTP-APIs as they are paged, aggregated in other ways, authenticated with OAuth and so on.
integrations are very easy if access to a sql layer would be given instead.
But of course direct database access is also a struggle if using a multi tenant environment which would need at least some kind of row-level-security and a own read-replica. Also the customer needs to know every detail of the database schema which makes internal changes harder again and is an unnecessary detail.
Is there any other way then filling a second database event based with the correct „business-based“ schema (like a cache) and provide direct access to this? Is there any option to provide an „sql“ api to the application architecture?
- bombcar 4y agoIf they're that big can you provision their "own" copy of your infrastructure or at least the database? And then foist handling database changes off on them.
- cyptus 4y agoNot that big as the costs for an own database or complete copy would suffice, including administrative expense
- bombcar 4y agoOffer it as a feature only available if they pay $(3-5x estimated costs). If they bite, wahooooo!
- cyptus 4y agowhich makes the cheaper approach by filling a second database by events and providing access even better, just want to check if i didn’t miss any simple option as for sure these kind of requests are quite common in b2b
- bombcar 4y agoPostgres offers some views and filters by account or something if I recall, but I don't know anything about it (and I'd still be skeeved out by giving someone raw database access when the system hadn't been designed from the ground up for it). https://www.postgresql.org/docs/current/ddl-rowsecurity.html https://www.postgresql.org/docs/current/ddl-rowsecurity.html
- Kepouick 4y agoIndeed this looks a lot like a custom request
- mooreds 4y agoIn rough order of effort (and inverse order of intrusiveness to your environment): You could use a view (or views) and a read-only user. The view could limit access to a single tenant and also abstract away changes to the schema if needed. If you want more separation, you could use a read replica, again with a view. You could also build an of ETL workflow to extract the tenants data, transform it if needed, and push it somewhere else. Finally, make sure they pay for this. I'd scope out what they need and quote a price before doing anything except exploratory work. We have an internal feature (we've yet to build) to offer a nightly database export of customers' data. We'll just send it to an s3 bucket they provide and let them take the data how they want. But this is a feature that will be restricted to our enterprise plans, most likely.
- cyptus 4y agothank you, for sure this will be an enterprise feature
- pkrotich 4y agoI would not add such access to your turnkey SAAS instrastructure. Perhaps you can offer a read-replica as an add-on but the headache will be massive when it comes to CI/CD with little ROI for most of your customers. This is an opportunity, I think, for you to sell an Enterprise Appliance version of your application at 1000X the price. Such whales (customers willing to pay for custom BI) can host it internally, in a private-cloud or as a manged private-cluster (copy). If you go add-on replica route - then perhaps replicating data to Analytic Database with nosql type storage format will be ideal in solving schema problems and decoupling the access.
- cyptus 4y agothank you! these customers are not ready for an self-hosted app with this pricetag, i am searching for kind of an way in between these solutions. i think we are on the right way now.
- _448 4y agoCouchDB would be ideal for this use case. Export your data to CouchDB on a per-tennant basis and use inbuilt access control.