[Bug]: MySQL Root user is removed upon initial creation of the instance

[deleocj]

Is there an existing issue for this?

• [X] I have searched the existing issues

Affected Resource(s)

databaseinstance.sql.gcp.upbound.io MySQL

Resource MRs required to reproduce the bug

No response

Steps to Reproduce

Applying the yaml file for a CloudSQL MySQL:

---
apiVersion: sql.gcp.upbound.io/v1beta1
kind: DatabaseInstance
metadata:
  annotations:
    meta.upbound.io/example-id: sql/v1beta1/databaseinstance
  labels:
    testing.upbound.io/example-name: example_instance
  name: cj-test-mysql-new2
spec:
  forProvider:
    databaseVersion: MYSQL_8_0
    deletionProtection: false
    region: us-central1
    settings:
      - ipConfiguration:
        - requireSsl: false
          ipv4Enabled: false
          privateNetwork: <NETWORK>
        diskSize: 20
        tier: db-perf-optimized-N-64
        userLabels:
          snow: my-snow
          beans: mybeans
        edition: ENTERPRISE_PLUS
        databaseFlags:
          - name: cloudsql_iam_authentication
            value: "on"
  writeConnectionSecretToRef:
    name: upbound-cj-sql-db-instance-secret
    namespace: upbound-system

What happened?

The instance is provisioned with the root user, then immediately after the instance finished creating the root user is dropped. My code relies on the root credentials to connect to the instance

Relevant Error Output Snippet

No response

Crossplane Version

1.15.2

Provider Version

1.1.0

Kubernetes Version

No response

Kubernetes Distribution

No response

Additional Info

No response

[deleocj]

PostgreSQL does not have this issue. The initial 'postgres' user remains active...very confused on why that is

[turkenf]

Hi @deleocj,

Thank you for bringing up this. When I look at the issue report, I can't quite understand what I expect, how to reproduce, and what the issue is. Could you please provide us with more information?

[deleocj]

@turkenf its actually really simple to reproduce this. I have a kurbernetes cluster, installed v1.15.2 of Crossplane, installed the provider gcp sql (as well as provider gcp family), applied the yaml for the managed resource. Instance creates in GCP but there is no 'root' user. Everyone whos created CloudSQL MySQL instances in GCP knows it gets created with a 'root' user. Very similar to PostgreSQL having a 'postgres' user upon creation.

There is no 'root' user on the MySQL instance when I use the provider to create an instance. I want that fixed, I want the 'root' user with the password stored in the kubernetes secret.

I read this "Second-generation instances include a default 'root'@'%' user with no password. This user will be deleted by Terraform on instance creation." from here: https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/sql_database_instance. So Terraform is dropping the 'root' user when an instance is created. Why?

[deleocj]

See screenshots from GCP console as reference. Both resources were created by the provider. One has the user I expect 'postgres' and the other has no user, I expect 'root' to be there

[deleocj]

We never experienced 'root' user being removed when we used previous versions of Crossplane and provider-gcp

[turkenf]

Thanks for the quick response 🙏

We never experienced 'root' user being removed when we used previous versions of Crossplane and provider-gcp

So, should I understand from here that this issue did not occur before provider v1.1.0? and have you tried with v1.2.0?

[deleocj]

No I previously used the public provider gcp that is no longer supported crossplane/provider-gcp-controller:v0.21.0. We now want to use the supported provider gcp family but getting blocked by this issue. We attempted using v1.2.0 as well as v1.0.0 and v1.1.0, same issue

[deleocj]

Im baffled that I'd be the only person to ever ask where the 'root' user is for a CloudSQL MySQL when 'root' user is standard lol

[turkenf]

I read this "Second-generation instances include a default 'root'@'%' user with no password. This user will be deleted by Terraform on instance creation." from here: https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/sql_database_instance. So Terraform is dropping the 'root' user when an instance is created. Why?

As you said, this is due to the behavior of the underlying provider, currently, the only solution seems to be to create a User.sql resource.

[deleocj]

@turkenf that is what we are doing as a workaround but if this is the behavior of the Terraform provider you are using, what can be done to stop Terraform from dropping the root user? Also why is this not happening with the 'postgres' user for PostgreSQL?

[github-actions[bot]]

This provider repo does not have enough maintainers to address every issue. Since there has been no activity in the last 90 days it is now marked as stale. It will be closed in 14 days if no further activity occurs. Leaving a comment starting with /fresh will mark this issue as not stale.

[github-actions[bot]]

This issue is being closed since there has been no activity for 14 days since marking it as stale. If you still need help, feel free to comment or reopen the issue!