3 ms·
> If you are using a relational database to its full extent, there is nothing as secure as built in row level security. You wouldn't dream of doing integrity ch
by snuxoll 7y ago
> If you are using a relational database to its full extent, there is nothing as secure as built in row level security. You wouldn't dream of doing integrity checks outside the database, why do security?
This isn’t exactly PostgreSQL’s fault, but mapping application users to database roles is not as easy in pretty much any framework I’ve worked compared to implementing basic post-retrieval filters.
I absolutely think bringing security into the database is good, but it’s also not straightforward to integrate in even assuming you know how to define the necessary database elements.
- miles-po 7y agoIf 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.
- deleted 7y ago[deleted]
- snuxoll 7y agoI’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.