GoogleCloudPlatform · jsimonweb · Oct 12, 2021 · Sep 30, 2021 · Sep 30, 2021 · Sep 30, 2021
diff --git a/cloud-sql/mysql/sqlalchemy/Dockerfile b/cloud-sql/mysql/sqlalchemy/Dockerfile
@@ -14,7 +14,7 @@
 
 # Use the official Python image.
 # https://hub.docker.com/_/python
-FROM python:3.9
+FROM python:3
 
 # Copy application dependency manifests to the container image.
 # Copying this separately prevents re-running pip install on every code change.
@@ -30,6 +30,9 @@ ENV APP_HOME /app
 WORKDIR $APP_HOME
 COPY . ./
 
+# Copy any certificates if present.
+COPY ./certs /app/certs
+
 # Run the web service on container startup. Here we use the gunicorn
 # webserver, with one worker process and 8 threads.
 # For environments with multiple CPU cores, increase the number of workers

diff --git a/cloud-sql/mysql/sqlalchemy/certs/.gitkeep b/cloud-sql/mysql/sqlalchemy/certs/.gitkeep
diff --git a/cloud-sql/mysql/sqlalchemy/main.py b/cloud-sql/mysql/sqlalchemy/main.py
@@ -58,9 +58,57 @@ def init_connection_engine():
     }
 
     if os.environ.get("DB_HOST"):
+        if os.environ.get("DB_ROOT_CERT"):
+            return init_tcp_sslcerts_connection_engine(db_config)
         return init_tcp_connection_engine(db_config)
-    else:
-        return init_unix_connection_engine(db_config)
+    return init_unix_connection_engine(db_config)
+
+
+def init_tcp_sslcerts_connection_engine(db_config):
+    # [START cloud_sql_mysql_sqlalchemy_create_tcp_sslcerts]
+    # Remember - storing secrets in plaintext is potentially unsafe. Consider using
+    # something like https://cloud.google.com/secret-manager/docs/overview to help keep
+    # secrets secret.
+    db_user = os.environ["DB_USER"]
+    db_pass = os.environ["DB_PASS"]
+    db_name = os.environ["DB_NAME"]
+    db_host = os.environ["DB_HOST"]
+    db_root_cert = os.environ["DB_ROOT_CERT"]
+    db_cert = os.environ["DB_CERT"]
+    db_key = os.environ["DB_KEY"]
+
+    # Extract port from db_host if present,
+    # otherwise use DB_PORT environment variable.
+    host_args = db_host.split(":")
+    if len(host_args) == 1:
+        db_hostname = host_args[0]
+        db_port = int(os.environ["DB_PORT"])
+    elif len(host_args) == 2:
+        db_hostname, db_port = host_args[0], int(host_args[1])
+
+    ssl_args = {
+        "ssl_ca": db_root_cert,
+        "ssl_cert": db_cert,
+        "ssl_key": db_key
+    }
+
+    pool = sqlalchemy.create_engine(
+        # Equivalent URL:
+        # mysql+pymysql://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>
+        sqlalchemy.engine.url.URL.create(
+            drivername="mysql+pymysql",
+            username=db_user,  # e.g. "my-database-user"
+            password=db_pass,  # e.g. "my-database-password"
+            host=db_hostname,  # e.g. "127.0.0.1"
+            port=db_port,  # e.g. 3306
+            database=db_name  # e.g. "my-database-name"
+        ),
+        connect_args=ssl_args,
+        **db_config
+    )
+    # [END cloud_sql_mysql_sqlalchemy_create_tcp_sslcerts]
+
+    return pool
 
 
 def init_tcp_connection_engine(db_config):
@@ -73,9 +121,14 @@ def init_tcp_connection_engine(db_config):
     db_name = os.environ["DB_NAME"]
     db_host = os.environ["DB_HOST"]
 
-    # Extract host and port from db_host
+    # Extract port from db_host if present,
+    # otherwise use DB_PORT environment variable.
     host_args = db_host.split(":")
-    db_hostname, db_port = host_args[0], int(host_args[1])
+    if len(host_args) == 1:
+        db_hostname = db_host
+        db_port = os.environ["DB_PORT"]
+    elif len(host_args) == 2:
+        db_hostname, db_port = host_args[0], int(host_args[1])
 
     pool = sqlalchemy.create_engine(
         # Equivalent URL:

diff --git a/cloud-sql/postgres/sqlalchemy/Dockerfile b/cloud-sql/postgres/sqlalchemy/Dockerfile
@@ -14,7 +14,7 @@
 
 # Use the official Python image.
 # https://hub.docker.com/_/python
-FROM python:3.9
+FROM python:3
 
 # Copy application dependency manifests to the container image.
 # Copying this separately prevents re-running pip install on every code change.
@@ -30,6 +30,9 @@ ENV APP_HOME /app
 WORKDIR $APP_HOME
 COPY . ./
 
+# Copy any certificates if present.
+COPY ./certs /app/certs
+
 # Run the web service on container startup. Here we use the gunicorn
 # webserver, with one worker process and 8 threads.
 # For environments with multiple CPU cores, increase the number of workers

diff --git a/cloud-sql/postgres/sqlalchemy/certs/.gitkeep b/cloud-sql/postgres/sqlalchemy/certs/.gitkeep
diff --git a/cloud-sql/postgres/sqlalchemy/main.py b/cloud-sql/postgres/sqlalchemy/main.py
@@ -15,6 +15,7 @@
 import datetime
 import logging
 import os
+import ssl
 
 from flask import Flask, render_template, request, Response
 import sqlalchemy
@@ -57,9 +58,57 @@ def init_connection_engine():
     }
 
     if os.environ.get("DB_HOST"):
+        if os.environ.get("DB_ROOT_CERT"):
+            return init_tcp_sslcerts_connection_engine(db_config)
         return init_tcp_connection_engine(db_config)
-    else:
-        return init_unix_connection_engine(db_config)
+    return init_unix_connection_engine(db_config)
+
+
+def init_tcp_sslcerts_connection_engine(db_config):
+    # [START cloud_sql_postgres_sqlalchemy_create_tcp_sslcerts]
+    # Remember - storing secrets in plaintext is potentially unsafe. Consider using
+    # something like https://cloud.google.com/secret-manager/docs/overview to help keep
+    # secrets secret.
+    db_user = os.environ["DB_USER"]
+    db_pass = os.environ["DB_PASS"]
+    db_name = os.environ["DB_NAME"]
+    db_host = os.environ["DB_HOST"]
+    db_root_cert = os.environ["DB_ROOT_CERT"]
+    db_cert = os.environ["DB_CERT"]
+    db_key = os.environ["DB_KEY"]
+
+    # Extract port from db_host if present,
+    # otherwise use DB_PORT environment variable.
+    host_args = db_host.split(":")
+    if len(host_args) == 1:
+        db_hostname = host_args[0]
+        db_port = int(os.environ["DB_PORT"])
+    elif len(host_args) == 2:
+        db_hostname, db_port = host_args[0], int(host_args[1])
+
+    ssl_context = ssl.SSLContext()
+    ssl_context.verify_mode = ssl.CERT_REQUIRED
+    ssl_context.load_verify_locations(db_root_cert)
+    ssl_context.load_cert_chain(db_cert, db_key)
+    ssl_args = {"ssl_context" : ssl_context}
+
+    pool = sqlalchemy.create_engine(
+        # Equivalent URL:
+        # postgresql+pg8000://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>
+        sqlalchemy.engine.url.URL.create(
+            drivername="postgresql+pg8000",
+            username=db_user,  # e.g. "my-database-user"
+            password=db_pass,  # e.g. "my-database-password"
+            host=db_hostname,  # e.g. "127.0.0.1"
+            port=db_port,  # e.g. 5432
+            database=db_name  # e.g. "my-database-name"
+        ),
+        connect_args=ssl_args,
+        **db_config
+    )
+    # [END cloud_sql_postgres_sqlalchemy_create_tcp_sslcerts]
+    pool.dialect.description_encoding = None
+    return pool
 
 
 def init_tcp_connection_engine(db_config):
@@ -72,9 +121,14 @@ def init_tcp_connection_engine(db_config):
     db_name = os.environ["DB_NAME"]
     db_host = os.environ["DB_HOST"]
 
-    # Extract host and port from db_host
+    # Extract port from db_host if present,
+    # otherwise use DB_PORT environment variable.
     host_args = db_host.split(":")
-    db_hostname, db_port = host_args[0], int(host_args[1])
+    if len(host_args) == 1:
+        db_hostname = db_host
+        db_port = os.environ["DB_PORT"]
+    elif len(host_args) == 2:
+        db_hostname, db_port = host_args[0], int(host_args[1])
 
     pool = sqlalchemy.create_engine(
         # Equivalent URL: