1# Running the Cloud SQL Proxy as a Service
2
3This example demonstrates how to run the Cloud SQL Auth Proxy with PgBouncer on
4Kubernetes as a service. It assumes you have already successfully completed all
5the steps in [Using the Cloud SQL Auth Proxy on Kubernetes][sidecar].
6
7In this example, you will deploy [PgBouncer][] with the Cloud SQL Auth Proxy as
8a sidecar, in addition to configuring encryption between the application and
9PgBouncer.
10
11## A Word of Warning
12
13Running PgBouncer with the Cloud SQL Auth Proxy may pose a significant
14operational burden and should be undertaken with caution given the attendant
15complexity.
16
17In general, we recommend [running the proxy as a sidecar][sidecar] to your
18application because it is simple, there is less overhead, it is secure out of
19the box, and there is less latency involved.
20
21However, the service pattern is useful when you are at very large scale, when
22you clearly need a database connection pooler, and when you are running into SQL
23Admin API quota problems.
24
25## Initial Setup
26
27Before we deploy PgBouncer with the Cloud SQL Auth Proxy, there are three
28initial steps to take.
29
30### Generate Certificates for PgBouncer
31
32First, you will need to generate certificates to encrypt the connection between
33the application and PgBouncer. We recommend using [CFSSL][] to handle
34certificate generation. Note: this example uses self-signed certificates. In
35some cases, using a certificate signed by a public certificate authority may be
36preferred. Alternatively, Kubernetes includes [an API for issuing
37certificates][k8s-tls]. See the documentation on
38[certificates][certificate-docs] for more details.
39
40The certificate signing request is encoded as JSON in
41[`ca_csr.json`](ca_csr.json) for the certificate authority and in
42[`server_csr.json`](server_csr.json) for the "server," here PgBouncer.
43
44First, we initialize our certificate authority.
45
46``` shell
47# This step produces ca-key.pem (the CA private key)
48# and ca.pem (the CA certificate).
49cfssl genkey -initca ca_csr.json | cfssljson -bare ca
50```
51
52Next, we generate a public and private key for the server. These will be what
53we will use to encrypt traffic from the application to PgBouncer.
54
55``` shell
56# This step produces server-key.pem (the server private key)
57# and server.pem (the server certicate).
58cfssl gencert -ca cert -ca-key key server_csr.json | cfssljson -bare server
59```
60
61### Save the certificates as secrets
62
63Second, with all the necessary certificates generated, we will save them as
64secrets:
65
66``` shell
67# First the CA cert
68kubectl create secret tls <YOUR-CA-SECRET> --key="ca-key.pem" --cert="ca.pem"
69
70# Next the server cert
71kubectl create secret tls <YOUR-SERVER-CERT-SECRET> --key="server-key.pem" \
72 --cert="server.pem"
73```
74
75### Containerize PgBouncer
76
77Third, we will containerize PgBouncer. Some users may prefer to containerize
78PgBouncer themselves. For this example, we will make use of an open source
79container, [edoburu/pgbouncer][edoburu]. One nice benefit of `edoburu/pgbouncer`
80is that it will generate all the PgBouncer configuration based on environment
81variables passed to the container.
82
83## Deploy PgBouncer as a Service
84
85With PgBouncer containerized, we will now create a deployment with PgBouncer and
86the proxy as a sidecar.
87
88First, we mount our CA certificate and server certificate and private key,
89renaming the certificate secrets to `cert.pem` and server private key to
90`key.pem`:
91
92> [`pgbouncer_deployment.yaml`](pgbouncer_deployment.yaml#L15-L29)
93
94``` yaml
95volumes:
96- name: cacert
97 secret:
98 secretName: <YOUR-CA-SECRET>
99 items:
100 - key: tls.crt
101 path: cert.pem
102- name: servercert
103 secret:
104 secretName: <YOUR-SERVER-CERT-SECRET>
105 items:
106 - key: tls.crt
107 path: cert.pem
108 - key: tls.key
109 path: key.pem
110```
111
112Next, we specify volume mounts in our PgBouncer container where the secrets will
113be stored:
114
115> [`pgbouncer_deployment.yaml`](pgbouncer_deployment.yaml#L31-L41)
116
117``` yaml
118- name: pgbouncer
119 image: <PG-BOUNCER-CONTAINER>
120 ports:
121 - containerPort: 5432
122 volumeMounts:
123 - name: cacert
124 mountPath: "/etc/ca"
125 readOnly: true
126 - name: servercert
127 mountPath: "/etc/server"
128 readOnly: true
129```
130
131Then we configure PgBouncer through environment variables. Note: we use 5431 for
132`DB_PORT` to leave 5432 available.
133
134> [`pgbouncer_deployment.yaml`](pgbouncer_deployment.yaml#L42-L69)
135
136``` yaml
137env:
138- name: DB_HOST
139 value: "127.0.0.1"
140- name: DB_USER
141 valueFrom:
142 secretKeyRef:
143 name: <YOUR-DB-SECRET>
144 key: username
145- name: DB_PASSWORD
146 valueFrom:
147 secretKeyRef:
148 name: <YOUR-DB-SECRET>
149 key: password
150- name: DB_NAME
151 valueFrom:
152 secretKeyRef:
153 name: <YOUR-DB-SECRET>
154 key: database
155- name: DB_PORT
156 value: "5431"
157- name: CLIENT_TLS_SSLMODE
158 value: "require"
159- name: CLIENT_TLS_CA_FILE
160 value: "/etc/ca/cert.pem"
161- name: CLIENT_TLS_KEY_FILE
162 value: "/etc/server/key.pem"
163- name: CLIENT_TLS_CERT_FILE
164 value: "/etc/server/cert.pem"
165```
166
167For the PgBouncer deployment, we add the proxy as a sidecar, starting it on port
1685431:
169
170> [`pgbouncer_deployment.yaml`](pgbouncer_deployment.yaml#L70-L76)
171
172``` yaml
173- name: cloud-sql-proxy
174 image: gcr.io/cloudsql-docker/gce-proxy:1.22.0 # make sure the use the latest version
175 command:
176 - "/cloud_sql_proxy"
177 - "-instances=<INSTANCE_CONNECTION_NAME>=tcp:5431"
178 securityContext:
179 runAsNonRoot: true
180```
181
182Next, we create a PgBouncer service, listening on port 5342:
183
184> [`pgbouncer_service.yaml`](pgbouncer_service.yaml#L1-L11)
185
186``` yaml
187apiVersion: v1
188kind: Service
189metadata:
190 name: <YOUR-SERVICE-NAME>
191spec:
192 selector:
193 app: <YOUR-APPLICATION-NAME>
194 ports:
195 - protocol: TCP
196 port: 5432
197 targetPort: 5432
198```
199
200With the PgBouncer service and deployment done, we are ready to point our
201application at it.
202
203## Configure your application
204
205First, we configure a volume for the CA certificate, mapping the file name to
206`cert.pem`.
207
208> [`deployment.yaml`](deployment.yaml#L1-L11)
209
210``` yaml
211volumes:
212- name: cacert
213 secret:
214 secretName: <YOUR-CA-CERT>
215 items:
216 - key: tls.crt
217 path: cert.pem
218```
219
220Next, we mount the volume within the application container:
221
222> [`deployment.yaml`](deployment.yaml#L28-L31)
223
224``` yaml
225volumeMounts:
226- name: cacert
227 mountPath: "/etc/ca"
228 readOnly: true
229```
230
231Then, we configure environment variables for connecting to the database, this
232time including a `CA_CERT`:
233
234> [`deployment.yaml`](deployment.yaml#L32-L53)
235
236``` yaml
237env:
238- name: DB_HOST
239 value: "<YOUR-SERVICE-NAME>.default.svc.cluster.local" # using the "default" namespace
240- name: DB_USER
241 valueFrom:
242 secretKeyRef:
243 name: <YOUR-DB-SECRET>
244 key: username
245- name: DB_PASS
246 valueFrom:
247 secretKeyRef:
248 name: <YOUR-DB-SECRET>
249 key: password
250- name: DB_NAME
251 valueFrom:
252 secretKeyRef:
253 name: <YOUR-DB-SECRET>
254 key: database
255- name: DB_PORT
256 value: "5432"
257- name: CA_CERT
258 value: "/etc/ca/cert.pem"
259```
260
261Note: now the `DB_HOST` value uses an internal DNS record pointing at the
262PgBouncer service.
263
264Finally, when configuring a database connection string, the application must
265provide the additional properties:
266
2671. `sslmode` must be set to at least `verify-ca`
2681. `sslrootcert` must set to the environment variable `CA_CERT`
269
270
271[certificate-docs]: https://kubernetes.io/docs/tasks/administer-cluster/certificates/
272[CFSSL]: https://github.com/cloudflare/cfssl
273[edoburu]: https://hub.docker.com/r/edoburu/pgbouncer
274[sidecar]: ../k8s-sidecar/README.md
275[k8s-tls]: https://kubernetes.io/docs/tasks/tls/managing-tls-in-a-cluster/
276[PgBouncer]: https://www.pgbouncer.org
277
View as plain text