While I already have SS and do not plan to be using any other version than the one I get from this website, and so might not be the target audience as it were, I would like to have a bit of input.
I don't have much time to play video games. In fact, when I do its usually because I'm skipping out on what little sleep I can afford. This caused a lot of issues when trying to play Stellaris. In Stellaris, I had a couple mods, like Glavius' AI mod to make AI usable for example. The issue was that by the time I could reach midgame, the game would've updated which will break my save. Paradox at the least does provide a sorta wonky work around with Steam's version selection feature where you can play the last patch of a major version, but that wouldn't stop the mod updates from break the game entirely. My friend did tell me later of a procedure where you go offline and change some files for the mods with saved backups to be able to play older mod versions with the older version of the game, but the procedure would have to be done each time I wanted to play as everytime I used Steam online it would auto-update. When my time to play games is already so small, the last thing I'm going to do is spend time having to replace files before turning on my game so I can play it. After a few cycles of trying to play and having my saves broken mid-game I decided that ultimately, I would not be able to play Stellaris and to let it collect digital dust.
Now, that anecdote is not to say that Steam workshop is bad for everyone or even most. I would say that I'm in the clear minority, but for most end users I would agree that steam workshop is very convenient. My bigger worry for it has to do with a couple of things.
First, like Soren said, literally anyone can post someone's content up onto Steam Workshop if they decided to. While you can report it, its likely a fair amount of work for the modder to have to deal with to prove its theirs. Unpleasant work that they might not want to do, but will have to in order to protect their content. That's not something I would want the modders to have to deal with for creating mod content and deciding not to put it on steam.
Secondly, my biggest concern would be splitting of the mod content. Right now, with its smaller audience all the mods are contained on this forum available for every user. While Nexus has been implemented, its far from having half the mods let alone all. If Steam Workshop were a thing, I would worry that most people that get the game on steam would make the mods for the Steam workshop, meaning that those of us who want to keep the game off steam would be locked out of all the mod content created only for steam.
Assuming this game scores a major hit with the audience it targets and becomes popular, I do not believe that this forum would be able to handle so many people regularly coming and going to get mods. Especially, when the more popular mods update. There may come a time where a place that can handle the mods created and downloaded regularly might be needed, and it would be nice if it provided an easy way to update when the user chooses to. But that option isn't entirely the steam workshop. The steam workshop would be good for steam users, but wouldn't work for those who want to get the game here or on the other platforms it may or may not be available on such as GoG, itch.io, or Epic and would risk losing any mods made only for the steam workshop for those users.
In short, I would like an option that produces less risk of exclusive mods on a single platform that also forces auto-updates, and can cause issues for modders that don't want to use the platform.