Skip to content

Commit e0849c0

Browse files
shubha-rajandinagraves
shubha-rajan
and
dinagraves
authored
feat: add SQL Server client-side encryption samples (GoogleCloudPlatform#5671)
* feat: add SQL Server client-side encryption samples * linting fixes * replace LargeBinary with VARBINARY * disabled bytes_to_unicode * fix license headers and broken README links * fix init_db * linting * rename column * use CONVERT to cast encrypted email to varbinary * update table definition * remove calls to team.encode Co-authored-by: Dina Graves Portman <dinagraves@google.com>
1 parent d25c944 commit e0849c0

13 files changed

+761
-0
lines changed

‎cloud-sql/sql-server/client-side-encryption/README.md‎

Lines changed: 95 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,95 @@
1+
# Encrypting fields in Cloud SQL - MySQL with Tink
2+
3+
## Before you begin
4+
5+
1. If you haven't already, set up a Python Development Environment by following the [python setup guide](https://cloud.google.com/python/setup) and
6+
[create a project](https://cloud.google.com/resource-manager/docs/creating-managing-projects#creating_a_project).
7+
8+
1. Create a 2nd Gen Cloud SQL Instance by following these
9+
[instructions](https://cloud.google.com/sql/docs/sqlserver/create-instance). Note the connection string,
10+
database user, and database password that you create.
11+
12+
1. Create a database for your application by following these
13+
[instructions](https://cloud.google.com/sql/docs/sqlserver/create-manage-databases). Note the database
14+
name.
15+
16+
1. Create a KMS key for your application by following these
17+
[instructions](https://cloud.google.com/kms/docs/creating-keys). Copy the resource name of your
18+
created key.
19+
20+
1. Create a service account with the 'Cloud SQL Client' permissions by following these
21+
[instructions](https://cloud.google.com/sql/docs/sqlserver/connect-admin-proxy#create-service-account).
22+
Download a JSON key to use to authenticate your connection.
23+
24+
1. **macOS / Windows only**: Configure gRPC Root Certificates: On some platforms you may need to
25+
accept the Google server certificates, see instructions for setting up
26+
[root certs](https://github.com/googleapis/google-cloud-cpp/blob/master/google/cloud/bigtable/examples/README.md#configure-grpc-root-certificates).
27+
28+
## Running locally
29+
30+
To run this application locally, download and install the `cloud_sql_proxy` by
31+
following the instructions
32+
[here](https://cloud.google.com/sql/docs/sqlserver/connect-admin-proxy#install).
33+
34+
Instructions are provided below for using the proxy with a TCP connection or a Unix Domain Socket.
35+
On Linux or Mac OS you can use either option, but on Windows the proxy currently requires a TCP
36+
connection.
37+
38+
### Launch proxy with TCP
39+
40+
To run the sample locally with a TCP connection, set environment variables and launch the proxy as
41+
shown below.
42+
43+
#### Linux / Mac OS
44+
Use these terminal commands to initialize environment variables:
45+
```bash
46+
export GOOGLE_APPLICATION_CREDENTIALS=/path/to/service/account/key.json
47+
export DB_HOST='127.0.0.1:1433'
48+
export DB_USER='<DB_USER_NAME>'
49+
export DB_PASS='<DB_PASSWORD>'
50+
export DB_NAME='<DB_NAME>'
51+
export GCP_KMS_URI='<GCP_KMS_URI>'
52+
```
53+
Note: Saving credentials in environment variables is convenient, but not secure - consider a more
54+
secure solution such as [Secret Manager](https://cloud.google.com/secret-manager/docs/quickstart) to
55+
help keep secrets safe.
56+
57+
Then use this command to launch the proxy in the background:
58+
```bash
59+
./cloud_sql_proxy -instances=<project-id>:<region>:<instance-name>=tcp:1433 -credential_file=$GOOGLE_APPLICATION_CREDENTIALS &
60+
```
61+
62+
#### Windows/PowerShell
63+
Use these PowerShell commands to initialize environment variables:
64+
```powershell
65+
$env:GOOGLE_APPLICATION_CREDENTIALS="<CREDENTIALS_JSON_FILE>"
66+
$env:DB_HOST="127.0.0.1:1433"
67+
$env:DB_USER="<DB_USER_NAME>"
68+
$env:DB_PASS="<DB_PASSWORD>"
69+
$env:DB_NAME="<DB_NAME>"
70+
$env:GCP_KMS_URI='<GCP_KMS_URI>'
71+
```
72+
Note: Saving credentials in environment variables is convenient, but not secure - consider a more
73+
secure solution such as [Secret Manager](https://cloud.google.com/secret-manager/docs/quickstart) to
74+
help keep secrets safe.
75+
76+
Then use this command to launch the proxy in a separate PowerShell session:
77+
```powershell
78+
Start-Process -filepath "C:\<path to proxy exe>" -ArgumentList "-instances=<project-id>:<region>:<instance-name>=tcp:1433 -credential_file=<CREDENTIALS_JSON_FILE>"
79+
```
80+
81+
### Install requirements
82+
83+
Next, setup install the requirements into a virtual enviroment:
84+
```bash
85+
virtualenv --python python3 env
86+
source env/bin/activate
87+
pip install -r requirements.txt
88+
```
89+
90+
### Run the demo
91+
92+
Add new votes and view the collected votes:
93+
```bash
94+
python snippets/query_and_decrypt_data.py
95+
```

‎cloud-sql/sql-server/client-side-encryption/noxfile_config.py‎

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,38 @@
1+
# Copyright 2021 Google LLC
2+
#
3+
# Licensed under the Apache License, Version 2.0 (the "License");
4+
# you may not use this file except in compliance with the License.
5+
# You may obtain a copy of the License at
6+
#
7+
# http://www.apache.org/licenses/LICENSE-2.0
8+
#
9+
# Unless required by applicable law or agreed to in writing, software
10+
# distributed under the License is distributed on an "AS IS" BASIS,
11+
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12+
# See the License for the specific language governing permissions and
13+
# limitations under the License.
14+
15+
# Default TEST_CONFIG_OVERRIDE for python repos.
16+
17+
# You can copy this file into your directory, then it will be inported from
18+
# the noxfile.py.
19+
20+
# The source of truth:
21+
# https://github.com/GoogleCloudPlatform/python-docs-samples/blob/master/noxfile_config.py
22+
23+
TEST_CONFIG_OVERRIDE = {
24+
# You can opt out from the test for specific Python versions.
25+
"ignored_versions": ["2.7", "3.6"],
26+
# Old samples are opted out of enforcing Python type hints
27+
# All new samples should feature them
28+
"enforce_type_hints": True,
29+
# An envvar key for determining the project id to use. Change it
30+
# to 'BUILD_SPECIFIC_GCLOUD_PROJECT' if you want to opt in using a
31+
# build specific Cloud project. You can also use your own string
32+
# to use your own Cloud project.
33+
"gcloud_project_env": "GOOGLE_CLOUD_PROJECT",
34+
# 'gcloud_project_env': 'BUILD_SPECIFIC_GCLOUD_PROJECT',
35+
# A dictionary you want to inject into your test. Don't put any
36+
# secrets here. These values will override predefined values.
37+
"envs": {},
38+
}

‎cloud-sql/sql-server/client-side-encryption/requirements-test.txt‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
pytest==6.2.2

‎cloud-sql/sql-server/client-side-encryption/requirements.txt‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
SQLAlchemy==1.4.4
2+
python-tds==1.10.0
3+
sqlalchemy-pytds==0.3.1
4+
tink==1.5.0

‎cloud-sql/sql-server/client-side-encryption/snippets/__init__.py‎

Whitespace-only changes.

‎cloud-sql/sql-server/client-side-encryption/snippets/cloud_kms_env_aead.py‎

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,48 @@
1+
# Copyright 2021 Google LLC
2+
#
3+
# Licensed under the Apache License, Version 2.0 (the "License");
4+
# you may not use this file except in compliance with the License.
5+
# You may obtain a copy of the License at
6+
#
7+
# http://www.apache.org/licenses/LICENSE-2.0
8+
#
9+
# Unless required by applicable law or agreed to in writing, software
10+
# distributed under the License is distributed on an "AS IS" BASIS,
11+
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12+
# See the License for the specific language governing permissions and
13+
# limitations under the License.
14+
15+
# [START cloud_sql_sqlserver_cse_key]
16+
import logging
17+
18+
import tink
19+
from tink import aead
20+
from tink.integration import gcpkms
21+
22+
logger = logging.getLogger(__name__)
23+
24+
25+
def init_tink_env_aead(
26+
key_uri: str,
27+
credentials: str) -> tink.aead.KmsEnvelopeAead:
28+
aead.register()
29+
30+
try:
31+
gcp_client = gcpkms.GcpKmsClient(key_uri, credentials)
32+
gcp_aead = gcp_client.get_aead(key_uri)
33+
except tink.TinkError as e:
34+
logger.error("Error initializing GCP client: %s", e)
35+
raise e
36+
37+
# Create envelope AEAD primitive using AES256 GCM for encrypting the data
38+
# This key should only be used for client-side encryption to ensure authenticity and integrity
39+
# of data.
40+
key_template = aead.aead_key_templates.AES256_GCM
41+
env_aead = aead.KmsEnvelopeAead(key_template, gcp_aead)
42+
43+
print(f"Created envelope AEAD Primitive using KMS URI: {key_uri}")
44+
45+
return env_aead
46+
47+
48+
# [END cloud_sql_sqlserver_cse_key]

‎cloud-sql/sql-server/client-side-encryption/snippets/cloud_kms_env_aead_test.py‎

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,37 @@
1+
# Copyright 2021 Google LLC
2+
#
3+
# Licensed under the Apache License, Version 2.0 (the "License");
4+
# you may not use this file except in compliance with the License.
5+
# You may obtain a copy of the License at
6+
#
7+
# http://www.apache.org/licenses/LICENSE-2.0
8+
#
9+
# Unless required by applicable law or agreed to in writing, software
10+
# distributed under the License is distributed on an "AS IS" BASIS,
11+
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12+
# See the License for the specific language governing permissions and
13+
# limitations under the License.
14+
15+
import os
16+
17+
import pytest
18+
19+
from snippets.cloud_kms_env_aead import init_tink_env_aead
20+
21+
22+
@pytest.fixture(name="kms_uri")
23+
def setup() -> str:
24+
kms_uri = "gcp-kms://" + os.environ["CLOUD_KMS_KEY"]
25+
26+
yield kms_uri
27+
28+
29+
def test_cloud_kms_env_aead(
30+
capsys: pytest.CaptureFixture, kms_uri: str) -> None:
31+
credentials = os.environ.get("GOOGLE_APPLICATION_CREDENTIALS", "")
32+
33+
# Create env_aead primitive
34+
init_tink_env_aead(kms_uri, credentials)
35+
36+
captured = capsys.readouterr().out
37+
assert f"Created envelope AEAD Primitive using KMS URI: {kms_uri}" in captured

‎cloud-sql/sql-server/client-side-encryption/snippets/cloud_sql_connection_pool.py‎

Lines changed: 83 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,83 @@
1+
# Copyright 2021 Google LLC
2+
#
3+
# Licensed under the Apache License, Version 2.0 (the "License");
4+
# you may not use this file except in compliance with the License.
5+
# You may obtain a copy of the License at
6+
#
7+
# http://www.apache.org/licenses/LICENSE-2.0
8+
#
9+
# Unless required by applicable law or agreed to in writing, software
10+
# distributed under the License is distributed on an "AS IS" BASIS,
11+
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12+
# See the License for the specific language governing permissions and
13+
# limitations under the License.
14+
15+
# [START cloud_sql_sqlserver_cse_db]
16+
import pytds
17+
import sqlalchemy
18+
from sqlalchemy import Column
19+
from sqlalchemy import DateTime
20+
from sqlalchemy import Integer
21+
from sqlalchemy import Table
22+
23+
24+
def init_tcp_connection_engine(
25+
db_user: str, db_pass: str, db_name: str, db_host: str
26+
) -> sqlalchemy.engine.base.Engine:
27+
# Remember - storing secrets in plaintext is potentially unsafe. Consider using
28+
# something like https://cloud.google.com/secret-manager/docs/overview to help keep
29+
# secrets secret.
30+
31+
# Extract host and port from db_host
32+
host_args = db_host.split(":")
33+
db_hostname, db_port = host_args[0], int(host_args[1])
34+
35+
def connect_with_pytds() -> pytds.Connection:
36+
return pytds.connect(
37+
db_hostname, # e.g. "127.0.0.1"
38+
user=db_user, # e.g. "my-database-user"
39+
password=db_pass, # e.g. "my-database-password"
40+
database=db_name, # e.g. "my-database-name"
41+
port=db_port, # e.g. 1433
42+
bytes_to_unicode=False # disables automatic decoding of bytes
43+
)
44+
45+
pool = sqlalchemy.create_engine(
46+
# This allows us to use the pytds sqlalchemy dialect, but also set the
47+
# bytes_to_unicode flag to False, which is not supported by the dialect
48+
"mssql+pytds://localhost",
49+
creator=connect_with_pytds,
50+
)
51+
52+
print("Created TCP connection pool")
53+
return pool
54+
55+
56+
def init_db(
57+
db_user: str,
58+
db_pass: str,
59+
db_name: str,
60+
db_host: str,
61+
table_name: str,
62+
) -> sqlalchemy.engine.base.Engine:
63+
64+
db = init_tcp_connection_engine(db_user, db_pass, db_name, db_host)
65+
66+
# Create tables (if they don't already exist)
67+
if not db.has_table(table_name):
68+
metadata = sqlalchemy.MetaData(db)
69+
Table(
70+
table_name,
71+
metadata,
72+
Column("vote_id", Integer, primary_key=True, nullable=False),
73+
Column("voter_email", sqlalchemy.types.VARBINARY, nullable=False),
74+
Column("time_cast", DateTime, nullable=False),
75+
Column("team", sqlalchemy.types.VARCHAR(6), nullable=False),
76+
)
77+
metadata.create_all()
78+
79+
print(f"Created table {table_name} in db {db_name}")
80+
return db
81+
82+
83+
# [END cloud_sql_sqlserver_cse_db]

‎cloud-sql/sql-server/client-side-encryption/snippets/cloud_sql_connection_pool_test.py‎

Lines changed: 76 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,76 @@
1+
# Copyright 2021 Google LLC
2+
#
3+
# Licensed under the Apache License, Version 2.0 (the "License");
4+
# you may not use this file except in compliance with the License.
5+
# You may obtain a copy of the License at
6+
#
7+
# http://www.apache.org/licenses/LICENSE-2.0
8+
#
9+
# Unless required by applicable law or agreed to in writing, software
10+
# distributed under the License is distributed on an "AS IS" BASIS,
11+
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12+
# See the License for the specific language governing permissions and
13+
# limitations under the License.
14+
15+
import os
16+
from typing import Dict
17+
import uuid
18+
19+
import pytest
20+
21+
from snippets.cloud_sql_connection_pool import (
22+
init_db,
23+
init_tcp_connection_engine,
24+
)
25+
26+
27+
@pytest.fixture(name="conn_vars")
28+
def setup() -> Dict[str, str]:
29+
try:
30+
conn_vars = {}
31+
conn_vars["db_user"] = os.environ["SQLSERVER_USER"]
32+
conn_vars["db_pass"] = os.environ["SQLSERVER_PASSWORD"]
33+
conn_vars["db_name"] = os.environ["SQLSERVER_DATABASE"]
34+
conn_vars["db_host"] = os.environ["SQLSERVER_HOST"]
35+
conn_vars["instance_conn_name"] = os.environ["SQLSERVER_INSTANCE"]
36+
conn_vars["db_socket_dir"] = os.getenv("DB_SOCKET_DIR", "/cloudsql")
37+
except KeyError:
38+
raise Exception(
39+
"The following env variables must be set to run these tests:"
40+
"SQLSERVER_USER, SQLSERVER_PASSWORD, SQLSERVER_DATABASE, SQLSERVER_HOST, "
41+
"SQLSERVER_INSTANCE")
42+
else:
43+
yield conn_vars
44+
45+
46+
def test_init_tcp_connection_engine(
47+
capsys: pytest.CaptureFixture,
48+
conn_vars: Dict[str, str]) -> None:
49+
50+
init_tcp_connection_engine(
51+
db_user=conn_vars["db_user"],
52+
db_name=conn_vars["db_name"],
53+
db_pass=conn_vars["db_pass"],
54+
db_host=conn_vars["db_host"],
55+
)
56+
57+
captured = capsys.readouterr().out
58+
assert "Created TCP connection pool" in captured
59+
60+
61+
def test_init_db(
62+
capsys: pytest.CaptureFixture,
63+
conn_vars: Dict[str, str]) -> None:
64+
65+
table_name = f"votes_{uuid.uuid4().hex}"
66+
67+
init_db(
68+
db_user=conn_vars["db_user"],
69+
db_name=conn_vars["db_name"],
70+
db_pass=conn_vars["db_pass"],
71+
db_host=conn_vars["db_host"],
72+
table_name=table_name,
73+
)
74+
75+
captured = capsys.readouterr().out
76+
assert f"Created table {table_name} in db {conn_vars['db_name']}" in captured

0 commit comments

Comments
 (0)