Not quite in real world. The problem with looking down and water in particular are reflections. Although its "easy" to work out how to polarise a single direction light source that rapidly becomes a nightmare when its reflected off about 23 different waves and bounced back etc.
Also theres an important difference between linear and circular polarisers. Drones ones are circular and need to be adjusted.
Whilst what you say is true that polarisation effect is highest at 90 degrees to the light source and none directly into and away from the light source that assume the filter is adjusted correctly in the first place. By rotating you're actually changing the polarisation angles blocked so it needs to be correct to do anything at all regardless of angles.
Ultimately the way to use them in real life is set yourself up with the sun at the same angle you plan your shot, rotate it whilst looking through it, when you see the effect (glare goes, sky darkens or whatever), look at the markers on the side and fit it to the drone at that exact angle.
Then provided you maintain that angle on the drone you'll have polarisation (provided you arent directly into or away from the light source as said above).
Obviously, rotate the drone, find a new subject and so on and that effect is going to be minimal or zero.
You see lots of people saying "i took this awesome sunset shot with a PL" whilst looking straight into the sun. Those people havent understood it and the PL had no effect.
From that CPLs are only really useful if you know in advance what shot you want and set it up before hand so obviously more useful for stills. I do use it a bit for video as i do a lot of coral reef video so want to remove glare. But the setup is the same and i know once i change angles ive lost the effect.
Where you really DONT want one is a panorama or 360. As those cover a range of angles from the light source you get light/dark patches in sky where there is and isn't polarising effect and it looks quite ugly so take it off if you're planning on those on a flight.
For example a 180 degree panorama is going to go all angles from directly into, 90 degrees from and directly away from a light source so there'll be areas of no and areas of maximum polarisation.
https://hoyafilterusa.com/how-circular-polarizer-filters-work/ isnt particularly well written but gives an idea.