Well, since there isn't a major website in the world that I'm aware of that hasn't had SEO work done the "profitable for web owners" are is pretty clear, else full-time professional SEO's like myself would not be in business.
On the other side, SEO's can do pretty well. For example, I make a pretty good living, and yet have been accused several times of under-charging. Most professional SEO's make between 100-500 per hour normally, with a bunch working on either side of that range.
Myself, I'm normally $250/hr, but for government, and long term clients that goes down to $100/hr. For the industry this is pretty low, frankly. (uh, BTW, I'm not looking for new clients without references right now, so don't take this as a sales pitch or anything).
I live in a rural area and have fairly low expenses, so I don't really need more than a couple hundred K a year to support my family and have a comfortable life. I'm working on increasing that, but more as a business and personal challenge than a desire to buy a Rolls Royce or anything like that. Besides, it's fun.
So, yes, you can make a reasonable income on either side of the SEO fence (web owner or consultant). Most of the time, you make more on the web owner side, but it's not always the case.
You can, of course, also be broke all the time if you choose the wrong industry or business model, or are just starting out and are building your knowledge, reputation and resources. Just like in the bricks and mortar world.
When I first started out, my wife supported me, and I considered a $500 (total) SEO deal to be a big thing and a validation of my poor pitiful existence. It was not fun around the dinner table or at bill time, let me tell you that.
I was making far more working part time at various jobs (McDonalds, computer sales, bus driver, etc) than as an SEO. Since before that I was a senior manager of major international public company, it was quite the shock to the family to see me wearing a uniform and a little plastic name tag. My poor wife thought I'd gone nuts!
But I believed in what I was doing, and in the direction of where the web was heading, and was determined to take advantage of it. Too many times I had let opportunities pass by without taking advantage of them because I didn't have the money, or didn't want to lose my steady paycheck or benefits, or didn't have the full support of my family or friends. So I decided to go for it.
For the first 2 years, I was pretty sure I had made a horrible mistake, but kept my promise to myself to keep trying. Finally, I got a few breaks, along with some help and advice from some great people, including, but not limited to, Jill Whalen (and the rest of the HR mods), Barbara Coll, Bill Hunt, Mike Grehan, Kim Krause Berg, Andrew Goodman, Dan Thies, Bob Massa, Doug Heil, Debra Mastaler, Christine Churchill and of course Danny Sullivan and the fine folks here at this forum. Other sources like Jared Spool, WMW, and various newsletters, websites and books also help a lot. I've avoided listing everyone who has helped me and stuck with some of the more well known ones, not because I don't appreciate everyone else, but because the list would be very, very long!
If you think about it, that's a lot of people, and it's only the tip of the iceberg. Most of the regulars here, and a large number of the newbies, have asked questions or provided insight that forced me to think and learn, even if I disagreed.
From there, I just leveraged my own skills and work, came up with my own opinions on how things should be, and eventually built up a business.
If I can do it, so can you. Some tips:
This isn't about SEO or SEM, it's about selling things people want or need. As long as capitalism and the internet exist, there will be a demand for internet marketers.
2. You are not alone.
This community is small, but very helpful and friendly compared to other business communities. I can't count the number of times fellow SEO's and SEM's have helped me, or sent business my way or just provided the virtual shoulder to lean on when times were tough.
3. Study and read everything.
Assume it's all BS unless you have tested it yourself. Always test it yourself. Sometimes great advice from smart people is outdated and no good anymore. Sometimes the people who know the least are the best communicators, and make seemingly persuasive arguments with bad data assumptions and faulty logic. Think for yourself.
4. Take the high road.
It's better to walk away from a client you don't really trust, or the latest "trick" in SEO that you are not sure of, than it is to get bogged down in details and schemes. You need to truly understand the rules before you can break them or make new ones. If you can't do SEO using traditional, safe and search engine approved tactics, then you are not skilled enough to try anything else. Non-traditional techniques are not a "god mode" or cheat code to be used when you are too lame to play the game properly, they are difficult and can be dangerous. You should also always be completely honest in all business dealings and commit to best practices wherever you can. It's not only a good idea, it will save your ass more times than you can count, if you are in business long enough.
5. SEO is more than little rules and templates about how to make websites attractive to search engines.
You need to learn about marketing, writing, business plan development, usability, standards, information retrieval science, hosting and coding issues, ROI calculations, website analytics and a great many other things. I can teach "SEO 101 for Dummies" in a 3 hour course, but it takes about 6 months of study and practice to be good enough to handle basic SEO on non-competitive sites for a client, and at least 3 YEARS of study to actually be what I would consider a professional SEO. That's not 3 years of working as an SEO (though that's a big part of it) that's 3 years of study. If that sounds like you are kind of training for a university degree, then you are not far off. It requires a huge investment of time and work to be a professional SEO. Otherwise, if it was as easy as some think, the clients would just do it themselves and save the money. Some try that, and usually end up hiring an SEO when they find out it's not that simple.
Wow, that was longer than I was expecting it to be, so I'll stop here. Good luck!