How do you isolate customer specific data?
I've been working on an app where I store financial data for my users. I've gone through a few iterations but have landed on RLS for partitioning the users data.
I considered using a schema per user, a database per user (probably would have used @SQLite ), or a layer in the ORM does some form of "roll your own rls" -- maybe appending a filter anytime the table has a user_id column.
It seems like @Supabase has popularized RLS and i'm wondering if thats what most people are doing these days?
Replies
RLS is the worst thing that you can use. I hate it. It's complicated, takes so much time to implement and one role error can lead to so much stress. You are better of chosing a normal postgres database and then encrypt and decrypt the information for the specific user with an encryption key. That's how I would do it if the data is very sensitive. But pls don't use RLS instead use RBAC in your auth layer. If you are looking for sqlite this also works fine, haven't dipped deeper into it yet
Tab Slayer
@niclas_pandey this is a pretty interesting take. so far ive had some annoyances with it for sure.
Some tables have RLS, others do not. you have to have more than one database user to manage various permissions (i.e a worker needs to be able to be an admin and see all of the data)
let me understand your model a bit more:
i get user A's data, i encrypt it with a key that i also save in the DB
i get user B's data, same thing -- encrypt with a key
the fact alone that they are encrypted with different keys makes it very hard (impossible i guess) to expose the other users data.
is that how you think about it?
Tab Slayer
@niclas_pandey also thanks for the reply, this is pretty insightful. im glad you posted up.
@catt_marroll that would be a solution. What i initially meant is that you have a random key that you encrypt and decrypt all your sensitive information with. So one env variable that is used to encrypt the data when it gets stored in the db. You can achieve this using the crypto module in Node.js for example.
If you use @Plaid to link accounts, do you actually store sensitive financial data or is it just a list of transactions? If it's just transactions, is there a need to implement RLS or something beyond encryption-at-rest and encryption-in-transit?
Tab Slayer
@steveb not using plaid yet, still just having people upload bank statements -- which are parsed + stored.
So the "sensitive" data is people transactions, no credentials.
RLS seems nice because you save yourself from having to write
on every query (which feels easy to forget with pretty bad outcomes).
I think the problem will be the same when I integrate with plaid, ill want to be pretty sure i never "cross-pollinate" peoples transaction logs
Tab Slayer
@rstankov do you use RLS at angry building?