search

pgadmin-org / pgadmin4

pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world.
https://www.pgadmin.org
Other
2.47k stars 648 forks source link

Password not loaded from .pgpass file #8029

Open Brice-D opened 1 week ago

Brice-D commented 1 week ago

Describe the bug

I setup a pgadmin service in my docker compose file with preloaded servers in servers.json and credentials configured in .pgpass. The servers are loaded at start up but when I open it in the browser I'm getting the following error and I need to enter the password manually

image

To Reproduce

Steps to reproduce the behavior:

servers.json

{
  "Servers": {
    "1": {
      "Name": "Servers",
      "Group": "GrafanaGroup",
      "Host": "pg_data_wh",
      "Port": 5432,
      "MaintenanceDB": "postgres",
      "Username": "grafana_data_user",
      "PassFile": "/var/lib/storage/pgpass/.pgpass",
      "SSLMode": "prefer",
      "ConnectionTimeout": 10
    }
  }
}

.pgpass

#hostname:port:database:username:password
pg_data_wh:5432:grafanaDataDB:grafana_data_user:heremypassword

service in docker compose

  pg_admin:
    hostname: pg_admin
    image: dpage/pgadmin4
    restart: always
    user: root
    environment:
      PGADMIN_DEFAULT_EMAIL: localadmin@test.com
      PGADMIN_DEFAULT_PASSWORD: mypgadminpasswordhere
      PGADMIN_LISTEN_ADDRESS: 0.0.0.0
      # more from https://www.pgadmin.org/docs/pgadmin4/8.12/container_deployment.html
      # PGADMIN_SERVER_JSON_FILE:
      PGPASSFILE: /var/lib/storage/pgpass/.pgpass
      PGADMIN_CONFIG_CONSOLE_LOG_LEVEL: 10
    ports:
      - "15432:80"
    volumes:
      - ./config/ZscalerRootCertificate-2048-SHA256.crt:/tmp/ZscalerRootCertificate-2048-SHA256.crt
      #- ./servers.json:/pgadmin4/servers.json
      #- pgAdminData:/var/lib/pgadmin
      - ./.pgpass:/etc/tmp/.pgpass
      - ./servers.json:/etc/tmp/servers.json
    entrypoint: >
      /bin/sh -c "
        mkdir -p /etc/ssl/certs/zscaler && \
        cp /tmp/ZscalerRootCertificate-2048-SHA256.crt /etc/ssl/certs/zscaler/ && \
        cat /etc/ssl/certs/zscaler/ZscalerRootCertificate-2048-SHA256.crt >> /etc/ssl/certs/ca-certificates.crt && \
        mkdir -p /var/lib/storage/pgpass && \
        cp /etc/tmp/.pgpass /var/lib/storage/pgpass/.pgpass && \
        chown pgadmin:root /var/lib/storage/pgpass/.pgpass && \
        chmod 0600 /var/lib/storage/pgpass/.pgpass && \
        cp /etc/tmp/servers.json /pgadmin4/servers.json && \
        chown pgadmin:root /pgadmin4/servers.json && \
        exec /entrypoint.sh
      "

Expected behavior

I'm expected the password to be read from .pgpass and not required when I open the servers in the Browser image

Error message

2024-10-11 07:25:56,994: INFO pgadmin: Connection Request for server#1 2024-10-11T07:25:57.006704649Z 2024-10-11 07:25:57,005: INFO pgadmin: Waiting for a lock. 2024-10-11T07:25:57.007041349Z 2024-10-11 07:25:57,006: INFO pgadmin: Acquired a lock. 2024-10-11T07:25:57.008473931Z 172.18.0.1 - - [11/Oct/2024:07:25:57 +0000] "GET /browser/server/obj/2/1 HTTP/1.1" 200 470 "http://localhost:15432/browser/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36" 2024-10-11T07:25:57.009967057Z 172.18.0.1 - - [11/Oct/2024:07:25:57 +0000] "GET /browser/server_group/nodes/ HTTP/1.1" 200 429 "http://localhost:15432/browser/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36" 2024-10-11T07:25:57.011130538Z 2024-10-11 07:25:57,010: INFO pgadmin: Released a lock. 2024-10-11T07:25:57.011179753Z 2024-10-11 07:25:57,011: INFO pgadmin: Failed to connect to the database server(#1) for connection (DB:postgres) with error message as below:connection failed: connection to server at "172.18.0.4", port 5432 failed: fe_sendauth: no password supplied 2024-10-11T07:25:57.011325101Z 2024-10-11 07:25:57,011: ERROR pgadmin: Could not connect to server(#1) - 'Servers'. 2024-10-11T07:25:57.011346488Z Error: connection failed: connection to server at "172.18.0.4", port 5432 failed: fe_sendauth: no password supplied 2024-10-11T07:25:57.011922796Z 172.18.0.1 - - [11/Oct/2024:07:25:57 +0000] "POST /browser/server/connect/2/1 HTTP/1.1" 401 303 "http://localhost:15432/browser/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36"

yogeshmahajan-1903 commented 4 days ago

@Brice-D Here is an example of docker-compose for passing pgpass file for connecting to the server. Please make sure the source file mapped to pgpass file has permission 600 or less. pgpass file should be present in user home directory.

version: "3.8"

services:
  pgadmin:
    container_name: pgadmin
    image: dpage/pgadmin4:8.12
    environment:
      - PGADMIN_DEFAULT_EMAIL=user@edb.com
      - PGADMIN_DEFAULT_PASSWORD=adminedb
      - PGADMIN_CONFIG_CONSOLE_LOG_LEVEL=10
    ports:
      - "5050:80"
    volumes:
      - ./config/servers2.json:/pgadmin4/servers.json
      - ./config/pgpass:/tmp/.pgpass
      - pgadmin-data:/var/lib/pgadmin
    entrypoint: >
        /bin/sh -c "
        mkdir -p /var/lib/pgadmin/storage/user_edb.com/;
        cp /tmp/.pgpass /var/lib/pgadmin/storage/user_edb.com/.pgpass;
        chown pgadmin:root /var/lib/pgadmin/storage/user_edb.com/.pgpass;
        chmod 600 /var/lib/pgadmin/storage/user_edb.com/.pgpass;
        /entrypoint.sh
        "

volumes:
  pgadmin-data:
    name: 'pgadmin-data_with_userlevle_pgpass'

server.json - 


{
    "Servers": {
        "1": {
            "Name": "pg16-pgpass",
            "Group": "AWS",
            "Host": "ip",
            "Port": port,
            "MaintenanceDB": "postgres",
            "Username": "postgres",
            "UseSSHTunnel": 0,
            "TunnelPort": "22",
            "TunnelAuthentication": 0,
            "KerberosAuthentication": false,
            "ConnectionParameters": {
                "sslmode": "prefer",
                "connect_timeout": 10,
                "sslcompression": 0,
                "passfile": "/.pgpass"
            }
        },
    }
}
Brice-D commented 4 days ago

@yogeshmahajan-1903 configured that way, the servers informations are no more loaded at startup and I'm getting an exception (bad parsing) -> see attached logs ErrorLogs.log

I need to mention that the container is start with the root use as you can see in my container definition. pgadmin use can't create a new directory, I was getting permission denied with pgadmin

pg_admin:
    hostname: pg_admin
    image: dpage/pgadmin4
    restart: always
    user: root
    environment:
      PGADMIN_DEFAULT_EMAIL: localadmin@test.com
      PGADMIN_DEFAULT_PASSWORD: mypassword
      PGADMIN_LISTEN_ADDRESS: 0.0.0.0
      # more from https://www.pgadmin.org/docs/pgadmin4/8.12/container_deployment.html
      # PGADMIN_SERVER_JSON_FILE:
      #POSTGRES_PASSFILE_STORE: /var/lib/storage/pgpass/.pgpass
      #PGADMIN_CONFIG_CONSOLE_LOG_LEVEL: 10
    ports:
      - "15432:80"
    volumes:
      - ./config/ZscalerRootCertificate-2048-SHA256.crt:/tmp/ZscalerRootCertificate-2048-SHA256.crt
      #- ./servers.json:/pgadmin4/servers.json
      #- pgAdminData:/var/lib/pgadmin
      - pgadmin-data:/var/lib/pgadmin
      - ./.pgpass:/tmp/.pgpass
      - ./servers.json:/pgadmin4/servers.json
    entrypoint: >
      /bin/sh -c "
        mkdir -p /etc/ssl/certs/zscaler && \
        cp /tmp/ZscalerRootCertificate-2048-SHA256.crt /etc/ssl/certs/zscaler/ && \
        cat /etc/ssl/certs/zscaler/ZscalerRootCertificate-2048-SHA256.crt >> /etc/ssl/certs/ca-certificates.crt && \
        mkdir -p /var/lib/pgadmin/storage/user_edb.com/;
        cp /tmp/.pgpass /var/lib/pgadmin/storage/user_edb.com/.pgpass;
        chown pgadmin:root /var/lib/pgadmin/storage/user_edb.com/.pgpass;
        chmod 600 /var/lib/pgadmin/storage/user_edb.com/.pgpass;
        /entrypoint.sh
      "
{
  "Servers": {
    "1": {
      "Name": "Servers",
      "Group": "GrafanaGroup",
      "Host": "pg_data_wh",
      "Port": 5432,
      "MaintenanceDB": "postgres",
      "Username": "grafana_data_user",
      "ConnectionParameters": {
        "sslmode": "prefer",
        "connect_timeout": 10,
        "sslcompression": 0,
        "passfile": "/.pgpass"
      }
    }
  }
}

.pgpass has the correct permission in the container image

yogeshmahajan-1903 commented 3 days ago

@Brice-D I guess server.json has incorrect key 'SSLmode'. It should be "sslmode". Servers from server.json is loaded only first time database creation. If persistent volume is used, then servers will not be loaded again. If you you want to add new servers to existing container with persistent volume, then you can use UI utility Tools > Import/Export Server. For existing servers, you need to add parameter form server configuration dialogue.