Postgres, rotating creds (root as well), and best practice naming

[lingfish]

Hi,

New and happy user of Vault here.  Love it!

I've successfully setup all of the Postgres integration, but I'm confused about a few things (when I say "database", I mean not server, but actual databases within one instance):

1. The naming under database/config/ is arbitrary, I get that, but due to the connection_url having to refer to a database, surely the standard there is one config per Postgres DB, right?  Hence, if I have two databases (a and b), I need to create database/config/a and database/config/b, and appropriate connection_url lines ending with a and b, correct?
2. Similarly, because database/roles/<name> has to refer to db_name, then one can't just create generic "read only", and "readwrite" Vault roles, but ones specific to each database/config/ endpoint, right?
3. Next is the database/creds endpoints, where we build a HCL file; so again, codifying that policy, what would be a logical naming convention?
4. If point 1 is true, and we're also using rotating root (DB) creds, will Vault and apps get confused when that root rotation occurs?  Ie. are they considered separate by Vault across database/config/ endpoints, or as one?

I'm just thoroughly confused as to how to structure and name all this, when I have multiple apps/projects, that all might talk to their own DBs, and obviously use their own creds etc.

[Matthew Tice]

1. Yes. That's correct.

2. Right, the db_name links it to a database configuration.

3. I don't understand what you're asking.  You'll make a GET request against /database/creds/[role]

4. I haven't used rotating root creds yet, sorry.

Here's an example of what we do for all the new databases we create.  Hopefully it helps.

1 We have our secret backend mounted up on 'cloud/pc/postgres'

2 We take the application_name and use that as our base name.

3 We create an admin role and a staff role based on the application_name.

4. We create the database config based on the application_name as well.  We do this all via terraform now but you'll get the idea.

resource "vault_database_secret_backend_connection" "postgres" {
  backend           = "${var.vault_backend}"
  name              = "${var.application_name}"
  verify_connection = false
  allowed_roles     = ["${var.application_name}-admin", "${var.application_name}-staff"]

  postgresql {
    connection_url = "postgres://super_user:${var.password}@${var.database_host}:5432/${var.application_name}?sslmode=disable"
  }
}

resource "vault_database_secret_backend_role" "admin" {
  backend             = "${var.vault_backend}"
  name                = "${var.application_name}-admin"
  db_name             = "${vault_database_secret_backend_connection.postgres.name}"
  creation_statements = ["CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' inherit in role \"${var.application_name}_admin\" VALID UNTIL '{{expiration}}';"]
  default_ttl         = 7200
  max_ttl             = 2592000
}

resource "vault_database_secret_backend_role" "staff" {
  backend             = "${var.vault_backend}"
  name                = "${var.application_name}-staff"
  db_name             = "${vault_database_secret_backend_connection.postgres.name}"
  creation_statements = ["CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';", "GRANT USAGE ON SCHEMA public TO \"{{name}}\";", "GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO \"{{name}}\";", "GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO \"{{name}}\";"]
  default_ttl         = 7200
  max_ttl             = 2592000
}

--
This mailing list is governed under the HashiCorp Community Guidelines - https://www.hashicorp.com/community-guidelines.html. Behavior in violation of those guidelines may result in your removal from this mailing list.
 
GitHub Issues: https://github.com/hashicorp/vault/issues
IRC: #vault-tool on Freenode
---
You received this message because you are subscribed to the Google Groups "Vault" group.
To unsubscribe from this group and stop receiving emails from it, send an email to vault-tool+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/vault-tool/3c896d8b-5d16-4eaa-9df0-6a7167c4860a%40googlegroups.com.

[lingfish]

Thanks heaps for replying Matt.

I wasn't all that clear re point 3; what I meant was a file naming standard for the actual policy, not in the URL ;)  I'm trying at least to VCS all of the policies I build.

Other than that, you've clarified what I suspected was true, and wasn't made abundantly clear to me in the otherwise excellent doco.  I looked at terra and didn't find that it fit my needs, but your code above is easy enough to read/map into Vault JSON in my head.

Thanks again!

To unsubscribe from this group and stop receiving emails from it, send an email to vault...@googlegroups.com.

[Matthew Tice]

Ah, I see.  Well the file names can be anything because once the file is read into vault as a policy, vault doesn't need the file anymore.  

But to answer your question, I'd set the name of the file the same as my policy name.  Example:

policy_name: db.postgres.[application_name]

file_name: db.postgres.[application_name].hcl

One point I forgot to mention is that my entire flow assumes `application_name` is unique.

Hope this helps,

Matt

To unsubscribe from this group and stop receiving emails from it, send an email to vault-tool+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/vault-tool/fca31760-1a86-41ed-ad26-fddb41150994%40googlegroups.com.

[lingfish]

Yep, sorry again, worded badly.  I was more talking about policy naming standards.

You've been a big help Matt, thanks heaps.

To view this discussion on the web visit https://groups.google.com/d/msgid/vault-tool/fca31760-1a86-41ed-ad26-fddb41150994%40googlegroups.com.