Just leaving this here for anyone else experiencing the same issue.

Check your account’s EIP limit. Redshift automatically allocates EIPs, and if your account has reached its limit(in my case It's 5), the operation will silently fail both in the console and via CLI. Really frustrating issue.

I honestly don’t understand how such a serious issue has persisted for over three years.

https://repost.aws/knowledge-center/redshift-serverless-publicly-accessible