8 ms·

> DBs have auth systems already, but we mostly bypass those and reimplement auth in the stateless layer. This is sane and good design. The db auth layer is for

by okket 10y ago

> DBs have auth systems already, but we mostly bypass those and reimplement auth in the stateless layer. This is sane and good design. The db auth layer is for db management, not frontend auth (are you seriously advocating this?). > DBs have programming capabilities but we mostly bypass that and build business logic in the stateless layer. This is just (initial) design / programmer laziness. I bet if you start to scale up, you will think a lot about moving your business logic further to the real data for safety and for performance reasons. This is the point when you realise that db systems are very different and switching from one to another is not that easy...

fragmede 10y ago
> The db auth layer is for db management, not frontend auth Yes, but why? The database already has a table of database users, and the different permissions they're allowed. (Well, hopefully your webapp doesn't just do everything as the root DB user with * privs.) Is there that much to be gained from the database having a second table of users that you've created, and then you get to reimplement a bunch of logic in your app server on top of the database so that you have... users and permissions?
- moron4hire 10y ago
  Because traditionally the database was managed by IT, not by engineering. IT came up with the idea that it should take forever to approve changes to the database, so engineering invented ways around their can-don't attitude. Can you imagine any CTO approving a web-based signup page being allowed to change the users to the database?!
  - mixmastamyk 10y ago
    If it only adds restricted users is that a big problem?
    - moron4hire 10y ago
      The problem is not an issue of technology.
  - bluGill 10y ago
    Note that IT has good reason for the complex change rules: if they make ANY change to the data base, everyone needs to change. The change itself is simple, but in many cases thousands of programs access the database. If taxes don't go out on time because the program that runs once a year at tax time didn't get updated - the best case is the CTO goes to prison, things get worse quickly. When prison is a very real risk of approving a database change would you approve it?
    - moron4hire 10y ago
      Nobody ever went to jail because a software defect made their tax filing late. Don't be ridiculous. It's this sort of over-aggrandizement of IT's supposed responsibility that is the cause of the problem. IT acts like they are a thin, red line single-handedly holding compliance and hackers at bay. The god complex needs to end.
      - bluGill 10y ago
        IT is still a risk. High level executives can go to prison for some failures, and tax issues are on the list. It isn't clear if courts will accept software issues as an excuse.
    - dragonwriter 10y ago
      > Note that IT has good reason for the complex change rules: if they make ANY change to the data base, everyone needs to change. Only if the DB is used by multiple apps and not designed correctly for that use (e.g., all apps use direct access to base tables rather than app-specific views.) Unfortunately, this is more common than it should be, but the solution is to stop doing that.
- taeric 10y ago
  You gain making users of your system a first class and distinct part of your application from those that maintain it. Or, would you rather swap a regular user entry into and away from a maintenance role when you hire/fire someone? (Certainly doable, but seems extreme.) You also gain the ability to severely lock down destructive permissions to the database behind fairly rigid authentication rules.
  - dpark 10y ago
    > Or, would you rather swap a regular user entry into and away from a maintenance role when you hire/fire someone? (Certainly doable, but seems extreme.) How is this more extreme or more cumbersome than deleting an admin account? For that matter, you could also just give admins a separate admin account even if they are also users. When I worked in "IT support", I had a separate admin account even though I had a user account in the same system.
    - taeric 10y ago
      You probably already have a separate system that is regionally and logically separated from your application to control access to company resources. The argument seems to be that the logical separation is not desired. But that implies that if I were an employee of Google, I should have a specially flagged Google account. And if I were to leave, I would lose that flag. Which means it should probably not be a simple flag, anymore, but a date gated property which may go off an on several times. And already, that sounds more complicated than just keeping your application accounts distinct and completely separate from your organizations accounts.
      - dpark 10y ago
        > You probably already have a separate system that is regionally and logically separated from your application to control access to company resources. The separate system your company's employees are tracked is is also likely not integrated with your app database's auth system. I kind of doubt that the various Postgres/MySQL/Whatever databases at Google have accounts in them for all of Google's employees who might need access. Instead they build more tooling on top of the same accounts the app uses to access the DB. Or maybe they create JIT accounts for admin functionality. As it stands today, though, most places don't use the DB auth at all really. > But that implies that if I were an employee of Google, I should have a specially flagged Google account. No. You have two accounts if you work at Google. Your personal account is not your corp account. That doesn't mean your corp account doesn't exist in the same systems as your personal account, though. > And already, that sounds more complicated than just keeping your application accounts distinct and completely separate from your organizations accounts. Maintaining two entirely separate auth systems does not sound less complicated to me.
jasonkostempski 10y ago
> we mostly bypass that and build business logic in the stateless layer. > This is just (initial) design / programmer laziness. I disagree, it's just good practice. I've never seen a system evolve towards SQL based business logic, only away from it. SQL is meant for storage, that's it, obviously there needs to be a coding layer to move data in and out of it efficiently but that's where it should end.
- AgentK20 10y ago
  That's what this article is arguing against. The "good practice" is more precedent than anything. Sure, we can argue that it "adds security" to the mix by adding a separate layer between the user and the actual place where billing info and passwords are stored, but if that front layer gets breached then you're still screwed. From what I understand, the article suggests the reverse of what you're saying. Not necessarily moving the business logic _into SQL_, but what if the DB actually had a very extensible framework and libraries that let you serve HTTP _directly_ from the DB? How would that change reactive websites whose sole "backend" is a SQL<->JSON+Permissions system? What about if the DB itself supported a psuedo-PHP runtime, or better yet closer to native code? Granted, I'm not saying it's a good idea. I do see a vast array of benefits, primarily in the sense of scalability, from separating the business logic from the DB, but it's an interesting topic to ponder nonetheless.
  - vog 10y ago
    > What about if the DB itself supported a psuedo-PHP runtime, or better yet closer to native code? Note that in PostgreSQL, user defined functions can be written in many languages, including SQL but also Python (very well supported) and PHP (not so well supported, but doable).
    - josephg 10y ago
      Post author here. I actually think it would be much cleaner to support an event log API and run this stuff out-of-process. My database shouldn't have to support the language-de-jour, and I don't want my react rendering process slowing down my database servers. You can totally decouple this stuff with a clean kafka-like eventlog API, but postgres doesn't provide one.
vog 10y ago
> This is sane and good design. The db auth layer is for db management, not frontend auth (are you seriously advocating this?). The auth layer of PostgreSQL is very advanced, up to row level security if you need that. [1] It was never intended to be "just for administration", quite the contrary! Moreover, some applications rely exclusively on a central PostgreSQL service rather than a central web service, such as "Artikel23" which is (was?) a CRM whose user management, encryption and security stuff is 100% handled by PostgreSQL. [1] https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html https://www.postgresql.org/docs/current/static/ddl-rowsecuri...
omginternets 10y ago
I think his argument is that it's "sane and good design" for a problem that now only partially reflects reality on the ground.

by okket 10y ago

fragmede 10y ago

> The db auth layer is for db management, not frontend auth Yes, but why? The database already has a table of database users, and the different permissions they're allowed. (Well, hopefully your webapp doesn't just do everything as the root DB user with * privs.) Is there that much to be gained from the database having a second table of users that you've created, and then you get to reimplement a bunch of logic in your app server on top of the database so that you have... users and permissions?

moron4hire 10y ago
Because traditionally the database was managed by IT, not by engineering. IT came up with the idea that it should take forever to approve changes to the database, so engineering invented ways around their can-don't attitude. Can you imagine any CTO approving a web-based signup page being allowed to change the users to the database?!
- mixmastamyk 10y ago
  If it only adds restricted users is that a big problem?
  - moron4hire 10y ago
    The problem is not an issue of technology.
- bluGill 10y ago
  Note that IT has good reason for the complex change rules: if they make ANY change to the data base, everyone needs to change. The change itself is simple, but in many cases thousands of programs access the database. If taxes don't go out on time because the program that runs once a year at tax time didn't get updated - the best case is the CTO goes to prison, things get worse quickly. When prison is a very real risk of approving a database change would you approve it?
  - moron4hire 10y ago
    Nobody ever went to jail because a software defect made their tax filing late. Don't be ridiculous. It's this sort of over-aggrandizement of IT's supposed responsibility that is the cause of the problem. IT acts like they are a thin, red line single-handedly holding compliance and hackers at bay. The god complex needs to end.
    - bluGill 10y ago
      IT is still a risk. High level executives can go to prison for some failures, and tax issues are on the list. It isn't clear if courts will accept software issues as an excuse.
  - dragonwriter 10y ago
    > Note that IT has good reason for the complex change rules: if they make ANY change to the data base, everyone needs to change. Only if the DB is used by multiple apps and not designed correctly for that use (e.g., all apps use direct access to base tables rather than app-specific views.) Unfortunately, this is more common than it should be, but the solution is to stop doing that.
taeric 10y ago
You gain making users of your system a first class and distinct part of your application from those that maintain it. Or, would you rather swap a regular user entry into and away from a maintenance role when you hire/fire someone? (Certainly doable, but seems extreme.) You also gain the ability to severely lock down destructive permissions to the database behind fairly rigid authentication rules.
- dpark 10y ago
  > Or, would you rather swap a regular user entry into and away from a maintenance role when you hire/fire someone? (Certainly doable, but seems extreme.) How is this more extreme or more cumbersome than deleting an admin account? For that matter, you could also just give admins a separate admin account even if they are also users. When I worked in "IT support", I had a separate admin account even though I had a user account in the same system.
  - taeric 10y ago
    You probably already have a separate system that is regionally and logically separated from your application to control access to company resources. The argument seems to be that the logical separation is not desired. But that implies that if I were an employee of Google, I should have a specially flagged Google account. And if I were to leave, I would lose that flag. Which means it should probably not be a simple flag, anymore, but a date gated property which may go off an on several times. And already, that sounds more complicated than just keeping your application accounts distinct and completely separate from your organizations accounts.
    - dpark 10y ago
      > You probably already have a separate system that is regionally and logically separated from your application to control access to company resources. The separate system your company's employees are tracked is is also likely not integrated with your app database's auth system. I kind of doubt that the various Postgres/MySQL/Whatever databases at Google have accounts in them for all of Google's employees who might need access. Instead they build more tooling on top of the same accounts the app uses to access the DB. Or maybe they create JIT accounts for admin functionality. As it stands today, though, most places don't use the DB auth at all really. > But that implies that if I were an employee of Google, I should have a specially flagged Google account. No. You have two accounts if you work at Google. Your personal account is not your corp account. That doesn't mean your corp account doesn't exist in the same systems as your personal account, though. > And already, that sounds more complicated than just keeping your application accounts distinct and completely separate from your organizations accounts. Maintaining two entirely separate auth systems does not sound less complicated to me.

jasonkostempski 10y ago

> we mostly bypass that and build business logic in the stateless layer. > This is just (initial) design / programmer laziness. I disagree, it's just good practice. I've never seen a system evolve towards SQL based business logic, only away from it. SQL is meant for storage, that's it, obviously there needs to be a coding layer to move data in and out of it efficiently but that's where it should end.

AgentK20 10y ago
That's what this article is arguing against. The "good practice" is more precedent than anything. Sure, we can argue that it "adds security" to the mix by adding a separate layer between the user and the actual place where billing info and passwords are stored, but if that front layer gets breached then you're still screwed. From what I understand, the article suggests the reverse of what you're saying. Not necessarily moving the business logic _into SQL_, but what if the DB actually had a very extensible framework and libraries that let you serve HTTP _directly_ from the DB? How would that change reactive websites whose sole "backend" is a SQL<->JSON+Permissions system? What about if the DB itself supported a psuedo-PHP runtime, or better yet closer to native code? Granted, I'm not saying it's a good idea. I do see a vast array of benefits, primarily in the sense of scalability, from separating the business logic from the DB, but it's an interesting topic to ponder nonetheless.
- vog 10y ago
  > What about if the DB itself supported a psuedo-PHP runtime, or better yet closer to native code? Note that in PostgreSQL, user defined functions can be written in many languages, including SQL but also Python (very well supported) and PHP (not so well supported, but doable).
  - josephg 10y ago
    Post author here. I actually think it would be much cleaner to support an event log API and run this stuff out-of-process. My database shouldn't have to support the language-de-jour, and I don't want my react rendering process slowing down my database servers. You can totally decouple this stuff with a clean kafka-like eventlog API, but postgres doesn't provide one.

vog 10y ago

> This is sane and good design. The db auth layer is for db management, not frontend auth (are you seriously advocating this?). The auth layer of PostgreSQL is very advanced, up to row level security if you need that. [1] It was never intended to be "just for administration", quite the contrary! Moreover, some applications rely exclusively on a central PostgreSQL service rather than a central web service, such as "Artikel23" which is (was?) a CRM whose user management, encryption and security stuff is 100% handled by PostgreSQL. [1] https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html https://www.postgresql.org/docs/current/static/ddl-rowsecuri...

omginternets 10y ago

I think his argument is that it's "sane and good design" for a problem that now only partially reflects reality on the ground.