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.53k stars 656 forks source link

Database restore over web ui won't work #6820

Closed suikast42 closed 1 year ago

suikast42 commented 1 year ago

Please note that security bugs or issues should be reported to security@pgadmin.org.

Describe the bug

Backup DB over PGAdmin and recover again ends up with error ' TypeError: expected str, bytes or os.PathLike object, not NoneType '

To Reproduce

image

The revocery works fine until version 7.4. Since 7.5 it won't work

yogeshmahajan-1903 commented 1 year ago

@suikast42 I am not able to reproduce the issue. Could you please share pgAdmin logs? What is OS? Also can you please try on latest version 7.7?

suikast42 commented 1 year ago

That is the logging output

postfix/postlog: starting the Postfix mail system
[2023-10-10 13:59:00 +0000] [1] [INFO] Starting gunicorn 20.1.0
[2023-10-10 13:59:00 +0000] [1] [INFO] Listening at: http://[::]:80 (1)
[2023-10-10 13:59:00 +0000] [1] [INFO] Using worker: gthread
[2023-10-10 13:59:00 +0000] [94] [INFO] Booting worker with pid: 94
2023-10-10 14:00:02,452: WARNING    pgadmin:    Invalid binary path.
2023-10-10 14:00:02,452: WARNING    pgadmin:    Invalid binary path.
2023-10-10 14:00:02,453: WARNING    pgadmin:    Invalid binary path.
2023-10-10 14:00:02,453: WARNING    pgadmin:    Invalid binary path.
2023-10-10 14:00:03,314: ERROR  pgadmin:    400 Bad Request: The CSRF session token is missing.
Traceback (most recent call last):
  File "/venv/lib/python3.11/site-packages/flask_wtf/csrf.py", line 261, in protect
    validate_csrf(self._get_csrf_token())
  File "/venv/lib/python3.11/site-packages/flask_wtf/csrf.py", line 103, in validate_csrf
    raise ValidationError("The CSRF session token is missing.")
wtforms.validators.ValidationError: The CSRF session token is missing.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/venv/lib/python3.11/site-packages/flask/app.py", line 1821, in full_dispatch_request
    rv = self.preprocess_request()
         ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/flask/app.py", line 2313, in preprocess_request
    rv = self.ensure_sync(before_func)()
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/flask_wtf/csrf.py", line 229, in csrf_protect
    self.protect()
  File "/venv/lib/python3.11/site-packages/flask_wtf/csrf.py", line 264, in protect
    self._error_response(e.args[0])
  File "/venv/lib/python3.11/site-packages/flask_wtf/csrf.py", line 307, in _error_response
    raise CSRFError(reason)
flask_wtf.csrf.CSRFError: 400 Bad Request: The CSRF session token is missing.
2023-10-10 14:00:27,769: ERROR  pgadmin:    400 Bad Request: The CSRF session token is missing.
Traceback (most recent call last):
  File "/venv/lib/python3.11/site-packages/flask_wtf/csrf.py", line 261, in protect
    validate_csrf(self._get_csrf_token())
  File "/venv/lib/python3.11/site-packages/flask_wtf/csrf.py", line 103, in validate_csrf
    raise ValidationError("The CSRF session token is missing.")
wtforms.validators.ValidationError: The CSRF session token is missing.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/venv/lib/python3.11/site-packages/flask/app.py", line 1821, in full_dispatch_request
    rv = self.preprocess_request()
         ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/flask/app.py", line 2313, in preprocess_request
    rv = self.ensure_sync(before_func)()
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.11/site-packages/flask_wtf/csrf.py", line 229, in csrf_protect
    self.protect()
  File "/venv/lib/python3.11/site-packages/flask_wtf/csrf.py", line 264, in protect
    self._error_response(e.args[0])
  File "/venv/lib/python3.11/site-packages/flask_wtf/csrf.py", line 307, in _error_response
    raise CSRFError(reason)
flask_wtf.csrf.CSRFError: 400 Bad Request: The CSRF session token is missing.
2023-10-10 14:00:38,880: ERROR  pgadmin:    Could not connect to server(#1) - 'postgresTesting'.
Error: connection failed: fe_sendauth: no password supplied
2023-10-10 14:01:52,314: ERROR  pgadmin:    expected str, bytes or os.PathLike object, not NoneType
Traceback (most recent call last):
  File "/pgadmin4/pgadmin/tools/restore/__init__.py", line 396, in create_restore_job
    p.start()
  File "/pgadmin4/pgadmin/misc/bgprocess/processes.py", line 345, in start
    p = Popen(
        ^^^^^^
  File "/usr/lib/python3.11/subprocess.py", line 1026, in __init__
    self._execute_child(args, executable, preexec_fn, close_fds,
  File "/usr/lib/python3.11/subprocess.py", line 1870, in _execute_child
    env_list.append(k + b'=' + os.fsencode(v))
                               ^^^^^^^^^^^^^^
  File "<frozen os>", line 812, in fsencode
TypeError: expected str, bytes or os.PathLike object, not NoneType

EDIT:

I am running pgadmin behind traefik.

I think the issue is pointing to https://stackoverflow.com/questions/64394628/csrf-token-is-missing-error-in-docker-pgadmin

yogeshmahajan-1903 commented 1 year ago

@suikast42 Can you please share your docker-compose.yaml? Also are you using pg_service to create a server? Is your server is SSL enabled?

suikast42 commented 1 year ago 
  pgadmin:
    image: dpage/pgadmin4:7.7
    restart: always
    mem_limit: 1024M
    hostname: pgadmin
    container_name: pgadmin
    logging:
      options:
        max-size: '100m'
        max-file: '1'
    ports:
      - 8080:80
    environment:
      PGADMIN_DEFAULT_EMAIL: foo@bar.com
      PGADMIN_DEFAULT_PASSWORD: fizzbuzz
    volumes:
      - pgadmin-data:/var/lib/pgadmin
suikast42 commented 1 year ago

@suikast42 Can you please share your docker-compose.yaml? Also are you using pg_service to create a server? Is your server is SSL enabled?

The same deployment works with 7.4 but not with 7.5+

yogeshmahajan-1903 commented 1 year ago

@suikast42 Can you please try backup & maintenance on same db? What are the parameters are used to connect to server in Create Server dialogue?. Else can you please try adding below line in web/pgadmin/misc/bgprocess/processes.py file at line #312

current_app.logger.debug(env)

And restart docker continuer. Try restore & provide docker logs for same. Error is occurring because one of the env value is getting passed as None.

suikast42 commented 1 year ago

I can' event backup image

postfix/postlog: starting the Postfix mail system
[2023-10-12 06:54:30 +0000] [1] [INFO] Starting gunicorn 20.1.0
[2023-10-12 06:54:30 +0000] [1] [INFO] Listening at: http://[::]:80 (1)
[2023-10-12 06:54:30 +0000] [1] [INFO] Using worker: gthread
[2023-10-12 06:54:30 +0000] [94] [INFO] Booting worker with pid: 94
2023-10-12 06:54:43,592: WARNING    pgadmin:    Invalid binary path.
2023-10-12 06:54:43,592: WARNING    pgadmin:    Invalid binary path.
2023-10-12 06:54:43,592: WARNING    pgadmin:    Invalid binary path.
2023-10-12 06:54:43,592: WARNING    pgadmin:    Invalid binary path.
2023-10-12 06:55:00,045: ERROR  pgadmin:    Could not connect to server(#1) - 'postgresTesting'.
Error: connection failed: fe_sendauth: no password supplied
2023-10-12 06:55:16,199: ERROR  pgadmin:    expected str, bytes or os.PathLike object, not NoneType
Traceback (most recent call last):
  File "/pgadmin4/pgadmin/tools/backup/__init__.py", line 476, in create_backup_objects_job
    p.start()
  File "/pgadmin4/pgadmin/misc/bgprocess/processes.py", line 345, in start
    p = Popen(
        ^^^^^^
  File "/usr/lib/python3.11/subprocess.py", line 1026, in __init__
    self._execute_child(args, executable, preexec_fn, close_fds,
  File "/usr/lib/python3.11/subprocess.py", line 1870, in _execute_child
    env_list.append(k + b'=' + os.fsencode(v))
                               ^^^^^^^^^^^^^^
  File "<frozen os>", line 812, in fsencode
TypeError: expected str, bytes or os.PathLike object, not NoneType
suikast42 commented 1 year ago

Ok I got the error.

I have had try to provision the db connection with pgadmin.

/pgadmin4/pass/dbpass

postgresTesting.service.consul:30000:postgresTesting:<user>:<passwd>

servers.json

{
  "Servers": {
    "1": {
      "Name": "${var.dbname}",
      "Group": "Servers",
      "Port": 30000,
      "Username": "${var.user}",
      "PassFile": "/pgadmin4/pass/dbpass",
      "Host": "postgresTesting.service.consul",
      "SSLMode": "prefer",
      "MaintenanceDB": "postgres"
    }
  }
}

With pgadmin4 v <'=7.4 I can connect query backup and resotre the db

With pgadmin4 v > 7.4 I can connect and query the db but backup and restore not works with the error 

ERROR   pgadmin:    Could not connect to server(#1) - 'postgresTesting'.
Error: connection failed: fe_sendauth: no password supplied

But with pgadmin > v7.5 if I create a custom connection ( not the provisined one ) then everything works as expected.

It seems so that soimething changes in thje connection provisioning ?

yogeshmahajan-1903 commented 1 year ago

@suikast42 Could you please how & where you created custom connection? Looking at your pgpass file, it seems you have only permission to connect to postgresTesting database. When you connect to this server, you will not be able to connect other DBs. Not sure how did you launch restore/backup pop for database 'boxbay'.

Can you please share the small video for same? or detailed steps to reproduce the issue.

suikast42 commented 1 year ago

I follow this setup here https://technology.amis.nl/continuous-delivery/provisioning/pgadmin-in-docker-provision-connections-and-passwords/

It works until v 7.4 not above

yogeshmahajan-1903 commented 1 year ago

@suikast42 Have you created custom connection using papas file only where backup/restore worked on 7.5 or greater?

Can you please try copying attached process.py file?

docker cp <Download location>/processes.py <pgadmin_container>:/pgadmin4/pgadmin/misc/bgprocess/processes.py

And restart the container & try again backup. Please provide logs for the same

processes.py.zip

suikast42 commented 1 year ago 
postfix/postlog: starting the Postfix mail system
[2023-10-18 07:11:58 +0000] [1] [INFO] Starting gunicorn 20.1.0
[2023-10-18 07:11:58 +0000] [1] [INFO] Listening at: http://[::]:80 (1)
[2023-10-18 07:11:58 +0000] [1] [INFO] Using worker: gthread
[2023-10-18 07:11:58 +0000] [94] [INFO] Booting worker with pid: 94
2023-10-18 07:12:28,003: WARNING    pgadmin:    Invalid binary path.
2023-10-18 07:12:28,003: WARNING    pgadmin:    Invalid binary path.
2023-10-18 07:12:28,003: WARNING    pgadmin:    Invalid binary path.
2023-10-18 07:12:28,004: WARNING    pgadmin:    Invalid binary path.
2023-10-18 07:12:38,572: ERROR  pgadmin:    Could not connect to server(#1) - 'postgresTesting'.
Error: connection failed: fe_sendauth: no password supplied
2023-10-18 07:13:21,145: WARNING    pgadmin:    {'NOMAD_ALLOC_NAME': 'Boxbay_Infrastructure.boxbay-pgadmin[0]', 'NOMAD_ADDR_http': '10.21.21.43:29379', 'NOMAD_CPU_LIMIT': '500', 'HOSTNAME': 'a9a7f838afe6', 'NOMAD_PORT_http': '80', 'SHLVL': '1', 'PGADMIN_DEFAULT_PASSWORD': 'wmsmanager', 'HOME': '/home/pgadmin', 'PGADMIN_SETUP_PASSWORD': 'wmsmanager', 'NOMAD_ALLOC_ID': '97882857-9483-53e2-08a2-e692a0222c69', 'NOMAD_ALLOC_INDEX': '0', 'NOMAD_MEMORY_LIMIT': '512', 'NOMAD_HOST_IP_http': '10.21.21.43', 'NOMAD_ALLOC_DIR': '/alloc', 'NOMAD_DC': 'nomadder1', 'NOMAD_JOB_NAME': 'Boxbay_Infrastructure', 'PGADMIN_DEFAULT_EMAIL': 'admin@amova.eu', 'PGADMIN_SETUP_EMAIL': 'admin@amova.eu', 'NOMAD_REGION': 'global', 'NOMAD_JOB_ID': 'Boxbay_Infrastructure', 'PATH': '/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin', 'NOMAD_MEMORY_MAX_LIMIT': '32768', 'NOMAD_TASK_NAME': 'pgadmin4', 'NOMAD_NAMESPACE': 'default', 'NOMAD_HOST_ADDR_http': '10.21.21.43:29379', 'NOMAD_HOST_PORT_http': '29379', 'NOMAD_IP_http': '10.21.21.43', 'NOMAD_SECRETS_DIR': '/secrets', 'PGADMIN_SERVER_JSON_FILE': '/pgadmin4/servers.json', 'NOMAD_ALLOC_PORT_http': '80', 'PWD': '/pgadmin4', 'NOMAD_GROUP_NAME': 'boxbay-pgadmin', 'NOMAD_TASK_DIR': '/local', 'NOMAD_SHORT_ALLOC_ID': '97882857', 'PYTHONPATH': '/pgadmin4', 'NOMAD_PARENT_CGROUP': 'nomad.slice', 'SERVER_SOFTWARE': 'gunicorn/20.1.0', 'OAUTHLIB_INSECURE_TRANSPORT': '1', 'CORRUPTED_DB_BACKUP_FILE': '', 'PGAPPNAME': 'pgAdmin 4 - DB:boxbay', '231018071321130602': 'wmsapp', 'PROCID': '231018071321130602', 'OUTDIR': '/var/lib/pgadmin/sessions/process_logs/231018071321130602', 'PGA_BGP_FOREGROUND': '1'}
2023-10-18 07:13:21,145: ERROR  pgadmin:    expected str, bytes or os.PathLike object, not NoneType
Traceback (most recent call last):
  File "/pgadmin4/pgadmin/tools/restore/__init__.py", line 396, in create_restore_job
    p.start()
  File "/pgadmin4/pgadmin/misc/bgprocess/processes.py", line 347, in start
    p = Popen(
        ^^^^^^
  File "/usr/lib/python3.11/subprocess.py", line 1026, in __init__
    self._execute_child(args, executable, preexec_fn, close_fds,
  File "/usr/lib/python3.11/subprocess.py", line 1870, in _execute_child
    env_list.append(k + b'=' + os.fsencode(v))
                               ^^^^^^^^^^^^^^
  File "<frozen os>", line 812, in fsencode
TypeError: expected str, bytes or os.PathLike object, not NoneType
2023-10-18 07:13:56,310: WARNING    pgadmin:    {'NOMAD_ALLOC_NAME': 'Boxbay_Infrastructure.boxbay-pgadmin[0]', 'NOMAD_ADDR_http': '10.21.21.43:29379', 'NOMAD_CPU_LIMIT': '500', 'HOSTNAME': 'a9a7f838afe6', 'NOMAD_PORT_http': '80', 'SHLVL': '1', 'PGADMIN_DEFAULT_PASSWORD': 'wmsmanager', 'HOME': '/home/pgadmin', 'PGADMIN_SETUP_PASSWORD': 'wmsmanager', 'NOMAD_ALLOC_ID': '97882857-9483-53e2-08a2-e692a0222c69', 'NOMAD_ALLOC_INDEX': '0', 'NOMAD_MEMORY_LIMIT': '512', 'NOMAD_HOST_IP_http': '10.21.21.43', 'NOMAD_ALLOC_DIR': '/alloc', 'NOMAD_DC': 'nomadder1', 'NOMAD_JOB_NAME': 'Boxbay_Infrastructure', 'PGADMIN_DEFAULT_EMAIL': 'admin@amova.eu', 'PGADMIN_SETUP_EMAIL': 'admin@amova.eu', 'NOMAD_REGION': 'global', 'NOMAD_JOB_ID': 'Boxbay_Infrastructure', 'PATH': '/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin', 'NOMAD_MEMORY_MAX_LIMIT': '32768', 'NOMAD_TASK_NAME': 'pgadmin4', 'NOMAD_NAMESPACE': 'default', 'NOMAD_HOST_ADDR_http': '10.21.21.43:29379', 'NOMAD_HOST_PORT_http': '29379', 'NOMAD_IP_http': '10.21.21.43', 'NOMAD_SECRETS_DIR': '/secrets', 'PGADMIN_SERVER_JSON_FILE': '/pgadmin4/servers.json', 'NOMAD_ALLOC_PORT_http': '80', 'PWD': '/pgadmin4', 'NOMAD_GROUP_NAME': 'boxbay-pgadmin', 'NOMAD_TASK_DIR': '/local', 'NOMAD_SHORT_ALLOC_ID': '97882857', 'PYTHONPATH': '/pgadmin4', 'NOMAD_PARENT_CGROUP': 'nomad.slice', 'SERVER_SOFTWARE': 'gunicorn/20.1.0', 'OAUTHLIB_INSECURE_TRANSPORT': '1', 'CORRUPTED_DB_BACKUP_FILE': '', 'PGAPPNAME': 'pgAdmin 4 - DB:boxbay', '231018071321130602': 'wmsapp', '231018071356303040': 'wmsapp', 'PROCID': '231018071356303040', 'OUTDIR': '/var/lib/pgadmin/sessions/process_logs/231018071356303040', 'PGA_BGP_FOREGROUND': '1'}
2023-10-18 07:13:56,310: ERROR  pgadmin:    expected str, bytes or os.PathLike object, not NoneType
Traceback (most recent call last):
  File "/pgadmin4/pgadmin/tools/restore/__init__.py", line 396, in create_restore_job
    p.start()
  File "/pgadmin4/pgadmin/misc/bgprocess/processes.py", line 347, in start
    p = Popen(
        ^^^^^^
  File "/usr/lib/python3.11/subprocess.py", line 1026, in __init__
    self._execute_child(args, executable, preexec_fn, close_fds,
  File "/usr/lib/python3.11/subprocess.py", line 1870, in _execute_child
    env_list.append(k + b'=' + os.fsencode(v))
                               ^^^^^^^^^^^^^^
  File "<frozen os>", line 812, in fsencode
TypeError: expected str, bytes or os.PathLike object, not NoneType
2023-10-18 07:14:42,475: WARNING    pgadmin:    {'NOMAD_ALLOC_NAME': 'Boxbay_Infrastructure.boxbay-pgadmin[0]', 'NOMAD_ADDR_http': '10.21.21.43:29379', 'NOMAD_CPU_LIMIT': '500', 'HOSTNAME': 'a9a7f838afe6', 'NOMAD_PORT_http': '80', 'SHLVL': '1', 'PGADMIN_DEFAULT_PASSWORD': 'wmsmanager', 'HOME': '/home/pgadmin', 'PGADMIN_SETUP_PASSWORD': 'wmsmanager', 'NOMAD_ALLOC_ID': '97882857-9483-53e2-08a2-e692a0222c69', 'NOMAD_ALLOC_INDEX': '0', 'NOMAD_MEMORY_LIMIT': '512', 'NOMAD_HOST_IP_http': '10.21.21.43', 'NOMAD_ALLOC_DIR': '/alloc', 'NOMAD_DC': 'nomadder1', 'NOMAD_JOB_NAME': 'Boxbay_Infrastructure', 'PGADMIN_DEFAULT_EMAIL': 'admin@amova.eu', 'PGADMIN_SETUP_EMAIL': 'admin@amova.eu', 'NOMAD_REGION': 'global', 'NOMAD_JOB_ID': 'Boxbay_Infrastructure', 'PATH': '/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin', 'NOMAD_MEMORY_MAX_LIMIT': '32768', 'NOMAD_TASK_NAME': 'pgadmin4', 'NOMAD_NAMESPACE': 'default', 'NOMAD_HOST_ADDR_http': '10.21.21.43:29379', 'NOMAD_HOST_PORT_http': '29379', 'NOMAD_IP_http': '10.21.21.43', 'NOMAD_SECRETS_DIR': '/secrets', 'PGADMIN_SERVER_JSON_FILE': '/pgadmin4/servers.json', 'NOMAD_ALLOC_PORT_http': '80', 'PWD': '/pgadmin4', 'NOMAD_GROUP_NAME': 'boxbay-pgadmin', 'NOMAD_TASK_DIR': '/local', 'NOMAD_SHORT_ALLOC_ID': '97882857', 'PYTHONPATH': '/pgadmin4', 'NOMAD_PARENT_CGROUP': 'nomad.slice', 'SERVER_SOFTWARE': 'gunicorn/20.1.0', 'OAUTHLIB_INSECURE_TRANSPORT': '1', 'CORRUPTED_DB_BACKUP_FILE': '', 'PGAPPNAME': 'pgAdmin 4 - DB:boxbay', '231018071321130602': 'wmsapp', '231018071356303040': 'wmsapp', '231018071442463896': 'wmsapp', 'PROCID': '231018071442463896', 'OUTDIR': '/var/lib/pgadmin/sessions/process_logs/231018071442463896', 'PGA_BGP_FOREGROUND': '1'}
2023-10-18 07:14:42,475: ERROR  pgadmin:    expected str, bytes or os.PathLike object, not NoneType
Traceback (most recent call last):
  File "/pgadmin4/pgadmin/tools/restore/__init__.py", line 396, in create_restore_job
    p.start()
  File "/pgadmin4/pgadmin/misc/bgprocess/processes.py", line 347, in start
    p = Popen(
        ^^^^^^
  File "/usr/lib/python3.11/subprocess.py", line 1026, in __init__
    self._execute_child(args, executable, preexec_fn, close_fds,
  File "/usr/lib/python3.11/subprocess.py", line 1870, in _execute_child
    env_list.append(k + b'=' + os.fsencode(v))
                               ^^^^^^^^^^^^^^
  File "<frozen os>", line 812, in fsencode
TypeError: expected str, bytes or os.PathLike object, not NoneType
yogeshmahajan-1903 commented 1 year ago

@suikast42

I have added more logging.Can you please try copying attached process.py file And restart the container & try again backup. Please provide logs for the same.

docker cp <Download location>/processes.py <pgadmin_container>:/pgadmin4/pgadmin/misc/bgprocess/processes.py

processes.py.zip

suikast42 commented 1 year ago

I do that by adding the file in my docker file 

COPY --chown=pgadmin:pgadmin   dcker/processes.py  /pgadmin4/pgadmin/misc/bgprocess/processes.py
yogeshmahajan-1903 commented 1 year ago

@suikast42

Should be fine. Just wanted to get more information from logs.