`alter_object_ownership`: `permission denied to reassign objects` due to concurrency

[egorshulga]

I am trying to analyze the code that handles ownership reassignment (here).

1. It locks the role currentUser.
2. It grants currentOwner to currentUser.
3. It invokes REASSIGN OWNED BY currentOwner TO newOwner.
4. Afterward, it unlocks the role currentUser.
5. Finally, it revokes currentOwner (which is actually the previousOwner) from currentUser. This is performed as deferred invocation (!).

I assume there is a problem in the order of 4 and 5: we can't revoke the role outside the locking scope.

I think this trouble causes errors for me. I try to change the owner of 6 databases, and I run terraform apply with the default level of parallelization. I can see that for some DBs it worked, but for some others it returns the errors:

╷
│ Error: Error reassigning objects owned by '<previousOwner>': pq: permission denied to reassign objects
│
│   with module.terraformPostgres.postgresql_database.database["db3"],
│   on .databases.tf line 13, in resource "postgresql_database" "database":
│   13: resource "postgresql_database" "database" {
│
╵
╷
│ Error: Error reassigning objects owned by '<previousOwner>': pq: permission denied to reassign objects
│
│   with module.terraformPostgres.postgresql_database.database["db5"],
│   on databases.tf line 13, in resource "postgresql_database" "database":
│   13: resource "postgresql_database" "database" {
│
╵
╷
│ Error: Error reassigning objects owned by '<previousOwner>': pq: permission denied to reassign objects
│
│   with module.terraformPostgres.postgresql_database.database["db6"],
│   on databases.tf line 13, in resource "postgresql_database" "database":
│   13: resource "postgresql_database" "database" {
│
╵

Could you please confirm my findings or correct me if I am wrong?

[hashimyousaf111]

I am facing the similar problem while setting same permissions in loop.

│ Error: could not execute revoke query: pq: tuple concurrently updated
│ 
│   with postgresql_grant.this["1"],
│   on main.tf line 51, in resource "postgresql_grant" "this":
│   51: resource "postgresql_grant" "this" {