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.

Sign up for GitHub

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

Jump to bottom

database_exists() throws exception when PostgreSQL role does not have access to postgres database #330

Open
williamjacksn opened this issue Jun 6, 2018 · 1 comment
Open

database_exists() throws exception when PostgreSQL role does not have access to postgres database #330

williamjacksn opened this issue Jun 6, 2018 · 1 comment

Comments

@williamjacksn
Copy link

williamjacksn commented Jun 6, 2018 •
edited
Loading

The PostgreSQL role I am using to connect has access to my personal database but not the postgres database. When I call database_exists(), I get an exception:

(passwords, hostnames, and IP addresses obscured)

>>> sqlalchemy_utils.database_exists('postgres://william:secretpassword@db.example.com:5433/william')
Traceback (most recent call last):
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
    return fn()
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/pool.py", line 403, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/pool.py", line 791, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/pool.py", line 532, in checkout
    rec = pool._do_get()
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/pool.py", line 1196, in _do_get
    self._dec_overflow()
  File "/home/william/.pyenv/versions/3.6.5/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/3.6.5/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/pool.py", line 1193, in _do_get
    return self._create_connection()
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/pool.py", line 350, in _create_connection
    return _ConnectionRecord(self)
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/pool.py", line 477, in __init__
    self.__connect(first_connect_check=True)
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/pool.py", line 674, in __connect
    connection = pool._invoke_creator(self)
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py", line 106, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 411, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/william/.pyenv/versions/3.6.5/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/3.6.5/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/3.6.5/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/3.6.5/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/3.6.5/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/3.6.5/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2162, in _wrap_pool_connect
    e, dialect, self)
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception_noconnection
    exc_info
  File "/home/william/.pyenv/versions/3.6.5/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/3.6.5/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
    return fn()
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/pool.py", line 403, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/pool.py", line 791, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/pool.py", line 532, in checkout
    rec = pool._do_get()
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/pool.py", line 1196, in _do_get
    self._dec_overflow()
  File "/home/william/.pyenv/versions/3.6.5/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/3.6.5/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/pool.py", line 1193, in _do_get
    return self._create_connection()
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/pool.py", line 350, in _create_connection
    return _ConnectionRecord(self)
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/pool.py", line 477, in __init__
    self.__connect(first_connect_check=True)
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/pool.py", line 674, in __connect
    connection = pool._invoke_creator(self)
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py", line 106, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/william/.pyenv/versions/3.6.5/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 411, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/william/.pyenv/versions/3.6.5/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
 (Background on this error at: http://sqlalche.me/e/e3q8)
@sdementen
Copy link

Could you check if removing the special case for 'postgresql' in line https://github.com/kvesteri/sqlalchemy-utils/blob/master/sqlalchemy_utils/functions/database.py#L470 and using the 'fallback' in line https://github.com/kvesteri/sqlalchemy-utils/blob/master/sqlalchemy_utils/functions/database.py#L487 works ?

I am not sure to understand the benefit of the special case for postgresql (nor for mysql). @kvesteri , would you have some hindsight on this ?

@sveinugu sveinugu mentioned this issue Jun 21, 2020
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@williamjacksn @sdementen