Add postgres user credential reset to terraform provider

[mardonner]

Problem description

I recently noticed that the portal allows to reset the credentials of users on my PostgreSQL Flex instances. Probably via this API call. It would be awesome to have this feature in the terraform provider. I'm not sure if this is possible however.

Proposed solution

In the case of my Secrets Manager, there is no password reset. I rotate the credentials by deleting and recreating the user on it. This works fine for me. My pipeline deals with passing those new credentials to my application.

Rough example:

resource "null_resource" "credential_rotation_trigger" {
  triggers = {
    rotate = var.rotate_credentials ? timestamp() : ""
  }
}
resource "stackit_secretsmanager_instance" "sm_cluster" {
  count      = var.sm_enabled ? 1 : 0
  project_id = var.stackit_project_id
  name = local.sm_cluster_name
  acls = local.acls_sm
}

resource "stackit_secretsmanager_user" "sm_cluster_user_ro" {
  count         = var.sm_enabled ? 1 : 0
  project_id    = var.stackit_project_id
  instance_id   = stackit_secretsmanager_instance.sm_cluster[0].instance_id
  description   = local.sm_cluster_ro_user_description
  write_enabled = false
  lifecycle {
    create_before_destroy = true
    replace_triggered_by  = [null_resource.credential_rotation_trigger]
  }
}

var.rotate_credentials is an input parameter in my pipeline dialogue so it is a one off task and don't have to set it in my .tfvars file and then remember to change it back.

For Postgres users this is another story. I can't use this mechanism for multiple reasons.

1. Deleting the user is not possible if other objects on the database (outside of the terraform provider) depend on it. (e.g. schemas, etc.). Trying to do so results in a 500 error response from the api.
2. Even if this is not the case, since user and database (not instance) resource are dependent on each other, when adopting the mechanism above, I'd also need to delete the database resource in order to create a new user with new credentials. I can't just drop my database.
3. I don't know if its possible for the user to change the password on the instance by themselves via psql command but let's say this is possible. Are the API/Portal aware of this change and does the terraform provider also respond accordingly to that change? I mean regarding state drift.
   Also, while we're at it. I assume, users created with createrole (Remove the role validation for Postgres #1116), outside of the portal/terraform will not appear in the portal, correct?

A clear and concise description of what you want to happen.

Again, I don't know if this is possible and don't know how an implementation would look like, but maybe something like this?

resource "stackit_postgresflex_user" "example" {
  project_id  = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
  instance_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
  username    = "username"
  roles       = ["role"]
  reset_password = var.rotate_credentials
}
variable "rotate_credentials" {
  description = "whether to rotate credentials"
  type        = bool
  default     = false
}

The resource should then not track the attribute reset_password in the state file, so the rotation is always and only triggered if reset_password is true. In the example above, if rotate_credentials is set to true, subsequent plan runs should always report a change on the password and uri attribute.

Alternative solutions (optional)

I'd prefer to do this in terraform instead of manually in the portal. Manual doesn't scale.

Additional information

EDIT: I just noticed that a credential reset in the portal is not recognized by terraform. So if you do that, any resource/provider/output will not be aware that the password changed and can now no longer connect to the db. Just a fact to be aware of.

[marceljk]

Hi @mardonner,

thanks for reporting this issue. We have already a Story in our Backlog, where we want to evaluate, if and how we can introduce a postgres user credentials reset within terraform. Terraform follows in general a declarative approach and we need to check, how we can cover a password reset within it.

I don't know if its possible for the user to change the password on the instance by themselves via psql command but let's say this is possible. Are the API/Portal aware of this change and does the terraform provider also respond accordingly to that change? I mean regarding state drift.

At the moment the password of the user is only sent once by the api. For example during the creation of a new user, the api response contains the password (Create request). But the password can't be read afterwards again (Get request).
So the password can only be read once (for obvious reason) and afterwards it can't be read again. So the portal will not know the password and also terraform will not know the password.

Best regards
Marcel from the STACKIT Developer Tools Team

[mardonner]

@marceljk Ok, nice that you have this already on your list. Hmm, yes the solution is not obvious here. How this provider (more accurately the api) handles these db user resources (or any user resource) is very different from how they work on gcp or azure.

---

I have an idea.
Let's look at the google_sql_user resource. It let's you set the password either via password or password_wo attribute in combination with password_wo_version. On the tf provider, I know you are limited by the API and I don't think users will be able to provide and manage db user passwords by themselves anytime soon, but let's take inspiration from this resource.

Again, not familiar with terraform provider development but what If the stackit_postgresflex_user had an attribute like password_version. Compared to the google resource, where we increase this value to indicate that we want to write a new password with the password_wo write-only attribute, we indicate to the stackit provider by increasing this number to trigger a password reset of the user and update the user resource, specifically the password attribute, with what we get back as a response from the api in our state file.

I've never seen this being done anywhere else before so this might be a bad idea for various reasons.

There is probably a debate about ephemeral resources somewhere to be found in here but I'm not familiar enough with those in order to comment on that and I don't know if they would solve my problem. Probably not.

---

Some additional context how I use the db user pw:
I use the password of the db user, write it into a secrets manager, pass it to ansible and let that configure everything else on the postgres db. I recently migrated away from doing the further db configuration with the cyrilgdn/postgresql terraform provider various reasons (1, 2, 3). Main reason being that creating a dynamic amount of postgres instances, users, provider configurations and database objects is not possible in the way I want to do it. Dynamic provider configuration in combination with submodules doesn't work.

[marceljk]

Thanks for your comment! I'll link this to our ticket.

We already had a similar solution in mind. Look at the service_account_key resource. There we have the attribute rotate_when_change, which is a map. This let users easily define multiple dependencies which should trigger a rotation.
In case of the service_account_key it's a replace of the whole resource. But for the the user resource this could be then just a in-place update.

But so far this is just an idea and we want to check if there are other approach, how to implement a credential reset in terraform.