Connect to PSQL through K8s Ingress

Question

I got my own cluster which has a control-plane and worker. i trying to connect to PostgreSQL in the cluster using psql -h -p 5432 -U postgres -W, but it occur an error:

psql: error: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

But curl response like this:

2022-05-27 04:00:50.406 UTC [208] LOG:  invalid length of startup packet

That response mean my request have reached to PostgreSQL Server, but why i cannot connect to my PostgreSQL Server?

Here are my resources:

Ingress:

apiVersion: networking.k8s.io/v1
kind: Ingress
metadata:
  name: preflight
  labels:
    helm.sh/chart: "preflight"
    helm.sh/version: "0.1.0"
  annotations:
    nginx.ingress.kubernetes.io/rewrite-target: /
    nginx.ingress.kubernetes.io/ssl-redirect: "false"
spec:
  ingressClassName: nginx
  rules:
  - http:
      paths:
      - path: /
        pathType: Exact
        backend:
          service:
            name: preflight
            port:
              number: 5432

Deployment:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: preflight
  labels:
    helm.sh/chart: "preflight"
    helm.sh/version: "0.1.0"
spec:
  replicas: 1
  selector:
    matchLabels:
      helm.sh/chart: "preflight"
      helm.sh/version: "0.1.0"
  template:
    metadata:
      labels:
        helm.sh/chart: "preflight"
        helm.sh/version: "0.1.0"
    spec:
      containers:
      - name: preflight
        image: postgres:14
        env:
        - name: POSTGRES_PASSWORD
          valueFrom:
            configMapKeyRef:
              name: preflight
              key: postgresPassword
        ports:
        - containerPort: 5432

Service:

apiVersion: v1
kind: Service
metadata:
  name: preflight
  labels:
    helm.sh/chart: "preflight"
    helm.sh/version: "0.1.0"
spec:
  type: ClusterIP
  selector:
    helm.sh/chart: "preflight"
    helm.sh/version: "0.1.0"
  ports:
  - port: 5432
    targetPort: 5432

ConfigMap values is a POSTGRES_PASSWORD=postgres.

user_1078969 · Answer

Nginx ingress controller is an HTTP proxy. You are trying to route PGSQL traffic over HTTP, and that simply can't work.

What you need to do is expose a TCP service through nginx ingress controller. See this page.

In a nutshell, you need to create a configmap like this:

apiVersion: v1
kind: ConfigMap
metadata:
  name: tcp-services
  namespace: 
data:
  5432: "default/preflight:5432"

then ensure your nginx ingress controller starts with the --tcp-services-configmap=tcp-services flag.

Finally, ensure the nginx ingress controller Service (the one with type == LoadBalancer) exposes port 5432:

apiVersion: v1
kind: Service
metadata:
  name: ingress-nginx
...
spec:
  type: LoadBalancer
  ports:
    ...
    - name: pgsql
      port: 5432
      targetPort: 5432
      protocol: TCP
...

Please note that your provider's Load Balancer should support TCP backends (most should do, but worth mentioning).

Connect to PSQL through K8s Ingress

1 Answers