I know that の shows possession, but I've also seen translations such as "Frog on stairs" from
「階段の蛙」
to say "on" or "at"
When is it actually used, and what other meanings does this particle have?
Japanese Language Stack Exchange is a question and answer site for students, teachers, and linguists wanting to discuss the finer points of the Japanese language. It only takes a minute to sign up.
Sign up to join this community「階段の蛙」translates literally to "the frog of the stair", which is to be understood as "the frog [that is] on the stair". So this expression does not actually deviate from the 'standard' meaning of の, in my opinion.
A construction that I think you would be interested in here would be something like こいつは妹のカエデ, ie. "this is my sister Kaede", which would be literally "Kaede of sister", though it should be understood "Kaede [who is] a sister".