3 ms·

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

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.
- deleted 2y ago
  [deleted]
- kbolino 2y ago
  You'd need another role to create the user roles and grant them to the app role. It should be possible, though you'd need to use a separately authenticated connection if not a separate app to ensure proper isolation.
everforward 2y ago
> 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. They don't have to be native DB roles. Row security policies extend far beyond an "owner". A naive version would use SQL to set a local variable to the user's ID, with row level security policies on each row that check that local variable. This is still very vulnerable to SQL injection, though, because if attackers can execute arbitrary SQL then they can also set that variable. A less naive version would involve setting a local variable to a JWT or other client-side secret, and having RLS validate access against a stored hash of that JWT or other secret. The app's DB account has no access to anything other than SELECTs on the JWT hash -> user ID mapping table, with RLS on all the other tables. JWT's would need to be generated by another service using it's own DB account that only has access to check passwords and write into the JWT table so that they can't use the compromised connection to just generate a new JWT they can hash themselves. That login service can be written to be virtually immune to SQL attacks because it doesn't have to handle generalized queries. Ban all the SQL control characters in usernames/passwords and 400 any request that contains them. Hash and base64 both the username and password so they get converted to something where SQL injections are impossible because of the character set used. There's a bunch of options; this doesn't have to handle general-purpose SQL queries, so it can afford to do stuff that would break normal queries like b64'ing the inputs. You end up with a system where you need a valid JWT to hit the API, but you also need to include a valid JWT in any SQL injection attacks for them to work. There's no point in SQL injection attacks; one of the prerequisites is "a valid authentication token for the user", at which point the attacker could just connect to the API as them.
- Mister_Snuggles 2y ago
  > Ban all the SQL control characters in usernames/passwords and 400 any request that contains them. Hash and base64 both the username and password so they get converted to something where SQL injections are impossible because of the character set used. The problem that this tries to solve has been solved by every SQL database for a long time. Bind-parameters in queries are your friend, never build a query using string concatenation.

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.
deleted 2y ago
[deleted]
kbolino 2y ago
You'd need another role to create the user roles and grant them to the app role. It should be possible, though you'd need to use a separately authenticated connection if not a separate app to ensure proper isolation.

everforward 2y ago

> 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. They don't have to be native DB roles. Row security policies extend far beyond an "owner". A naive version would use SQL to set a local variable to the user's ID, with row level security policies on each row that check that local variable. This is still very vulnerable to SQL injection, though, because if attackers can execute arbitrary SQL then they can also set that variable. A less naive version would involve setting a local variable to a JWT or other client-side secret, and having RLS validate access against a stored hash of that JWT or other secret. The app's DB account has no access to anything other than SELECTs on the JWT hash -> user ID mapping table, with RLS on all the other tables. JWT's would need to be generated by another service using it's own DB account that only has access to check passwords and write into the JWT table so that they can't use the compromised connection to just generate a new JWT they can hash themselves. That login service can be written to be virtually immune to SQL attacks because it doesn't have to handle generalized queries. Ban all the SQL control characters in usernames/passwords and 400 any request that contains them. Hash and base64 both the username and password so they get converted to something where SQL injections are impossible because of the character set used. There's a bunch of options; this doesn't have to handle general-purpose SQL queries, so it can afford to do stuff that would break normal queries like b64'ing the inputs. You end up with a system where you need a valid JWT to hit the API, but you also need to include a valid JWT in any SQL injection attacks for them to work. There's no point in SQL injection attacks; one of the prerequisites is "a valid authentication token for the user", at which point the attacker could just connect to the API as them.

Mister_Snuggles 2y ago
> Ban all the SQL control characters in usernames/passwords and 400 any request that contains them. Hash and base64 both the username and password so they get converted to something where SQL injections are impossible because of the character set used. The problem that this tries to solve has been solved by every SQL database for a long time. Bind-parameters in queries are your friend, never build a query using string concatenation.