Why are there no (automatic) rear turrets on the X-Wing and Y-Wing?

I am not familiar with any model X-Wing having rear turrets, let alone manned ones, nor appearing in Episodes I-III.

As for the Y-Wings, I assume you are referring to the BTL-B "Republic" Y-Wing, as seen here (can be missing the panelling, which has other lore reasons I won't go into here):

We can see that, like you stated, there's a bubble with space for a living gunner, instead of it being controlled by for instance an astromech, targeting computer or the pilot themselves. The benefit of a wide bubble like this was that the gunner could easily see around him, while the seat and turret could turn a full 360 degrees around.

However, there were many downsides too, as one could imagine. Firstly, such a bubble exposes the gunner greatly to enemy fire. And as Legends has stated, at first, a shot canopy would mean the whole cockpit got sucked out. This later got fixed by separating the space of the turret and the pilot space (see Incredible Cross-Sections of Star Wars: the Ultimate Guide to Star Wars Vehicles and Spacecraft). But yet, the issue of the gunner easily getting shot out remained. This meant the gunner had to be moved somewhere more safe and concealed. This led to the development of the BTL-S3 and later series of Y-Wings, the ones you refer to when speaking about Episodes IV-VI.

Throughout the whole of Star Wars, guns like these are usually controlled by someone other than the pilot simply for the reason it'd make things too complicated for the pilot, who's already under a lot of strain during combat. So, there's an external party needed to help. The Republic solved this by means of another clone in the bubble. This also has to do with the fact that at this point, it is implied astro mechs are not advanced enough to do both hyperspace planning and co-piloting as well as controlling such systems.

Here we see a Y-Wing (either BTL-S3 or BTL-A4) during the trench run. You can see the gunner bubble is gone, yet I can assure you that there's still very much a gunner present (in most cases). But instead of being in an obvious, easy to shoot bubble, they are in the back of the canopy, under the turret. In Star Wars, humans are simply more trusted with targeting and shooting targets than computers (besides tracking assistance systems).

"The BTL-S3 was a variant of the Y-wing starfighter line. It was typically crewed by a pilot and gunner, who sat back-to-back in the cockpit." -Wookiepedia, excerpt from BTL-S3 Y-Wing

However, at this point of the Star Wars timeline, these targeting computers and astromechs have gotten much more advanced. So we start to see the gunner being removed, and instead the astromech controls the turret, or there's a targeting computer placed under the turret (although this was rare because of the aforementioned reasons).

Additionally, there was also the BTL-A4 Y-Wing which completely removed the gunner seat and instead locked the overhead cannons into an forward aiming position (sometimes in different archs, or other angles), so that the pilot could just pull the trigger at what he was flying at.

"The Koensayr BTL-A4 Y-wing starfighter was crewed by a single pilot. The 16-meter-long attack starfighter had an ArMek SW-4 ion cannon turret which was preset to one of four arcs and was typically locked forward, although some pilots chose to lock it facing aft for extra cover." -Wookiepedia, excerpt from BLT-A4 Y-Wing

BTL-A4 Y-Wing as found in Incredible Cross-Sections of Star Wars: the Ultimate Guide to Star Wars Vehicles and Spacecraft shows the removed gunner seat, instead filled with electronics (probably retrofitted to improve other systems or allow for a bigger targeting computer, although there are no details on this). The texts on the page also back up the information I've been talking about about the BTL-S3 and BTL-A4 models.

Here is my speculation on why Star Wars relies heavily on traditional army place configurations (a.k.a. any gun not pointing forward is controlled by an added gunner(s)): Star wars was first conceived during the late 1900's. It was also heavily inspired by World War II (see 'Historical')** when it comes to the warfare. At this time, the idea of such advanced computers in such a small package, or that are able to distinguish and attack 'enemies' was simply not thought of. Although, droids do exist, so do many other computers doing all sorts of stuff like this. So the only other reason I can think of is because Lucas simply wanted to keep to tradition of planes with multiple crew, possibly to keep it somewhat recognisable for the viewers.

"The space battles in A New Hope were based on filmed World War I and World War II dogfights." -Wikipedia, excerpt from Star Wars sources, Historical**

** From "Empire of Dreams: the Story of the Star Wars Trilogy" (2004, IMDb). In this documentary, George Lucas speaks about the inspirations behind Star Wars' technology, clothing, but also the way fights are orchistrated and what vehicles are based on, amongst much more.

Think of the Star Wars fighters as WWII aircraft, mostly carrier aircraft.

• The most effective day fighters were single-seat models, like the Hellcat ou de corsário. Mostly armed with forward-firing cannon, but also rockets or bombs. Think X-Wing, or take the zero for TIE -- agile but fragile.
• To go after enemy ships (not enemy fighters) there were two-seaters or three-seaters, like the Vingador ou de Kate. They had a rear gun, but it didn't help much. Those are the Y-Wing.
• Naval fighters with turrets like the Roc didn't work out.
• The ARC-170 feels more like the Viúva Negra. The Black Widow was an excellent noite fighter, not a dia fighter.

So a single-seater without tail guns fits the tropo. Tail guns for day fighters didn't work in WWII, so they don't happen much in Star Wars.

Some Y-Wings, at least, do have a turret above the cockpit, as in this specimen from Vampira One:

All the references I've found to Y-Wing turrets, in both canon and legends, indicate that the turrets are controlled by either a gunner in a second seat, or the pilot. (The pilot obviously has a lot to deal with already, so it tends to be locked off forward or back in this configuration.)

I'm not familiar with an X-Wing variant that has a turret, in either canon or legends.

As for why the Y-Wing has a turret gunner and not an autoturret, well, the state of Star Wars technology seems to indicate that living gunners are just better. That's why the fighters have living pilots, and the capital ships have actual gunnery crews.

Another thing to consider is Manpower. The clone army has troops to spare so creating a ship that needs 6-7 soldirs to fly it and man all the guns is not a problem, if that ship is shot down there are plenty of replacements to call up for the lost soldirs. By the time of the rebellion manpower is stretched thinly it is therefore better to have a crew of 2 split between 2 seperate fighters then to put those 2 in one ship. At least then if a ship is lost you only lose 1 pilot and by fighting as wings the risk of not having a rear gunner is somewhat negated by the fact your wingman can take out the ship behind you.

The rebellion was also limited by which ships it could lay its hands on. The X Wing was designed to be easy and quick to build and so the rebellion took what they could get there hands on.

There are no turrets or rear gunner in the X-Wings because they are fighters, and fighters dogfight with other fighters by maneuvering rapidly.

The Y-Wing is an attack bomber and tends to deliver its payload of ordnance in relative stable flight in a straight in approach, hence a rear gunner is useful to defend against rear attacks while the ship is making an attack run.