Unable to use postgresql_default_privileges in Azure Flex Server

[glangho]

Hi there,

Thank you for opening an issue. Please provide the following information:

Terraform Version

v1.5.7

Affected Resource(s)

• postgresql_default_privileges
• postgresql_grant_role

Terraform Configuration Files

resource "postgresql_role" "read_only" {
  name                  = "role_ro"
}

resource "postgresql_role" "owner" {
  name                  = "role_owner"
  login                   = true
  password            = *******
}

resource "postgresql_default_privileges" "read_only_defaults" {
  database             = "my_database"
  role                     = postgresql_role.read_only.name
  schema               = "my_schema"
  owner                 = postgresql_role.owner.name
  object_type        = "table"
  privileges           = ["SELECT"]
}

Expected Behavior

I have an azure entra id setup as an admin on an azure flex postgres server. This id is used as our deployer service principal. I use this to connect through azure_identity_auth in the postgresql provider without problem. When I create role_ro and role_owner as the entra id I expect the read_only_defaults to be successful.

Actual Behavior

Error: could not revoke default privileges: pg: permission denied to change default privileges

Steps to Reproduce

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

1. terraform apply

Important Factoids

I believe this is an issue specific to Azure and using an admin entra id. When the entra id creates the two roles I can see that they are both granted to the entra id with admin_option = true. The grantor is azuresu. Even running an "alter default permissions for role" directly on the database logged in as the same id will fail. I need to explicitly do a grant role of role_owner to the entra id. I do not understand why the grant from azuresu seemingly does nothing.

I have tried using a postgresql_grant_role in the terraform like this:

resource "postgresql_grant_role" "grant_sp" {
  role                             = var.deployer_role # name of the entra id
  grant_role                   = postgresql_role.owner.name

This works however it then creates two entries in pg_auth_members, one where the grantor is azuresu and admin_option true, another with the grantor as the entra id and admin_option false. If I run another apply directly after, the provider tries to recreate the postgresql_grant_role resource but with admin_option = true. It doesn't look like it actually creates anything new and further applies pickup no changes. I guess when doing the state comparison it picks the wrong row up, updates the state, and then is happy.

If I try to create the postgresql_grant_role with admin_option = true I get the following error:
Error: could not execute grant query: pg: ADMIN option cannot be granted back to your own grantor

So far the only solution I can think of to get this working in terraform is to add a lifecycle ignore_changes on with_admin_option so that subsequent applies don't needlessly run. My bigger concern is having multiple of the same grants just with different grantors and different admin_option values. I was hoping maybe someone here had further insight on the Azure side.