I think you need to use scale_fill_manual since you are using the fill aesthetic.
library(tidyverse)
#> Warning: package 'tibble' was built under R version 4.1.2
DF <- data.frame(Station = c("A", "B", "C", "A", "B", "C"),
median = c(2,4,3,3,5,4),
Species = c("Y", "Y", "Y", "Z", "Z", "Z"))
ggplot(DF, aes(Station, median, fill = Species)) + geom_col()

COLORS = c(Y = "pink", Z = "green")
legend_order <- c("Z", "Y")
ggplot(DF, aes(Station, median, fill = Species)) + geom_col() +
scale_fill_manual(breaks = legend_order, values = COLORS)

Created on 2022-06-10 by the reprex package (v2.0.1)