Imagine you're working in a place with a rather large sales/marketing department. Now, sales is a pretty cutthroat job, and some people will do any cheat possible to get ahead of their co-workers. In a typical sales database, someone who manages to borrow a bit of sql from a techie friend could potentially go in and get sales leads from co-workers, poaching their deals. With row level security, the sleezier co-worker isn't allowed to look at those rows to be able to poach.

Do you mean as another security layer? I guess the sales people don't normally have direct access to the databases and the softwares already restricts which data they can see.

Maybe you mean just in case they manage to bypass the software restrictions.

In some cases, people will defer to database level security restrictions. It really depends on how much application logic is in the database. Some applications are designed with as much logic as possible in the DB, including each user being a db user with credentialed access. Others will treat the DB as dumb storage with all access through a programatic API... with thin API shims over DBMS, the db security is paramount.

Imagine how much simpler every single query becomes, when you don't have to do complex access checking as part of the criteria. You can just write a normal select/join! The database handles access checking for you.

Suddently, it's not that scary to ask the intern to generate a rather complex report - he can't show anyone data they're not allowed to see by accident.

Exactly. Defense in depth. Most people are going to play by the rules, but making it harder to break the rules is useful.

To be more specific, with Postgrest you're supposed to be able to use database-level permissions as your client-side permissions. (I personally haven't tried it so can't say how well it works)