We'll have to agree to disagree. Writing a manual security layer that doesn't understand your data model is one of the worst things you can do. Multiple databases? Now you're also manually doing integrity and probably manually doing joins. That's a ton of extra things to go wrong.
As an example, consider a query that is fairly complex. How exactly are you going to make resuable security tests for it? How do you know that an error in the query won't expose unexpected data, or that a modification to the query that introduces and error exposes unexpected data? Your security now has to be customized to every query like this.
The alternative case is that you only use super simple queries so that you can write resuable security checks. But now I'm going to ask why you are using a relational database in the first place? Because almost certainly you're doing manual joins in this scenario and incurring huge performance hits.
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?
> 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.
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.
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.
I think you make some good points here, and RLS is an ideal data security model in many respects.
The parent's point is that tooling around this is weak, which undermines its value. Not being able to review and easily test authz changes will almost certainly lead to issues.
As an example, consider a query that is fairly complex. How exactly are you going to make resuable security tests for it? How do you know that an error in the query won't expose unexpected data, or that a modification to the query that introduces and error exposes unexpected data? Your security now has to be customized to every query like this.
The alternative case is that you only use super simple queries so that you can write resuable security checks. But now I'm going to ask why you are using a relational database in the first place? Because almost certainly you're doing manual joins in this scenario and incurring huge performance hits.
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?