galaxyproject / galaxyproject/galaxy
testing for existence of galaxy db fails for restrictive postgres setups
- Dominant language
- Python
- Stars
- 1.9k
- Forks
- 1.2k
- Avg merge
- 2d 16h
- Merged PRs (30d)
- 169
Description
sqlalchemy-util tries to connect to a db called 'postgres' for testing the existence of the galaxy data base (it also does for creation and dropping the galaxy db). If there is no connect privilege for this DB we get:
```
Queue Worker on sqlalchemy+postgresql://********:********@postgres.intranet.ufz.de/galaxy-dev
Traceback (most recent call last):
File "lib/galaxy/webapps/galaxy/buildapp.py", line 49, in app_factory
app = galaxy.app.UniverseApplication(global_conf=global_conf, **kwargs)
File "lib/galaxy/app.py", line 89, in __init__
self._configure_models(check_migrate_databases=self.config.check_migrate_databases, check_migrate_tools=check_migrate_tools, config_file=config_file)
File "lib/galaxy/config.py", line 1165, in _configure_models
create_or_verify_database(db_url, config_file, self.config.database_engine_options, app=self)
File "lib/galaxy/model/migrate/check.py", line 33, in create_or_verify_database
new_database = not database_exists(url)
File "/gpfs1/data/galaxy_server/galaxy-dev/.venv/lib/python2.7/site-packages/sqlalchemy_utils/functions/database.py", line 481, in database_exists
return bool(get_scalar_result(engine, text))
....
File "/gpfs1/data/galaxy_server/galaxy-dev/.venv/lib/python2.7/site-packages/psycopg2/__init__.py", line 130, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
OperationalError: (psycopg2.OperationalError) FATAL: permission denied for database "postgres"
DETAIL: User does not have CONNECT privilege.
```
For usegalaxy-eu the solution is this: https://github.com/usegalaxy-eu/galaxy/commit/59305effd73c0b6c195afa9cb0f4f0d1ca8e994f
Maybe the following PR for sqlalchemy-utils brings a better solution https://github.com/kvesteri/sqlalchemy-utils/pull/372
Contributor guide
Research direction
Start in lib/galaxy/model/migrate/check.py, where create_or_verify_database calls database_exists, and trace its use from lib/galaxy/config.py. Reproduce the failure with a PostgreSQL user denied CONNECT on the postgres database, then compare the linked usegalaxy-eu commit and sqlalchemy-utils PR; done means restrictive PostgreSQL setups can initialize without this failure.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, python, sqlalchemy
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100