Azure: `postgresql_grant` fails because it attempts to revoke `azure_pg_admin` role from session user

[jtv8]

Problem

Under certain circumstances (in this case, using an Azure managed identity that has does not have the azure_pg_admin role), when a postgresql_grant resource is created it will attempt to revoke an existing permanent role from the session user (which it should not do) and will be denied, with the following error:

  # postgresql_grant.example will be created
  + resource "postgresql_grant" "example" {
      + database          = "exampledb"
      + id                = (known after apply)
      + object_type       = "schema"
      + privileges        = [
          + "CREATE",
          + "USAGE",
        ]
      + role              = "examplerole"
      + schema            = "public"
      + with_grant_option = false
    }
Plan: 1 to add, 0 to change, 0 to destroy.
postgresql_grant.example: Creating...
╷
│ Error: error revoking role azure_pg_admin from example-managed-identity: pq: Cannot revoke azure_pg_admin from session user
│ 
│   with postgresql_grant.example,
│   on modules/postgres-database/main.tf line 27, in resource "postgresql_grant" "example":
│   27: resource "postgresql_grant" "example" {
│ 
╵

Replication

Provider configuration

provider "postgresql" {
  host                = data.azurerm_postgresql_flexible_server.example.fqdn
  port                = 5432
  database            = "postgres"
  username            = "example-managed-identity"
  sslmode             = "require"
  superuser           = false
  azure_identity_auth = true
  azure_tenant_id     = data.azurerm_client_config.current.tenant_id
}

Resource configuration

resource "postgresql_grant" "example" {
  database    = "exampledb"
  role        = "examplerole"
  schema      = "public"
  object_type = "schema"
  privileges = [
    "CREATE",
    "USAGE"
  ]
}

Analysis

The following appears to be the relevant code path:

terraform-provider-postgresql/postgresql/resource_postgresql_grant.go

Line 139 in 450ce85

func resourcePostgreSQLGrantCreateOrUpdate(db *DBConnection, d *schema.ResourceData, usePrevious bool) error {

terraform-provider-postgresql/postgresql/resource_postgresql_grant.go

Line 194 in 450ce85

if err := withRolesGranted(txn, owners, func() error {

terraform-provider-postgresql/postgresql/helpers.go

Line 142 in 450ce85

func withRolesGranted(txn *sql.Tx, roles []string, fn func() error) error {

terraform-provider-postgresql/postgresql/helpers.go

Line 218 in 450ce85

if _, err := revokeRoleMembership(txn, role, currentUser); err != nil {

terraform-provider-postgresql/postgresql/helpers.go

Line 117 in 450ce85

func revokeRoleMembership(db QueryAble, role, member string) (bool, error) {

terraform-provider-postgresql/postgresql/helpers.go

Line 134 in 450ce85

return false, fmt.Errorf("error revoking role %s from %s: %w", role, member, err)

For some reason, the function withRolesGranted seems to believe that it granted the role azure_pg_admin to the session user (even though it did not need to) and then attempts to revoke it again. This could be due to the function isMemberOfRole reporting a false negative, or some other more subtle cause.

Presumably, the function withRolesGranted attempts to grant the role azure_pg_admin to the session user (and believed it is successful) and then tries and fails to revoke it again. I am attempting to get some more detailed database logs to investigate what exactly is happening.