Role cannot be dropped because some objects depend on it, even after the provider issued REASSIGN and DROP OWNED BY

[adaptivegarage]

Hi there,

Terraform Version

Terraform v1.1.8 on linux_amd64

• provider registry.terraform.io/cyrilgdn/postgresql v1.15.0

Affected Resource(s)

• postgresql_role

Terraform Configuration Files

resource "postgresql_role" "gaas_oper" {
  name        = "gaas_oper"
  search_path = [postgresql_schema.gaas.name]
  skip_reassign_owned = false
  skip_drop_role = false
}

Debug Output

Terraform debug output is not needed here. I am attaching PostgreSQL (14.2.0) statements log instead.

2022-04-18 21:26:19.607 GMT [9571] LOG:  statement: SELECT VERSION()
2022-04-18 21:26:19.922 GMT [9572] LOG:  statement: BEGIN READ WRITE
2022-04-18 21:26:20.009 GMT [9572] LOG:  statement: SET statement_timeout = 0
2022-04-18 21:26:20.097 GMT [9572] LOG:  execute <unnamed>: SELECT pg_advisory_xact_lock(oid::bigint) FROM pg_roles WHERE rolname = $1
2022-04-18 21:26:20.097 GMT [9572] DETAIL:  parameters: $1 = 'gaas_oper'
2022-04-18 21:26:20.228 GMT [9572] LOG:  execute <unnamed>: SELECT pg_advisory_xact_lock(member::bigint) FROM pg_auth_members JOIN pg_roles ON roleid = pg_roles.oid WHERE rolname = $1
2022-04-18 21:26:20.228 GMT [9572] DETAIL:  parameters: $1 = 'gaas_oper'
2022-04-18 21:26:20.273 GMT [9572] LOG:  statement: SELECT CURRENT_USER
2022-04-18 21:26:20.363 GMT [9572] LOG:  execute <unnamed>: SELECT rolsuper FROM pg_roles WHERE rolname = $1
2022-04-18 21:26:20.363 GMT [9572] DETAIL:  parameters: $1 = 'postgres'
2022-04-18 21:26:20.406 GMT [9572] LOG:  statement: REASSIGN OWNED BY "gaas_oper" TO "postgres"
2022-04-18 21:26:20.449 GMT [9572] LOG:  statement: DROP OWNED BY "gaas_oper"
2022-04-18 21:26:20.497 GMT [9572] LOG:  statement: DROP ROLE "gaas_oper"
2022-04-18 21:26:20.497 GMT [9572] ERROR:  role "gaas_oper" cannot be dropped because some objects depend on it
2022-04-18 21:26:20.497 GMT [9572] DETAIL:  204 objects in database gaas
2022-04-18 21:26:20.497 GMT [9572] STATEMENT:  DROP ROLE "gaas_oper"
2022-04-18 21:26:20.537 GMT [9572] LOG:  statement: ROLLBACK

Expected Behavior

The postgresql_role.gaas_oper should have been dropped.

Actual Behavior

As you can see from the PostgreSQL (14.2.0) log, the provider (correctly) tries to REASSIGN and DROP OWNED before DROP ROLE. Nevertheless the SQL server complains: ERROR: role "gaas_oper" cannot be dropped because some objects depend on it.

When I manually log to the SQL server, connect to gaas database, issue DROP OWNED BY "gaas_oper" and run terraform again, everything works well. This is how the statements log looks like then:

2022-04-18 21:30:48.565 GMT [10066] LOG:  statement: REASSIGN OWNED BY "gaas_oper" TO "postgres"
2022-04-18 21:30:48.649 GMT [10066] LOG:  statement: DROP OWNED BY "gaas_oper"
2022-04-18 21:30:48.689 GMT [10066] LOG:  statement: DROP ROLE "gaas_oper"
2022-04-18 21:30:48.775 GMT [10066] LOG:  statement: COMMIT

I have two ideas:

1. The REASSIGN OWNED BY and DROP OWNED BY commands need to be commited prior to dropping the role.
2. Or (more likely) the REASSIGN OWNED BY and DROP OWNED BY are not issued on the gaas database. If true, I don't have any idea, how to make terraform to connect to the database before issuing the commands.

[patst]

We have the same issue. One observation to add which could point to your second idea:

if we configure the provider in terraform with the attribute database="<db-name>" the role deletion works. But then the deletion of the database fails with Error: Error blocking connections to database: pq: cannot disallow connections for current database

[patst]

I'm trying to figure out the reason why this is happening. For debugging I created this minimal example: https://gist.github.com/patst/9e0984b3db15abd7dfb3969955608c30

Just start a postgres database locally with docker run --name postgres -d -p 5432:5432 -e POSTGRES_PASSWORD=asdf123! -e POSTGRES_USER=pgadmin postgres:11

Then run terraform apply (an table is created in the db) and after that run terraform destroy (this step will fail)

[cyrilgdn]

Hi @adaptivegarage ,

Or (more likely) the REASSIGN OWNED BY and DROP OWNED BY are not issued on the gaas database. If true, I don't have any idea, how to make terraform to connect to the database before issuing the commands.

It's clearly because of that but I don't think we can do anything.

The reassign/drop by setting were created before the provider was able to create databases, so everything were executed on a single database (the one defined in the provider configuration).

Now it's still executed on the database defined in the provider, so not in your gaas database. The provider cannot assume it has/can(maybe it's not allow to) to connect on other databases to drop/reassign objects for this role.

[cyrilgdn]

But if this role is specific to this database, you could defined a specific provider (with an alias) bound to this database and use it to defined the postgresql_role resource. Then during destroy it will properly reassign/drop its objects.

provider "postgresql" {
  alias = "gaas"
  
  [...]
  database = "gaas"
}

resource "postgresql_role" "gaas_oper" {
  provider = postgresql.gaas

  name        = "gaas_oper"
  search_path = [postgresql_schema.gaas.name]
  skip_reassign_owned = false
  skip_drop_role = false
}

[adaptivegarage]

Hi @adaptivegarage ,

Or (more likely) the REASSIGN OWNED BY and DROP OWNED BY are not issued on the gaas database. If true, I don't have any idea, how to make terraform to connect to the database before issuing the commands.

It's clearly because of that but I don't think we can do anything.

The reassign/drop by setting were created before the provider was able to create databases, so everything were executed on a single database (the one defined in the provider configuration).

Now it's still executed on the database defined in the provider, so not in your gaas database. The provider cannot assume it has/can(maybe it's not allow to) to connect on other databases to drop/reassign objects for this role.

Hi @cyrilgdn (and @patst ), thanks for getting back to me. And yes, that's what I realised soon after posting this issue. I have rewritten the Terraform config since then (introduced aliased provider for every database being created) and it works now (I'm having a hard time accepting how verbose and redundant it is now, but I understand that it can't be done any other way, because if I'm not mistaken, the database can only be selected once in the case of Postrges, when connecting. Present version of Terraform doesn't support for_each in the provider block.) If you don't mind, I'll leave this issue open, because it's possible that similar problems to those mentioned by @patst have started to appear instead. Once I check it out, I'll either close or create a new issue, ok?

[cyrilgdn]

Once I check it out, I'll either close or create a new issue, ok?

To be honest, I prefer to close it and let you open it back or open a new one if you have any troubles. I can let it open a couple of days though in case you find it quickly.

[evtimokn]

Hi @cyrilgdn , I have the same problem as described above. After reading the comments I undestand why. Anyway, how do I solve this using the approach you mentionied? I am initialising the provider with an user from a service credential and then I am using the postgresql_role resource to create a specific user. When I go for destroy, tf will initialise the provider in the same way, so I am not able to do something like "On destroy initialise the provider with another user". Any ideas how to solve this chicken-egg problem?

[hassinerhouma]

Hi everyone,

What if we're creating a database and a user in the same configuration?

Is there a way to achieve this using the provider currently (especially the terraform destroy step) @cyrilgdn?

Terraform does not support "provider dependencies", and in the solution you mention @cyrilgdn, one provider declaration would be depending on a resource created in the same configuration.

I would appreciate any leads on this! Moving the database and the user creation into different layers would be a great inconvenience to us :(.