The only really must have mods typically add visual enhancements, more enemies, new armor + weapon models or skins, etc. Those would not do well on a console because it does not have the extra horsepower to pull it off. Games typically max the system and the only way you see improvements as the devs learn to better optimize the games.
Mods on console of course would be nice, but the must have ones would not be possible. There some cases where a mod would be possible on a console and would make a big difference, but it would be stuff like NMS low flight mod (I am assuming this would not cause the PS4 to crash).
That first paragraph is highly debatable, depends on the game in question. For Skyrim, I would say the must have mods do inlcude those (Skyrim Flora Overhaul, one of the three big HD texture mods, Open Cities etc) but among the most desirable I would list Falskaar - a DLC sized add on with new lands, quests, a storyline, etc etc - and Enderal: Shards of Order - a total conversion. Bethesda's games have incredible modding communities with some really excellent big projects.
As for the topic at hand, I assure you this is one side of a two sided tale. I have no doubt Sony were perhaps more hard nosed and unreasonable than MS - I mean Sony is King of the Hill while MS is looking for any advantage they can get in the console space. But I am sure part of the issue is that Sony didn't want the modding launch on PS4 to be the absolutely train wreck it was on Xbox with mod theft and poor regulation of game-damaging mods. Which would mean Bethesda would need to be more hands on. I personally find it hilarious they are saying Sony won't let modders do what they wish when they themselves do not allow anything and everything on Xbox.