Migrating multi-tenant saas app from PG10


#1

Hey guys,

First off all, thank you for all the hard work building Cockroach DB!

I’ve been evaluating moving from Postgres 10 to Cockroach DB, but came across a few issues/questions that I wasn’t able to find answers on that I hope you can advise on.

  1. How do I replace PG’s row-level security feature to avoid application errors / issues from leaking one tenants data to an other?
    A previous question last year pointed to https://github.com/cockroachdb/cockroach/issues/16978 as a way to track it - but it seems to refer more to partitioning than security. Any updates to adding row level security to CRDB?

  2. Notifications for micro-services
    One of my favourite PG features is LISTEN/NOTIFY. From what I’ve read, this is not possible in CRDB. It appears that change feeds MAY help, but I’m not sure since it is enterprise only and still in development?

Our use case is that we have a whole bunch of micro-services that need to act when there are certain changes made to a table. I use a combination of PG triggers, functions and LISTEN/NOTIFY to achieve this.

Say we have a table that I need to track changes on. I attach a trigger that calls a function to assemble a JSON payload of the change, and send it via the NOTIFY channel.

I have a service listening on that channel, which then publishes this change. This allows any service listening to capture that payload and decide whether it’s useful or not. This includes updating a cache, or kick starting a process.

How can I achieve something similar with CRDB? I’m assuming I’ll need to add some code on top of the database. I’d appreciate some guidance on where to start since it looks like triggers, functions and listen/notify are not supported.

Thanks again for your time!

Shahram


(Ron Arévalo) #2

Hi @shrumm,

Thanks for reaching out!

At the moment, we do not have a feature that is similar to Postgres 10’s row-level security. The partitioning feature you linked is unrelated to security. We usually tend to discourage multi-tenant workloads when the tenants have different security domains because our security was not designed to handle such a scenario. Ultimately what we would suggest is for the application(s) to handle and be fully responsible for security.

As for your second question, we have a feature called Change Data Capture (CDC) which I believe would be appropriate for your use case. We have the documentation for it here. But as you mentioned this is an enterprise only feature and it is also still in active development.

Thanks,

Ron


#3

Hey @ronarev!

I’m happy to build both functionality into an application layer, would love some advice on how to get started on both.

  1. My ideal solution to replace row level security would be to have an application that sits on top of CockRoach DB that all other apps connect to. Making sure every current and future application is compliant would be too much complexity. Even a simple layer that lets me validate an incoming query to ensure that queries on sensitive tables are always filtered by tenant_id.

https://github.com/awslabs/pgbouncer-rr-patch seems to have something like this - allowing apps to connect to it, and then allowing me to re-write / reject the query. I would rather return nothing at all to the application than return too much.

Any other approach you’d suggest?

  1. I can’t think of any other approach to solve this except for ensuring that only one service can modify a certain table, and then assigning the responsibility to announce data changes to that service.

Do let me know if you have any other tips/suggestions.

Keep up the awesome work