[Winsaucerer]

I've experimented (i.e., not used in anger) with doing something similar to this, except by placing the authorisation logic in the database via things like row level security and stored procedures. So if you run the query using the 'unsafe' method, then it skips security checks, but if you run it via the normal path, you're forced to provide details about the requester.

The difference here though is that the normal path only provides information about the requester, and not the actual business logic for whether they're authorised. That goes in the database in the form of RLS or other permission checks in stored procedures.

It uses a pattern similar to PostgREST, where there is a local value set with the user ID (e.g., 'set local "request.web.sub" = '{}''). Though there is some trickiness with connection sharing, to ensure that these settings get reset properly.

And to use an example from another commenter here, this means that if there's only 3 rows that should be returned, the database returns just 3 rows, rather than 10,000 that need to be discarded down to 3.

I haven't come up with a good answer regarding protecting which fields can be seen yet.