I grow enough fruit and veg that I barter some surplus with other local growers, and the only resources I use are wastewater and composted kitchen leftovers, and the occasional bag of potting soil (like 1 a year). It's not especially efficient but I'm happy with a sub-optimal producer surplus, given the minimal investment required. The opportunity cost is likewise minimal - the space used for growing food would otherwise be filled with decorative but unproductive plants, which don't have any particular economic value for me because I'm not into flowers or having a well-manicured lawn.

Admittedly it's easy for me because living near the Oaklnd/Berkeley border I enjoy great weather, major pollutants were phased out earlier here than anywhere else in the country, I don't need to work the soil intensively to produce more than I can eat, nor am I dependent on selling food for my income. You're quite right that commercial food production involves large comparative advantages. But getting several hundred dollars' worth of food a year out of the garden suits me fine and I'm pretty sure the resultant carbon footprint is too shallow to measure.