[miles-po]

If you're trying to map app users to DB users for more than 1,000 users, you're doing it wrong. Roles at that scale tend to be more vague: admin, hr, analysis, etc. Users (and tags) go in a table. Then row-level security authorizes through the user table for individual queries.

Row-level security is absolutely not dependent upon DB roles. Table-level security on the other hand is sufficiently coarse-grained that mapping GRANT/REVOKE to applied roles should be feasible.

If you're punting all of this security to the app layer, that's your prerogative, but don't pretend that it's somehow more straightforward or secure. If it seems simpler at the app layer, you may very well be missing something.

[snuxoll]

I’m talking more along the lines of “hr user logs in, now you need to SET ROLE for the request and make absolutely sure RESET ROLE is called when the connection goes back to the pool”. A lot of frameworks make this harder than it needs to be.