4 ms·

I don’t think passing auth to the database is crazy. It’s almost an exact parallel to filesystems, which everyone is fine with. It’s far weirder to me is that

I don’t think passing auth to the database is crazy. It’s almost an exact parallel to filesystems, which everyone is fine with. It’s far weirder to me is that the accepted status quo is to run transactions as a user that can read anything, and then try to have the app filter out stuff the user shouldn’t see. Just make the transaction run as a user that only has permissions to read what you want, and stop worrying about whether your auth system handles edge cases. This status quo is like having a filesystem where everything is owned by root, then writing a Fuse driver that lets users read those file by trying to infer permissions from the path. It’s a weird thing. I’ve always assumed the reason databases had such poor access control (or little usage of the good access control) was because it was too slow in ye olden days when disks were slow, clock cycles were limited, and read replicas were not yet common. In the modern world, I don’t know why anyone would prefer that their database can leak data in SQL injection attacks. The costs seem low, and the benefits seem high.

encoderer 2y ago
What happens when I’m allowed to view a column for some entities but not others? In the real world authorization logic can get very complicated. And who wants to migrate this kind of stuff once you’ve implemented it one way? Honestly it’s best to keep your database doing what it does best: storing data.
- dragonwriter 2y ago
  > What happens when I’m allowed to view a column for some entities but not others? Column-level rules are a DB design smell, because if some people have a reason to view a table that is coherent without a column, that suggests that you've got at least two different fact shapes communicated by the table that should be normalized into separate tables. Each of those tables then needs appropriate RLS rules. Sometimes you do a join (OUTER on one side) of these tables, so that they look like one, and this also solves the combined row/column security challenge that comes up when you have the less normalized table.
  - gopher_space 2y ago
    I'm looking at a table with columns intentionally removed because they're none of my business, but with calculations I'd want to do on those columns provided in a separate table as anonymity allows. Column-level rules seem like they'd make generating this export fairly easy, no?
  - bavell 2y ago
    Views seem much more appropriate a tool for customizing/restricting column access but I'm no DBA.
kbolino 2y ago
I'd love to hear an explanation of how a modern web or mobile app backend could reasonably be expected to create a new native DB role for every new end user and manage their DB access credentials securely, such that the overall risk of a SQL injection attack is lessened compared to the single-role-per-app model.
- gopher_space 2y ago
  I'm getting lost. How do the DB and app know about each other in this scenario?
  - everforward 2y ago
    Not OP, but I'm presuming they mean something like adding a "db_account_password" column to the users table to store the password for that user's database account (i.e. not their password, but the password the app uses to log in to the database as them), so the app can get the credentials to log in to a user's database account before running queries as them. You'd configure it like a normal webapp with the host, port, username, etc. It wouldn't be something you could just add to the config for an existing app, it would take custom code.
- dragonwriter 2y ago
  > I'd love to hear an explanation of how a modern web or mobile app backend could reasonably be expected to create a new native DB role for every new end user and manage their DB access credentials securely. Create all the individual user roles as NOLOGIN and the grant a single authenticator role used by the app rights to assume the user roles. It's exactly as secure as the “single app account with authz managed by the app” model, but lets the DB deal with authz. In either case, authn is on the app side, except for authn of the app to its DB account. But it also means that, since the app role itself has virtually no access and can only transition into a user role, you can't compromise multiple users worth of data at a time via an injection attack.
  - everforward 2y ago
    I responded to the other user, but you can get around the app role being able to transition to other roles by passing the session auth token to the DB via a local variable in the transaction/query. Create an extra session table with a column that stores the hashes of session auth tokens (JWT, shared secret, whatever) and another column that stores the user ID that owns it. Give the app's DB account access to read from, but not write to, that table. Then set up RLS on each row with a policy that reads that JWT/etc local variable, hashes it, looks up the hash in that table and compares the user ID to the "owner" of the row. The app account is unable to create JWT's, so the attacker will need either a valid JWT or a hash collision (which means you have bigger problems anyways). Then isolate the "login service" into it's own application with its own DB account that has access to check passwords and write sessions, and that's it. Make it super picky about what it will run; it only handles usernames and passwords, so it can probably just 4xx any requests that contain SQL characters or just base64 all of its inputs so the character set doesn't even include injection as a possibility. It ends up with an app account that basically only has CONNECT permissions unless the attacker gives it a valid JWT, at which point they could just impersonate the user anyways.
devman0 2y ago
No app I've ever deployed does passthrough authz to the filesystem, the app itself runs as in the context of a particular user and manipulates files as it's own principal which is basically the same as what happens with databases.
- gneray 2y ago
  +1 to this
- everforward 2y ago
  I have, fairly frequently in systems that are designed to be able to propagate auth like NTLM. The app itself runs as a user that has virtually no permissions, and attaches the auth context to file access/database queries/HTTP calls/etc. Last I checked, it was also the suggested auth implementation for "platform as a service" stuff like Firebase/Supabase. Saving people from having to write their own authz is a huge selling point for those platforms.

encoderer 2y ago

What happens when I’m allowed to view a column for some entities but not others? In the real world authorization logic can get very complicated. And who wants to migrate this kind of stuff once you’ve implemented it one way? Honestly it’s best to keep your database doing what it does best: storing data.

dragonwriter 2y ago
> What happens when I’m allowed to view a column for some entities but not others? Column-level rules are a DB design smell, because if some people have a reason to view a table that is coherent without a column, that suggests that you've got at least two different fact shapes communicated by the table that should be normalized into separate tables. Each of those tables then needs appropriate RLS rules. Sometimes you do a join (OUTER on one side) of these tables, so that they look like one, and this also solves the combined row/column security challenge that comes up when you have the less normalized table.
- gopher_space 2y ago
  I'm looking at a table with columns intentionally removed because they're none of my business, but with calculations I'd want to do on those columns provided in a separate table as anonymity allows. Column-level rules seem like they'd make generating this export fairly easy, no?
- bavell 2y ago
  Views seem much more appropriate a tool for customizing/restricting column access but I'm no DBA.

kbolino 2y ago

I'd love to hear an explanation of how a modern web or mobile app backend could reasonably be expected to create a new native DB role for every new end user and manage their DB access credentials securely, such that the overall risk of a SQL injection attack is lessened compared to the single-role-per-app model.

gopher_space 2y ago
I'm getting lost. How do the DB and app know about each other in this scenario?
- everforward 2y ago
  Not OP, but I'm presuming they mean something like adding a "db_account_password" column to the users table to store the password for that user's database account (i.e. not their password, but the password the app uses to log in to the database as them), so the app can get the credentials to log in to a user's database account before running queries as them. You'd configure it like a normal webapp with the host, port, username, etc. It wouldn't be something you could just add to the config for an existing app, it would take custom code.
dragonwriter 2y ago
> I'd love to hear an explanation of how a modern web or mobile app backend could reasonably be expected to create a new native DB role for every new end user and manage their DB access credentials securely. Create all the individual user roles as NOLOGIN and the grant a single authenticator role used by the app rights to assume the user roles. It's exactly as secure as the “single app account with authz managed by the app” model, but lets the DB deal with authz. In either case, authn is on the app side, except for authn of the app to its DB account. But it also means that, since the app role itself has virtually no access and can only transition into a user role, you can't compromise multiple users worth of data at a time via an injection attack.
- everforward 2y ago
  I responded to the other user, but you can get around the app role being able to transition to other roles by passing the session auth token to the DB via a local variable in the transaction/query. Create an extra session table with a column that stores the hashes of session auth tokens (JWT, shared secret, whatever) and another column that stores the user ID that owns it. Give the app's DB account access to read from, but not write to, that table. Then set up RLS on each row with a policy that reads that JWT/etc local variable, hashes it, looks up the hash in that table and compares the user ID to the "owner" of the row. The app account is unable to create JWT's, so the attacker will need either a valid JWT or a hash collision (which means you have bigger problems anyways). Then isolate the "login service" into it's own application with its own DB account that has access to check passwords and write sessions, and that's it. Make it super picky about what it will run; it only handles usernames and passwords, so it can probably just 4xx any requests that contain SQL characters or just base64 all of its inputs so the character set doesn't even include injection as a possibility. It ends up with an app account that basically only has CONNECT permissions unless the attacker gives it a valid JWT, at which point they could just impersonate the user anyways.

devman0 2y ago

No app I've ever deployed does passthrough authz to the filesystem, the app itself runs as in the context of a particular user and manipulates files as it's own principal which is basically the same as what happens with databases.

gneray 2y ago
+1 to this
everforward 2y ago
I have, fairly frequently in systems that are designed to be able to propagate auth like NTLM. The app itself runs as a user that has virtually no permissions, and attaches the auth context to file access/database queries/HTTP calls/etc. Last I checked, it was also the suggested auth implementation for "platform as a service" stuff like Firebase/Supabase. Saving people from having to write their own authz is a huge selling point for those platforms.