Up to now I always used one database user, and handled permission checking, and corresponding filtering in my application.
This pattern has the drawback that the programming language which was chosen when the application "was born" is mandatory.
All the permission checking stuff gets handled in this programming language.
Since I currently start an application from scratch I re-think this pattern.
Why not use a corresponding PG-user for every user which exists in the application?
So authentication still happens in my application, but as soon as I know that Bob is Bob (and not somebody else) I use SET_ROLE so that the following SQL statements get executed by the PG-user Bob.
Question: Why not use one PostgreSQL users for every application users?
Background: If I do the permission handling in PG (Row security policies), then I can give the users (readonly) SQL access. Hint: My users are scientist, and I think they able to learn SQL.
I guess this kind of application development is unusual. I googled but could not find talks or article. Please leave a comment if you know a article/talk about this topic.