Description
What version of the credhub server you are using?
2.11.1
What version of the credhub cli you are using?
2.9.0
If you were attempting to accomplish a task, what was it you were attempting to do?
We rotate credentials regularly, and have seen that the size of the CredHub DB is growing constantly. The DB size is growing even if we rotate a credential by deleting and setting it (this deletes old credential versions).
What did you expect to happen?
I would expect, that the credhub database doesn't grow so fast. We had databases using 15 GB storage. After I have manually deleted the orphaned entries the database size was only 40 MB.
What was the actual behaviour?
Values it the encrypted_value table are worthless if there are no entries in the credential_version, password_credential or user_credential table, because nobody can know to which credential the encrypted value belongs to.
If I generate a password credential like this:
credhub generate --name=/foo --type=password
I get two additional entries in the encrypted_value table. One for the value and one for the parameters. But If I delete the credential, no values will be deleted from the encrypted_value table.
To get rid of these entries, you may use the following SQL query (use at your own risk!):
DELETE FROM encrypted_value
WHERE
NOT EXISTS (select 1 from credential_version where encrypted_value_uuid=encrypted_value.uuid)
AND
NOT EXISTS (select 1 from password_credential where password_parameters_uuid=encrypted_value.uuid)
AND
NOT EXISTS (select 1 from user_credential where password_parameters_uuid=encrypted_value.uuid)
But if the database has already a huge size, this will take some time (It took about a week to delete all orphaned entries)
By adding some indexes you could speed it up dramatically:
CREATE INDEX CONCURRENTLY IF NOT EXISTS credential_version_encrypted_value_uuid_idx ON credential_version USING btree (encrypted_value_uuid);
CREATE INDEX CONCURRENTLY IF NOT EXISTS password_credential_password_parameters_uuid_idx ON password_credential USING btree (password_parameters_uuid);
CREATE INDEX CONCURRENTLY IF NOT EXISTS user_credential_password_parameters_uuid_idx ON user_credential USING btree (password_parameters_uuid);
But that is just to get rid of existing entries. I would expect that the encrypted values get deleted together with the corresponding entries in the other tables.
Please confirm where necessary:
- I have included a log output (N/A)
- My log includes an error message (N/A)
- I have included steps for reproduction
If you are a PCF customer with an Operation Manager (PCF Ops Manager) please direct your questions to support (https://support.pivotal.io/)