2

I'm running a test Python script that uploads a small amount of data to a PostgreSQL database using SQL Alchemy. Apache Airflow (hosted by Google Cloud Composer) is running this script on a schedule. The script always runs totally fine whenever I run it on Airflow locally and connect directly to the DB.

When I run it on Cloud Composer however, I need to connect through Google's Cloud SQL Proxy for security reasons.

Here's the weird part - the script runs fine about 50% of the time when connecting through Google's cloud-sql-proxy. The other 50% of the time I get the error (psycopg2.OperationalError) FATAL: password authentication failed for user "XXXX. And then usually it retries and it connects just fine.

I'm assuming it's probably something to do with how I am managing my connection (how it's being opened, pooled, or closed) so here is how I'm doing that. I'm using SQLAlchemy in my Python script like so:

from sqlalchemy import create_engine

engine = create_engine(f"postgresql://{USER}:{PASS}@{HOST}/{DB_NAME}")
conn = engine.connect()
# Upload a bit of data here
conn.close()
engine.dispose()

I've always used this way of uploading data locally so I'm a bit confused why that wouldn't work with Google Cloud Proxy / Google Cloud Composer (hosted Airflow).

Matt
  • 1,368
  • 1
  • 26
  • 54
  • Since this is intermittent, let's gather more information. 1) Is the user in the error a system user or a custom user? 2) Please share the whole error since it is possible that other details can help. 3) For replication purposes could you confirm if you are following [this procedure](https://cloud.google.com/sql/docs/postgres/connect-external-app#sqlalchemy-tcp)? I'm daring to suspect that issue can be caused on the proxy initialization, for example, a configuration to connect to multiple instances with the same user but different user (I'm just imagining all possible scenarios). – rsantiago Apr 22 '20 at 02:52

0 Answers0