DatabaseUrl bug when using Unix domain socket

I'm deploying a FastAPI application on Google Cloud Run which connects to a Cloud SQL instance using this package. The crux of the issue is that connecting with:

db = databases.Database(url)
await db.connect()

fails whereas connecting through sqlalchemy's create_engine with

engine = create_engine(url)
engine.connect()

works.

The connection url uses unix_sock structure (docs here) rather than the regular sqlalchemy connection url, something like this:

#  all these urls work fine when connecting with sqlalchemy create_engine
"postgresql://user:pass@/db_name?host=/path/to/sock"
"postgresql+psycopg2://user:pass@/db_name?host=/path/to/sock"
"postgresql+pg8000://user:pass@/db_name?unix_sock=/path/to/sock/.s.PGSQL.5432"

I'm unsure whether this would be an issue with using async in the Google Cloud environment or something about how connection urls like the one above get translated in this package to work with sqlalchemy. I've posted on Stack Overflow about it here but thought I'd raise an issue here as well in case it was the latter.

@dbatten5

@dbatten5 Thanks for reporting this.

Can you provide the failure details? And if you could provide a complete example, it would be great.

@aminalaee

@aminalaee yes sorry i should have included that in the original message. traceback:

async with self.lifespan_context(app):
File "/opt/pysetup/.venv/lib/python3.9/site-packages/starlette/routing.py", line 518, in __aenter__
await self._router.startup()
File "/opt/pysetup/.venv/lib/python3.9/site-packages/starlette/routing.py", line 598, in startup
await handler()
File "/{app_name}/app/main.py", line 46, in startup
await database_.connect()
File "/opt/pysetup/.venv/lib/python3.9/site-packages/databases/core.py", line 88, in connect
await self._backend.connect()
File "/opt/pysetup/.venv/lib/python3.9/site-packages/databases/backends/postgres.py", line 70, in connect
self._pool = await asyncpg.create_pool(**kwargs)
File "/opt/pysetup/.venv/lib/python3.9/site-packages/asyncpg/pool.py", line 407, in _async__init__
await self._initialize()
File "/opt/pysetup/.venv/lib/python3.9/site-packages/asyncpg/pool.py", line 435, in _initialize
await first_ch.connect()
File "/opt/pysetup/.venv/lib/python3.9/site-packages/asyncpg/pool.py", line 127, in connect
self._con = await self._pool._get_new_connection()
File "/opt/pysetup/.venv/lib/python3.9/site-packages/asyncpg/pool.py", line 477, in _get_new_connection
con = await connection.connect(
File "/opt/pysetup/.venv/lib/python3.9/site-packages/asyncpg/connection.py", line 2045, in connect
return await connect_utils._connect(
File "/opt/pysetup/.venv/lib/python3.9/site-packages/asyncpg/connect_utils.py", line 790, in _connect
raise last_error
File "/opt/pysetup/.venv/lib/python3.9/site-packages/asyncpg/connect_utils.py", line 776, in _connect
return await _connect_addr(
File "/opt/pysetup/.venv/lib/python3.9/site-packages/asyncpg/connect_utils.py", line 676, in _connect_addr
return await __connect_addr(params, timeout, True, *args)
File "/opt/pysetup/.venv/lib/python3.9/site-packages/asyncpg/connect_utils.py", line 720, in __connect_addr
tr, pr = await compat.wait_for(connector, timeout=timeout)
File "/opt/pysetup/.venv/lib/python3.9/site-packages/asyncpg/compat.py", line 66, in wait_for
return await asyncio.wait_for(fut, timeout)
File "/usr/local/lib/python3.9/asyncio/tasks.py", line 481, in wait_for
return fut.result()
File "/opt/pysetup/.venv/lib/python3.9/site-packages/asyncpg/connect_utils.py", line 586, in _create_ssl_connection
tr, pr = await loop.create_connection(
File "uvloop/loop.pyx", line 2024, in create_connection
File "uvloop/loop.pyx", line 2001, in uvloop.loop.Loop.create_connection
ConnectionRefusedError: [Errno 111] Connection refused

complete example is a little tricky but essentially i have this in a FastAPI application:

from fastapi import FastAPI
import databases

database = databases.Database(settings.sqlalchemy_database_uri) # mapped from an env var

app = FastAPI()

app.state.database = database

@app.on_event("startup")
async def startup() -> None:
    database_ = app.state.database
    if not database_.is_connected:
        await database_.connect()

and the application is started with:

gunicorn --bind :$PORT --workers 1 --worker-class uvicorn.workers.UvicornWorker  --threads 4 app.main:app

where $PORT is injected by Google Cloud Run deploying a revision. hopefully that's enough context but do let me know if there's any other info i can provide

I think the issue is how DatabaseUrl is parsing the url here.:

With this database_uri: "postgresql://user:password@/dbname?host=/var/run/postgresql/.s.PGSQL.5432

I can see that I get the following parsed data from DatabaseUrl:

{'host': None, 'port': None, 'user': 'user', 'password': 'password', 'database': 'dbname'}

Which has invalid host, as the host is now available in the query part, and should be read from the options part of url.

This shouldn't be too complicated. Feel free to create a PR for it.

ah ok that's interesting, seems like that would be the issue then. will try and get a pr raised for that

Thanks. I'll update the PR to be more precise then.

@aminalaee

@aminalaee just to confirm - the host should be /var/run/postgresql/.s.PGSQL.5432 in that example right?

I think that should be ok for now.
asyncpg mentions a few common places here. Which covers the one in our example. Please do double check.

interesting they have quite a few fallbacks. would you like me to add them to this pr? or should that come as a separate piece of work when the time comes

Can you explain what the fallbacks are?

for asyncpg?

if the host can't be parsed from dsn (either the regular hostname part of the dsn or a host= query) then:

the value of the PGHOST environment variable,
on Unix, common directories used for PostgreSQL Unix-domain sockets: "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/run/postgresql", "/var/run/postgresl", "/var/pgsql_socket", "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/private/tmp", and "/tmp",
"localhost"

Well for the first one I don't think we can do much, as we need to cover more than just asyncpg.

Fore the second one though, I think we should be fine if asyncpg can accept host=None and by that I mean it will try the fallbacks when host=None. If the fallbacks are ignore with host=None we need to omit that from the input.

I think it's probably not worth it.

Ok makes sense. Is my approach in the pr alright or is it missing the mark?

I think it's pretty good and what we want.
I just need to test it locally and make sure it does what we want.

Because we only test DatabaseUrl, we don't test the integration with postgres.

Navigation Menu

Repository navigation

DatabaseUrl bug when using Unix domain socket #422

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

DatabaseUrl bug when using Unix domain socket #422

Description

Activity

aminalaee commented on Nov 15, 2021

dbatten5 commented on Nov 15, 2021

aminalaee commented on Nov 15, 2021

dbatten5 commented on Nov 15, 2021

aminalaee commented on Nov 15, 2021

dbatten5 commented on Nov 15, 2021

aminalaee commented on Nov 15, 2021

dbatten5 commented on Nov 15, 2021

aminalaee commented on Nov 15, 2021

dbatten5 commented on Nov 15, 2021

aminalaee commented on Nov 15, 2021

dbatten5 commented on Nov 15, 2021

aminalaee commented on Nov 15, 2021

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions