search

testdrivenio / fastapi-tdd-docker

Test-Driven Development with FastAPI and Docker
165 stars 49 forks source link

SSL certificate for database connection #5

Closed idjotherwise closed 3 years ago

idjotherwise commented 3 years ago

I'm trying to do the deploy to heroku step (first bit of Part 2) but having some SSL related problems. It seems that Heroku requires connections to Postgres be with an SSL certificate? Has anyone else has stumbled across this problem and managed to solve it?

This is the information I get from heroku logs

[2021-03-07 17:11:39 +0000] [132] [ERROR] Application startup failed. Exiting.
2021-03-07T17:11:39.433115+00:00 app[web.1]: [2021-03-07 17:11:39 +0000] [132] [INFO] Worker exiting (pid: 132)
2021-03-07T17:11:39.662653+00:00 app[web.1]: [2021-03-07 17:11:39 +0000] [137] [INFO] Booting worker with pid: 137
2021-03-07T17:11:40.711469+00:00 app[web.1]: [2021-03-07 17:11:40 +0000] [137] [INFO] Started server process [137]
2021-03-07T17:11:40.711604+00:00 app[web.1]: [2021-03-07 17:11:40 +0000] [137] [INFO] Waiting for application startup.
2021-03-07T17:11:40.917185+00:00 app[web.1]: [2021-03-07 17:11:40 +0000] [137] [ERROR] Traceback (most recent call last):
2021-03-07T17:11:40.917187+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 526, in lifespan
2021-03-07T17:11:40.917188+00:00 app[web.1]: async for item in self.lifespan_context(app):
2021-03-07T17:11:40.917191+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 467, in default_lifespan
2021-03-07T17:11:40.917192+00:00 app[web.1]: await self.startup()
2021-03-07T17:11:40.917193+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 502, in startup
2021-03-07T17:11:40.917193+00:00 app[web.1]: await handler()
2021-03-07T17:11:40.917193+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/tortoise/contrib/fastapi/__init__.py", line 92, in init_orm
2021-03-07T17:11:40.917194+00:00 app[web.1]: await Tortoise.init(config=config, config_file=config_file, db_url=db_url, modules=modules)
2021-03-07T17:11:40.917194+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/tortoise/__init__.py", line 567, in init
2021-03-07T17:11:40.917195+00:00 app[web.1]: await cls._init_connections(connections_config, _create_db)
2021-03-07T17:11:40.917195+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/tortoise/__init__.py", line 385, in _init_connections
2021-03-07T17:11:40.917196+00:00 app[web.1]: await connection.create_connection(with_db=True)
2021-03-07T17:11:40.917197+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/tortoise/backends/asyncpg/client.py", line 94, in create_connection
2021-03-07T17:11:40.917197+00:00 app[web.1]: self._pool = await asyncpg.create_pool(None, password=self.password, **self._template)
2021-03-07T17:11:40.917197+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/asyncpg/pool.py", line 398, in _async__init__
2021-03-07T17:11:40.917198+00:00 app[web.1]: await self._initialize()
2021-03-07T17:11:40.917198+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/asyncpg/pool.py", line 426, in _initialize
2021-03-07T17:11:40.917199+00:00 app[web.1]: await first_ch.connect()
2021-03-07T17:11:40.917199+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/asyncpg/pool.py", line 125, in connect
2021-03-07T17:11:40.917199+00:00 app[web.1]: self._con = await self._pool._get_new_connection()
2021-03-07T17:11:40.917200+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/asyncpg/pool.py", line 468, in _get_new_connection
2021-03-07T17:11:40.917200+00:00 app[web.1]: con = await connection.connect(
2021-03-07T17:11:40.917201+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 1718, in connect
2021-03-07T17:11:40.917201+00:00 app[web.1]: return await connect_utils._connect(
2021-03-07T17:11:40.917202+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/asyncpg/connect_utils.py", line 663, in _connect
2021-03-07T17:11:40.917202+00:00 app[web.1]: con = await _connect_addr(
2021-03-07T17:11:40.917202+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/asyncpg/connect_utils.py", line 642, in _connect_addr
2021-03-07T17:11:40.917203+00:00 app[web.1]: await asyncio.wait_for(connected, timeout=timeout)
2021-03-07T17:11:40.917203+00:00 app[web.1]: File "/usr/local/lib/python3.8/asyncio/tasks.py", line 491, in wait_for
2021-03-07T17:11:40.917204+00:00 app[web.1]: return fut.result()
2021-03-07T17:11:40.917205+00:00 app[web.1]: asyncpg.exceptions.InvalidAuthorizationSpecificationError: no pg_hba.conf entry for host "34.207.122.191", user "cslyxnkrbufuls", database "d517o756cbcm5n", SSL off

I tried adding "ssl=True" to the db_url like so


def init_db(app: FastAPI) -> None:

    register_tortoise(
        app,
        db_url=os.environ.get("DATABASE_URL") + "?ssl=True",
        modules={"models": ["app.models.tortoise"]},
        generate_schemas=False,
        add_exception_handlers=True,
    )

but it seems that it can't verify a self signed certificate..

2021-03-07T17:16:56.727597+00:00 app[web.1]: [2021-03-07 17:16:56 +0000] [77] [ERROR] Application startup failed. Exiting.
2021-03-07T17:16:56.727835+00:00 app[web.1]: [2021-03-07 17:16:56 +0000] [77] [INFO] Worker exiting (pid: 77)
2021-03-07T17:16:56.789371+00:00 app[web.1]: [2021-03-07 17:16:56 +0000] [82] [INFO] Booting worker with pid: 82
2021-03-07T17:16:57.142427+00:00 app[web.1]: [2021-03-07 17:16:57 +0000] [82] [INFO] Started server process [82]
2021-03-07T17:16:57.142517+00:00 app[web.1]: [2021-03-07 17:16:57 +0000] [82] [INFO] Waiting for application startup.
2021-03-07T17:16:57.207812+00:00 app[web.1]: [2021-03-07 17:16:57 +0000] [82] [ERROR] Traceback (most recent call last):
2021-03-07T17:16:57.207814+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 526, in lifespan
2021-03-07T17:16:57.207814+00:00 app[web.1]: async for item in self.lifespan_context(app):
2021-03-07T17:16:57.207815+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 467, in default_lifespan
2021-03-07T17:16:57.207816+00:00 app[web.1]: await self.startup()
2021-03-07T17:16:57.207816+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 502, in startup
2021-03-07T17:16:57.207816+00:00 app[web.1]: await handler()
2021-03-07T17:16:57.207817+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/tortoise/contrib/fastapi/__init__.py", line 92, in init_orm
2021-03-07T17:16:57.207817+00:00 app[web.1]: await Tortoise.init(config=config, config_file=config_file, db_url=db_url, modules=modules)
2021-03-07T17:16:57.207817+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/tortoise/__init__.py", line 567, in init
2021-03-07T17:16:57.207817+00:00 app[web.1]: await cls._init_connections(connections_config, _create_db)
2021-03-07T17:16:57.207818+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/tortoise/__init__.py", line 385, in _init_connections
2021-03-07T17:16:57.207818+00:00 app[web.1]: await connection.create_connection(with_db=True)
2021-03-07T17:16:57.207819+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/tortoise/backends/asyncpg/client.py", line 94, in create_connection
2021-03-07T17:16:57.207819+00:00 app[web.1]: self._pool = await asyncpg.create_pool(None, password=self.password, **self._template)
2021-03-07T17:16:57.207819+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/asyncpg/pool.py", line 398, in _async__init__
2021-03-07T17:16:57.207822+00:00 app[web.1]: await self._initialize()
2021-03-07T17:16:57.207822+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/asyncpg/pool.py", line 426, in _initialize
2021-03-07T17:16:57.207822+00:00 app[web.1]: await first_ch.connect()
2021-03-07T17:16:57.207822+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/asyncpg/pool.py", line 125, in connect
2021-03-07T17:16:57.207823+00:00 app[web.1]: self._con = await self._pool._get_new_connection()
2021-03-07T17:16:57.207823+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/asyncpg/pool.py", line 468, in _get_new_connection
2021-03-07T17:16:57.207823+00:00 app[web.1]: con = await connection.connect(
2021-03-07T17:16:57.207823+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 1718, in connect
2021-03-07T17:16:57.207824+00:00 app[web.1]: return await connect_utils._connect(
2021-03-07T17:16:57.207824+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/asyncpg/connect_utils.py", line 674, in _connect
2021-03-07T17:16:57.207824+00:00 app[web.1]: raise last_error
2021-03-07T17:16:57.207824+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/asyncpg/connect_utils.py", line 663, in _connect
2021-03-07T17:16:57.207825+00:00 app[web.1]: con = await _connect_addr(
2021-03-07T17:16:57.207825+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/asyncpg/connect_utils.py", line 632, in _connect_addr
2021-03-07T17:16:57.207825+00:00 app[web.1]: tr, pr = await asyncio.wait_for(
2021-03-07T17:16:57.207826+00:00 app[web.1]: File "/usr/local/lib/python3.8/asyncio/tasks.py", line 491, in wait_for
2021-03-07T17:16:57.207826+00:00 app[web.1]: return fut.result()
2021-03-07T17:16:57.207826+00:00 app[web.1]: File "/usr/local/lib/python3.8/site-packages/asyncpg/connect_utils.py", line 564, in _create_ssl_connection
2021-03-07T17:16:57.207827+00:00 app[web.1]: new_tr = await loop.start_tls(
2021-03-07T17:16:57.207827+00:00 app[web.1]: File "uvloop/loop.pyx", line 1617, in start_tls
2021-03-07T17:16:57.207827+00:00 app[web.1]: File "uvloop/loop.pyx", line 1610, in uvloop.loop.Loop.start_tls
2021-03-07T17:16:57.207827+00:00 app[web.1]: File "uvloop/sslproto.pyx", line 517, in uvloop.loop.SSLProtocol._on_handshake_complete
2021-03-07T17:16:57.207828+00:00 app[web.1]: File "uvloop/sslproto.pyx", line 499, in uvloop.loop.SSLProtocol._do_handshake
2021-03-07T17:16:57.207828+00:00 app[web.1]: File "/usr/local/lib/python3.8/ssl.py", line 944, in do_handshake
2021-03-07T17:16:57.207828+00:00 app[web.1]: self._sslobj.do_handshake()
2021-03-07T17:16:57.207828+00:00 app[web.1]: ssl.SSLCertVerificationError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: self signed certificate (_ssl.c:1124)

Any ideas?

gmurman770 commented 3 years ago

Hi! I had the same problem and asked a question on StackOverflow: https://stackoverflow.com/questions/66119608/testdriven-io-test-driven-development-with-fastapi-and-docker-heroku-getting/66359374#66359374 The solution from there kinda works, but now I got stuck on Continues Integration section where I can't get DATABASE_URL (because we pass DATABASE_TEST_URL in test job). p.s. sorry for my English=)

idjotherwise commented 3 years ago

Hey! Thanks for this 😄 that got it working in the end (after I saw your comment about adding it also to the generate_schema).

There must be a better way to fix it, but at least it got my heroku deployment to work - though now I can't run the docker image locally since the database url locally is sqlite://sqlite.db and splitting it to get the credentials breaks.

Maybe I'll be back here once I reach the Continuous Integration part...

mjhea0 commented 3 years ago

@idjotherwise which command are you running? heroku run python app/db.py?

Also, can you run heroku run pip freeze and copy and paste the requirements. For example:

aiosqlite==0.16.1
asyncpg==0.22.0
attrs==20.3.0
certifi==2020.12.5
chardet==4.0.0
click==7.1.2
fastapi==0.63.0
gunicorn==20.0.4
h11==0.12.0
httptools==0.1.1
idna==2.10
iniconfig==1.1.1
iso8601==0.1.14
packaging==20.9
pluggy==0.13.1
py==1.10.0
pydantic==1.8.1
pyparsing==2.4.7
PyPika==0.44.1
pytest==6.2.2
python-dotenv==0.15.0
pytz==2020.5
PyYAML==5.4.1
requests==2.25.1
starlette==0.13.6
toml==0.10.2
tortoise-orm==0.16.21
typing-extensions==3.7.4.3
urllib3==1.26.3
uvicorn==0.13.4
uvloop==0.15.2
watchgod==0.7
websockets==8.1
mjhea0 commented 3 years ago

I'm updating the course right now and I'm still not seeing this error.

idjotherwise commented 3 years ago

@mjhea0 The error was happening when the application tried to start, so just after deploying with heroku container:release web --app secret-crag-02876 (with my app name).

After adding @gmurman770 's suggestion, it wasn't happening anymore, and it deployed successfully (and heroku run python app/db.py also completed fine). But then when I try to run the same docker image locally with docker run --name fastapi-tdd -e PORT=8765 -e DATABASE_URL=sqlite://sqlite.db -p 5003:8765 registry.heroku.com/secret-crag-02876/web:latest (with my app name) it fails because the DATABASE_URL is in a different format. I guess I can just work around that by checking first if the database is postgres otherwise register the tortoise_orm like normal, but I tend to think this goes against the purpose of the course!

Here's my pip freeze:

aiosqlite==0.16.1
asyncpg==0.21.0
attrs==20.3.0
certifi==2020.12.5
chardet==3.0.4
click==7.1.2
fastapi==0.62.0
gunicorn==20.0.4
h11==0.12.0
idna==2.10
iniconfig==1.1.1
iso8601==0.1.14
packaging==20.9
pluggy==0.13.1
py==1.10.0
pydantic==1.8.1
pyparsing==2.4.7
PyPika==0.44.1
pytest==6.2.0
pytz==2020.5
requests==2.25.0
starlette==0.13.6
toml==0.10.2
tortoise-orm==0.16.18
typing-extensions==3.7.4.3
urllib3==1.26.3
uvicorn==0.13.1

Also when running docker-compose up it fails to start with the following information from the logs:

web-db_1  | The files belonging to this database system will be owned by user "postgres".
web-db_1  | This user must also own the server process.
web-db_1  | 
web-db_1  | The database cluster will be initialized with locale "en_US.utf8".
web-db_1  | The default database encoding has accordingly been set to "UTF8".
web-db_1  | The default text search configuration will be set to "english".
web-db_1  | 
web-db_1  | Data page checksums are disabled.
web-db_1  | 
web-db_1  | fixing permissions on existing directory /var/lib/postgresql/data ... ok
web-db_1  | creating subdirectories ... ok
web-db_1  | selecting dynamic shared memory implementation ... posix
web-db_1  | selecting default max_connections ... 100
web-db_1  | selecting default shared_buffers ... 128MB
web-db_1  | selecting default time zone ... UTC
web-db_1  | creating configuration files ... ok
web-db_1  | running bootstrap script ... ok
web-db_1  | sh: locale: not found
web-db_1  | 2021-03-13 10:32:16.542 UTC [30] WARNING:  no usable system locales were found
web-db_1  | performing post-bootstrap initialization ... ok
web-db_1  | syncing data to disk ... ok
web-db_1  | 
web-db_1  | initdb: warning: enabling "trust" authentication for local connections
web-db_1  | You can change this by editing pg_hba.conf or using the option -A, or
web-db_1  | --auth-local and --auth-host, the next time you run initdb.
web-db_1  | 
web-db_1  | Success. You can now start the database server using:
web-db_1  | 
web-db_1  |     pg_ctl -D /var/lib/postgresql/data -l logfile start
web-db_1  | 
web-db_1  | waiting for server to start....2021-03-13 10:32:17.431 UTC [35] LOG:  starting PostgreSQL 13.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit
web-db_1  | 2021-03-13 10:32:17.434 UTC [35] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
web-db_1  | 2021-03-13 10:32:17.439 UTC [36] LOG:  database system was shut down at 2021-03-13 10:32:17 UTC
web-db_1  | 2021-03-13 10:32:17.442 UTC [35] LOG:  database system is ready to accept connections
web-db_1  |  done
web-db_1  | server started
web-db_1  | 
web-db_1  | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/create.sql
web-db_1  | CREATE DATABASE
web-db_1  | CREATE DATABASE
web-db_1  | 
web-db_1  | 
web-db_1  | 2021-03-13 10:32:17.748 UTC [35] LOG:  received fast shutdown request
web-db_1  | waiting for server to shut down....2021-03-13 10:32:17.749 UTC [35] LOG:  aborting any active transactions
web-db_1  | 2021-03-13 10:32:17.749 UTC [35] LOG:  background worker "logical replication launcher" (PID 42) exited with exit code 1
web-db_1  | 2021-03-13 10:32:17.750 UTC [37] LOG:  shutting down
web-db_1  | 2021-03-13 10:32:17.759 UTC [35] LOG:  database system is shut down
web-db_1  |  done
web-db_1  | server stopped
web-db_1  | 
web-db_1  | PostgreSQL init process complete; ready for start up.
web-db_1  | 
web-db_1  | 2021-03-13 10:32:17.874 UTC [1] LOG:  starting PostgreSQL 13.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit
web-db_1  | 2021-03-13 10:32:17.874 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
web-db_1  | 2021-03-13 10:32:17.874 UTC [1] LOG:  listening on IPv6 address "::", port 5432
web-db_1  | 2021-03-13 10:32:17.877 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
web-db_1  | 2021-03-13 10:32:17.881 UTC [49] LOG:  database system was shut down at 2021-03-13 10:32:17 UTC
web-db_1  | 2021-03-13 10:32:17.884 UTC [1] LOG:  database system is ready to accept connections
web_1     | Waiting for postgres...
web_1     | PostgreSQL started.
web_1     | INFO:     Uvicorn running on http://0.0.0.0:8000 (Press CTRL+C to quit)
web_1     | INFO:     Started reloader process [1] using statreload
web_1     | INFO:     Started server process [42]
web_1     | INFO:     Waiting for application startup.
web_1     | INFO:     Starting up...
web_1     | INFO:     Initializing DB...
web_1     | INFO:     postgres://postgres:postgres@web-db:5432/web_dev
web_1     | ERROR:    Traceback (most recent call last):
web_1     |   File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 526, in lifespan
web_1     |     async for item in self.lifespan_context(app):
web_1     |   File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 467, in default_lifespan
web_1     |     await self.startup()
web_1     |   File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 502, in startup
web_1     |     await handler()
web_1     |   File "/usr/local/lib/python3.9/site-packages/tortoise/contrib/fastapi/__init__.py", line 92, in init_orm
web_1     |     await Tortoise.init(config=config, config_file=config_file, db_url=db_url, modules=modules)
web_1     |   File "/usr/local/lib/python3.9/site-packages/tortoise/__init__.py", line 567, in init
web_1     |     await cls._init_connections(connections_config, _create_db)
web_1     |   File "/usr/local/lib/python3.9/site-packages/tortoise/__init__.py", line 385, in _init_connections
web_1     |     await connection.create_connection(with_db=True)
web_1     |   File "/usr/local/lib/python3.9/site-packages/tortoise/backends/asyncpg/client.py", line 94, in create_connection
web_1     |     self._pool = await asyncpg.create_pool(None, password=self.password, **self._template)
web_1     |   File "/usr/local/lib/python3.9/site-packages/asyncpg/pool.py", line 398, in _async__init__
web_1     |     await self._initialize()
web_1     |   File "/usr/local/lib/python3.9/site-packages/asyncpg/pool.py", line 426, in _initialize
web_1     |     await first_ch.connect()
web_1     |   File "/usr/local/lib/python3.9/site-packages/asyncpg/pool.py", line 125, in connect
web_1     |     self._con = await self._pool._get_new_connection()
web_1     |   File "/usr/local/lib/python3.9/site-packages/asyncpg/pool.py", line 468, in _get_new_connection
web_1     |     con = await connection.connect(
web_1     |   File "/usr/local/lib/python3.9/site-packages/asyncpg/connection.py", line 1718, in connect
web_1     |     return await connect_utils._connect(
web_1     |   File "/usr/local/lib/python3.9/site-packages/asyncpg/connect_utils.py", line 674, in _connect
web_1     |     raise last_error
web_1     |   File "/usr/local/lib/python3.9/site-packages/asyncpg/connect_utils.py", line 663, in _connect
web_1     |     con = await _connect_addr(
web_1     |   File "/usr/local/lib/python3.9/site-packages/asyncpg/connect_utils.py", line 632, in _connect_addr
web_1     |     tr, pr = await asyncio.wait_for(
web_1     |   File "/usr/local/lib/python3.9/asyncio/tasks.py", line 476, in wait_for
web_1     |     return fut.result()
web_1     |   File "/usr/local/lib/python3.9/site-packages/asyncpg/connect_utils.py", line 556, in _create_ssl_connection
web_1     |     do_ssl_upgrade = await pr.on_data
web_1     | ConnectionError: PostgreSQL server at "web-db:5432" rejected SSL upgrade
web_1     | 
web_1     | ERROR:    Application startup failed. Exiting.
mjhea0 commented 3 years ago

@idjotherwise

Can you try upgrading the following dependencies:

asyncpg==0.22.0 fastapi==0.63.0 requests==2.25.1 tortoise-orm==0.16.21

Then, do a new build, push the image to the registry, and release.

idjotherwise commented 3 years ago

Ah-ha that seems to have fixed it! Everything is working as expected now (without the need for the certificate work around). Thanks @mjhea0 - and thanks for the course as well, it's top quality stuff.