CODEWITHSUNDEEP
pythonpostgresqlfastapiasyncpg
Daniel
Daniel

Reputation: 943

How to connect to an Azure Postgres DB that requires ssl using asyncpg

I'm migrating an application from Flask to FastAPI and with it also the database connection from psycopg2 to asyncpg. In the old implementation the databse url was defined like this: SQLALCHEMY_DATABASE_URI: str = f"postgresql+psycopg2://{DB_USER}:{DB_PASSWD}@{DB_HOST}:5432/{DB_NAME}?sslmode=require"

When switching to asyncpg I had to omit the sslmode argument as apparently this is not supported. Now the connection url looks like this: SQLALCHEMY_DATABASE_URI: str = f"postgresql+asyncpg://{DB_USER}:{DB_PASSWD}@{DB_HOST}:5432/{DB_NAME}"

The problem is that now the connection ends in a timeout error as azure refuses the connection attempt due to the ssl dependency.

My code to connect to the database is as follows:

from sqlmodel import SQLModel
from sqlmodel.ext.asyncio.session import AsyncSession
from sqlalchemy.ext.asyncio import AsyncEngine, create_async_engine
from sqlalchemy.orm import sessionmaker
from typing import AsyncGenerator
from config import config
from utils.logging import logger

engine: AsyncEngine = create_async_engine(
    url=config.SQLALCHEMY_DATABASE_URI,
    echo=config.DB_ECHO,
    pool_pre_ping=True,
    pool_size=20,
    max_overflow=10
)

async_session_maker = sessionmaker(
    bind=engine,
    class_=AsyncSession,
    expire_on_commit=False,
    autocommit=False,
    autoflush=False,
)


async def get_session() -> AsyncGenerator[AsyncSession, None]:
    logger.debug(f"DB url: {config.SQLALCHEMY_DATABASE_URI}")
    """Yield a database session"""
    async with async_session_maker() as session:
        try:
            yield session
            await session.commit()
        except Exception as e:
            await session.rollback()
            raise

In the old version simply setting a flag sslmode=require was enough to make it work without issues in azure. Now apparently based on this GitHub issue it seems like the only way to make this work is by providing the actual ssl certificate like this:

# Set up SSL context
ssl_context = ssl.create_default_context(ssl.Purpose.CLIENT_AUTH)
ssl_context.load_cert_chain(certfile=config.SSL_CERT_FILE, keyfile=config.SSL_KEY_FILE)

# SQLAlchemy URL, now with SSL parameters
SQLALCHEMY_DATABASE_URI: str = f"postgresql+asyncpg://{DB_USER}:{DB_PASSWD}@{DB_HOST}:5432/{DB_NAME}"

# Create engine with SSL context
engine = create_async_engine(
    url=SQLALCHEMY_DATABASE_URI,
    echo=config.DB_ECHO,
    pool_pre_ping=True,
    pool_size=20,
    max_overflow=10,
    connect_args={"ssl": ssl_context}  # Pass SSL context
)

However I don't have access to the SSL certificate and I need a way to connect to this azure db via an async method without providing the ssl certificate as a file. How can this be done?

Upvotes: 0

Views: 133

Answers (1)

Daniel
Daniel

Reputation: 943

I finally found the answer myself.

Turns out the easiest way is to use the URL function from sqlalchemy.engine where you can pass in the SSL mode.

from sqlalchemy.engine import URL


# PostgreSQL connection string
SQLALCHEMY_DATABASE_URI: str = URL.create(
    drivername="postgresql+asyncpg",
    username=DB_USER,
    password=DB_PASSWD,
    host=DB_HOST,
    port=5432,
    database=DB_NAME,
    query={
        "ssl": "require"
    }
)

Upvotes: 0

Related Questions