3 ms·

Maybe I’m totally out of it, but creating an actual database user for each account of your application sounds like you can rely on database security and don’t r

Maybe I’m totally out of it, but creating an actual database user for each account of your application sounds like you can rely on database security and don’t run the risk of application bugs causing security vulnerabilities. This means a more complex database level of roles and privileges, which may be it’s own can of worms, but if you have to choose between problems to have, what would you select?

warent 3y ago
I don't think this literally means each user of your app gets their own DB user, rather that you create different db users for different aspects of your app. What you're describing is what RLS (row-level security) is for, where you log into a generic global "app_user" user with certain permissions that don't include things like admin tables etc, and then define the specific user that is using the session via session variables.
- louwrentius 3y ago
  I've seen blog posts decades ago from DB admins that actually advocated for a DB account per user account. They stated the DB was totally build for that, but people just don't know. They use what they understand, as in: app people may know too little about database security and privileges and just decide to solve it in code.
- patmorgan23 3y ago
  There are applications that are built to where literally every user gets a database login. For example Dynamics SL (formerly Solomon) is built this way with MS SQL server.
  - TheNewsIsHere 3y ago
    We use an application in my company that’s designed to run in the vendor cloud or on-prem. They’re pretty transparent that every cloud customer is running in their own specific database, and they’re just clustering all of those databases. The SaaS build of the app even exposes this in the URLs. Everyone is on the same codebase, and there’s just some additional database logic to connect the right deployments to the right databases.
_jal 3y ago
We are heavy postgres users, and we do both SOC2 and a more stringent audit demanded by our corporate parent. Lots of roles, lots of RLS, encrypted media, additional column-level encryption for some specific things for not great reasons). The postgres roles in particular were a recurring mess until we built provisioning that stuff in to our onboarding automation. If you are going to have individual named users, I highly recommend taking humans out of the process, we make too many mistakes. And yes, there are multiple advantages to enforcing access control as close to the data as possible. I tend to think of databases as "data structures that can defend themselves" - not just security, but type and data validation, relational references, etc. all can both save you from bugs and help you find them earlier.
- louwrentius 3y ago
  Yes, as an armchair "expert" that's what I was thinking. But managing swats of SQL: a developer friend said that this is difficult too. I don't know. But if I had to choose, I would make the SQL / DB side rock solid, even if it required some business logic in SQL.

warent 3y ago

I don't think this literally means each user of your app gets their own DB user, rather that you create different db users for different aspects of your app. What you're describing is what RLS (row-level security) is for, where you log into a generic global "app_user" user with certain permissions that don't include things like admin tables etc, and then define the specific user that is using the session via session variables.

louwrentius 3y ago
I've seen blog posts decades ago from DB admins that actually advocated for a DB account per user account. They stated the DB was totally build for that, but people just don't know. They use what they understand, as in: app people may know too little about database security and privileges and just decide to solve it in code.
patmorgan23 3y ago
There are applications that are built to where literally every user gets a database login. For example Dynamics SL (formerly Solomon) is built this way with MS SQL server.
- TheNewsIsHere 3y ago
  We use an application in my company that’s designed to run in the vendor cloud or on-prem. They’re pretty transparent that every cloud customer is running in their own specific database, and they’re just clustering all of those databases. The SaaS build of the app even exposes this in the URLs. Everyone is on the same codebase, and there’s just some additional database logic to connect the right deployments to the right databases.

_jal 3y ago

We are heavy postgres users, and we do both SOC2 and a more stringent audit demanded by our corporate parent. Lots of roles, lots of RLS, encrypted media, additional column-level encryption for some specific things for not great reasons). The postgres roles in particular were a recurring mess until we built provisioning that stuff in to our onboarding automation. If you are going to have individual named users, I highly recommend taking humans out of the process, we make too many mistakes. And yes, there are multiple advantages to enforcing access control as close to the data as possible. I tend to think of databases as "data structures that can defend themselves" - not just security, but type and data validation, relational references, etc. all can both save you from bugs and help you find them earlier.

louwrentius 3y ago
Yes, as an armchair "expert" that's what I was thinking. But managing swats of SQL: a developer friend said that this is difficult too. I don't know. But if I had to choose, I would make the SQL / DB side rock solid, even if it required some business logic in SQL.