add well-supported storage backend for vault

[jamieklassen]

please put your configuration in code with terraform

[pivotal-bin-ju]

TODOs:

• [ ] scalable storage backend
  • [x] research the available backend (after a discussion, we decide to adopt cloudsql/postgres)
  • [ ] create a new vault for nci.
  • [x] add postgres to terraform and apply to gcp.
  • [x] manually create the tables for postgres (connect to postgres by the command: psql 'host=35.184.82.147 port=5432 user=atc password=PASSWD sslkey=/tmp/vault-database-private-key sslcert=/tmp/vault-db-cert sslrootcert=/tmp/vault-db-root-ca dbname=vault', the first time without dbname to create the db vault, and then execute this command to create table)
  • [ ] automate the tables creation for postgres
  • [x] create a new branch base on concourse/hush-house/ci, use postgres as the backend.
  • [x] ln -s vault vault-nci, make deploy-vault-nci
  • [x] load the creds from lastpass by running make creds-vault-nci.
  • [x] make the vault db connection password in the backend configuration to be secure
  • [ ] research HA mode.
  • [x] manually setup auto-backup for the postgres
  • [x] investigate how to automate the auto-backup (it's already enabled by terraform)
  • [x] secrets copy (across different vaults, a script would be needed)
  • [ ] data consistency verify
  • [ ] switch the backend for the new vault
  • [ ] decommision old vault for nci
  • [ ] doc update
• [ ] draft a PR to helm-vault
• backup plan. relative link: https://github.com/pivotal/concourse-ops/wiki/Operating-Vault#backing-up-vault-secrets

[jamieklassen]

@deniseyu made some nice notes on this topic in #37, so I'd say they should both close at the same time

[pivotal-bin-ju]

Research on the storage and backup for vault

1.Postgresql/Cloudsql or gcs?
Please see the discussion with @pivotal-jamie-klassen and @xtreme-sameer-vohra on slack

gcs with its simple configuration and SLA around data integrity seems like the best choice. When we look into the backup/restore for vault, it does not guarantee the atomic operation when talking to the storage backend, even low-level interface is exposed to postgresql backend. so it is not safe to snapshot the backend when it is running. Stop it while doing a backup which makes no sense. Luckily for our case, the most frequent interaction with vault is reading, not writing. so theoretically we have a very low chance to get inconsistent data while we do a backup.

Base on the assumption above, we should be OK to backup the online vault. gcs object versioning may be hard to track and the performance would be low when data is frequently updated.
Leveraging the automatic backup of cloudsql may benefit us.

• configuration for postgresql/cloudsql:

  storage "postgresql" {
  connection_url = "postgres://user123:secret123!@localhost:5432/vault"
  }

  (Do we need another vault to keep the password for the connection? :) )

• Manual steps for initialization:

  • create table vault_kv_store

    CREATE TABLE vault_kv_store (
    parent_path TEXT COLLATE "C" NOT NULL,
    path        TEXT COLLATE "C",
    key         TEXT COLLATE "C",
    value       BYTEA,
    CONSTRAINT pkey PRIMARY KEY (path, key)
    );
    
    CREATE INDEX parent_path_idx ON vault_kv_store (parent_path);

  • To support HA feature:

    CREATE TABLE vault_ha_locks (
    ha_key                                      TEXT COLLATE "C" NOT NULL,
    ha_identity                                 TEXT COLLATE "C" NOT NULL,          
    ha_value                                    TEXT COLLATE "C",  
    valid_until                                 TIMESTAMP WITH TIME ZONE NOT NULL,
    CONSTRAINT ha_key PRIMARY KEY (ha_key)
    );

  • do not need to create the function vault_kv_putdue to the newer version will be used than 9.5.
  • more detail please see the storage configuration

[pivotal-bin-ju]

DEV NOTE What I did:

• terraform: added a new cloudsql/postgres as the backend of vault
• chart: deployed a new chart vault-nci remained Issues: the chart now complain the issue:

  Error initializing storage of type postgresql: failed to check for native upsert: pq: Private key file has group or world access. Permissions should be u=rw (0600) or less

  To Do:

• Solve the problem above(I's been solved by copying the file to new location, then chmod the files)
• Check the to-do list

[deniseyu]

Can the auto-unseal and backend changes exist as separate PRs?

[zoetian]

@deniseyu yeah that makes sense. Originally it seemed easier to do both "while you're at it", but both tasks are pretty big and worth splitting apart. Auto-unseal: #48

[pivotal-bin-ju]

Dev Note Today I:

• populated the secret on lastpass, so you can load the secret by running make creds-vault-nci
• read postgres IP and secret from k8s secrets.
• created a draft PR https://github.com/concourse/hush-house/pull/87.

Next check out the TODO list

[pivotal-bin-ju]

The data has been ported from ci to nci which has postgres as backend, and the auto backup is enabled.

The next step is data consistency verify.