[FIXED] How to add a SSL postgres connection using CLI for Cloud Composer?

Issue

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 !

Solution

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

Answered By - Sakshi Gatyan

Answer Checked By - Clifford M. (PHPFixing Volunteer)