3

using airflow locally, I was able to add a SSL Postgres connection using this :

./airflow.sh connections add connection_name --conn-uri 'postgres://user:@host:port/db?sslmode=verify-ca&sslcert=<>.crt&sslca=<>.crt&sslkey=<>.key.pk8'

Now I'm using Cloud Composer, and I want to add this connection again, but I can't find how. I tried using gcloud CLI as explained here but I can't find the right method or the right arguments to use.

Does anyone know how to do it ? Thanks in advance !

atleastthreecharacters
  • 35
  • 3
  • Easiest way to configure PostgreSQL connection is by using the Airflow Webserver console and navigating to Admin > Connection > Create. [refer](https://airflow.apache.org/docs/apache-airflow-providers-postgres/stable/connections/postgres.html) – Sakshi Gatyan Oct 23 '21 at 07:59
  • For [CLI](https://cloud.google.com/composer/docs/how-to/accessing/airflow-cli#running-commands) could you try the following command : `gcloud composer environments run \ ENVIRONMENT_NAME \ -- location LOCATION \ connections -- --add \ -- conn_id=CONNECTION_ID \ --conn-uri 'postgresql://user:@host:port/db?sslmode=verify-ca&sslcert=<>.crt&sslca=<>.crt&sslkey=<>.key.pk8' ` – Sakshi Gatyan Oct 23 '21 at 09:35
  • @SakshiGatyan I tried this like [here](https://cloud.google.com/composer/docs/how-to/managing/connections#creating_a_connection_to_another_project) both with airflow 1.* and 2.* since the command changes a little, but I keep having the error : `(gcloud.beta.composer.environments.run) unrecognized arguments: dt_test --conn-uri (did you mean '--configuration'?) ` or something similar : every arguments after the `--` are unrecognized. – atleastthreecharacters Oct 25 '21 at 09:00
  • Okay I found the issue : windows. For some reason, that command doesn't seem to work using the CLI on windows, but I tried on linux and it worked fine. – atleastthreecharacters Oct 25 '21 at 09:15
  • @SakshiGatyan Yeah i tried but I need more reputation to upvote your awnser... it tells me : Thanks for the feedback! You need at least 15 reputation to cast a vote, but your feedback has been recorded. – atleastthreecharacters Oct 28 '21 at 10:07

1 Answers1

1

As per our discussion in comments, you can configure PostgreSQL connection in Cloud Composer using :

CLI (Linux)

Cloud SDK supports Airflow CLI subcommand to run. When specifying the connection as URI, extras (ie. sslmode,sslcert etc) are passed as parameters of the URI.

For example refer the following command:

gcloud composer environments run \
  ENVIRONMENT_NAME \
  --location LOCATION \
  connections -- --add \
  --conn_id=CONNECTION_ID \
  --conn-uri 'postgresql://postgres_user:XXXXXXXXXXXX@1.1.1.1:5432/postgresdb?sslmode=verify-ca&sslcert=%2Ftmp%2Fclient-cert.pem&sslkey=%2Ftmp%2Fclient-key.pem&sslrootcert=%2Ftmp%2Fserver-ca.pem'

For more information refer to this Airflow Documentation.

Cloud Composer Airflow Console

  • In the Airflow webserver console generated by Cloud Composer, navigate to Admin > Connection > Create
  • Specify Connection Id,Connection Type (Postgres)
  • Fill the required parameters ie. Host,Login, Password. (Refer)
  • Extra: extra parameters (as json) ie.sslmode,sslcert, sslca, sslkey
Sakshi Gatyan
  • 1,903
  • 7
  • 13