Postgres Credentials
Requirements
- Kubernetes cluster 1.15 or newer
- Vault 1.3.1 or newer
- Vault Injector 0.3.0 or newer
- Tetrate Service Bridge 0.8.0 or newer
- Postgres 11.1 or newer
Setup Vault
Install Vault (customers are supposed to handle Vault on their side. Vault may not be installed in the Kubernetes cluster, but should be reachable from inside the Kubernetes cluster).
The Vault Injector (agent-injector) have to be installed into the cluster and configured to inject sidecars. This is automatically done by the Helm chart v0.5.0+
which install Vault 0.12+ and Vault-Injector 0.3.0+. We assume here that Vault is installed in the tcc namespace.
helm install --name=vault --set='server.dev.enabled=true' ./vault-helm
Set up Database secret engine for Postgres
Enable the database secrets engine in Vault.
vault secrets enable database
Success! Enabled the database secrets engine at: database/
By default, the secrets engine is enabled at the name of the engine. To enable the secrets engine at a different path, use the -path argument.
Configure Vault with the proper plugin and connection information.
Replace postgres:5432 by the full URL:port of your Postgres cluster. Only change the lower username and password with your own, don't edit the one between {{ }} in the URL, it is used as a template:
vault write database/config/tsb \
plugin_name=postgresql-database-plugin \
allowed_roles="pg-role" \
connection_url="postgresql://{{username}}:{{password}}@postgres:5432/?sslmode=disable" \
username="<postgres-username>" \
password="<postgres-password>"
You can review the configuration by using the read action:
vault read database/config/tcc
# Key Value
# --- -----
# allowed_roles [pg-role]
# connection_details map[connection_url:postgresql://{{username}}:{{password}}@postgres:5432/?sslmode=disable username:postgres]
# plugin_name postgresql-database-plugin
# root_credentials_rotate_statements []
Configure a role that maps a name in Vault to an SQL statement to execute to create the database credential.
The max_ttl defines how long the new credentials are valid.
The default_ttl defines a lease time, and the Vault-Injector will renew it until we reach the max_ttl.
At this time, a new database credential will be created.
TTL values must be paired with the application's database connection life time to ensure they are all closed before the TTL expire.
Here, again, don't edit the parameters between {{ }}, they are used as a template by Vault:
vault write database/roles/pg-role \
db_name=tcc \
creation_statements="CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; \
GRANT ALL ON ALL TABLES IN SCHEMA public TO \"{{name}}\";" \
default_ttl="12h" \
max_ttl="24h"
Success! Data written to: database/roles/pg-role
Again, use the read action to check the setup:
vault read database/roles/pg-role
# Key Value
# --- -----
# creation_statements [CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; GRANT SELECT ON ALL TABLES IN SCHEMA public TO "{{name}}";]
# db_name tcc
# default_ttl 24h
# max_ttl 24h
# renew_statements []
# revocation_statements []
# rollback_statements []
Now, generate a new credential by reading from the /creds endpoint with the name of the role.
This is the mechanism that will be used by the Vault-Injector to grab credentials for your Kubernetes application:
vault read database/creds/pg-role
Key Value
--- -----
lease_id database/creds/pg-role/tUEs8eogkk9KL5erU5rLv7hD
lease_duration 24h
lease_renewable true
password A1a-1ZYMcUHKJIJH6rrc
username v-token-pg-role-KQ4ze3GYi5He0D70tEmo-1587973449
Set up Kubernetes Secret Engine
Configure a policy named “pg-auth”, This is a very non-restrictive policy, and in a production setting, we should lock this down more.
vault policy write pg-auth - <<EOF
path "database/creds/*" {
capabilities = ["read"]
}
EOF
Success! Uploaded policy: pg-auth
Configured Vault to enable access to the Kubernetes API. This example assumes that you are running commands in the Vault pod using kubectl exec.
If not, you will need to find the right JWT Token, Kubernetes API URL (that Vault will use to connect to Kubernetes) and the CA certificate of the vaultserver service account:
vault auth enable kubernetes
vault write auth/kubernetes/config \
token_reviewer_jwt="$(cat /var/run/secrets/kubernetes.io/serviceaccount/token)" \
kubernetes_host=https://${KUBERNETES_PORT_443_TCP_ADDR}:443 \
kubernetes_ca_cert=@/var/run/secrets/kubernetes.io/serviceaccount/ca.crt
Attach the database policy to service accounts from the management namespace (tcc namespace here):
vault write auth/kubernetes/role/pg \
bound_service_account_names=* \
bound_service_account_namespaces=tsb \
policies=pg-auth \
ttl=24h
If you want more restrictions, you can create one role per ServiceAccount. For Postgres, you will need one for tcc-oap SA and one for tcc-spm SA:
vault write auth/kubernetes/role/spm \
bound_service_account_names=tcc-spm \
bound_service_account_namespaces=tcc \
policies=pg-auth \
ttl=24h
vault write auth/kubernetes/role/oap \
bound_service_account_names=tcc-oap \
bound_service_account_namespaces=tcc \
policies=pg-auth \
ttl=24h
Inject secrets into the pod
To use Vault Agent Injector in combination with Postgres, add the following deployment pod annotations and environment variables to the ManagementPlane custom resource.
release 0.8.0 or newer
An overlay is used to re-configure the deployment on the fly:
spec:
components:
apiServer:
kubeSpec:
deployment:
podAnnotations:
vault.hashicorp.com/agent-inject: 'true'
vault.hashicorp.com/agent-inject-secret-config.yaml: 'database/creds/pg-role'
vault.hashicorp.com/agent-inject-template-config.yaml: |
{{- with secret "database/creds/pg-role" -}}
data:
username: {{ .Data.username }}
password: {{ .Data.password }}
{{- end -}}
vault.hashicorp.com/role: 'pg'
vault.hashicorp.com/secret-volume-path: /etc/dbvault
overlays:
- apiVersion: v1
kind: Deployment
name: tsb
patches:
- path: spec.template.spec.containers[name:tsb].args.[:/etc/db/config\.yaml]
value: /etc/dbvault/config.yaml
- path: spec.template.spec.containers[name:tsb].args.[:86400s]
value: "1h"
spmServer:
kubeSpec:
deployment:
podAnnotations:
vault.hashicorp.com/agent-inject: 'true'
vault.hashicorp.com/agent-inject-secret-config.yaml: 'database/creds/pg-role'
vault.hashicorp.com/agent-inject-template-config.yaml: |
{{- with secret "database/creds/pg-role" -}}
data:
username: {{ .Data.username }}
password: {{ .Data.password }}
{{- end -}}
vault.hashicorp.com/role: 'pg'
vault.hashicorp.com/secret-volume-path: /etc/dbvault
job:
podAnnotations:
vault.hashicorp.com/agent-inject: 'true'
vault.hashicorp.com/agent-inject-secret-config.yaml: 'database/creds/pg-role'
vault.hashicorp.com/agent-pre-populate-only: "true"
vault.hashicorp.com/agent-inject-template-config.yaml: |
{{- with secret "database/creds/pg-role" -}}
data:
username: {{ .Data.username }}
password: {{ .Data.password }}
{{- end -}}
vault.hashicorp.com/role: 'pg'
vault.hashicorp.com/secret-volume-path: /etc/dbvault
overlays:
- apiVersion: v1
kind: Deployment
name: spm
patches:
- path: spec.template.spec.containers[name:spm].args.[:/etc/db/config\.yaml]
value: /etc/dbvault/config.yaml
- path: spec.template.spec.containers[name:spm].args.[:86400s]
value: "1h"
overlays:
- apiVersion: v1
kind: CronJob
name: spmsync
patches:
- path: spec.jobTemplate.spec.template.spec.containers[name:spmsync].args.[:/etc/db/config\.yaml]
value: /etc/dbvault/config.yaml
- path: spec.jobTemplate.spec.template.spec.containers[name:spmsync].args.[:86400s]
value: "1h"
Debugging
Check roles in Postgres
You can use the Postgres command line client psql to check the role creation inside the target database, tcc:
psql -h postgres -p 5432 -U tcc -d tcc
Once connected to the database, you can use the \du command to list all the current roles for the database:
\du
List of roles
Role name | Attributes | Member of
----------------------------------------------------+------------------------------------------------------------+-------------------------------------------------------------
rds_ad | Cannot login | {}
rds_iam | Cannot login | {}
rds_password | Cannot login | {}
rds_replication | Cannot login | {}
rds_superuser | Cannot login | {pg_monitor,pg_signal_backend,rds_replication,rds_password}
rdsadmin | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
| Password valid until infinity |
rdsrepladmin | No inheritance, Cannot login, Replication | {}
tcc | Create role, Create DB +| {rds_superuser}
| Password valid until infinity |
| | {}
v-kubernet-pg-role-5OUfsUQv3xAASWZkbECV-1589890098 | Password valid until 2020-05-20 12:08:23+00 | {}
v-kubernet-pg-role-7uiTkWgsxphogXub0qpp-1589887199 | Password valid until 2020-05-20 11:20:04+00 | {}
...
Here you can see the role tcc that was used when configuring the database inside Vault and some roles like
v-kubernet-pg-role-5OUfsUQv3xAASWZkbECV-1589890098 which correspond to dynamic roles created by the Vault-Injector sidecar.
You can also list the access rights that were granted to the dynamic roles.
Here is an example with the role v-kubernet-pg-role-5OUfsUQv3xAASWZkbECV-1589890098:
SELECT grantee AS user, CONCAT(table_schema, '.', table_name) AS table,
CASE
WHEN COUNT(privilege_type) = 7 THEN 'ALL'
ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ')
END AS grants
FROM information_schema.role_table_grants
WHERE grantee='v-kubernet-pg-role-5OUfsUQv3xAASWZkbECV-1589890098'
GROUP BY table_name, table_schema, grantee;
user | table | grants
----------------------------------------------------+------------------------+--------
v-kubernet-pg-role-5OUfsUQv3xAASWZkbECV-1589890098 | public.application | ALL
v-kubernet-pg-role-5OUfsUQv3xAASWZkbECV-1589890098 | public.assignment | ALL
v-kubernet-pg-role-5OUfsUQv3xAASWZkbECV-1589890098 | public.association | ALL
...