Of course you engage your core.
In the saddle forward position, if you are trying to avoid supporting yourself with your hands, you would have to engage your core hard, really hold your trunk rigid, because you are fighting the imbalance that wants to topple you forward. Your center of gravity is too far forward, and an unbalanced object still tips over, no matter how rigid it is. In the saddle rearward position, you still engage your core, but you don't have to do it as hard, because you are not fighting an imbalanced position, you are simply doing a not-very-deep squat.
Here is another experiment you can try. You've done squats with a bar and weights, I assume. Get in the squat rack, put that loaded bar on your shoulders, keep your butt directly over your feet, and bend forward at the waist. You'll hurt yourself, fall forward, and get yelled at by all the lifters who know what they are about. One of them will get in the squat rack and show you the right way. He'll push his butt way back while bending forward at the hips. Because he is balanced, he will not fall over. Sure he is engaging his core, but so were you; you were imbalanced and he is balanced.
My squatting form has been called 'impeccable' by observers. I know very well how many inches my hoo hoo needs to move rearward to keep me from falling on my face and it is much more than a couple or three inches! Maybe a crank forward design has that kind of rearward displacement of the saddle but nothing commercially available in a drop handlebar design does. Considerable weight on the hands and arms is thus an
unavoidable trade-off to obtain the aerodynamic efficiency that makes commuting and sport cycling enjoyable and practical.
To be technical about it, you do NOT use your core muscles to hold your torso up when you are in riding position. You use your back muscles! Muscles can only contract. Your core muscles can do nothing about the forces that are pressing your torso down towards the bars. Only your back muscles can do that. And they get very strong as a result. Your core, not so much. Your core, however, is constantly needed to oppose the back muscles. To hold a desired position requires the continual interaction of both sets of muscles, one set of which is always working at a mechanical advantage. Over time, the muscles working through more mechanical advantage get weak and undeveloped. It is the imbalance in muscular development between your back muscles and your core muscles that lead to chronic, often acute, back pain and injury.
It was to address this often overlooked aspect of fitness why I mentioned abdominal exercise in my last post. You don't need to do 200 crunches to ride a bike, you need to do 200 crunches 3x/wk so that when you are 55 you won't be sitting in an orthopedic surgeons consultation room learning about the gory details of your upcoming back surgery. One of my riding buddies is 63 and he was facing surgery. One of my good friends is a chiropractor and he told me to try and convince my riding friend to opt out of a surgical intervention. Luckily his wife was also of that opinion and so between the two of us we got him on an abdominal exercise regimen. At present he is no worse than many people who have actually undergone back surgery and does not have any of the expense or possible complications of back surgery. Lets get back to bikes.
Riding very erect will take the weight off your arms and hands but, as noted will kill your aero big time. It also reduces the amount of power you can apply to the pedals. This is a double hit on
performance. You are not going to show up to a club ride on a cruiser and wipe up the floor with roadies rocking quasi-time trial machinery. It's just not gonna happen. You can train your arms to hold up a lot of the weight of your torso and the ladies will love you for it. Your hands may complain. Drop bars have lots of alternate positions to give hand muscles opportunity to relax and get blood flow. Use them. Ride the drops occasionally even if you aren't going after a personal best.
The bottom line is, if you are set up correctly, there is going to be considerable weight on your arms and hands on even what we call flat-bar road bikes. You have to go to a very extreme cruiser or Dutch bike riding position where the bars are not only 4" to 6" above saddle height but are also pulled towards you so that the grips are practically in your lap. The Dutch ride like that around town for a couple or three miles but when they want to cover distance they ride bikes that look much like what we use and call drop handlebar road bicycles. I'm surprised no one has mentioned recumbents <running, ducking>