There aren't a lot of benefits to doing it. If you have frequent migrations, then it probably isn't something you ever want to do.
For a site I run, I have one large shared read-only database everyone can access, and then one database per user.
The per-user DB isn't the most performant way of doing things, but it made it easier to:
+ Encrypt an entire user's data at rest using a key I can't reverse engineer. (The user's DB can only be accessed by the user whilst they're logged in.)
+ Securely delete a user's data once they delete their account. (A backup of their account is maintained for sixty days... But I can't decrypt it during that time. I can restore the account by request, but they still have to login to access it).
Can you elaborate on how you achieved encryption at rest with a key you can’t access? I’m assuming the key is sent in an authorization header, then lives in memory for the duration of the session, but wondering what your tool chain looks like.
The encoded key is sent, once, in a header, and then stored in a secure session cookie, that has a reasonable timeout on it, and is user-revokable, and is encrypted in memory server-side, unless it is being accessed.
(Setting up session cookies to only decrypt when being accessed sort of required reinventing the wheel, as that's apparently not something anyone goes to the effort of usually, and sends you down some optimisation paths around timing that will have you pulling out your hair).
User-revokable session cookies are simple enough - each user gets their own session cookie key, and they can roll that over from a settings page.
Worth noting: This is a great way to decimate your server performance, because most websites aren't constantly handling decryption.
The prototype was written for Flask [0], then rewritten for Bottle [1] when it was clear I wasn't using 90% of the Flask stack, and monkeypatching most of what I was using. Nowadays it's a strange mix of Hug [3] and Bottle.
But there's nothing there that's unique to Python or even the framework. It's easily doable in just about any language. I made three prototypes when I was coming up with this batty idea, the Flask prototype, one for vibe.d (D), and one for Go. I settled on Python for no particular reason. They all had similar performance, because encryption became the bottleneck.
Thanks for sharing, that’s an interesting approach. Does seem very hard to scale. Do they just set their key from a settings page and then off to the races? i.e. no login credentials?
Certificate file. Generated on registration and handed over as a download and shredded server-side. Not as trust-fulfilling as a user supplying one, but less of a learning curve. (Still need validation on it either way, which can be painful).
Which, of course, means "forgot my password" doesn't work.
For a site I run, I have one large shared read-only database everyone can access, and then one database per user.
The per-user DB isn't the most performant way of doing things, but it made it easier to:
+ Encrypt an entire user's data at rest using a key I can't reverse engineer. (The user's DB can only be accessed by the user whilst they're logged in.)
+ Securely delete a user's data once they delete their account. (A backup of their account is maintained for sixty days... But I can't decrypt it during that time. I can restore the account by request, but they still have to login to access it).
There are other, better, ways of doing the above.