The user of the postgresql provider can grant the "cloudsqlsuperuser" role for other users, but not itself

[modulitos]

Terraform Version

❯ terraform -v Terraform v0.14.5

• provider registry.terraform.io/cloudflare/cloudflare v2.27.0
• provider registry.terraform.io/cyrilgdn/postgresql v1.14.0
• provider registry.terraform.io/gitlabhq/gitlab v3.7.0
• provider registry.terraform.io/hashicorp/aws v3.64.2
• provider registry.terraform.io/hashicorp/google v3.90.1
• provider registry.terraform.io/hashicorp/helm v0.10.6
• provider registry.terraform.io/hashicorp/kubernetes v1.13.4
• provider registry.terraform.io/hashicorp/random v3.1.0
• provider registry.terraform.io/hashicorp/template v2.2.0
• provider registry.terraform.io/vancluever/acme v2.4.0

Your version of Terraform is out of date! The latest version is 1.0.11. You can update by downloading from https://www.terraform.io/downloads.html

Affected Resource(s)

Please list the resources as a list, for example:

• postgresql_role

Terraform Configuration Files

provider "postgresql" {
  host      = "my-project:us-central1:my-db-instance"
  alias     = "db"
  scheme    = "gcppostgres"
  username  = "postgres"
  database  = "postgres"
  password  = "postgres_pass"
  superuser = false
}

// Other resources like google_sql_database_instance, google_sql_database, google_sql_user
// have been omitted for brevity

resource "postgresql_role" "foo" {
  name            = "foo"
  create_database = true
  create_role     = true
  login           = true
  roles           = ["cloudsqlsuperuser"]
  password        = "pass"
}

resource "postgresql_role" "postgres" {
  name            = "postgres"
  create_database = true
  create_role     = true
  login           = true
  roles           = ["cloudsqlsuperuser"]
  password        = "pass"
}

Expected Behavior

I'm able to make changes to the foo and postgres roles above, and plan/apply.

Actual Behavior

Applying fails with this error:

Error: could not grant role cloudsqlsuperuser to postgres: pq: must be superuser or cloudsqlsuperuser to grant or revoke role "cloudsqlsuperuser"

  on myfile.tf line 123, in resource "postgresql_role" "postgres":
 123: resource "postgresql_role" "postgres" {

I'm able to work around this by switching the provider block to the foo user:

provider "postgresql" {
  host      = "my-project:us-central1:my-db-instance"
  alias     = "db"
  scheme    = "gcppostgres"
  username  = "foo"
  database  = "postgres"
  password  = "foo_pass"
  superuser = false
}

Then re-planning and applying works. But if I leave the provider configured with the foo user, and I make changes to the postgres_role.foo user, I get the same error:

Error: could not grant role cloudsqlsuperuser to postgres: pq: must be superuser or cloudsqlsuperuser to grant or revoke role "cloudsqlsuperuser"

  on myfile.tf line 123, in resource "postgresql_role" "foo":
 123: resource "postgresql_role" "foo" {

Note how this is reversed, in that I'm unable to apply changes to my foo user now.

It seems like the provider can only apply changes to other users, but not its own user?

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. terraform apply

Important Factoids

I am running on Google CloudSQL.

References

Are there any other GitHub issues (open or closed) or Pull Requests that should be linked here? For example:

• Somewhat related to this issue, but not directly: https://github.com/cyrilgdn/terraform-provider-postgresql/issues/141

[ggramal]

I think we had the same issue and this problem probably happens because of several factors:

1. First of all postgresql provider tries to revoke all roles that are granted to postgresql_role and then grant those specified in roles attribute (which in my opinion is ok). This approach works fine for all roles except cloudsqlsuperuser
2. If you simulate the behaviour in cloudsql environment i.e.

BEGIN;
REVOKE cloudsqlsuperuser from postgres;
GRANT cloudsqlsuperuser to postgres;

you will get the same error ERROR: must be superuser or cloudsqlsuperuser to grant or revoke role "cloudsqlsuperuser". So we assume gcp uses a modified version of postgresql that throws this error.

So as a workaround we are creating a user used in postgresql provider configuration with google_sql_user and use postgresql_grant_role to grant him needed roles excluding clousqlsuperuser because its granted by default when you create cloudsql user via google_sql_user.

Something like this

locals {
  gcp_provider = {
    project      = "someproject"
    region       = "us-west1"
    zone         = "us-west1-c"
  }
  postgres_roles = [
    "role_a",
    "role_b",
    "role_c",
  ]
}

provider "google" {
  project = local.gcp_provider.project
  region  = local.gcp_provider.region
  zone    = local.gcp_provider.zone
}

provider "postgresql" {
  host      = "${local.gcp_provider.project}:${local.gcp_provider.region}:${google_sql_database_instance.instance.name
}"
  alias     = "db"
  scheme    = "gcppostgres"
  username  = "postgres"
  database  = "postgres"
  password = random_string.password.result
  superuser = false
}

terraform {
  required_providers {
    google = {
      source = "hashicorp/google"
    }
    random = {
      source = "hashicorp/random"
    }
    postgresql = {
      source = "cyrilgdn/postgresql"
    }
  }
  required_version = ">= 1.0.9, <1.1.0"

  backend "local" {
    path = "./123"
  }
}


resource "google_sql_database_instance" "instance" {
  name             = "someinstance"
  database_version = "POSTGRES_9_6"

  settings {
    tier = "db-f1-micro"
  }
}

resource "google_sql_user" "superuser" {
  name     = "postgres"
  instance = google_sql_database_instance.instance.name
  password = random_string.password.result
}

resource "random_string" "password" {
  length  = 32
  special = false
}

resource "postgresql_grant_role" "postgres_roles" {
  for_each = toset(local.postgres_roles)
  role              = google_sql_user.superuser.name
  grant_role        = each.value
  with_admin_option = true
}