Why are roadies so hunched over?!?
Let me begin by characterizing why road bikes have their riding position vs why MTBs have theirs. It's a gross generalization of course, but most riders and riding positions fall decently into the two categories.
Road bikes: To minimize frontal area for aerodynamic benefits and place the hips in the best position to generate power on flat roads, the road bike position is bent over forwards with the bars below the saddle. This also puts more weight on the bars, which I believe increases stability. As you said, bump absorption is a minor concern.
Mountain bikes: The rider is expected to ride downhill, on the flat, and uphill. The upper body is kept more upright to allow the rider to see the trail better, with less strain on the neck and back. This position also makes it easier to bend the legs in response to terrain undulations. Additionally, less weight is put on the bars to avoid flipping over the bars (as you've experienced) and generally control the fore/aft weight distribution.
Feel free to comment with why you believe MTBs should also have a road-esque hunched over position. Not trying to shame you, just understanding the logic.
Edit: @the_endian has replied to this:
to answer your question re why MTB should have hunched over position - note that my frame of reference is from my experience on mtb/dirt trails on the gravel bike (yes, I did ride actual mtb trails on it), and successfully maintaining control of said bike the whole time. It's probably a function of more powerful or even grabbier front brakes on my MTB (+ more traction as you mentioned from the tires), and my more time behind my gravel bike than anything else. Maybe MTB's shouldn't mimic gravel bike geometry, but I think it's now obvious why I felt that way. Thanks.
In a very localized context, yes, it's probably the lowered center of mass that saved you in this scenario. However, you would have been just fine on a MTB had you moved your body mass rearwards enough. As the trails get rougher and steeper, the hunched-over position of a road bike becomes a significant limitation. Your familiarity with the gravel bike certainly played a major role as well. And as a side note, it's gravel bikes that are imitating MTB geometry, not the other way around.
What is stability?
There are some technological factors that come into play (again, stereotyping):
Wheelbase: a longer bike will be more stable.
Reach: A longer body position will be more stable by stretching your body out and literally making it harder for you to move, almost locking you in position.
Front center / rear center balance: Essentially your fore/aft balance as dictated by where the BB is placed relative to the axles. Longer rear center (AKA chainstay length) is more stable, shorter gives you what's often referred to as a more "flickable" feel.
Steering geometry: Slacker head angles (lower angle number) are more "stubborn" against being knocked side to side by trail undulations AND resist your steering inputs, so you have to act more forcefully to turn. More trail "disconnects" your steering inputs from wheel turning movement, so again, you have to work harder to turn the bike, so it is more stable.
BB height, as I'm about to elaborate on. This doesn't mean that it's the most important factor, but rather that it leads to a significant point about how dynamic stability is achieved.
I see where you are coming from with the idea that since you ride low in the drops on a road bike to gain stability, surely the same procedure must apply on a MTB. To some extent, it does, yes. Bikes with lower bottom brackets are often seen as riding smoother or more planted than bikes with higher bottom brackets. However, having your mass up high can also work well because the bike acts like an inverted pendulum. Much like balancing a broomstick on your finger, keeping your mass high lets the (comparatively lighter) bike dance beneath you while your body "floats" over the terrain. Excessive built-in stability can also come at the expense of nimbleness, which highly experienced riders prefer because the bike is easier to jump etc.
I noticed that there's a concept of leaning back with one's hips while riding a mountain bike [...]
Okay, so how do I stay on the bike?
[...] a position which supports staying on the bike rather than flying off the bike when riding over obstacles [...]
Time to adapt to the mountain biker's way of thinking! On a road bike, emphasis is placed on finding the most ergonomic, most powerful position and holding it as best as possible. On a MTB, the opposite is the case. Your body position must be constantly changing. Hence, it's not a case of "thou shalt have thy hips rearwards when riding on a negative slope". Instead, think of body positioning as a tool or parameter you are constantly adjusting as you ride. Learning how to do this is a key aspect of "skill development"; many of the tweaks become subconscious.
At the end of the day, the key to staying on the bike is to practice on easier trails and develop the bike handling and body positioning techniques that allow this to happen. Assuming there are no major equipment issues here (like a bike that's too small, a fairly old bike with old-fashioned geometry, very weird handlebar setup, very wrong suspension setup), those two factors are the primary considerations.
There isn't really a universally accepted method of riding a MTB, but my philosophy is "high and loose". By that, I mean keeping the body high (keeping the legs fairly extended), and riding with very active arms and legs that react to changes in terrain as much as possible. You don't want to be statically holding a rearwards-heels-dropped position while letting the suspension absorb everything for you. Furthermore, the part about staying high means that you should NOT squat down as your default position, even when the dropper is down. This is to allow for more space between the bike and your groin AND promote the disconnect between what you are doing and what the bike is doing.
This relates to what I brought up in the paragraph about BB height earlier. You need to ride dynamically, with the bike moving under you. You have a mass of what, 70kg? The bike is probably less than 15kg. If you look at how your suspension works, the wheels and some suspension components move around, while the rest of the frame and your body stay level. We want to extend this relationship to the entire bike and your body: let the bike do its dancing and terrain-tracking under you, while your torso stays somewhat level. Yes, this means your arms and legs need to be moving the whole time too to allow for this "bike-body separation", as you'll often find it called.
Another thing is to pick better "lines". Even the narrowest trails have multiple possible paths to choose from, at least in terms of where exactly you choose to place your tires. You're certainly well familiar with the concept from riding on the road: avoiding potholes on descents, avoiding bad pavement, gravel, etc. Pick lines that are smoother and less steep so you have a lower risk of coming off the bike. Again, do not just resort to locking your body in a static position and hoping the suspension will save you.
You have a greater mass than the bike. Let the bike do its thing underneath you. Use it and the suspension to help keep your body stable.
Keep your butt up. Don't crouch down. Aim to maximize the amount of vertical space between the saddle and your groin so you have the largest amount of "real estate" to work with. If you're sitting on the saddle when it's in the dropped position, that's not good.
Your body needs to be dynamic. Arms and legs should be constantly moving. Fore/aft weight balance is also dynamic according to the local terrain slope and features.
You are not obligated to hit every rock on the trail. Pick the smoothest line you can!
The other stuff
[...] if I lower my dropper post significantly [...]
Why isn't it all the way down? You have it there for a reason.
[...] I accidentally pulled the front brake too hard while going downhill, and I flew over the bars.
You already know what you did wrong here. Your mountain bike may very well have stronger brakes than the road and gravel bikes. The wider, knobby, soft rubber also is likely to grip better, and the suspension helps keep the front wheel in contact with the ground better.
[...] even when I road the gravel bike on the exact same mountain bike trails as I fell on, and even when I once had to brake at 40mph [...]
If you really did flip over the bars at 40mph, I'd be writing this as part of your obituary, so let's first be thankful that you didn’t do that. You may have felt less confident braking on the gravel bike and hence applied the brakes less firmly, especially if you're very familiar with that bike and less so with the MTB.
Stability under braking isn't the only form of stability. You'll soon find that all of this becomes much more...exciting...when you get to trails that are so rough, you pretty much just lose control over the steering and the bike goes flying out from underneath you. You may also find yourself desiring more consistency and stability in the air when riding jumps. These are good problems to arrive at, and I believe you can do it!