I cannot get post hole diggers or even a standard shovel under the deck
to dig new holes because it is too low and it is also roofed over.

Sure you can. Pull up the decking. You've already seen what a half-ass job
ends up being. Don't repeat it. Correct the drainage problem FIRST. No
job will be worth doing if the water gets back in there and rots it out
again. THEN pull up the boards and put down proper holes for the posts.
Check with your local code and DO IT RIGHT. If the roof you've got on this
is already attached to your house then you don't want this deck heaving
up/down due to winter weather or other conditions. Properly sunk posts will
avoid this trouble.

The old posts are directly below the
posts that support the roof over the deck.

That complicates things, of course, but if you make enough room you should
be able to dig out a hole at that point and sink a new post to support the
one above it. No sense in putting down columns elsewhere and then end up
having the roof start sagging.

Or I could dig a hole as deep as I can

As deep as you can't won't matter if you're in frost area. You have to dig
it to the depth code requires. Otherwise you're just asking for new
problems.

after cutting out the rotted post
and pour a pier with a metal standoff bracket anchored on top and use a
post above that to go up to support the rim joist.

Yes, that would be one idea that might work.

Does anyone out there have any experience with this problem and know of
any other way(s) to address it?

I've tried working around an existing deck. More often than not it ended up
requiring removing existing decking material anyway. Best to just cut to
the chase and do the job right.