POSTGRES_PASSWORD ignored and can access DB without or with any password

Question

As the title says, I'm setting a POSTGRES_PASSWORD and after spinning up the cluster with Skaffold (--port-forward on so I can access the DB with pgAdmin), I can access the database with or without the correct password. POSTGRES_DB and POSTGRES_USER work as expected.

I am seeing in the documentation on Docker Hub for Postgres:

Note 1: The PostgreSQL image sets up trust authentication locally so you may notice a password is not required when connecting from localhost (inside the same container). However, a password will be required if connecting from a different host/container.

I think the --port-forward could possibly be the culprit since it is registering as localhost.

Anyway to prevent this behavior?

I guess the concern is someone having access to my laptop and easily being able to connect to the DB.

This is my postgres.yaml:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres-deployment
spec:
  replicas: 1
  selector:
    matchLabels:
      component: postgres
  template:
    metadata:
      labels:
        component: postgres
    spec:
      containers:
        - name: postgres
          image:testproject/postgres
          ports:
            - containerPort: 5432
          env: 
            - name: POSTGRES_DB
              value: dev
            - name: POSTGRES_USER
              value: dev
            - name: POSTGRES_PASSWORD
              value: qwerty
          volumeMounts:
            - name: postgres-storage
              mountPath: /var/lib/postgresql/data
              subPath: postgres
      volumes:
        - name: postgres-storage
          persistentVolumeClaim:
            claimName: postgres-storage
---
apiVersion: v1
kind: Service
metadata:
  name: postgres-cluster-ip-service
spec:
  type: ClusterIP
  selector:
    component: postgres
  ports:
    - port: 5432
      targetPort: 5432

And the skaffold.yaml:

apiVersion: skaffold/v1beta15
kind: Config
build:
  local:
    push: false
  artifacts:
    - image: testproject/postgres
      docker:
        dockerfile: ./db/Dockerfile.dev
      sync:
        manual:
          - src: "***/*.sql"
            dest: .
    - image: testproject/server
      docker:
        dockerfile: ./server/Dockerfile.dev
      sync:
        manual:
          - src: "***/*.py"
            dest: .
deploy:
  kubectl:
    manifests:
      - k8s/ingress.yaml 
      - k8s/postgres.yaml
      - k8s/server.yaml

The Dockerfile.dev too:

FROM postgres:11-alpine
EXPOSE 5432
COPY ./db/*.sql /docker-entrypoint-initdb.d/

Earwig

For Postgres itself that's controlled in the pg_hba.conf. I don't know how that comes into play in your environment though. But maybe it's valuable as a hint. – Hertfordshire

As sticky said it is controller by pg_hba.conf. You can change all trust to md5 to force password authorization – Driving

Answer 1

Ok, reread the postgres Docker docs and came across this:

POSTGRES_INITDB_ARGS

This optional environment variable can be used to send arguments to postgres initdb. The value is a space separated string of arguments as postgres initdb would expect them. This is useful for adding functionality like data page checksums: -e POSTGRES_INITDB_ARGS="--data-checksums".

That brought me to the initdb docs:

--auth=authmethod

This option specifies the authentication method for local users used in pg_hba.conf (host and local lines). Do not use trust unless you trust all local users on your system. trust is the default for ease of installation.

That brought me to the Authentication Methods docs:

19.3.2. Password Authentication

The password-based authentication methods are md5 and password. These methods operate similarly except for the way that the password is sent across the connection, namely MD5-hashed and clear-text respectively.

If you are at all concerned about password "sniffing" attacks then md5 is preferred. Plain password should always be avoided if possible. However, md5 cannot be used with the db_user_namespace feature. If the connection is protected by SSL encryption then password can be used safely (though SSL certificate authentication might be a better choice if one is depending on using SSL).

PostgreSQL database passwords are separate from operating system user passwords. The password for each database user is stored in the pg_authid system catalog. Passwords can be managed with the SQL commands CREATE USER and ALTER ROLE, e.g., CREATE USER foo WITH PASSWORD 'secret'. If no password has been set up for a user, the stored password is null and password authentication will always fail for that user.

Long story short, I just did this and it takes only the actual password now:

env: 
  ...
  - name: POSTGRES_INITDB_ARGS
    value: "-A md5"

Earwig