Where is the pgpass file in pgadmin4 docker container when this file is mounted as a volume

Question

I'm using the following image https://hub.docker.com/r/dpage/pgadmin4/ to set up pgAdmin4 on Ubuntu 18-04.

I have mounted a volume containing a pgpass file (which was also chmod for the pgadmin user inside the container) as you can see in my Compose file:

version: '3.8'
services:
  pgadmin4:
    image: dpage/pgadmin4
    container_name: pgadmin4
    environment:
      - PGADMIN_DEFAULT_EMAIL=me@localhost
      - PGADMIN_DEFAULT_PASSWORD=******************
      - PGADMIN_LISTEN_PORT=5050
      - PGADMIN_SERVER_JSON_FILE=servers.json
    volumes:
      - ./config/servers.json:/pgadmin4/servers.json # <-- this file is well taken into account
      - ./config/pgpass:/pgpass # <- there is no way to find this one on the other hand
    ports:
      - "5000:5000"
    restart: unless-stopped
    network_mode: host

But the it seems it's not recognized from the pgadmin webpage when I right click on a server and check its Advanced properties:

And if I manually specify /pgpass in the top greenish box where there's only a slash in the image, it says:

But if I log into the container, I can actually list that file:

/ $ ls -larth /pgpass
-rw-------    1 pgadmin  pgadmin      574 Mar 10 22:37 /pgpass

What did I do wrong?
How can I get the pgpass file to be recognized by the application?

Answer 1

I got it working with the following insight.

In servers.json when you specify:

"PassFile": "/pgpass"

It means that / in the path begins in the user's storage dir, i.e.

pattern:

/var/lib/pgadmin/storage/<USERNAME>_<DOMAIN>/

example:

/var/lib/pgadmin/storage/postgres_acme.com/

Here's a working example that copies everything into the right spot and sets the perms correctly.

  pgadmin:
    image: dpage/pgadmin4
    restart: unless-stopped
    environment:
      PGADMIN_DEFAULT_EMAIL: postgres@acme.com
      PGADMIN_DEFAULT_PASSWORD: postgres
      PGADMIN_LISTEN_ADDRESS: '0.0.0.0'
      PGADMIN_LISTEN_PORT: 5050
    tty: true
    ports:
      - 5050:5050
    volumes:
      - ~/data/pgadmin_data:/var/lib/pgadmin
      - ./local-cloud/servers.json:/pgadmin4/servers.json # preconfigured servers/connections   
      - ./local-cloud/pgpass:/pgadmin4/pgpass # passwords for the connections in this file
    entrypoint: >
      /bin/sh -c "
      mkdir -m 700 /var/lib/pgadmin/storage/postgres_acme.com;
      chown -R pgadmin:pgadmin /var/lib/pgadmin/storage/postgres_acme.com;
      cp -prv /pgadmin4/pgpass /var/lib/pgadmin/storage/postgres_acme.com/;
      chmod 600 /var/lib/pgadmin/storage/postgres_acme.com/pgpass;
      /entrypoint.sh
      " 

Answer 2

On pgAdmin 6.2, PassFile points to the absolute path inside container, instead of a path under STORAGE_DIR (/var/lib/pgadmin).

Before the entrypoint phase, just need to set owner and permissions for the pgpass file.

docker-compose.yml

  pgadmin:
    image: dpage/pgadmin4:6.2
    entrypoint: >
      /bin/sh -c "
      cp -f /pgadmin4/pgpass /var/lib/pgadmin/;
      chmod 600 /var/lib/pgadmin/pgpass;
      chown pgadmin:pgadmin /var/lib/pgadmin/pgpass;
      /entrypoint.sh
      "
    environment:
      PGADMIN_DEFAULT_EMAIL: ${PGADMIN_DEFAULT_EMAIL:-pgadmin4@pgadmin.org}
      PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_DEFAULT_PASSWORD:-admin}
      PGADMIN_CONFIG_SERVER_MODE: "False"
      PGADMIN_CONFIG_MASTER_PASSWORD_REQUIRED: "False"
    volumes:
      - ./config/servers.json:/pgadmin4/servers.json
      - ./config/pgpass:/pgadmin4/pgpass
    ports:
      - "${PGADMIN_PORT:-5050}:80"

servers.json

{
  "Servers": {
    "1": {
      "Name": "pgadmin4@pgadmin.org",
      "Group": "Servers",
      "Host": "postgres",
      "Port": 5432,
      "MaintenanceDB": "postgres",
      "Username": "postgres",
      "SSLMode": "prefer",
      "PassFile": "/var/lib/pgadmin/pgpass"
    }
  }
}

pgpass

postgres:5432:postgres:postgres:Welcome01

Update:

Updated entrypoint on docker-compose.yml and PassFile on servers.json for a cross platform working solution.

Update 2:

I created a container image (dcagatay/pwless-pgadmin4) for passwordless pgadmin4.

Answer 3

The following config worked for me:

• pgpass
• servers.json
• docker-compose.yaml
• dockerfile_for_pgadmin

pgpass

docker_postgres_db:5432:postgres:postgres:postgres

servers.json

{
  "Servers": {
    "1": {
      "Name": "docker_postgres",
      "Group": "docker_postgres_group",
      "Host": "docker_postgres_db",
      "Port": 5432,
      "MaintenanceDB": "postgres",
      "Username": "postgres",
      "PassFile": "/pgpass",
      "SSLMode": "prefer"
    }
  }
}

docker-compose.yaml

version: "3.9"
services:

  docker_postgres_db:
    image: postgres
    volumes:
      - ./postgres_db_data:/var/lib/postgresql/data # mkdir postgres_db_data before docker compose up
    environment:
      - POSTGRES_DB=postgres
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    ports:
      - "15432:5432"


  pgadmin:
     build:
       context: .
       dockerfile: ./dockerfile_for_pgadmin
     environment:
       PGADMIN_DEFAULT_EMAIL: pgadmin@pgadmin.com
       PGADMIN_DEFAULT_PASSWORD: pgadmin
     ports:
       - "5050:80"
     volumes:
       - ./servers.json:/pgadmin4/servers.json # preconfigured servers/connections

dockerfile+for_pgadmin

FROM dpage/pgadmin4
USER pgadmin
RUN mkdir -p  /var/lib/pgadmin/storage/pgadmin_pgadmin.com
COPY ./pgpass /var/lib/pgadmin/storage/pgadmin_pgadmin.com/
USER root
RUN chown pgadmin:pgadmin /var/lib/pgadmin/storage/pgadmin_pgadmin.com/pgpass
RUN chmod 0600 /var/lib/pgadmin/storage/pgadmin_pgadmin.com/pgpass
USER pgadmin
ENTRYPOINT ["/entrypoint.sh"]

Answer 4

The problem here seems to be that '/' in the servers.json file does not mean '/' in the filesystem, but something relative to the STORAGE_DIR set in the config. In fact, a separate storage directory for each user is created, so with your user me@localhost you will have to mount ./config/pgpass to /var/lib/pgadmin4/storage/me_localhost/pgpass, but still refer to it as /pgpass in your servers.json.

Answer 5

I'm running the latest version of pgadmin4 as of this post (6.11). It took me forever to find the answer as to how to set a pgpass file location without storing it in the user's uploads dir (insecure IMO).

Unfortunately it does not seem to work using an absolute path e.g. /var/lib/pgadmin/pgpass.

However, what did work was this workaround I found here: https://github.com/rowanruseler/helm-charts/issues/72#issuecomment-1002300143

Basically if you use ../../pgpass, you can traverse the filesystem instead of the default behaviour of looking inside the user's uploads folder.

Example servers.json:

{
  "Servers": {
    "1": {
      "Name": "my-postgres-instance",
      "Group": "Servers",
      "Host": "postgres",
      "Port": 5432,
      "MaintenanceDB": "postgres",
      "Username": "postgres",
      "SSLMode": "prefer",
      "PassFile": "../../pgpass"
    }
  }
}

Also, setting the file permission as 0600 is a critical step - the file can not be world-readable, see https://stackoverflow.com/a/28152568/15198761 for more info.

In a K8s environment, using the offical pgadmin image I use a configmap for the servers.json along with the following command:

          command:
            - sh
            - -c
            - |
              set -e
              cp -f /pgadmin4/pgpass /var/lib/pgadmin/
              chown 5050:5050 /var/lib/pgadmin/pgpass
              chmod 0600 /var/lib/pgadmin/pgpass
              /entrypoint.sh

Using a combination of the above, I was finally able to connect to my postgres instance without needing to put in a password or keeping the pgpass file in the user's uploads dir.

Answer 6

For pgadmin 7.4:

None of the answers here directly helped me, however cobbling them together, I managed to produce a solution.

For me, the key was to run the container as the root user, which allows me to run chmod. As others mention, you can't run chmod when you're logged in as pgadmin.

docker_compose.yaml:

  pgadmin:
    image: dpage/pgadmin4
    restart: unless-stopped
    user: "root"
    volumes:
      - ./pgadmin/servers.json:/pgadmin4/servers.json
      - ./pgadmin/pgpass:/pgadmin4/pgpass
    depends_on:
      - postgres
    ports:
      - "8000:80"
    environment:
      PGADMIN_DEFAULT_EMAIL: admin@pgadmin.org
      PGADMIN_DEFAULT_PASSWORD: admin
    entrypoint: >
      sh -c "
      cp -f /pgadmin4/pgpass /var/lib/pgadmin;
      chmod 600 /var/lib/pgadmin/pgpass;
      /entrypoint.sh
      "

servers.json:

{
  "Servers": {
    "1": {
      "Name": "Server",
      "Group": "Servers",
      "Host": "postgres",
      "Port": 5432,
      "Username": "postgres",
      "MaintenanceDB": "postgres",
      "SSLMode": "disable",
      "PassFile": "../../pgpass"
    }
  }
}

Note that, although someone mentions that the relative path for PassFile issue was fixed in pgadmin6, an absolute path did not appear to work for me, so I stuck with the relative.

Answer 7

today I was tasked with deploying pgAdmin to an AKS cluster (so we can access our databases deployed into private network) - I am serving it publicly via application gateway ingress controller via {app_gw_public_ip}:5432

Thanks to this thread (and few others) i managed to produce quite universal kubernetes manifest to achieve my goal, you can find it below. I hope somebody will find it useful (at least as an insipration or a baseline to start with)

This manifest assumes you already have AKS cluster with properly configured AGIC and stuff. Also, I am replacing all tokens (${xxx}) via my CICD pipelines.

Please keep in mind that in my case I am still on a POC stage of the project, therefore this solution is not fully production-friendly (as in my case I am serving the pgAdmin publicly via app gateway and the only protection to the database is actually the pgadmin login/password) - in produciton environments access to the pgAdmin should not be public, and rather limited to selected IP addresses.

Few remarks about below manifest:

1. This setup automatically loads the servers.json and couples it with the pgpass file - so after logging in to PgAdmin there are no additional inputs necessary to start querying the databases
2. pgadmin-config-system-py was necessary in my case as I was seeing MIME type (“text/html”) mismatch and was not able to log in to the console

kind: StatefulSet
apiVersion: apps/v1
metadata:
 name: pgadmin
spec:
 serviceName: pgadmin-service
 podManagementPolicy: Parallel
 replicas: 1
 updateStrategy:
   type: RollingUpdate
 selector:
   matchLabels:
     app: pgadmin
 template:
   metadata:
     labels:
       app: pgadmin
   spec:
     terminationGracePeriodSeconds: 10
     containers:
       - name: pgadmin
         image: dpage/pgadmin4:7.5
         securityContext:
           runAsUser: 0
           runAsGroup: 0
         imagePullPolicy: Always
         command: ["/bin/sh", "-c"]
         args: 
           - set -e;
             cp -f /pgadmin4/pgpass.txt /var/lib/pgadmin/;
             chown 5050:5050 /var/lib/pgadmin/pgpass.txt;
             chmod 0600 /var/lib/pgadmin/pgpass.txt;
             /entrypoint.sh;
         env:
         - name: PGADMIN_DEFAULT_EMAIL
           value: ${pgAdminLogin}
         - name: PGADMIN_DEFAULT_PASSWORD
           valueFrom:
             secretKeyRef:
               name: pgadmin
               key: pgadmin-password
         ports:
         - name: http
           containerPort: 80
           protocol: TCP
         volumeMounts:
         - name: pgadmin-data
           mountPath: /var/lib/pgadmin
         - name: pgadmin-config-system-py
           mountPath: /etc/pgadmin/config_system.py
           subPath: config_system.py
           readOnly: true
         - name: pgadmin-servers-json
           mountPath: /pgadmin4/servers.json
           subPath: servers.json
           readOnly: true
         - name: pgadmin-pgpass-txt
           mountPath: /pgadmin4/pgpass.txt
           subPath: pgpass.txt
           readOnly: true

     volumes:
     - name: pgadmin-config-system-py
       configMap:
         name: pgadmin-config-system-py
     - name: pgadmin-servers-json
       configMap:
         name: pgadmin-servers-json
     - name: pgadmin-pgpass-txt
       configMap:
         name: pgadmin-pgpass-txt

 volumeClaimTemplates:
 - metadata:
     name: pgadmin-data
   spec:
     accessModes: [ "ReadWriteOnce" ]
     resources:
       requests:
         storage: 3Gi

---
kind: Service
apiVersion: v1
metadata:
 name: pgadmin-service
spec:
 type: ClusterIP
 selector:
   app: pgadmin
 ports:
 - port: 5432
   targetPort: 80
   name: http

---
kind: Ingress
apiVersion: networking.k8s.io/v1
metadata:
  name: pgadmin-http-ingress
  annotations:
    kubernetes.io/ingress.class: azure/application-gateway
    appgw.ingress.kubernetes.io/override-frontend-port: "5432"
    appgw.ingress.kubernetes.io/use-regex: 'true'
    appgw.ingress.kubernetes.io/request-timeout: "300"
spec:
  rules:
    - http:
        paths:         
          - path: /
            pathType: Prefix
            backend:
              service:
                name: pgadmin-service
                port:
                  number: 5432

---
kind: Secret
apiVersion: v1
type: Opaque
metadata:
 name: pgadmin
stringData:
 pgadmin-password: ${pgAdminPassword}

---
kind: ConfigMap
apiVersion: v1
metadata:
 name: pgadmin-config-system-py
data:
 config_system.py: |
    X_CONTENT_TYPE_OPTIONS = ""    # default value is nosniff
    ENHANCED_COOKIE_PROTECTION = False
    X_XSS_PROTECTION = "0"  # default value is '1; mode=block'

---
kind: ConfigMap
apiVersion: v1
metadata:
 name: pgadmin-servers-json
data:
 servers.json: |
    {
      "Servers": {
        "1": {
          "Name": "Thingsboard DB",
          "Group": "Servers",
          "Host": "${psqlFQDN}",
          "Port": 5432,
          "Username": "${psqlLogin}",
          "PassFile": "../../pgpass.txt",
          "SSLMode": "prefer",
          "MaintenanceDB": "postgres"
        }
      }
    }

---
kind: ConfigMap
apiVersion: v1
metadata:
 name: pgadmin-pgpass-txt
data:
 pgpass.txt: |
    ${psqlFQDN}:5432:*:${psqlLogin}:${psqlPassword}

Answer 8

None of the answers here were working for me with the dpage/pgadmin4:7.4 image. In the end I based my solution on Frictionless Local Postgres with Docker Compose :

version: '3.7'
name: 'myproject'
services:
  postgres:
    image: postgres:15.4-bullseye
    restart: always
    environment:
      - POSTGRES_DB=postgres
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=password
    logging:
      options:
        max-size: 10m
        max-file: "3"
    ports:
      - '5432:5432'
    volumes:
      - postgres-data:/var/lib/postgresql/data
  pgadmin4:
    image: dpage/pgadmin4:7.6
    restart: always
    user: 'root'
    depends_on:
      - postgres
    ports:
      - '5050:80'
    environment:
      PGADMIN_CONFIG_MASTER_PASSWORD_REQUIRED: 'False'
      PGADMIN_CONFIG_SERVER_MODE: 'False'
    volumes:
      - pgadmin4-data:/var/lib/pgadmin
    entrypoint: >
      /bin/sh -c '
      echo "postgres:5432:*:postgres:password" > /tmp/pgpass;
      chmod 600 /tmp/pgpass;
      echo "{\"Servers\":{\"1\":{\"Name\":\"postgres\",\"Group\":\"Servers\",\"Host\":\"postgres\",\"Port\":5432,\"MaintenanceDB\":\"postgres\",\"Username\":\"postgres\",\"PassFile\":\"/tmp/pgpass\",\"SSLMode\":\"prefer\"}}}" > /pgadmin4/servers.json;
      /entrypoint.sh
      '

volumes:
  postgres-data:
  pgadmin4-data:

This allows you to connect to http://localhost:5050 and start managing the database without entering any password. Likely you can do some environment variable interpolation to configure everything dynamically, without having to modify servers.json or pgpass templates locally...