Images cannot connect to Azure DBs

[pdelboca]

Hello all!

I have been testing and trying this images for a deployment in Azure. However, I'm facing problems when trying to connect the instance to the Azure DB.

Steps to reproduce

1. Create an Azure Postgresql Database
   a. usually something like: postgresql://<user>:<pass>@<app>.database.azure.com/<db>?sslmode=require
2. Set the sqlalchemy.url to point to the database.
3. Run the instance

You will get an error when trying to start the application. (it will fail the first time trying to connect to the database)

unable to load app 0 (mountpoint='') (callable not found or import error)
*** no app loaded. going in full dynamic mode ***
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "nrc-postgresql.postgres.database.azure.com" (172.178.55.110), port 5432 failed: could not open certificate file "/root/.postgresql/postgresql.crt": Permission denied

Mode details

After debugging for quite some hours I think the error comes from uWSGI (although it points to an issue reading with a certificate).

Why I think this is the case? First, if I login into the container and run ckan shell I can access the database. If I replace the start_ckan.sh with a call to start CKAN using gunicorn it will work.

This works:

FROM ckan/ckan-base:2.10.1

# Just to setup the configuration file with the DB connection
# SQLALCHEMY_URL = `postgresql://<user>:<pass>@<app>.database.azure.com/<db>?sslmode=require`
ARG SQLALCHEMY_URL  
COPY setup-configuration-file.sh .
RUN ./setup-configuration-file.sh

RUN pip install gunicorn

CMD ["gunicorn", "--workers", "2", "--bind", "0.0.0.0:5000", "wsgi:application"]

This doesn't work:

FROM ckan/ckan-base:2.10.1

# Just to setup the configuration file with the DB connection
# SQLALCHEMY_URL = `postgresql://<user>:<pass>@<app>.database.azure.com/<db>?sslmode=require`
ARG SQLALCHEMY_URL  
COPY setup-configuration-file.sh .
RUN ./setup-configuration-file.sh

# Default to CMD ["/srv/app/start_ckan.sh"]

Any ideas or someone else has encountered this error?

[wardi]

uwsgi switches the user account
https://github.com/ckan/ckan-docker-base/blob/f5b8eef066026ec1ede0d8611adf7364f6313f0c/ckan-2.9/base/setup/start_ckan.sh#L35C1-L35C30

Does UID/GID 29 have access to /root/.postgresql/postgresql.crt?

It probably shouldn't. Maybe use a file from its home directory instead, or clean out the environment before switching UID/GID

[pdelboca]

Thanks for the pointers @wardi ! Indeed it is an issue with the user running uWSGI.

Note that /root/.postgresql/postgresql.crt does not exist. I think it is just internal library trying to access for some reason.

After some more debugging and searching I ran into this comment: psycopg/psycopg2#1535 (comment)

Even when we are running uWSGI with the UID/GID parameters, the environment variable HOME still points to /root thus, making the internal logic to fail. By simply adding a export HOME=/home/ckan fixes the issue.

How shall we proceed to fix this? We are still running uWSGI as root (but with UID/GID parameters) so maybe it is worth to switch to the ckan user and run it with that user?

It also worth noticing that we shouldn't do all the pip3 install in the system, but rather create a virtual environment and do it there. This may involve some refactoring in the image but I think it is the best long term solution.

Any thoughts @kowh-ai ?

[kowh-ai]

Sorry @pdelboca - been on other things - so you are saying only when the uwsgi tries to connect to an external database (external to the local docker network) it will try to access (root’s) postgresql.crt file? Are you using ?sslmode=require in all connection strings? even when it is successful?

I guess the uid and gid arguments to uwsgi creates an “effective user” change rather than a “real user”change. That makes sense given the errors you describe

Wondering if there is a way to control what postgresql.crt file (and it’s location) is accessed. That would be a cleaner workaround to the issue rather than completely change the CKAN runtime

Could be something to try in here: https://www.postgresql.org/docs/current/libpq-envars.html