Vault Postgres revocation statements not working - how to target database?

Vault
1

I’m having some issues with revoking roles from postgres with Vault, I have this set up with terraform:

resource "vault_database_secret_backend_connection" "postgres" {
  backend           = vault_mount.db.path
  name              = "postgres"
  verify_connection = true
  allowed_roles     = ["admin", "authelia", "nextcloud"]
  postgresql {
    connection_url          = "postgres://{{username}}:{{password}}@postgres.service.consul:5432/postgres?sslmode=disable"
    max_open_connections    = 50
    max_idle_connections    = 5
    max_connection_lifetime = 600
  }

  data = {
    username = "${data.vault_generic_secret.postgres.data["vault_role"]}"
    password = "${data.vault_generic_secret.postgres.data["vault_role_password"]}"
  }
}

resource "vault_database_secret_backend_role" "authelia" {
  backend     = vault_mount.db.path
  name        = "authelia"
  db_name     = vault_database_secret_backend_connection.postgres.name
  default_ttl = "3600"
  max_ttl     = "86400"

  creation_statements = [
    "CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';",
    "REASSIGN OWNED BY authelia TO \"{{name}}\";",
  ]

  revocation_statements = [
    "REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM \"{{name}}\";",
    "REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM \"{{name}}\";",
    "REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM \"{{name}}\";",
    "REVOKE ALL PRIVILEGES ON SCHEMA public FROM \"{{name}}\";",
    "ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON SEQUENCES FROM \"{{name}}\";",
    "ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON TABLES FROM \"{{name}}\";",
    "ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM \"{{name}}\";",
    "REVOKE USAGE ON SCHEMA public FROM \"{{name}}\";",
    "REASSIGN OWNED BY \"{{name}}\" TO authelia;",
    "DROP USER \"{{name}}\";",
  ]
}

I think the problem is that the revocation statements are executed in the postgres database, and not in the authelia database where I need them to run. But how can I specify that other than how it’s set in the connection string? Surely the Vault Postgres backend doesn’t need to be defined once for every logical database that I want to use dynamic secrets with?

2

I would recommend doing this by CLI and Vault first, when you get it working then automate it using terraform or import the object into your statefile.

“Some” issue is not a good descriptive if you’re asking for help in solving an issue.