I'd like to provide the db credentials to pgadmin at startup. I have the following error (scroll to the bottom):
postgis |
postgis | PostgreSQL Database directory appears to contain a database; Skipping initialization
postgis |
postgis | 2023-03-01 23:55:55.287 UTC [1] LOG: starting PostgreSQL 15.2 (Debian 15.2-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
postgis | 2023-03-01 23:55:55.288 UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432
postgis | 2023-03-01 23:55:55.288 UTC [1] LOG: listening on IPv6 address "::", port 5432
postgis | 2023-03-01 23:55:55.296 UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgis | 2023-03-01 23:55:55.301 UTC [29] LOG: database system was interrupted; last known up at 2023-03-01 23:54:41 UTC
postgis | 2023-03-01 23:55:55.603 UTC [29] LOG: database system was not properly shut down; automatic recovery in progress
postgis | 2023-03-01 23:55:55.607 UTC [29] LOG: redo starts at 0/402DCF0
postgis | 2023-03-01 23:55:55.607 UTC [29] LOG: invalid record length at 0/402DD28: wanted 24, got 0
postgis | 2023-03-01 23:55:55.607 UTC [29] LOG: redo done at 0/402DCF0 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
postgis | 2023-03-01 23:55:55.612 UTC [27] LOG: checkpoint starting: end-of-recovery immediate wait
postgis | 2023-03-01 23:55:55.620 UTC [27] LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.002 s, total=0.011 s; sync files=2, longest=0.002 s, average=0.001 s; distance=0 kB, estimate=0 kB
postgis | 2023-03-01 23:55:55.629 UTC [1] LOG: database system is ready to accept connections
backend exited with code 0
pgadmin | NOTE: Configuring authentication for SERVER mode.
pgadmin |
pgadmin | Traceback (most recent call last):
pgadmin | File "/pgadmin4/run_pgadmin.py", line 4, in <module>
pgadmin | from pgAdmin4 import app
pgadmin | File "/pgadmin4/pgAdmin4.py", line 93, in <module>
pgadmin | app = create_app()
pgadmin | File "/pgadmin4/pgadmin/__init__.py", line 487, in create_app
pgadmin | paths.init_app()
pgadmin | File "/pgadmin4/pgadmin/utils/paths.py", line 102, in init_app
pgadmin | raise InternalServerError(
pgadmin | werkzeug.exceptions.InternalServerError: 500 Internal Server Error: The user does not have permission to read and write to the specified storage directory.
pgadmin | ----------
pgadmin | Loading servers with:
pgadmin | User: pgadmin4@pgadmin.org
pgadmin | SQLite pgAdmin config: /var/lib/pgadmin/pgadmin4.db
pgadmin | ----------
pgadmin | Added 0 Server Group(s) and 1 Server(s).
pgadmin | [2023-03-01 23:56:21 +0000] [1] [INFO] Starting gunicorn 20.1.0
pgadmin | [2023-03-01 23:56:21 +0000] [1] [INFO] Listening at: http://[::]:80 (1)
pgadmin | [2023-03-01 23:56:21 +0000] [1] [INFO] Using worker: gthread
pgadmin | [2023-03-01 23:56:21 +0000] [93] [INFO] Booting worker with pid: 93
pgadmin | [2023-03-01 23:56:25 +0000] [93] [ERROR] Exception in worker process
pgadmin | Traceback (most recent call last):
pgadmin | File "/venv/lib/python3.10/site-packages/gunicorn/arbiter.py", line 589, in spawn_worker
pgadmin | worker.init_process()
pgadmin | File "/venv/lib/python3.10/site-packages/gunicorn/workers/gthread.py", line 92, in init_process
pgadmin | super().init_process()
pgadmin | File "/venv/lib/python3.10/site-packages/gunicorn/workers/base.py", line 134, in init_process
pgadmin | self.load_wsgi()
pgadmin | File "/venv/lib/python3.10/site-packages/gunicorn/workers/base.py", line 146, in load_wsgi
pgadmin | self.wsgi = self.app.wsgi()
pgadmin | File "/venv/lib/python3.10/site-packages/gunicorn/app/base.py", line 67, in wsgi
pgadmin | self.callable = self.load()
pgadmin | File "/venv/lib/python3.10/site-packages/gunicorn/app/wsgiapp.py", line 58, in load
pgadmin | return self.load_wsgiapp()
pgadmin | File "/venv/lib/python3.10/site-packages/gunicorn/app/wsgiapp.py", line 48, in load_wsgiapp
pgadmin | return util.import_app(self.app_uri)
pgadmin | File "/venv/lib/python3.10/site-packages/gunicorn/util.py", line 359, in import_app
pgadmin | mod = importlib.import_module(module)
pgadmin | File "/usr/lib/python3.10/importlib/__init__.py", line 126, in import_module
pgadmin | return _bootstrap._gcd_import(name[level:], package, level)
pgadmin | File "<frozen importlib._bootstrap>", line 1050, in _gcd_import
pgadmin | File "<frozen importlib._bootstrap>", line 1027, in _find_and_load
pgadmin | File "<frozen importlib._bootstrap>", line 1006, in _find_and_load_unlocked
pgadmin | File "<frozen importlib._bootstrap>", line 688, in _load_unlocked
pgadmin | File "<frozen importlib._bootstrap_external>", line 883, in exec_module
pgadmin | File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
pgadmin | File "/pgadmin4/run_pgadmin.py", line 4, in <module>
pgadmin | from pgAdmin4 import app
pgadmin | File "/pgadmin4/pgAdmin4.py", line 93, in <module>
pgadmin | app = create_app()
pgadmin | File "/pgadmin4/pgadmin/__init__.py", line 487, in create_app
pgadmin | paths.init_app()
pgadmin | File "/pgadmin4/pgadmin/utils/paths.py", line 102, in init_app
pgadmin | raise InternalServerError(
pgadmin | werkzeug.exceptions.InternalServerError: 500 Internal Server Error: The user does not have permission to read and write to the specified storage directory.
pgadmin | [2023-03-01 23:56:25 +0000] [93] [INFO] Worker exiting (pid: 93)
pgadmin | [2023-03-01 23:56:26 +0000] [1] [INFO] Shutting down: Master
pgadmin | [2023-03-01 23:56:26 +0000] [1] [INFO] Reason: Worker failed to boot.
I found different solutions online but I can't get it to work.
I am working with macOS Ventura and Docker Desktop for Mac.
Docker compose:
version: "3"
services:
db:
container_name: postgis
image: postgis/postgis:15-3.3
environment:
- POSTGRES_USER=postgres
- POSTGRES_PASSWORD=postgres
- POSTGRES_DB=GOYN
ports:
- "5432:5432"
volumes:
- pgdata:/var/lib/postgresql/data
pgadmin:
container_name: pgadmin
image: dpage/pgadmin4:6.20
environment:
- PGADMIN_DEFAULT_EMAIL=pgadmin4@pgadmin.org
- PGADMIN_DEFAULT_PASSWORD=admin
- PGADMIN_CONFIG_SERVER_MODE=True
ports:
- "5050:80"
volumes:
- ./backend/pgadmin/servers.json:/pgadmin4/servers.json
- ./backend/pgadmin/pgpass:/var/lib/pgadmin/storage/pgadmin4_pgadmin.org/pgpass
depends_on:
- db
File ./backend/pgadmin/servers.json:
{
"Servers": {
"1": {
"Name": "MYDB Server",
"Group": "Servers",
"Host": "db",
"Port": 5432,
"MaintenanceDB": "MYDB",
"Username": "postgres",
"PassFile": "/pgpass",
"SSLMode": "prefer"
}
}
}
File ./backend/pgadmin/pgpass::
db:5432:MYDB:postgres:postgres
EDIT: based on @arsks answer, I created this Dockerfile:
FROM dpage/pgadmin4:6.20
ADD servers.json /pgadmin4/servers.json
ADD pgpass /var/lib/pgadmin/storage/pgadmin4_pgadmin.org/pgpass
RUN whoami
USER root
RUN chown -R pgadmin /var/lib/pgadmin
USER pgadmin
The server is automatically added to pgadmin. However, it seems that pgadmin cannot find the password:
pgadmin | Error: connection to server at "db" (172.22.0.2), port 5432 failed: fe_sendauth: no password supplied
The problem is the pgpass volume mount:
volumes:
- ./backend/pgadmin/servers.json:/pgadmin4/servers.json
- ./backend/pgadmin/pgpass:/var/lib/pgadmin/storage/pgadmin4_pgadmin.org/pgpass
If you look at the image filesystem, you will see that /var/lib/pgadmin exists and is owned by user pgadmin:
$ docker run --rm --entrypoint sh dpage/pgadmin4:6.20 -c 'ls -ld /var/lib/pgadmin'
drwxrwxr-x 1 pgadmin root 0 Feb 6 10:56 /var/lib/pgadmin
When you mount a file onto /var/lib/pgadmin/storage/..., Docker creates /var/lib/pgadmin/storage (and all subdirectories and files) as owned by root, effectively preventing the pgadmin user from writing any files in this directory.
One way of dealing with this issue is to create an "init container" that takes care of setting up a Docker volume with appropriate permissions, like this:
version: "3"
volumes:
pg_data:
pgadmin_data:
services:
db:
image: postgis/postgis:15-3.3
environment:
- POSTGRES_USER=postgres
- POSTGRES_PASSWORD=postgres
- POSTGRES_DB=MYDB
ports:
- "5432:5432"
volumes:
- pg_data:/var/lib/postgresql/data
init-pgadmin:
image: dpage/pgadmin4:6.20
user: root
entrypoint:
- sh
- -c
- |
mkdir -p /var/lib/pgadmin/storage/pgadmin4_pgadmin.org/
cp /config/pgpass /var/lib/pgadmin/storage/pgadmin4_pgadmin.org/pgpass
chown -R pgadmin /var/lib/pgadmin
volumes:
- ./backend/pgadmin:/config
- pgadmin_data:/var/lib/pgadmin
pgadmin:
image: dpage/pgadmin4:6.20
environment:
- PGADMIN_DEFAULT_EMAIL=pgadmin4@pgadmin.org
- PGADMIN_DEFAULT_PASSWORD=admin
- PGADMIN_CONFIG_SERVER_MODE=True
ports:
- "5050:80"
volumes:
- pgadmin_data:/var/lib/pgadmin
- ./backend/pgadmin/servers.json:/pgadmin4/servers.json
depends_on:
init-pgadmin:
condition: service_completed_successfully
The init-pgadmin container runs before the pgadmin container. It copies the pgpass file into the correct location and sets the ownership appropriately.
For this example, the pgpass file looks like:
db:5432:MYDB:postgres:postgres
And the servers.json file looks like:
{
"Servers": {
"1": {
"Name": "MYDB Server",
"Group": "Servers",
"Host": "db",
"Port": 5432,
"MaintenanceDB": "MYDB",
"Username": "postgres",
"PassFile": "/pgpass",
"SSLMode": "prefer"
}
}
}