Root password not set on postgres instance

[mruoss]

Describe the bug
I'm creating an SQLInstance of type POSTGRES_12. As rootPassword I pass a secret key reference which contains the desired password for the root (postgres) user. The instance is created successfully and my expectation is that I can connect to it with the user postgres and the password I defined in the secret. However, my connection failed with the reason "wrong password".

After setting the password over the following gcloud command, the connection works:

gcloud sql users set-password postgres --prompt-for-password --instance=test

I even tried to define a resource of type SQLUser with name postgres, but no luck either.

ConfigConnector Version
1.21.1

To Reproduce
Apply the manifest below.

YAML snippets:

---
apiVersion: sql.cnrm.cloud.google.com/v1beta1
kind: SQLInstance
metadata:
  name: test
  namespace: test
spec:
  databaseVersion: POSTGRES_12
  region: europe-west6
  settings:
    activationPolicy: ALWAYS
    availabilityType: ZONAL
    diskAutoresize: true
    diskSize: 10
    diskType: PD_HDD
    ipConfiguration:
      ipv4Enabled: false
      privateNetworkRef:
        external: projects/ufg-demo/global/networks/default
      requireSsl: false
    tier: db-f1-micro
  rootPassword:
    valueFrom:
      secretKeyRef:
        name: test-credentials
        key: password
---
apiVersion: v1
kind: Secret
metadata:
  name: test-credentials
  namespace: test
stringData:
  password: some-password

[caieo]

Hi @mruoss , thank you for reporting this issue with clear reproduction steps & I'm sorry this is not working the way it should. We will look into fixing this soon.

[caieo]

Hi @mruoss , it looks like the field rootPassword is ignored for PostgreSQL. Unfortunately, our public documentation is lacking definitions for the fields, but if you look at the CRD, the field has this description: Initial root password. Required for MS SQL Server, ignored by MySQL and PostgreSQL. We'll work on making that more clear in the future.

I also tried having a SQLUser as postgres to access the SQLInstance, but it did not work, so we will investigate further.

[mruoss]

Hi @caieo, thank you very much for your responses. I see now, but yes, the documentation is not very clear. In my case, what I need is to create a super user with the same root privileges as the postgres user and with a given password. It does not necessarily have to be the postgres user. But it can. ;) I currently don't see a way to achieve that with config connector. Thanks for your investigations.

[xiaobaitusi]

Hi @mruoss, can you kubectl describe your SQLUser CR 'postgres'? Is it update-to-date or update-failed with the password change?

Also looking at the doc here, do you mind trying to create another user with different name and use it to connect the instance? Per the doc, other created users should have the same privileges as postgres.

[mruoss]

Hi @xiaobaitusi

See the status part of the kubectl describe below. It looks a bit strange to me. While the status reports UpToDate and Ready, I keep getting these events of type warning that say reference SQLInstance test/test is not ready. However, the instance is ready. I know that because I can set the password with the gcloud command (see my initial issue report) ,I can connect to it and also, I created a second user with a different name but otherwise exact same spec. This user works as expected. So the instance is ready despite the warnings on the postgres user resource.

So for me this workaround of creating a different user is good enough as it has the same privileges as the postgres user. I leave it up to you whether to keep this issue open and investigate the problems with the postgres user or close it.

kubectl describe sqluser/test

Name: postgres
[...]
Status:
  Conditions:
    Last Transition Time:  2020-10-01T06:40:53Z
    Message:               The resource is up to date
    Reason:                UpToDate
    Status:                True
    Type:                  Ready
Events:
  Type     Reason              Age                      From                Message
  ----     ------              ----                     ----                -------
  Normal   UpToDate            42m (x403 over 5d18h)    sqluser-controller  The resource is up to date
  Warning  DependencyNotReady  112s (x5020 over 5d18h)  sqluser-controller  reference SQLInstance test/test is not ready

[xiaobaitusi]

@mruoss, we will keep investigating why the default user cannot be updated by ConfigConnector. Thanks for your detailed information.

[mruoss]

Hi @xiaobaitusi
I faced another issue. Apparently the user created with SQLUser is not a SUPERUSER like the postgres user. So it is not equivalent to the postgres user and I am stuck again...

[maqiuyujoyce]

Hi @mruoss , I'm sorry it's still not working for you. Could you provide more details about it? For example, what operations/permissions are you blocked on?

I created a new SQLUser for the PosgreSQL SQLInstance using KCC, and verified that it has the same set of permissions as the user postgres. I.e. the permissions listed in select * from pg_roles; are the same for postgres and the new SQL user. Could you run the command and verify if the permissions look right?

[mruoss]

Hi @maqiuyujoyce
My bad, sorry. I was expecting postgres user to have the SUPERUSER flag set. But that's not actually the case. So the two are indeed equivalent. All good.

[tonybenchsci]

@caieo @maqiuyujoyce We have a related issue and probably same root cause as this. We suspect it has to do with the 1.19.0 update that did "Fix issue where SQLuser would constantly update despite there being no changes."

I'm afraid we might have been using the constant update as a feature instead of a bug. We have a system where we rely on KCC's reconciliation loop to rectify a SQLUser password (via a secretRef) after a restoring a sqlinstance from a backup (which overwrites credentials).

The expected behaviour is that each time the SQLUser resource is "up-to-date", then the password should be whatever is specified in secretRef. But we see "up-to-date" but the password is not changed.

[jcanseco]

Hey @tonybenchsci, I opened a new thread (#292) about your issue given that it's kind of its own topic. Let us continue the conversation there.