2 ms·

> No, they aren’t. At least not Postgres Yes, Row Based Security exists. But not by default. It also requires you to have one database user per external user.

> No, they aren’t. At least not Postgres Yes, Row Based Security exists. But not by default. It also requires you to have one database user per external user. Something I (and most InfoSec professionals) wouldn't be keen on automating or managing, for fear of getting it wrong. [EDIT] The following was removed from the parent post. It was in response to "you can't audit queries". > No, you don’t Citation needed. Yelling "No, you don't" without anything else is fscking useless in moving a conversation forward. If the query is coming from the frontend, it's coming from a client that is outside of your control. I.E. you don't know what queries will come from the frontend, because the user can execute an arbitrary query. If you take an effort to extract and bake those queries into a backend (perhaps calling it a proxy), then you're not actually emitting SQL from the front end, you're relying on a backend to emit the actual SQL used after performing a number of security checks. AKA, the status quo.

dragonwriter 6y ago
> It also requires you to have one database user per external user. So? You are adding a database entry per external user, along with data identifying their roles/permissions in the app, one way or the other. You can either use custom code you’ve built on top of the DB engine to apply this, or you can use the far more battle-tested code in the database engine. I’m not sure why so many developers believe reinventing the wheel on database security is more effective than understanding their tools.
- falcolas 6y ago
  How many millions, or billions, of database users are you comfortable managing? 0 millions for me. I'll stick with the well tested and explored method of having a users table with foreign keys on the ID to other tables to identify data ownership. The idea of using one DB user per end user is, at best, novel and untested. We don't know where or how it will fail. Scaling would be a real bear too. And I'm fairly certain an InfoSec professional would have kittens if they saw that attempted in a production environment.
  - dragonwriter 6y ago
    > The idea of using one DB user per end user is, at best, novel and untested Its a technique older in continuous use with RDBMSs and more battle-tested than, say, the web itself, and plenty of enterprises (even the kind that have apps that not only don’t provided direct DB access to their frontend, but don’t even provide their backend access to tables or views but mediate all external access to the DB through sprocs) have it as a security norm and (correctly) view apps that manage end-user access outside of the database as taking a relatively novel, untested, and risky approach.
  - zerg2k 6y ago
    Looks like you have some knowledge hole. Let’s start with MSSQL you can use AD groups and those are the one and only thing that database setups are concerned with. On the AD side users get added/removed all the time. I have personally managed this kind of system with nearly “0” overhead on the DB side. Group assignment is the maintained by team managers decentralizing the whole thing. For non Kerberos Linux systems using LDAP to synchronize group membership is the only extra automation needed. It is possible and fairly easy to manage a very large number of accounts IF done properly.

dragonwriter 6y ago

> It also requires you to have one database user per external user. So? You are adding a database entry per external user, along with data identifying their roles/permissions in the app, one way or the other. You can either use custom code you’ve built on top of the DB engine to apply this, or you can use the far more battle-tested code in the database engine. I’m not sure why so many developers believe reinventing the wheel on database security is more effective than understanding their tools.

falcolas 6y ago
How many millions, or billions, of database users are you comfortable managing? 0 millions for me. I'll stick with the well tested and explored method of having a users table with foreign keys on the ID to other tables to identify data ownership. The idea of using one DB user per end user is, at best, novel and untested. We don't know where or how it will fail. Scaling would be a real bear too. And I'm fairly certain an InfoSec professional would have kittens if they saw that attempted in a production environment.
- dragonwriter 6y ago
  > The idea of using one DB user per end user is, at best, novel and untested Its a technique older in continuous use with RDBMSs and more battle-tested than, say, the web itself, and plenty of enterprises (even the kind that have apps that not only don’t provided direct DB access to their frontend, but don’t even provide their backend access to tables or views but mediate all external access to the DB through sprocs) have it as a security norm and (correctly) view apps that manage end-user access outside of the database as taking a relatively novel, untested, and risky approach.
- zerg2k 6y ago
  Looks like you have some knowledge hole. Let’s start with MSSQL you can use AD groups and those are the one and only thing that database setups are concerned with. On the AD side users get added/removed all the time. I have personally managed this kind of system with nearly “0” overhead on the DB side. Group assignment is the maintained by team managers decentralizing the whole thing. For non Kerberos Linux systems using LDAP to synchronize group membership is the only extra automation needed. It is possible and fairly easy to manage a very large number of accounts IF done properly.