multiple postgresql_grant resources freeze plan

[daviskirk]

After creating multiple instances of a postgresql_grant resource, subsequent plan steps freeze, making further work impossible.

Terraform Version

Terraform v0.11.13
+ provider.postgresql v0.4.0

Affected Resource(s)

Please list the resources as a list, for example:

• postgresql_grant

Terraform Configuration Files

variable "database_name" {
  default = "postgres"
}

variable "test_schemas" {
  description = "User name."
  type = "list"
  default = [
    "test1",
    "test2",
    "test3",
    "test4",
    "test5",
    "test6"
  ]
}

provider "postgresql" {
  host            = "127.0.0.1"
  port            = "5432"
  database        = "${var.database_name}"
  username        = "postgres"
  password        = "postgres"
  sslmode         = "disable"
}

resource "postgresql_role" "test" {
  name = "test"
}

resource "postgresql_schema" "test" {
  count = "${length(var.test_schemas)}"
  name  = "${element(var.test_schemas, count.index)}"

  policy {
    usage = true
    role  = "${postgresql_role.test.name}"
  }
}

resource "postgresql_grant" "test_public_edit_tables" {
  count       = "${postgresql_schema.test.count}"
  database    = "${var.database_name}"
  role        = "${postgresql_role.test.name}"
  schema      = "${element(postgresql_schema.test.*.name, count.index)}"
  object_type = "table"
  privileges  = ["SELECT", "INSERT", "UPDATE", "DELETE"]
}

Debug Output

https://gist.github.com/daviskirk/470d15ac5fb7a6ebd3ad4b19f15367c5

Expected Behavior

Plan should work as expected.

Actual Behavior

Plan freezes at postgresql_grant.test_public_edit_tables[2]: Refreshing state... (ID: test_postgres_test3_table)

Steps to Reproduce

1. terraform init
2. terraform plan (works)
3. terraform apply (works)
4. terraform plan (hangs)

Important Factoids

postgres database in docker container for easy testing (local host postgres/postgres login). Also tested on a locally installed postgres and on a staging grade postgres database in cloud.

Note that if a single postgresql_grant instance is used, this error does not occur (and even using 2 is fine). When using 3 "grants" it might work the first time and start failing after the second "plan"/"apply" sound. Starting at 4 this triggers pretty much every time (for this reason the example uses 6).

The bug can also be reproduced

[pyranja]

I think I see the same bug, but already with just 2 grants and default privileges. The log output did not show any immediate clues. If you can give me some directions I can try to debug more.

resource "postgresql_grant" "read_only_tables" {
  database    = "..."
  schema      = "..."
  role        = "..."
  object_type = "table"
  privileges  = ["SELECT"]
}

resource "postgresql_default_privileges" "read_only_tables" {
  database = "..."
  schema   = "..."
  role     = "..."

  owner       = "..."
  object_type = "table"
  privileges  = ["SELECT"]
}

# grant access to all existing and future sequences
resource "postgresql_grant" "read_only_seqs" {
  database    = "..."
  schema      = "..."
  role        = "..."
  object_type = "sequence"
  privileges  = ["USAGE", "SELECT"]
}

resource "postgresql_default_privileges" "read_only_seqs" {
  database = "..."
  schema   = "..."
  role     = "..."

  owner       = "..."
  object_type = "sequence"
  privileges  = ["USAGE", "SELECT"]
}

[cyrilgdn]

Hi @daviskirk ,

Many thanks for raising this issue. Thanks to your example I easily reproduced this problem. I just discovered that there's actually a connections number limit in the provider which is 4 by default.

Since some versions, we are using more connections than before so creating 4 schemas is using all the connections (currently we open one connection per schema).

I need to check if we can reduce this connections and/or to increase the default value (4 is not a lot)

Meanwhile, this default value can be override in the provider configuration with the max_connections parameter. You can set a higher value or zero for unlimited connections.

[andyyaldoo]

❯ terraform version
Terraform v0.11.14
+ provider.postgresql v0.4.0

Setting max_connections to 0 returns

Error: provider.postgresql: max_connections can not be less than 1

[glerma]

provider.postgresql v1.1.0

I can confirm and reproduce the issue. I had max connections set to default: 4, but had a count iterator in my resource for grants that went above 4, and got the same reported symptoms of the plan or apply "hanging". No error was thrown.

resource postgresql_grant "seq_privs" {
  count       = "${length(var.schemas)}"
  database    = "${var.db}"
  role        = "${var.group_name}"
  schema      = "${element(var.schemas,count.index)}"
  object_type = "sequence"
  privileges  = ["${var.privs_list}"]
  depends_on  = ["postgresql_schema.role_assign"]
}```

Interestingly, in the provider version i used, i got an error when attempting to set connections to unlimted by setting the provider max_connections = 0:
`Error: provider.postgresql: max_connections can not be less than 1`   As the documentation says:  "The default is 4. Zero means unlimited open connections." i'm not sure what equates to unlimited connections.

[AlexanderMann]

PostgreSQL Version: 11.5
Cloud Provider: `AWS`
Number of Schema Resources: 5
Number of Role Resources: 3
Number of Grant Resources: 9

Can confirm over here that the same issue is happening. We just crossed the magic threshold of 4 grants.

In killing the process I get some additional information which may be of use:

2019/11/22 22:18:55 [ERROR] module.my_project: eval: *terraform.EvalRefresh, err: rpc error: code = Unavailable desc = transport is closing
2019/11/22 22:18:55 [ERROR] module.my_project: eval: *terraform.EvalSequence, err: rpc error: code = Unavailable desc = transport is closing
2019/11/22 22:18:55 [TRACE] [walkRefresh] Exiting eval tree: module.my_project.postgresql_grant.readonly_tables__schema_0__user_0
2019/11/22 22:18:55 [TRACE] vertex "module.my_project.postgresql_grant.readonly_tables__schema_0__user_0": visit complete
2019/11/22 22:18:55 [TRACE] vertex "module.my_project.postgresql_grant.readonly_tables__schema_0__user_0": dynamic subgraph encountered errors
2019/11/22 22:18:55 [TRACE] vertex "module.my_project.postgresql_grant.readonly_tables__schema_0__user_0": visit complete
2019/11/22 22:18:55 [ERROR] module.my_project: eval: *terraform.EvalRefresh, err: rpc error: code = Unavailable desc = transport is closing
2019/11/22 22:18:55 [ERROR] module.my_project: eval: *terraform.EvalSequence, err: rpc error: code = Unavailable desc = transport is closing
2019/11/22 22:18:55 [TRACE] [walkRefresh] Exiting eval tree: module.my_project.postgresql_schema.sandbox
2019/11/22 22:18:55 [TRACE] vertex "module.my_project.postgresql_schema.sandbox": visit complete
2019/11/22 22:18:55 [TRACE] vertex "module.my_project.postgresql_schema.sandbox": dynamic subgraph encountered errors
2019/11/22 22:18:55 [TRACE] vertex "module.my_project.postgresql_schema.sandbox": visit complete
2019/11/22 22:18:55 [TRACE] dag/walk: upstream of "module.my_project.postgresql_grant.readonly_tables__sandbox__user_1" errored, so skipping
2019/11/22 22:18:55 [ERROR] module.my_project: eval: *terraform.EvalRefresh, err: rpc error: code = Unavailable desc = transport is closing
2019/11/22 22:18:55 [ERROR] module.my_project: eval: *terraform.EvalSequence, err: rpc error: code = Unavailable desc = transport is closing
2019/11/22 22:18:55 [TRACE] [walkRefresh] Exiting eval tree: module.chordstrateg.postgresql_grant.readonly_tables__schema_1__user_1": visit complete
2019/11/22 22:18:55 [ERROR] module.my_project: eval: *terraform.EvalRefresh, err: rpc error: code = Unavailable desc = transport is closing
2019/11/22 22:18:55 [ERROR] module.my_project: eval: *terraform.EvalSequence, err: rpc error: code = Unavailable desc = transport is closing
2019/11/22 22:18:55 [TRACE] [walkRefresh] Exiting eval tree: module.my_project.postgresql_grant.readonly_tables__tmp__user_1
2019/11/22 22:18:55 [TRACE] vertex "module.my_project.postgresql_grant.readonly_tables__tmp__user_1": visit complete
2019/11/22 22:18:55 [TRACE] vertex "module.my_project.postgresql_grant.readonly_tables__tmp__user_1": dynamic subgraph encountered errors
2019/11/22 22:18:55 [TRACE] vertex "module.my_project.postgresql_grant.readonly_tables__tmp__user_1": visit complete
2019/11/22 22:18:55 [ERROR] module.my_project: eval: *terraform.EvalRefresh, err: rpc error: code = Unavailable desc = transport is closing
2019/11/22 22:18:55 [ERROR] module.my_project: eval: *terraform.EvalSequence, err: rpc error: code = Unavailable desc = transport is closing
2019/11/22 22:18:55 [TRACE] [walkRefresh] Exiting eval tree: module.my_project.postgresql_grant.readonly_tables__schema_1__user_0
2019/11/22 22:18:55 [TRACE] vertex "module.my_project.postgresql_grant.readonly_tables__schema_1__user_0": visit complete
2019/11/22 22:18:55 [TRACE] vertex "module.my_project.postgresql_grant.readonly_tables__schema_1__user_0": dynamic subgraph encountered errors
2019/11/22 22:18:55 [TRACE] vertex "module.my_project.postgresql_grant.readonly_tables__schema_1__user_0": visit complete
2019/11/22 22:18:55 [ERROR] module.my_project: eval: *terraform.EvalRefresh, err: rpc error: code = Unavailable desc = transport is closing
2019/11/22 22:18:55 [ERROR] module.my_project: eval: *terraform.EvalSequence, err: rpc error: code = Unavailable desc = transport is closing
2019/11/22 22:18:55 [TRACE] [walkRefresh] Exiting eval tree: module.my_project.postgresql_grant.readonly_tables__tmp__user_0
2019/11/22 22:18:55 [TRACE] vertex "module.my_project.postgresql_grant.readonly_tables__tmp__user_0": visit complete
2019/11/22 22:18:55 [TRACE] vertex "module.my_project.postgresql_grant.readonly_tables__tmp__user_0": dynamic subgraph encountered errors
2019/11/22 22:18:55 [TRACE] vertex "module.my_project.postgresql_grant.readonly_tables__tmp__user_0": visit complete
2019/11/22 22:18:55 [ERROR] module.my_project: eval: *terraform.EvalRefresh, err: rpc error: code = Unavailable desc = transport is closing
2019/11/22 22:18:55 [ERROR] module.my_project: eval: *terraform.EvalSequence, err: rpc error: code = Unavailable desc = transport is closing
2019/11/22 22:18:55 [TRACE] [walkRefresh] Exiting eval tree: module.my_project.postgresql_grant.readonly_tables__app__user_0
2019/11/22 22:18:55 [TRACE] vertex "module.my_project.postgresql_grant.readonly_tables__app__user_0": visit complete
2019/11/22 22:18:55 [TRACE] vertex "module.my_project.postgresql_grant.readonly_tables__app__user_0": dynamic subgraph encountered errors
2019/11/22 22:18:55 [TRACE] vertex "module.my_project.postgresql_grant.readonly_tables__app__user_0": visit complete
2019/11/22 22:18:55 [TRACE] dag/walk: upstream of "module.my_project.provider.postgresql.bog (close)" errored, so skipping
2019/11/22 22:18:55 [TRACE] dag/walk: upstream of "root" errored, so skipping

Given the nature of this, I'd expect the issue to lay in here since you'd think that the plan is doing the Read only. At the moment nothing is jumping out at me as to why >4 grants causes this silly thing to hang.

[AlexanderMann]

To whomever stumbles in here: A temporary workaround

This bug can be temporarily sidestepped by increasing the number of max_connections to the number of postgresql_grant resources you have.

In my case, I had 9, so my max_connections in the provider ONLY WORKED once set to at least 9.

[der-eismann]

Another workaround we found is using the terraform plan -parallelism=3 option if you want to keep your connections low.

[schmurfy]

I think I got hit by this or some variants, I added two postgresql_grant in my plan, when I apply it I have an infinite loop, I commented one it worked, I uncommented it, it goes back into hanging...

I tried parallelism=3 and also increased the max_connectionson my postgresql provider but it did not help, I have no idea how to fix this and already lost far too much time on it.