database_secret_backend_role does not support command arrays

[mrburrito]

Terraform Version

v0.11.11

Affected Resource(s)

Please list the resources as a list, for example:

• vault_database_secret_backend_role

Terraform Configuration Files

Desired

resource "vault_database_secret_backend_role" "myrole" {
  backend = "postgres"
  name = "myrole"
  db_name = "mydatabase"
  default_ttl = "3600"
  max_ttl = "3600"
  creation_statements = [
      "RESET ROLE;",
      "GRANT \"myrole\" TO \"rds_master_user\";",
      "CREATE OR REPLACE FUNCTION myfunc AS $$$$ BEGIN ... END; $$$$ LANGUAGE pgplsql;",
      "..."
  ]
}

Currently Required Workaround

locals {
  create_statements = [
      "RESET ROLE;",
      "GRANT \"myrole\" TO \"rds_master_user\";",
      "CREATE OR REPLACE FUNCTION myfunc AS $$$$ DECLARE obj object; BEGIN do_something(); do_something_else(); END; $$$$ LANGUAGE pgplsql;",
      "..."
  ]
}

resource "vault_database_secret_backend_role" "myrole" {
  backend = "postgres"
  name = "myrole"
  db_name = "mydatabase"
  default_ttl = "3600"
  max_ttl = "3600"
  creation_statements = "${base64encode(jsonencode(local.create_statements))}"
}

Problem

Vault's database secret backend supports multiple methods of configuring creation_statements, revocation_statements, renew_statements, and rollback_statements including the ability to provide a JSON array. The vault_database_secret_backend_role only supports providing a single string.

When injecting block statements such as function definitions, this becomes a problem because Vault, under the hood, splits the string on ; characters to generate the array of statements. Since block statements (in Postgres at least) are passed as a string constant, the splitter generates an invalid SQL command because the string literal is unterminated.

In the example above, Vault sends the command CREATE OR REPLACE FUNCTION myfunc AS $$ DECLARE obj object; to Postgres, which generates the following error:

» vault read postgres/creds/myrole
Error reading postgres/creds/myrole: Error making API request.

* 1 error occurred:
	* pq: unterminated dollar-quoted string at or near "$$ DECLARE obj object"

After several attempts, I finally found the workaround above where base64 encoding a JSON serialized array of the commands properly configures the command array in the Vault backend. This isn't documented anywhere, especially not in the resource documentation, and seems to be a little bit of a kludgy, unintuitive solution.

Solution

Update the vault_database_secret_backend_role to support either string or list values for the *_statements properties. As indicated by my workaround, the single string provides support for:

• semicolon-separated string
• base64 encoded semicolon-separated string
• base64 encoded serialized JSON array

The list parameter should, behind the scenes, serialize to JSON and provide the array to the database backend. This provides an intuitive, clean way of sending multiple statements, especially those that should not be split on semicolons, to the database backend.

References

• https://github.com/hashicorp/vault/issues/6098

[JasonIamAUnixAdmin]

As I noted in the vault issue linked above, using TF ver 0.12.12 and vault provider 2.6.0 it seems that the base64 encoding is not being recognized by TF and is insisting on it being a list of strings which it then splits on semicolons breaking the loop.

[Legogris]

@mrburrito Did you ever get this working? I need to alter the ownership of tables and no amount of escaping permutations seem to make it work. (Getting unterminated quoted string at or near)

FWIW, trying something like this:

  creation_statements = [
    "CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';",
    <<-EOT
SELECT 'ALTER TABLE "' || schemaname || '"."' || tablename || '" OWNER TO {{name}};'
FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename \gexec;
EOT
,
]

vault 1.5.0, terraform 0.12.29, vault provider v2.11.0. From here: https://stackoverflow.com/questions/1348126/postgresql-modify-owner-on-all-tables-simultaneously-in-postgresql

[mrburrito]

@Legogris we have it working, but we're still on 0.11. This is one of the things preventing us from moving to 0.12! I forget what versions of Vault and the Vault provider we're using but I'm a bit frustrated that this is still an issue 16 months later.

[petems]

I might be misunderstanding the issue (as it does look like a mix of interpolation chalenges with 0.11 vs 0.12 TF), but I think this might not entirely be a TF issue, as it looks like there were issues with decoding multi-line strings for the pg backend in Vault: https://github.com/hashicorp/vault/pull/8512/files

This was fixed in Vault 1.5.0 it looks like, are you able to test it there?

[petems]

EDIT: Ah, the error is on credential creation, let me double check my tests

[petems]

Ok, so yes it appears this isn't a Terraform or provider issue: It was the original fix for multi-line PSQL statements that was fixed in Vault 1.5.0, we just added that to the changelog as well (it was missing before):

• secrets/database: Fix parsing of multi-line PostgreSQL statements [GH-8512]

When using your example statement with Vault 1.4.3 and the Vault provider:

Error reading database/creds/myrole: Error making API request.

URL: GET http://0.0.0.0:8200/v1/database/creds/myrole
Code: 500. Errors:

* 1 error occurred:
	* pq: unterminated dollar-quoted string at or near "$Q$ DECLARE result text"

When using with Vault 1.5.0:

Error reading database/creds/myrole: Error making API request.

URL: GET http://0.0.0.0:8200/v1/database/creds/myrole
Code: 500. Errors:

* 1 error occurred:
	* pq: syntax error at or near "AS"

Presumably the creation statement you gave was an example, if you have the working statement I can double check but I believe if you upgrade Vault to 1.5.0 your issue should be fixed.

Here's my reproduction code: https://github.com/petems/vault-provider-psql-reproduction

[mrburrito]

Thanks @petems! We'll try upgrading Vault and see if the issue is fixed!

[kenske]

Seems like this is still an issue. Were you able to find a workaround @mrburrito @petems ?