search

sdementen / piecash

Pythonic interface to GnuCash SQL documents
Other
290 stars 73 forks source link

With postgres backend, piecash attempts to connect to 'postgres' database instead of database I specify #91

Closed williamjacksn closed 4 years ago

williamjacksn commented 6 years ago

My GnuCash database is on a PostgreSQL server. My user account has access to the GnuCash database (named "gnucash") but not the "postgres" database.

When I call open_book, piecash attempts to connect to the "postgres" database and fails.

(passwords, hostnames, and IP addresses obscured)

>>> piecash.open_book(uri_conn='postgres://william:secretpassword@db.example.com:5433/gnucash')
Traceback (most recent call last):
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
    return fn()
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/pool.py", line 403, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/pool.py", line 788, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/pool.py", line 532, in checkout
    rec = pool._do_get()
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/pool.py", line 1193, in _do_get
    self._dec_overflow()
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/pool.py", line 1190, in _do_get
    return self._create_connection()
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/pool.py", line 350, in _create_connection
    return _ConnectionRecord(self)
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/pool.py", line 477, in __init__
    self.__connect(first_connect_check=True)
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/pool.py", line 671, in __connect
    connection = pool._invoke_creator(self)
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py", line 106, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 410, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/psycopg2/__init__.py", line 130, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: FATAL:  no pg_hba.conf entry for host "x.x.x.x", user "william", database "postgres", SSL on
FATAL:  no pg_hba.conf entry for host "x.x.x.x", user "william", database "postgres", SSL off

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

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/piecash/core/session.py", line 307, in open_book
    if not database_exists(uri_conn):
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy_utils/functions/database.py", line 472, in database_exists
    return bool(get_scalar_result(engine, text))
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy_utils/functions/database.py", line 455, in get_scalar_result
    result_proxy = engine.execute(sql)
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2074, in execute
    connection = self.contextual_connect(close_with_result=True)
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2123, in contextual_connect
    self._wrap_pool_connect(self.pool.connect, None),
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2162, in _wrap_pool_connect
    e, dialect, self)
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception_noconnection
    exc_info
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
    return fn()
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/pool.py", line 403, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/pool.py", line 788, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/pool.py", line 532, in checkout
    rec = pool._do_get()
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/pool.py", line 1193, in _do_get
    self._dec_overflow()
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/pool.py", line 1190, in _do_get
    return self._create_connection()
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/pool.py", line 350, in _create_connection
    return _ConnectionRecord(self)
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/pool.py", line 477, in __init__
    self.__connect(first_connect_check=True)
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/pool.py", line 671, in __connect
    connection = pool._invoke_creator(self)
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py", line 106, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 410, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/william/.pyenv/versions/piecash/lib/python3.6/site-packages/psycopg2/__init__.py", line 130, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL:  no pg_hba.conf entry for host "x.x.x.x", user "william", database "postgres", SSL on
FATAL:  no pg_hba.conf entry for host "x.x.x.x", user "william", database "postgres", SSL off
4 (Background on this error at: http://sqlalche.me/e/e3q8)
sdementen commented 6 years ago

surprising! I can't reproduce it on my side.

Could you try 2 twhing:

williamjacksn commented 6 years ago

At piecash.core.session line 307, uri_conn is postgres://william:secretpasword@db.example.com:5433/gnucash

It looks like the problem is in sqlalchemy_utils.database_exists. I can submit a bug over there.

In the meantime, I patched my copy of piecash to add a flag that will bypass the existence check: https://github.com/williamjacksn/piecash/commit/0c090479b301cf0f933180c32a4b22ea12520382

sdementen commented 6 years ago

That was a bit my first intuition... And you understand the bug in sqlalchemy_utils?

williamjacksn commented 6 years ago

It's pretty straightforward. sqlalchemy_utils.database_exists() just assumes I have access to the postgres database and switches to it on purpose to make the connection. Not a "bug", but a flawed implementation.

sdementen commented 6 years ago

so instead of using the database given in the URI, it uses the postgres database ?

williamjacksn commented 6 years ago

That's right. The logic is pretty easy to follow:

https://github.com/kvesteri/sqlalchemy-utils/blob/master/sqlalchemy_utils/functions/database.py#L433

From the PostgreSQL documentation:

After initialization, a database cluster will contain a database named postgres, which is meant as a default database for use by utilities, users and third party applications. The database server itself does not require the postgres database to exist, but many external utility programs assume it exists.

So SQLAlchemy-Utils assumes the postgres database is there and that I have access to it.

sdementen commented 6 years ago

Given line 462, why is there a need for lines 463-466?

sdementen commented 4 years ago

@williamjacksn with the latest version of piecash (and dependencies), is the issue still there ? or can we close it ?

williamjacksn commented 4 years ago

This is still an issue, but for a slightly different reason. The call to sqlalchemy_utils.database_exists() still fails, but instead of attempting a connection to the postgres database, it attempts to connect to a database whose name matches the role I am connecting with. No such database exists, so the connection fails.

I am still using my patched fork of piecash that bypasses the call to database_exists(): https://github.com/williamjacksn/piecash/commit/0c090479b301cf0f933180c32a4b22ea12520382.

This has been reported in https://github.com/kvesteri/sqlalchemy-utils/issues/462.

>>> piecash.open_book(uri_conn='postgres://william:secretpassword@db.example.com/gnucash')
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2336, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 364, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 778, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 495, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 140, in _do_get
    self._dec_overflow()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 137, in _do_get
    return self._create_connection()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 309, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 440, in __init__
    self.__connect(first_connect_check=True)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 656, in __connect
    connection = pool._invoke_creator(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 493, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.9/site-packages/psycopg2/__init__.py", line 127, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: FATAL:  database "william" does not exist

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

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.9/site-packages/piecash/core/session.py", line 366, in open_book
    if not database_exists(uri_conn):
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy_utils/functions/database.py", line 466, in database_exists
    return bool(get_scalar_result(engine, text))
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy_utils/functions/database.py", line 445, in get_scalar_result
    result_proxy = engine.execute(sql)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2234, in execute
    connection = self._contextual_connect(close_with_result=True)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2302, in _contextual_connect
    self._wrap_pool_connect(self.pool.connect, None),
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2339, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1583, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2336, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 364, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 778, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 495, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 140, in _do_get
    self._dec_overflow()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 137, in _do_get
    return self._create_connection()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 309, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 440, in __init__
    self.__connect(first_connect_check=True)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 656, in __connect
    connection = pool._invoke_creator(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 493, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.9/site-packages/psycopg2/__init__.py", line 127, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL:  database "william" does not exist

(Background on this error at: http://sqlalche.me/e/13/e3q8)
sdementen commented 4 years ago

Ha! Great to know you have the same issue as myself this early morning! I had to create a database with name my username to pass through the issue. So if I understand well, it is a bug in sqlalchemy-utils that could be worth to workaround now in piecash , hoping a future version of sqla-utils fixes it (and the we can remove the workaround you propose)

sdementen commented 4 years ago

@williamjacksn could you try to open your postgres gnucash book (without check_exists=False) but with a pip install SQLAlchemy-Utils==0.36.7 before as 0.38.8 has the issue of username database ?

I am not sure if, with 0.36.7, you will fall back on the issue of "postgres" db being used for existence or if it had solved the issue (and we just have a regression with 0.38.8).

sdementen commented 4 years ago

Released piecash 1.1.1 with your flag check_exists. I will close the issue once the sqlalchemy-utils issue is clearer

williamjacksn commented 4 years ago

Thank you so much, @sdementen !

sdementen commented 4 years ago

You're welcome and thank you for the feature/debugging. Could you just confirm if with the previous release of sqla-utils it works (my comment here above)

williamjacksn commented 4 years ago

It works for me with SQLAlchemy-Utils 0.36.7!

# pip list
Package          Version
---------------- ---------
certifi          2020.6.20
chardet          3.0.4
click            7.1.2
idna             2.10
piecash          1.1.1
pip              20.2.4
psycopg2         2.8.6
pytz             2020.1
requests         2.24.0
setuptools       50.3.2
six              1.15.0
SQLAlchemy       1.3.20
SQLAlchemy-Utils 0.36.7
tzlocal          2.1
urllib3          1.25.11
wheel            0.35.1
>>> piecash.open_book(uri_conn='postgres://william:secretpassword@db.example.com/gnucash')
Book<postgres://william:secretpassword@db.example.com/gnucash>
sdementen commented 4 years ago

Great, then I will release a new version that make sure 0.36.8 is not used

sdementen commented 4 years ago

1.1.2 fixes this by installing SQLAlchemy-Utils !=0.36.8