add create-or-update role support

[czka]

My use case:

• A user someone is added to a GCP Cloud SQL PostgreSQL instance using sql-db module's additional_users [1] (or sql_user resource [2]).
• Such user is automatically granted CREATEROLE and CREATEDB attributes, as well as the cloudsqlsuperuser role.
• These are quite excessive permissions.
• Trying to revoke these attributes and role using PostgreSQL provider fails as follows:

resource "postgresql_role" "someone" {
  name            = "someone"
  login           = true
  create_database = false
  create_role     = false
  roles           = []
}

Error: error creating role someone: pq: role "someone" already exists

It would be great if the provider just altered someone's attributes and role membership instead. The respective SQL statements are:

ALTER ROLE "someone" NOCREATEROLE;
ALTER ROLE "someone" NOCREATEDB;
REVOKE "cloudsqlsuperuser" FROM "someone";

I could import someone into postgresql_role, then terraform apply again. Thing is this takes a manual intervention and 2 terraform apply runs, while ideally a single, automatable terraform apply should suffice.

To fix this I moved my users setup from sql-db module to PostgreSQL provider altogether - at a cost of having to add few lines of code for random password provisioning and outputs, duplicating the functionality already present in sql-db module:

locals {
  additional_users = toset(var.additional_users)

  keepers = {
    name = var.name
  }
  depends_on = [module.sql-db]
}

resource "random_password" "additional_users_passwords" {
  for_each = local.additional_users
  length   = 32
  special  = false

  keepers = {
    name = var.name
  }
  depends_on = [module.sql-db]
}

resource "postgresql_role" "additional_users" {
  for_each        = local.additional_users
  name            = each.key
  login           = true
  create_database = false # Revokes "Create DB" attribute.
  create_role     = false # Revokes "Create role" attribute.
  roles           = []    # Revokes all roles.
  superuser       = false # Default anyway, unrelated to "cloudsqlsuperuser".
  password        = random_password.additional_users_passwords[each.key].result
  depends_on      = [module.sql-db]
}

output "cloudsql_additional_users" {
  description = "List of maps of additional users and passwords."
  value = [for r in postgresql_role.additional_users :
    {
      name     = r.name
      password = r.password
    }
  ]
  sensitive = true
}

If postgresql_role is not the right place to implement update functionality, maybe do it with a dedicated resource - e.g. postgresql_role_alter?

[1]https://registry.terraform.io/modules/GoogleCloudPlatform/sql-db/google/latest/submodules/postgresql [2]https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/sql_user

[gaurav517]

I am facing similar issues. I am creating CLOUD_IAM_SERVICE_ACCOUNT type user: backstage@my-gcp-project-ops.iam using google_sql_user.. and then granting that user a role: postgres using postgresql_grant_role.

             Role name             |                         Attributes                         |              Member of
-----------------------------------+------------------------------------------------------------+--------------------------------------
 backstage@my-gcp-project-ops.iam  |                                                            | {cloudsqliamserviceaccount,postgres}
 cloudsqladmin                     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 cloudsqlagent                     | Create role, Create DB                                     | {cloudsqlsuperuser}
 cloudsqliamserviceaccount         | Cannot login                                               | {}
 cloudsqliamuser                   | Cannot login                                               | {}
 cloudsqlimportexport              | Create role, Create DB                                     | {cloudsqlsuperuser}
 cloudsqlreplica                   | Replication                                                | {pg_monitor}
 cloudsqlsuperuser                 | Create role, Create DB                                     | {pg_monitor,pg_signal_backend}
 postgres                          | Create role, Create DB                                     | {cloudsqlsuperuser}

I expect backstage@my-gcp-project-ops.iam to inherit all privileges for postgres.. but thats not working.

CREATE DATABASE "backstage_plugin_catalog" - permission denied to create database

I tried creating an intermediate role: create-db-role using postgresql_role (setting create_database and inherit to true) and then granting backstage@my-gcp-project-ops.iam that role (create-db-role) but that didn't work.

As existing role can't be altered using this module, is there a good workaround to solve this problem using terraform? (not very familiar with postgresql)

Thanks.

[gaurav517]

hmm looking at the docs, CREATEDB is never inherited:

The role attributes LOGIN, SUPERUSER, CREATEDB, and CREATEROLE can be thought of as special privileges, but they are never inherited as ordinary privileges on database objects are. You must actually SET ROLE to a specific role having one of these attributes in order to make use of the attribute.

Does that mean Alter is the only option which this provider doesn't support? Any ideas/workaround please? Thanks.

[rowanmoul]

The use-case is nearly identical on Azure Postgres. Roles created through the azurerm provider that authenticate with Azure Active Directory are automatically given CREATEDB, CREATEROLE, and membership in a role called azure_pg_admin that owns things like template0 and template1. I would like to be able to remove these attributes and role membership in terraform. Currently I am using a local-exec provisioner to do this via psql but it would be much better to do it natively in terraform. These roles cannot be created outside of azurerm due to the special authentication setup for active directory.