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 a
by dragonwriter 3y 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 3y agoI 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 3y ago[deleted]
- kbolino 3y agoYou'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.