I’m having an issue where Vault or Nomad doesn’t seem to update a vault role lease properly for my postgres db. I have a role set up for vaultwarden there, and it works fine for about a day, then it seems to lose permissions and I get a database permissions error in the logs when I try to log into the vaultwarden web ui.
According to the vaultwarden docs the db user should have these privileges:
CREATE USER vaultwarden WITH ENCRYPTED PASSWORD 'yourpassword';
GRANT all privileges ON database vaultwarden TO vaultwarden;
The vault role is set up like this with Terraform (each.value will be vaultwarden for this role). I use these same statements for other services like huginn without issues:
resource "vault_database_secret_backend_role" "role" {
for_each = local.postgres_services
backend = vault_mount.postgres.path
name = each.value
db_name = each.value
default_ttl = "3600"
max_ttl = "86400"
creation_statements = [
"CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';",
"GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO \"{{name}}\";",
"GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO \"{{name}}\";",
"GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO \"{{name}}\";",
"REASSIGN OWNED BY ${each.value} TO \"{{name}}\";",
]
renew_statements = [
"ALTER ROLE \"{{name}}\" VALID UNTIL '{{expiration}}';",
]
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 ${each.value};",
"DROP USER \"{{name}}\";",
]
}
in the nomad job I just use my vault policy to give the task access to the postgres engine and then I have this templated for the relevant environment variable:
{{ with secret "postgres/creds/vaultwarden" -}}
DATABASE_URL='postgresql://{{ .Data.username }}:{{ .Data.password }}@postgres.service.consul:5432/vaultwarden?application_name=vaultwarden')
{{- end }
The max_ttl for the vault role is 24h as you can see, so I’m guessing that once it runs out the issue appears, but I’m not sure why Nomad isn’t just renewing the lease and keep running.
The template above is also set to change_mode = restart
So what reason could there be for the database access to initially work for 24h when I start the vaultwarden service, and then after about a day it loses permissions? If I stop the Nomad job and start it up again the service works for another day before it fails again.