Skip to content

Commit 6d9e108

Browse files
jsimonwebjsimonweb
authored
Update Cloud SQL sample apps with SSL example (GoogleCloudPlatform#6777)
* Update Cloud SQL sample apps with SSL example * Address presubmit error. * Update Dockerfile * Update Dockerfile * Address review comments.
1 parent d8f4875 commit 6d9e108

6 files changed

Lines changed: 123 additions & 10 deletions

File tree

‎cloud-sql/mysql/sqlalchemy/Dockerfile‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414

1515
# Use the official Python image.
1616
# https://hub.docker.com/_/python
17-
FROM python:3.9
17+
FROM python:3
1818

1919
# Copy application dependency manifests to the container image.
2020
# Copying this separately prevents re-running pip install on every code change.
@@ -30,6 +30,9 @@ ENV APP_HOME /app
3030
WORKDIR $APP_HOME
3131
COPY . ./
3232

33+
# Copy any certificates if present.
34+
COPY ./certs /app/certs
35+
3336
# Run the web service on container startup. Here we use the gunicorn
3437
# webserver, with one worker process and 8 threads.
3538
# For environments with multiple CPU cores, increase the number of workers

‎cloud-sql/mysql/sqlalchemy/certs/.gitkeep‎

Whitespace-only changes.

‎cloud-sql/mysql/sqlalchemy/main.py‎

Lines changed: 57 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -58,9 +58,57 @@ def init_connection_engine():
5858
}
5959

6060
if os.environ.get("DB_HOST"):
61+
if os.environ.get("DB_ROOT_CERT"):
62+
return init_tcp_sslcerts_connection_engine(db_config)
6163
return init_tcp_connection_engine(db_config)
62-
else:
63-
return init_unix_connection_engine(db_config)
64+
return init_unix_connection_engine(db_config)
65+
66+
67+
def init_tcp_sslcerts_connection_engine(db_config):
68+
# [START cloud_sql_mysql_sqlalchemy_create_tcp_sslcerts]
69+
# Remember - storing secrets in plaintext is potentially unsafe. Consider using
70+
# something like https://cloud.google.com/secret-manager/docs/overview to help keep
71+
# secrets secret.
72+
db_user = os.environ["DB_USER"]
73+
db_pass = os.environ["DB_PASS"]
74+
db_name = os.environ["DB_NAME"]
75+
db_host = os.environ["DB_HOST"]
76+
db_root_cert = os.environ["DB_ROOT_CERT"]
77+
db_cert = os.environ["DB_CERT"]
78+
db_key = os.environ["DB_KEY"]
79+
80+
# Extract port from db_host if present,
81+
# otherwise use DB_PORT environment variable.
82+
host_args = db_host.split(":")
83+
if len(host_args) == 1:
84+
db_hostname = host_args[0]
85+
db_port = int(os.environ["DB_PORT"])
86+
elif len(host_args) == 2:
87+
db_hostname, db_port = host_args[0], int(host_args[1])
88+
89+
ssl_args = {
90+
"ssl_ca": db_root_cert,
91+
"ssl_cert": db_cert,
92+
"ssl_key": db_key
93+
}
94+
95+
pool = sqlalchemy.create_engine(
96+
# Equivalent URL:
97+
# mysql+pymysql://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>
98+
sqlalchemy.engine.url.URL.create(
99+
drivername="mysql+pymysql",
100+
username=db_user, # e.g. "my-database-user"
101+
password=db_pass, # e.g. "my-database-password"
102+
host=db_hostname, # e.g. "127.0.0.1"
103+
port=db_port, # e.g. 3306
104+
database=db_name # e.g. "my-database-name"
105+
),
106+
connect_args=ssl_args,
107+
**db_config
108+
)
109+
# [END cloud_sql_mysql_sqlalchemy_create_tcp_sslcerts]
110+
111+
return pool
64112

65113

66114
def init_tcp_connection_engine(db_config):
@@ -73,9 +121,14 @@ def init_tcp_connection_engine(db_config):
73121
db_name = os.environ["DB_NAME"]
74122
db_host = os.environ["DB_HOST"]
75123

76-
# Extract host and port from db_host
124+
# Extract port from db_host if present,
125+
# otherwise use DB_PORT environment variable.
77126
host_args = db_host.split(":")
78-
db_hostname, db_port = host_args[0], int(host_args[1])
127+
if len(host_args) == 1:
128+
db_hostname = db_host
129+
db_port = os.environ["DB_PORT"]
130+
elif len(host_args) == 2:
131+
db_hostname, db_port = host_args[0], int(host_args[1])
79132

80133
pool = sqlalchemy.create_engine(
81134
# Equivalent URL:

‎cloud-sql/postgres/sqlalchemy/Dockerfile‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414

1515
# Use the official Python image.
1616
# https://hub.docker.com/_/python
17-
FROM python:3.9
17+
FROM python:3
1818

1919
# Copy application dependency manifests to the container image.
2020
# Copying this separately prevents re-running pip install on every code change.
@@ -30,6 +30,9 @@ ENV APP_HOME /app
3030
WORKDIR $APP_HOME
3131
COPY . ./
3232

33+
# Copy any certificates if present.
34+
COPY ./certs /app/certs
35+
3336
# Run the web service on container startup. Here we use the gunicorn
3437
# webserver, with one worker process and 8 threads.
3538
# For environments with multiple CPU cores, increase the number of workers

‎cloud-sql/postgres/sqlalchemy/certs/.gitkeep‎

Whitespace-only changes.

‎cloud-sql/postgres/sqlalchemy/main.py‎

Lines changed: 58 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -15,6 +15,7 @@
1515
import datetime
1616
import logging
1717
import os
18+
import ssl
1819

1920
from flask import Flask, render_template, request, Response
2021
import sqlalchemy
@@ -57,9 +58,57 @@ def init_connection_engine():
5758
}
5859

5960
if os.environ.get("DB_HOST"):
61+
if os.environ.get("DB_ROOT_CERT"):
62+
return init_tcp_sslcerts_connection_engine(db_config)
6063
return init_tcp_connection_engine(db_config)
61-
else:
62-
return init_unix_connection_engine(db_config)
64+
return init_unix_connection_engine(db_config)
65+
66+
67+
def init_tcp_sslcerts_connection_engine(db_config):
68+
# [START cloud_sql_postgres_sqlalchemy_create_tcp_sslcerts]
69+
# Remember - storing secrets in plaintext is potentially unsafe. Consider using
70+
# something like https://cloud.google.com/secret-manager/docs/overview to help keep
71+
# secrets secret.
72+
db_user = os.environ["DB_USER"]
73+
db_pass = os.environ["DB_PASS"]
74+
db_name = os.environ["DB_NAME"]
75+
db_host = os.environ["DB_HOST"]
76+
db_root_cert = os.environ["DB_ROOT_CERT"]
77+
db_cert = os.environ["DB_CERT"]
78+
db_key = os.environ["DB_KEY"]
79+
80+
# Extract port from db_host if present,
81+
# otherwise use DB_PORT environment variable.
82+
host_args = db_host.split(":")
83+
if len(host_args) == 1:
84+
db_hostname = host_args[0]
85+
db_port = int(os.environ["DB_PORT"])
86+
elif len(host_args) == 2:
87+
db_hostname, db_port = host_args[0], int(host_args[1])
88+
89+
ssl_context = ssl.SSLContext()
90+
ssl_context.verify_mode = ssl.CERT_REQUIRED
91+
ssl_context.load_verify_locations(db_root_cert)
92+
ssl_context.load_cert_chain(db_cert, db_key)
93+
ssl_args = {"ssl_context" : ssl_context}
94+
95+
pool = sqlalchemy.create_engine(
96+
# Equivalent URL:
97+
# postgresql+pg8000://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>
98+
sqlalchemy.engine.url.URL.create(
99+
drivername="postgresql+pg8000",
100+
username=db_user, # e.g. "my-database-user"
101+
password=db_pass, # e.g. "my-database-password"
102+
host=db_hostname, # e.g. "127.0.0.1"
103+
port=db_port, # e.g. 5432
104+
database=db_name # e.g. "my-database-name"
105+
),
106+
connect_args=ssl_args,
107+
**db_config
108+
)
109+
# [END cloud_sql_postgres_sqlalchemy_create_tcp_sslcerts]
110+
pool.dialect.description_encoding = None
111+
return pool
63112

64113

65114
def init_tcp_connection_engine(db_config):
@@ -72,9 +121,14 @@ def init_tcp_connection_engine(db_config):
72121
db_name = os.environ["DB_NAME"]
73122
db_host = os.environ["DB_HOST"]
74123

75-
# Extract host and port from db_host
124+
# Extract port from db_host if present,
125+
# otherwise use DB_PORT environment variable.
76126
host_args = db_host.split(":")
77-
db_hostname, db_port = host_args[0], int(host_args[1])
127+
if len(host_args) == 1:
128+
db_hostname = db_host
129+
db_port = os.environ["DB_PORT"]
130+
elif len(host_args) == 2:
131+
db_hostname, db_port = host_args[0], int(host_args[1])
78132

79133
pool = sqlalchemy.create_engine(
80134
# Equivalent URL:

0 commit comments

Comments
 (0)