postgresql_default_privileges schema provider error when assigning privileges

[fmarino-412]

Hi there,

We're having issues when assigning default privileges on schema to a specific role. The provider returns an error related to a Root object that was present and now absent. Nothing changed between plan and apply operations.

Below all the details:

Terraform Version

Terraform v1.6.1

Affected Resource(s)

Please list the resources as a list, for example:

• postgresql_default_privileges (schema)

Expected Behavior

resource "postgresql_default_privileges" "schema" {

  database    = "custom-database-name"
  role        = "custom-database-role"
  object_type = "schema"

  owner      = "custom-database-role"
  privileges = ["USAGE", "CREATE"]
}

Should grant "custom-database-role" with privileges to use and create schemas on the specified database.

Actual Behavior

This is the output that we are getting back from the previous resource creation:

│ Error: Provider produced inconsistent result after apply
│ 
│ When applying changes to
│ postgresql_default_privileges.schema,
│ provider "provider[\"registry.terraform.io/cyrilgdn/postgresql\"]" produced
│ an unexpected new value: Root object was present, but now absent.
│ 
│ This is a bug in the provider, which should be reported in the provider's
│ own issue tracker.

Steps to Reproduce

terraform apply

Important Factoids

This is running on AWS Aurora with 15.5 as engine version. At steady state we expect to have a custom schema whose owner will be "custom-database-role". The creation of the schema happens after the creation of the role and the assignmenet of deafult permissions.

Thanks in advance for your help/support :)

[nekater2]

Hello,

yesterday we encountered the same issue for table and sequence default privileges in our databases (Aurora PostgreSQL 14.6 and Aurora PostgreSQL 15.5). After doing some debugging here is what we found out:

In the database logs we could see that the provider correctly executes the ALTER DEFAULT PRIVILEGES SQL statement originating from the grantRoleDefaultPrivileges function:

terraform-provider-postgresql/postgresql/resource_postgresql_default_privileges.go

Lines 298 to 304 in f46ec22

	query := fmt.Sprintf("ALTER DEFAULT PRIVILEGES FOR ROLE %s %s GRANT %s ON %sS TO %s",
	pq.QuoteIdentifier(d.Get("owner").(string)),
	inSchema,
	strings.Join(privileges, ","),
	strings.ToUpper(d.Get("object_type").(string)),
	pq.QuoteIdentifier(role),
	)

After executing the ALTER DEFAULT PRIVILEGES SQL statement, the function readRoleDefaultPrivileges is called to check if the planned new state matches the actual new state. And here is the issue:

To check the provisioned default privileges the readRoleDefaultPrivileges function executes the following SQL statement:

terraform-provider-postgresql/postgresql/resource_postgresql_default_privileges.go

Lines 231 to 248 in f46ec22

	if pgSchema != "" {
	query = `SELECT array_agg(prtype) FROM (
	SELECT defaclnamespace, (aclexplode(defaclacl)).* FROM pg_default_acl
	WHERE defaclobjtype = $3
	) AS t (namespace, grantor_oid, grantee_oid, prtype, grantable)
	JOIN pg_namespace ON pg_namespace.oid = namespace
	WHERE grantee_oid = $1 AND nspname = $2 AND pg_get_userbyid(grantor_oid) = $4;
	`
	queryArgs = []interface{}{roleOID, pgSchema, objectTypes[objectType], owner}
	} else {
	query = `SELECT array_agg(prtype) FROM (
	SELECT defaclnamespace, (aclexplode(defaclacl)).* FROM pg_default_acl
	WHERE defaclobjtype = $2
	) AS t (namespace, grantor_oid, grantee_oid, prtype, grantable)
	WHERE grantee_oid = $1 AND namespace = 0 AND pg_get_userbyid(grantor_oid) = $3;
	`
	queryArgs = []interface{}{roleOID, objectTypes[objectType], owner}
	}

BUT, the SELECT to pg_default_acl does not return any rows if you only grant the standard set of privileges for a given object type to the database owner. For schemas the standard set is USAGE and CREATE which can be seen here: postgreSQL docs. In your example "custom-database-role" is probably the owner of "custom-database-name" which is why the SELECT mentioned in the code would not return any rows if you grant the standard set (USAGE and CREATE) to it.

Because the SELECT is not returning any rows, terraform thinks that the actual new state differs from the planned new state. This results in the inconsistency mentioned in the supplied error message:
Provider produced inconsistent result after apply. Specifically Root object was present, but now absent. Because terraform expects the function readRoleDefaultPrivileges to return the applied state, which it doesn't when applying the standard set.

But if you would provision default privileges that differ from the standard set (e.g. only USAGE for schemas) to the owner OR provision default privileges to any other role which is not the owner, it will work, as the SELECT correctly returns the expected privileges.

Probably the correct way would be to fix this inside the provider (e.g. updating the readRoleDefaultPrivileges logic so it assumes the standard set if no rows are returned).

However a workaround could be, to only provision the resource if the privileges differ from the standard set of the object type.

Maybe as an additional note: As we are also using Aurora PostgreSQL it's probably worth to check if for native postgreSQL the behaviour is different.

[valorl]

Maybe as an additional note: As we are also using Aurora PostgreSQL it's probably worth to check if for native postgreSQL the behaviour is different.

I can confirm the same behavior on a standard postgres Cloud SQL instance in GCP

[lmaos-code]

Maybe as an additional note: As we are also using Aurora PostgreSQL it's probably worth to check if for native postgreSQL the behaviour is different.

I can also confirm this behavior on a standard Azure-PSQL-Flexible Server.