I believe the basic reason is that particles in the exhaust from rocket fuel scatter and reflect sunlight back to us. I think it is easiest to see this happen after sunset (on the ground) when it is starting to get dark but sunlight can still hit the rocket exhaust since it is at high altitude.

We can also see the same sort of effect when there are interesting clouds or smoke from a fire in the sky at sunset. You can also try looking at airplanes flying after sunset. Sometimes they will have a glow of reflected sunlight.

Rockets of the kind that take up space shuttles (launched by NASA in the Florida or the European Space Agency in French Guiana) just have a lot of smoke and bright light trailing them. The color of the light changes from red to white depending on how hot it is. They are nice to watch but not as colorful as the firecracker rockets that one sees on the fourth of July. In firecracker rockets, certain special salts are added to give the flame nice colors. For example, potassium salts provide a nice crimson color, blue is provided by copper salts, green by barium salts etc. The actual mechanism whereby these salts, when heated emit light of different colors requires an understanding of the structures of the atoms.