My pain point . I have an Ec2 with Postgres installed(which is in private subnet), everything is working fine. All member can connect to DB only when they are in the DEV cluster (I mean same CIDR, security group is designed to get traffic only from that CIDR). The problem here is I wanted to get connection locally. I cannot alter the security group. Intially I planned to create an public Network load balancer and make the ec2 as target with port 5432 and installed apache2 for health check (200 success as health). But still unable to connect that Ec2 machine. Can anyone advice the best practise.
As per our policy only HTTPS port can be opened
If you are limited to only enabling 443 open then you would need to update your Postgres instance to be served over that port, although the recommended port for both TLS and plain text connections to Postgres is 5432.
For a DB instance you would use a Network Load Balancer, the inbound traffic is determined by the inbound security group rules of the instances.
You should avoid installing apache on your postgres servers to allow load balancer health checks to pass as its not related to the health check of the postgres service. Instead a TCP health check on the port of your Postgres service should be checked.
I would suggest discussing with your peers as to why Postgres must run on port 443, as it is not best practice and could lead to confusion. Normally these kinds of limitations are limited for web traffic, and for other services in the future you might find you're limited with available ports.