Set Postgres users' passwords declaratively

[gricey432]

Overview

2807 provided a way to set a password for a user using kubectl patch.

But there doesn't seem to be a way to do this declaratively in my postgres.yaml file.

Use Case

We're importing existing databases into a LAN-only k8s cluster and these clusters have existing passwords we'd like to keep using.

We're destroying and re-creating PGO clusters, but I can't fix their passwords in place.

Desired Behavior

PostgresCluster.spec.users[index].password would provide a passthrough for stringData and/or data.

Or maybe explicit password and verifier fields, whatever makes more sense.

Environment

Tell us about your environment:

Please provide the following details:

• Platform: EKS
• Platform Version: eks.2 k8s 1.22
• PGO Image Tag: ubi8-14.2-1
• Postgres Version: 14
• Storage: gp3
• Number of Postgres clusters: 2

[pere3]

any way of providing a password through secret/configmap ref would suffice, actually

[pere3]

https://access.crunchydata.com/documentation/postgres-operator/5.1.2/architecture/user-management/#custom-passwords there is a way tho

[gricey432]

https://access.crunchydata.com/documentation/postgres-operator/5.1.2/architecture/user-management/#custom-passwords there is a way tho

Yeah, I've mentioned that in the first line of my ticket. This is about being able to do it declaratively through manifests (AWS CDK in my case), this helps with repeatable deployments or high volumes.

[0x86f]

Ist there any commitment or workaround (not the manual patch-way) for this enhancement?

I would like to set a custom password by using a secretRef. The referenced secret would be created for example via sealedsecrets-operator.

I would like to use this enhancement for a Gitops-workflow.

[tony-landreth]

Hello, @gricey432! We've captured this request in our backlog. Thanks.

[arnouthoebreckx]

bump, this would be very useful for us to allow the usage of secretRefs. We store our secrets in an external secret manager and would like to use this secret.

[hdiass]

+1

[lukastopiarz]

+1 We would definitely love the possibility of secretRef for the external secret with password. Currently, in our gitops workflow we have to create Kyverno policy to patch pguser secret (To allow sealed secrets controller patch and manage existing file) and then patching the password key using sealed secret. Not a very nice gitops/declarative approach at all.

[kkapper]

+1

[rj-home]

+1

[dominik0711]

+1

[rj-home]

+1

[hdiass]

+1

[paretl]

+1

[koshrf]

I have a workaround I use with rancher fleet and sops operator, I can't change the secret directly with sops operator because the ownership, but I can save the password in a sopsecret and then run a job that patch the pgo secret. Probably not the best, but it works. PGO should be able to reference an external secret tho like other postgres operators. If you use external secret operator just read #3343

This is just an example, adapt to your preferences.

apiVersion: batch/v1
kind: Job
metadata:
  name: secret-patch-job
spec:
  backoffLimit: 6
  ttlSecondsAfterFinished: 3600
  template:
    metadata:
      name: secret-patch-job
    spec:
      serviceAccountName: secret-manager-sa
      containers:
      - name: secret-patch-container
        image: dtzar/helm-kubectl:latest #Just use whatever that can run kubectl commands and bash.
        command: ["/bin/bash", "-c"]
        env:
          - name: NAMESPACE
            value: "yourNamespace"
          - name: SOURCE_SECRET
            value: "sourceSecretOrSopsGeneratedSecret" #Contains: user, password, verify
          - name: TARGET_SECRET
            value: "database-pguser-database" 
        args:
          - |
            PASSWORD=$(kubectl get secret ${SOURCE_SECRET} -n ${NAMESPACE} -o jsonpath="{.data.password}" | base64 -d)
            USER=$(kubectl get secret ${SOURCE_SECRET} -n ${NAMESPACE} -o jsonpath="{.data.user}" | base64 -d)
            VERIFY=$(kubectl get secret ${SOURCE_SECRET} -n ${NAMESPACE} -o jsonpath="{.data.verify}" | base64 -d)
            #The job will retry if the secret doesn't exist, sops secrets aren't created instantly in some cases
            if [ -z "$PASSWORD" ] && [ -z "$USER" ]; then
              echo "Failed to retrieve secret."
              exit 1
            else
              echo "Password: ***************"
              echo "User: $USER"
              echo "Verify: $VERIFY"
              kubectl patch secret ${TARGET_SECRET} -n ${NAMESPACE} -p "{\"stringData\":{\"user\":\"$(echo -n $USER)\",\"password\":\"$(echo -n $PASSWORD)\",\"verifier\":\"$(echo -n $VERIFY)\"}}"
            fi
      restartPolicy: Never

And remember to create the Service Account

apiVersion: v1
kind: ServiceAccount
metadata:
  name: secret-manager-sa

---

apiVersion: rbac.authorization.k8s.io/v1
kind: Role
metadata:
  name: secret-access-role
rules:
- apiGroups: [""]
  resources: ["secrets"]
  verbs: ["get", "patch", "list"]

---

apiVersion: rbac.authorization.k8s.io/v1
kind: RoleBinding
metadata:
  name: secret-access-binding
subjects:
- kind: ServiceAccount
  name: secret-manager-sa 
roleRef:
  kind: Role
  name: secret-access-role
  apiGroup: rbac.authorization.k8s.io

[Alansyf]

+1, all live postgresql hard to asking for a simple password change. (this require huge efforts to ask all application team to change their code and redeploy). All existing secrets are stored as k8s secret, thus a secretRef is the best thing we are looking for.

[gricey432]

I think this might actually be available in 5.6, relevant section in the docs: https://access.crunchydata.com/documentation/postgres-operator/5.6/tutorials/basic-setup/user-management#creating-a-new-user-declaratively

I haven't tried it out yet myself so I can't comment on the behaviour, especially around things like what happens if you modify the password after cluster creation.

[koshrf]

I think this might actually be available in 5.6, relevant section in the docs: https://access.crunchydata.com/documentation/postgres-operator/5.6/tutorials/basic-setup/user-management#creating-a-new-user-declaratively

I haven't tried it out yet myself so I can't comment on the behaviour, especially around things like what happens if you modify the password after cluster creation.

I hope this isn't the solution because it doesn't solve the issue. That just create an user with no privileges and without access to the database created on the cluster, it is pretty much worthless since it will require another job to set permissions right.

I don't think anyone asked to create users with a secret, what was asked was to use secrets to manipulate the current users on the PostgresCluster manifest. The PostgresCluster can already create users, don't know why would anyone want to create an user with no permission and outside the original PG cluster manifest, it feels anti-pattern.

Edit: I might be wrong tho, if the secret can update the password of the admin user already created then the wording on the documentation is confusing.

[benjaminjb]

Hello, sorry for some of the confusion around this issue and I have revised some of the docs to make it clearer what you can do with this new feature in PGO v5.6.

The new feature is that the operator now has a deterministic protocol for using preexisting Kubernetes Secrets to set passwords for users. I'll expand on why I stress "deterministic protocol" in a moment, but information and instructions for creating a secret and using it for a new user can be found here.

But here's the short version: You can create a secret with a password and as long as that secret is named and labeled so that PGO can find it, you can add a user to Postgres with a password that you set yourself before that user even exists.

There was some confusion in the docs, so just to reiterate: once you create a secret, you can then create the user through the PostgresCluster's spec.users field, just like you used to be able to and everything should act the same.

deterministic protocol?

Previously, if you created a user through a PostgresCluster spec, the operator would create a secret with a password for that user, and it would do so by searching for / creating a secret with labels indicating what cluster and what user this secret was for.

What would the operator do if it found a secret or multiple secrets that matched those labels? It would list them all (in a non-deterministic order) and pull the first secret off the list (if I remember correctly).

So now, because the listing is not random, we can safely say which password is being used, even if there are multiple secrets with the same labels.

Setting a custom password

And to clarify, PGO has the ability to set a password for an existing user. In that case, what you need to do is update the Secret that PGO created as described here.

Some current gotchas

Right now, if you create a secret with the name other-name and the correct labels for a cluster/user, PGO will grab that secret and use the password you set in that secret. BUT PGO will create its own secret with the usual name pattern (<clusterName>-pguser-<userName>), and from then on, PGO will look at that secret for this user's password. So if you change the password in other-name, the password won't be updated unless you delete the <clusterName>-pguser-<userName> secret. (Or change the password in that <clusterName>-pguser-<userName> secret.)

But if you create a secret with the name-pattern <clusterName>-pguser-<userName>, then PGO will set the cluster as the owner, which means if you delete the cluster, the secret will be deleted.

Summary

As of PGO v5.6, there is a way to pre-create a password by creating / reusing a secret that has the correct cluster/user labels. You can do this before the user is created.

(Once the user is created, as the original poster said, you can use the patch mechanism to customize the Secret to change the password.)

I'm going to leave this issue open a few days for feedback, but I hope this feature meets your needs and makes it easier to use PGO in the future.

[cloudcafetech]

Confluence does not accepting/recognize grater than less than " > < " sign in password.

  patroni:
    dynamicConfiguration:
      postgresql:
        pg_hba:
          - host all all 0.0.0.0/0 md5

any suggestion ??

[gricey432]

Confluence does not accepting/recognize grater than less than " > < " sign in password.

  patroni:
    dynamicConfiguration:
      postgresql:
        pg_hba:
          - host all all 0.0.0.0/0 md5

any suggestion ??

I'd make a new ticket for your question, it's unrelated to this closed issue