FastAPI+Docker应用部署至Heroku时遭遇asyncpg.exceptions.InvalidAuthorizationSpecificationError问题求助

That asyncpg.exceptions.InvalidAuthorizationSpecificationError with the "no pg_hba.conf entry... SSL off" message is a super common gotcha when deploying PostgreSQL-connected apps to Heroku. Let’s break down what’s happening and fix it:

The Root Cause #

Heroku’s managed PostgreSQL instances force all external connections to use SSL for security. Your FastAPI app is trying to connect without SSL enabled, so Postgres rejects the connection outright. This causes your app to crash on startup, which is why you’re seeing the 503 Service Unavailable error when accessing the /ping/ endpoint.

Step-by-Step Fix #

1. Update Your Database Connection to Enable SSL

How you do this depends on how you’re connecting to Postgres:

• Direct asyncpg connection:
  When calling asyncpg.connect(), explicitly pass the ssl=True parameter:

  import asyncpg
  import os
  
  async def get_db_connection():
      conn = await asyncpg.connect(
          dsn=os.getenv("DATABASE_URL"),
          ssl=True
      )
      return conn
  

• Using SQLAlchemy with asyncpg:
  Modify your engine creation to include SSL requirements in the connection arguments:

  from sqlalchemy.ext.asyncio import create_async_engine
  import os
  
  DATABASE_URL = os.getenv("DATABASE_URL")
  engine = create_async_engine(
      DATABASE_URL,
      connect_args={"ssl": {"sslmode": "require"}}
  )
  

• If using a raw connection string:
  Double-check that your DATABASE_URL includes sslmode=require (Heroku usually adds this automatically, but custom parsing can sometimes strip it out). Example valid URL:

  postgres://user:pass@host:port/dbname?sslmode=require
  

2. Test the Change Locally (Optional but Smart)

To avoid breaking your local dev environment, add a conditional to disable SSL only when running locally:

connect_args = {}
if os.getenv("ENVIRONMENT") != "development":
    connect_args["ssl"] = {"sslmode": "require"}

engine = create_async_engine(DATABASE_URL, connect_args=connect_args)

3. Rebuild and Redeploy to Heroku

Once your code is updated, rebuild your Docker image and push it to Heroku:

docker build -t registry.heroku.com/APP_NAME/web .
docker push registry.heroku.com/APP_NAME/web
heroku container:release web --app APP_NAME

4. Verify the Fix

Check the Heroku logs again to confirm the connection error is gone:

heroku logs --tail --app APP_NAME

You should see your app start successfully, and hitting the /ping/ endpoint will now return a 200 response.

内容的提问来源于stack exchange，提问作者user8902140