postgresql - How do I minimize round-trips to SQL with user-based authorization? -

context: mvc web service backed sql db. have user relation in database, , set of relations reference through chain of fks. example let's have table:

sales_people car_dealership cars 

where sales person belongs car dealership, , cars. sales people should able see cars belong specific dealership. have few options here:

i can bake authorization business logic sql query itself:

select * cars c, sales_people sp, car_dealerships cd where c.dealership_id = cd.id , sp.dealership_id = cd.id , sp.id = ? , c.id = ? 

assuming caller has verified sales_people id legit , prevents trivial spoofing of id, query above prevent user getting hold of cars aren't his. extended arbitrary # of tables long join isn't massive.

upside? 1 single db call.

downside?

• the authorization business logic here basic. user referenced 1 of tables? sure, can pass. let's have have more complex access rules. it's might not doable 1 simple query.
• it's hard tell if user requested unauthorized row or if row authorized doesn't exist, makes error reporting tricky. wouldn't know if should report 200 or 403 (although depending on type of api might want use 200 in these cases prevent exposing information attacker).

the other option see make queries before or after fact validate data indeed accessible user. e.g. list of ids of cars sales person authorized , perform query on subset, or exact other way around.

the upside can make more calls , run more logic in business layer , sophisticated want.

the downside generating more db traffic deal-breaker depending on how frequent request made.

i'm missing few other options here, , i'd love hear how you've solved problem before. there better way?

i think general rule, should make code behave logically, , scale performance in other ways, e.g. bigger more powerful database or caching results.

for application, use multiple queries. i've timed our system, , 5-10 round trips take less 1 ms. that's enough me. i've seen others create complicated stored procedures want. result can return things 403 or 404 database.

i prefer make multiple trips database make code cleaner , easier read. true if load not big. hardware cheap, time not.