Execute DDL scripts on GCP Cloud SQL instance via Ansible

Get Help
,
1

Hi All,
I am working on an ansible playbook to execute DDL scripts on a private google cloud SQL instance. Below is the playbook code i am using:

  • name: Check postgreSQL version
    hosts: localhost
    gather_facts: no
    become: yes
    vars:
    project_id: “xxxxxx”
    instance_name: “xxxxxx”
    region: “xxxxxx”
    tasks:

    • name: Install python3
      apt:
      name: python3

    • name: Install pip3
      apt:
      name: python3-pip

    • name: Install libpq-dev
      apt:
      name: libpq-dev

    • name: Install psycopg2
      apt:
      name: python3-psycopg2

    • name: Connect to SQL instance
      community.postgresql.postgresql_db:
      db: “{{ db_name }}”
      login_host: “{{ ansible_host }}”
      login_password: “{{ db_password }}”
      login_user: “{{ db_user }}”
      port: “5432”
      state: present
      target: DDL.sql

Below is the snippet of my inventory file:

cloudsql-instance ansible_host=x.x.x.x ansible_user=postgres ansible_ssh_private_key_file=~/.ssh/id_rsa

I am executing the playbook from a VM which is on the same network as that of the private cloud sql instance, but the playbook is not able to connect to the instance. How can i achieve this? Kindly assist.

Thanks

2

You’re targeting localhost, so it’s not clear where the inventory is used or where the playbook is failing. Assuming you can ssh manually to cloudsql-instance, you can run your playbook with -vvvv to compare the ssh command ansible is using with what you expect.

4

We cannot ssh into private google cloud sql instance. That is the challenge. Is there any way to do it? Any collection that supports connection mechanisms other than ssh?

5

I don’t see any custom connection plugins in the google.cloud or community.google collections. :disappointed: One could be created probably - here’s an example of an alternate connection method for AWS community.aws.aws_ssm connection – connect to EC2 instances via AWS Systems Manager — Ansible Community Documentation (all of the google.cloud stuff is autogenerated though, so I’m not sure new plugins can easily be added to that collection).

How would you manually connect? You might be able to configure a custom ssh executable that invokes the command instead of ssh (here’s an example using gcloud compute ssh google cloud platform - Ansible GCP IAP tunnel - Stack Overflow).

7

I believe shell commands can be used, but i wanted to understand if there is any collection/plugin available for ssh connectivity to cloudSQL instance or via API calls.

8

Yes, I understand. I could not find a custom connection plugin. You can see the list of plugins included in the collection here Google.Cloud — Ansible Community Documentation.