Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Password in pg_service.conf contains '@' not work on import Interlis-Files #478

Open
tomep opened this issue Oct 29, 2024 · 2 comments
Open
Labels
plugin Concerns the wastewater plugin security sqlalchemy

Comments

@tomep
Copy link
Collaborator

tomep commented Oct 29, 2024

Describe the bug
When importing an Interlis data set, e.g. organizations, the process is aborted if the password contains an '@' character.

To Reproduce
Exact steps to reproduce the behavior:

  1. push de importbutton
  2. choose the organisation.xtf
  3. Click OK
  4. Abort after a few seconds

Expected behavior
normal import process

Screenshots / data
Ein Fehler trat bei der Ausführung von Python-Code auf:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not translate host name "4106@127.0.0.1" to address: Beim Datenbankaufruf ist ein nicht behebbarer Fehler aufgetreten. (Background on this error at: https://sqlalche.me/e/20/e3q8)
Traceback (most recent call last):
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\engine\base.py", line 146, in init
self._dbapi_connection = engine.raw_connection()
^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\engine\base.py", line 3302, in raw_connection
return self.pool.connect()
^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\pool\base.py", line 449, in connect
return _ConnectionFairy._checkout(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\pool\base.py", line 1263, in _checkout
fairy = _ConnectionRecord.checkout(pool)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\pool\base.py", line 712, in checkout
rec = pool._do_get()
^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\pool\impl.py", line 179, in _do_get
with util.safe_reraise():
^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\util\langhelpers.py", line 146, in exit
raise exc_value.with_traceback(exc_tb)
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\pool\impl.py", line 177, in _do_get
return self._create_connection()
^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\pool\base.py", line 390, in _create_connection
return _ConnectionRecord(self)
^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\pool\base.py", line 674, in init
self.__connect()
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\pool\base.py", line 900, in __connect
with util.safe_reraise():
^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\util\langhelpers.py", line 146, in exit
raise exc_value.with_traceback(exc_tb)
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\pool\base.py", line 896, in __connect
self.dbapi_connection = connection = pool.invoke_creator(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\engine\create.py", line 643, in connect
return dialect.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\engine\default.py", line 621, in connect
return self.loaded_dbapi.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\PROGRA1\QGIS331.11\apps\Python312\Lib\site-packages\psycopg2_init
.py", line 122, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.OperationalError: could not translate host name "4106@127.0.0.1" to address: Beim Datenbankaufruf ist ein nicht behebbarer Fehler aufgetreten.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "C:\Users/tommeppiel/AppData/Roaming/QGIS/QGIS3\profiles\TEKSI_DSS2020_Test/python/plugins\teksi_wastewater\teksi_wastewater_plugin.py", line 650, in actionImportClicked
self.interlisImporterExporter.action_import()
File "C:\Users/tommeppiel/AppData/Roaming/QGIS/QGIS3\profiles\TEKSI_DSS2020_Test/python/plugins\teksi_wastewater\interlis\gui\interlis_importer_exporter_gui.py", line 87, in action_import
raise exception
File "C:\Users/tommeppiel/AppData/Roaming/QGIS/QGIS3\profiles\TEKSI_DSS2020_Test/python/plugins\teksi_wastewater\interlis\gui\interlis_importer_exporter_gui.py", line 68, in action_import
self.interlis_importer_exporter.interlis_import(
File "C:\Users/tommeppiel/AppData/Roaming/QGIS/QGIS3\profiles\TEKSI_DSS2020_Test/python/plugins\teksi_wastewater\interlis\interlis_importer_exporter.py", line 152, in interlis_import
raise exception
File "C:\Users/tommeppiel/AppData/Roaming/QGIS/QGIS3\profiles\TEKSI_DSS2020_Test/python/plugins\teksi_wastewater\interlis\interlis_importer_exporter.py", line 106, in interlis_import
tww_session = self._import_from_intermediate_schema(import_model)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users/tommeppiel/AppData/Roaming/QGIS/QGIS3\profiles\TEKSI_DSS2020_Test/python/plugins\teksi_wastewater\interlis\interlis_importer_exporter.py", line 254, in _import_from_intermediate_schema
self._init_model_classes(import_model)
File "C:\Users/tommeppiel/AppData/Roaming/QGIS/QGIS3\profiles\TEKSI_DSS2020_Test/python/plugins\teksi_wastewater\interlis\interlis_importer_exporter.py", line 605, in _init_model_classes
self.model_classes_interlis = ModelInterlis().classes()
^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users/tommeppiel/AppData/Roaming/QGIS/QGIS3\profiles\TEKSI_DSS2020_Test/python/plugins\teksi_wastewater\interlis\interlis_model_mapping\model_base.py", line 19, in classes
tww_sqlalchemy.prepare_automap_base(self.Base, self.schema)
File "C:\Users/tommeppiel/AppData/Roaming/QGIS/QGIS3\profiles\TEKSI_DSS2020_Test/python/plugins\teksi_wastewater\interlis\utils\tww_sqlalchemy.py", line 72, in prepare_automap_base
base.prepare(
File "", line 2, in prepare
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\util\deprecations.py", line 281, in warned
return fn(*args, **kwargs) # type: ignore[no-any-return]
^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\ext\automap.py", line 1255, in prepare
cls.metadata.reflect(autoload_with, **opts) # type: ignore[arg-type] # noqa: E501
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\sql\schema.py", line 5761, in reflect
with inspection.inspect(bind)._inspection_context() as insp:
^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\inspection.py", line 140, in inspect
ret = reg(subject)
^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\engine\reflection.py", line 312, in _engine_insp
return Inspector._construct(Inspector._init_engine, bind)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\engine\reflection.py", line 245, in _construct
init(self, bind)
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\engine\reflection.py", line 256, in _init_engine
engine.connect().close()
^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\engine\base.py", line 3278, in connect
return self._connection_cls(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\engine\base.py", line 148, in init
Connection._handle_dbapi_exception_noconnection(
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\engine\base.py", line 2442, in _handle_dbapi_exception_noconnection
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\engine\base.py", line 146, in init
self._dbapi_connection = engine.raw_connection()
^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\engine\base.py", line 3302, in raw_connection
return self.pool.connect()
^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\pool\base.py", line 449, in connect
return _ConnectionFairy._checkout(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\pool\base.py", line 1263, in _checkout
fairy = _ConnectionRecord.checkout(pool)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\pool\base.py", line 712, in checkout
rec = pool._do_get()
^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\pool\impl.py", line 179, in _do_get
with util.safe_reraise():
^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\util\langhelpers.py", line 146, in exit
raise exc_value.with_traceback(exc_tb)
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\pool\impl.py", line 177, in _do_get
return self._create_connection()
^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\pool\base.py", line 390, in _create_connection
return _ConnectionRecord(self)
^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\pool\base.py", line 674, in init
self.__connect()
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\pool\base.py", line 900, in __connect
with util.safe_reraise():
^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\util\langhelpers.py", line 146, in exit
raise exc_value.with_traceback(exc_tb)
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\pool\base.py", line 896, in __connect
self.dbapi_connection = connection = pool.invoke_creator(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\engine\create.py", line 643, in connect
return dialect.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages\sqlalchemy\engine\default.py", line 621, in connect
return self.loaded_dbapi.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\PROGRA1\QGIS331.11\apps\Python312\Lib\site-packages\psycopg2_init
.py", line 122, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not translate host name "4106@127.0.0.1" to address: Beim Datenbankaufruf ist ein nicht behebbarer Fehler aufgetreten.

(Background on this error at: https://sqlalche.me/e/20/e3q8)

Python-Version: 3.12.6 (main, Sep 7 2024, 18:59:42) [MSC v.1938 64 bit (AMD64)]
QGIS-Version: 3.34.11-Prizren Prizren, 2904bcec

Python-Pfad:
C:/PROGRA1/QGIS331.11/apps/qgis-ltr/./python
C:/Users/tommeppiel/AppData/Roaming/QGIS/QGIS3\profiles\TEKSI_DSS2020_Test/python
C:/Users/tommeppiel/AppData/Roaming/QGIS/QGIS3\profiles\TEKSI_DSS2020_Test/python/plugins
C:/PROGRA1/QGIS331.11/apps/qgis-ltr/./python/plugins
C:\PROGRA1\QGIS331.11\apps\grass\grass84\etc\python
L:\Allgemeines\M Ä R K I AG\Werkinformation\neues System\TEKSI\2024.0.3
C:\PROGRA1\QGIS331.11\bin\python312.zip
C:\PROGRA1\QGIS331.11\apps\Python312\DLLs
C:\PROGRA1\QGIS331.11\apps\Python312\Lib
C:\PROGRA1\QGIS331.11\bin
C:\Users\tommeppiel\AppData\Roaming\Python\Python312\site-packages
C:\PROGRA1\QGIS331.11\apps\Python312
C:\PROGRA1\QGIS331.11\apps\Python312\Lib\site-packages
C:\PROGRA1\QGIS331.11\apps\Python312\Lib\site-packages\win32
C:\PROGRA1\QGIS331.11\apps\Python312\Lib\site-packages\win32\lib
C:\PROGRA1\QGIS331.11\apps\Python312\Lib\site-packages\Pythonwin
C:/Users/tommeppiel/AppData/Roaming/QGIS/QGIS3\profiles\TEKSI_DSS2020_Test/python
L:/Allgemeines/M Ä R K I AG/Werkinformation/neues System/TEKSI/2024.0.3
C:\Users/tommeppiel/AppData/Roaming/QGIS/QGIS3\profiles\TEKSI_DSS2020_Test/python/plugins\teksi_wastewater\interlis\gui....

Desktop (please complete the following information):

  • TWW version 2024.0.3
  • QGIS Version 3.34.11
  • OS win10

Additional context
When i replace the password without '@' it the import works fine.

@cymed
Copy link
Contributor

cymed commented Oct 30, 2024

@tomep The Error points at the host address, not the password

@ponceta
Copy link
Member

ponceta commented Oct 30, 2024

Yes, the issue is with the way the password is passed to python because "4106" you see are the last characters of the password.

See https://stackoverflow.com/questions/72049617/operationalerror-psycopg2-operationalerror-could-not-translate-host-name-143 for reference. Solution should not be too complicated to implement.

@ponceta ponceta added the plugin Concerns the wastewater plugin label Oct 31, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
plugin Concerns the wastewater plugin security sqlalchemy
Projects
None yet
Development

No branches or pull requests

3 participants