You seem to have several questions here.
The Rambam at the beginning of Hachelek refers to the fools who take Aggadic text literally in a rational way. He is referring to others who held this way. Is anyone familiar with an opinion in the Rishonim/ early Achronim that states one must understand all Aggadic text in such a manner?.
Please go to Sefaria and search for a book called "Or Neerav" Read the first chapter.
I have copied and pasted a few quotes below from the book (Or Neerav)
The Maharsha states one has to believe it literally,
Chafets chaim, Rashash, Gra, speak heavy on learning further than the pshat. If you read the book "Or neerav", they mention anyone who only learns pshat in any level of the Tora, is in major trouble.
Does anyone take this to the extreme that one must believe every
Midrash in a literal and rational sense?
Everyone agrees you have to read the Pshat first, it's like identifying the variable of the equation, Then you must learn, as we mentioned above and mentioned below, the hidden meaning.
The Book Or Neerav starts here.
"it is concerning these men and their like that King Solomon, peace upon him, said, The fool does not desire understanding, but only to air his thoughts (Prov. 18:2). It is apparent that one who follows his desires and spurns enlightenment in the mysteries of the Torah can be called a fool, since he continues his folly and his intoxication with this lowly world. [The verse] states, The fool does not desire understanding—these are the esoteric subjects hidden within the exoteric matter. This definition of understanding is the one which [the sages], their memory be a blessing, [used when they] said, “He who understands one thing from another.”"
Of these men and their like, Rabbi Simeon bar Yoḥai, peace upon him, said in the Tikkunim [no. 43]:
בראשית
In the beginning (Gen. 1:1).
תמן את״ר יב״ש ודא איהו ונהר יחרב ויבש.
There it is dry, and here the river dries up and is parched (Job14:11).
בההוא זמנא דאיהו יבש ואיהי יבשה, בנין צווחין לתתא ביחודא ואומרים שמע ישראל,
At that time when he is dry and she is dry, sons cry out below in unification and say, Hear, O Israel (Deut. 6:4).
ואין קול ואין עונה,
And there is no voice in response (I Kings 18:26).
הדא הוא דכתיב אז יקראנני ולא אענה.
Thus it is written, Then they will call to Me, and I will not answer (Prov. 1:28).
והכי מאן דגרים דאסתלק קבלה וחכמתא מאורייתא דבעל פה ומאורייתא דבכתב, וגרים דלא ישתדלון בהון ואמר דלא אית אלא פשט באורייתא ובתלמודא,
This concerns one who removes Kabbalah and wisdom from the oral Torah and the written Torah, who causes that they should not attempt [to acquire] them, and who asserts that there is nothing beyond the plain meaning of the Torah and the Talmud.
בודאי כאילו הוא יסלק נביעו מההוא נהר ומההוא גן.
Certainly it is as if he removed the flow from that river and that garden.
ווי ליה,
Woe to him.
טב ליה דלא אברי בעלמא, ולא יוליף ההיא אורייתא דבכתב וההיא אורייתא דבעל פה, דאתחשב ליה כאילו חזר עלמא לתהו ובהו, וגרים עניות לעלמא, ואורך גלותא". עכ״ל:
It were better for him had he never been created in the world, and had he never taught the written Torah and oral Torah, for it is considered as if he had returned the world to formlessness and void (Gen. 1:2) and bequeathed poverty and length of exile to the world.
Of these men and their like, Rabbi Simeon bar Yoḥai, peace upon him, said in the Tikkunim [no. 43]:
