how to expose postgres hosted in EKS and connect to postgres using PG admin or DBeaver non SSL

in kubernetes i have postgres as a stateful set and i have it defined as service postgres then i want to expose it as an ingress. i have changed type from clusterip to NodePort of the service and i have created ingress for postgres like below

kind: Ingress
metadata:
  name: postgres
  namespace: postgres

  annotations:
    alb.ingress.kubernetes.io/group.name: eks-dev-test-postgres-group
    alb.ingress.kubernetes.io/listen-ports: '[{"HTTP":5432}]'
    alb.ingress.kubernetes.io/load-balancer-name: eks-dev-test-alb-postgres
    alb.ingress.kubernetes.io/scheme: internal
    alb.ingress.kubernetes.io/tags: Environment=dev-test,Team=devops


spec:
  ingressClassName: alb
  rules:
    - host: postgres.test.XXXXXX.com
      http:
        paths:
          - path: /
            pathType: Prefix
            backend:
              service:
                name: postgres
                port:
                  number: 5432

i use alb ingress controller to create/manage ingress

here in AWS, i have created new load balancer name : eks-dev-test-alb-postgres region : us-east-1 load balancer arn: arn:aws:elasticloadbalancing:us-east-1:XXXXX:loadbalancer/app/eks-dev-test-alb-postgres/XXXXX and security group inbound rules updated to 5432 opens to everyone this vpc is secured by aws vpn and i`m connected to vpn

i turned off ssl in pgadmin and attached the snip of error

in DB weaver, when im trying to TEST CONNECTION in universal database management tool (DBeaver) im facing "An error occurred while setting up the SSL connection".

ANSWER:

Service type should be : LoadBalancer

in service annotations : nlb and internal should add like below

apiVersion: v1
kind: Service
metadata:
  name: postgres-postgresql-external
  labels:
    app: postgresql
  annotations:
    service.beta.kubernetes.io/aws-load-balancer-internal: "true"
    service.beta.kubernetes.io/aws-load-balancer-type: nlb
spec:
  type: LoadBalancer
  ports:
  - name: postgresql
    port: 5432
    targetPort: postgresql
  selector:
    app: postgresql
    release: "postgres"
  loadBalancerSourceRanges:
  - "172.16.0.0/16"

---

REF link: https://kubernetes-sigs.github.io/aws-load-balancer-controller/v2.5/guide/service/nlb/

Network Load Balancer (NLB) doesnt have SG.

In EC2 Instance Security Group need to open as 5432

REF link: https://repost.aws/questions/QUuueXAi20QuisbkOhinnbzQ/aws-nlb-security-group

Check telnet

then disable SSL in pgamin while creating connection

Test the connection

Ingress only works for HTTP traffic. You will not able to expose TCP/5432 using ingress.

Consider creating a service of type LoadBalancer with the appropriate annotations by following the AWS Load Balancer Controller documentation:

https://kubernetes-sigs.github.io/aws-load-balancer-controller/v2.5/guide/service/nlb/