> 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.