Skip to content

Commit 3a7fc1b

Browse files
shubha-rajandandhleeleahecole
authored
feat: add postgres samples for client-side encryption (GoogleCloudPlatform#5670)
* feat: add postgres samples for client-side encryption * update database connection code * address review comments Co-authored-by: Dan Lee <71398022+dandhlee@users.noreply.github.com> Co-authored-by: Leah E. Cole <6719667+leahecole@users.noreply.github.com>
1 parent 1c18559 commit 3a7fc1b

13 files changed

+852
-0
lines changed

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

Lines changed: 134 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,134 @@
1+
# Encrypting fields in Cloud SQL - Postgres 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/postgres/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/postgres/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/postgres/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+
29+
## Running locally
30+
31+
To run this application locally, download and install the `cloud_sql_proxy` by
32+
following the instructions [here](https://cloud.google.com/sql/docs/postgres/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:5432'
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:5432 -credential_file=$GOOGLE_APPLICATION_CREDENTIALS &
60+
```
61+
62+
Note: if you are running a local Postgres server, you will need to turn it off before running the command above or use a different port.
63+
64+
#### Windows/PowerShell
65+
Use these PowerShell commands to initialize environment variables:
66+
```powershell
67+
$env:GOOGLE_APPLICATION_CREDENTIALS="<CREDENTIALS_JSON_FILE>"
68+
$env:DB_HOST="127.0.0.1:5432"
69+
$env:DB_USER="<DB_USER_NAME>"
70+
$env:DB_PASS="<DB_PASSWORD>"
71+
$env:DB_NAME="<DB_NAME>"
72+
$env:GCP_KMS_URI='<GCP_KMS_URI>'
73+
```
74+
Note: Saving credentials in environment variables is convenient, but not secure - consider a more
75+
secure solution such as [Secret Manager](https://cloud.google.com/secret-manager/docs/quickstart) to
76+
help keep secrets safe.
77+
78+
Then use this command to launch the proxy in a separate PowerShell session:
79+
```powershell
80+
Start-Process -filepath "C:\<path to proxy exe>" -ArgumentList "-instances=<project-id>:<region>:<instance-name>=tcp:5432 -credential_file=<CREDENTIALS_JSON_FILE>"
81+
```
82+
83+
Note: if you are running a local Postgres server, you will need to turn it off before running the command above or use a different port.
84+
85+
### Launch proxy with Unix Domain Socket
86+
NOTE: this option is currently only supported on Linux and Mac OS. Windows users should use the
87+
[Launch proxy with TCP](#launch-proxy-with-tcp) option.
88+
89+
To use a Unix socket, you'll need to create a directory for the sockets and
90+
initialize an environment variable containing the directory you just created.
91+
For example:
92+
93+
```bash
94+
export DB_SOCKET_DIR=$(mktemp -d cloudsql)
95+
```
96+
97+
Use these terminal commands to initialize other environment variables as well:
98+
```bash
99+
export GOOGLE_APPLICATION_CREDENTIALS=/path/to/service/account/key.json
100+
export INSTANCE_CONNECTION_NAME='<MY-PROJECT>:<INSTANCE-REGION>:<INSTANCE-NAME>'
101+
export DB_USER='<DB_USER_NAME>'
102+
export DB_PASS='<DB_PASSWORD>'
103+
export DB_NAME='<DB_NAME>'
104+
export GCP_KMS_URI='<GCP_KMS_URI>'
105+
```
106+
Note: Saving credentials in environment variables is convenient, but not secure - consider a more
107+
secure solution such as [Secret Manager](https://cloud.google.com/secret-manager/docs/quickstart) to
108+
help keep secrets safe.
109+
110+
Then use this command to launch the proxy in the background:
111+
```bash
112+
./cloud_sql_proxy -dir=$DB_SOCKET_DIR --instances=$INSTANCE_CONNECTION_NAME --credential_file=$GOOGLE_APPLICATION_CREDENTIALS &
113+
```
114+
115+
### Install requirements
116+
117+
Next, setup install the requirements into a virtual enviroment:
118+
```bash
119+
virtualenv --python python3 env
120+
source env/bin/activate
121+
pip install -r requirements.txt
122+
```
123+
124+
### Run the demo
125+
126+
Add new votes:
127+
```bash
128+
python snippets/encrypt_and_insert_data.py
129+
```
130+
131+
View the collected votes:
132+
```bash
133+
python snippets/query_and_decrypt_data.py
134+
```

‎cloud-sql/postgres/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/postgres/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.3

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

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
SQLAlchemy==1.4.4
2+
pg8000==1.19.2
3+
tink==1.5.0

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

Whitespace-only changes.

‎cloud-sql/postgres/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_postgres_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_postgres_cse_key]

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

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,40 @@
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", None)
32+
if credentials is None:
33+
raise Exception(
34+
"Environment variable GOOGLE_APPLICATION_CREDENTIALS is not set")
35+
36+
# Create env_aead primitive
37+
init_tink_env_aead(kms_uri, credentials)
38+
39+
captured = capsys.readouterr().out
40+
assert f"Created envelope AEAD Primitive using KMS URI: {kms_uri}" in captured

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

Lines changed: 106 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,106 @@
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_postgres_cse_db]
16+
import sqlalchemy
17+
18+
19+
def init_tcp_connection_engine(
20+
db_user: str, db_pass: str, db_name: str, db_host: str
21+
) -> sqlalchemy.engine.base.Engine:
22+
# Remember - storing secrets in plaintext is potentially unsafe. Consider using
23+
# something like https://cloud.google.com/secret-manager/docs/overview to help keep
24+
# secrets secret.
25+
26+
# Extract host and port from db_host
27+
host_args = db_host.split(":")
28+
db_hostname, db_port = host_args[0], int(host_args[1])
29+
30+
pool = sqlalchemy.create_engine(
31+
# Equivalent URL:
32+
# postgresql+pg8000://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>
33+
sqlalchemy.engine.url.URL(
34+
drivername="postgresql+pg8000",
35+
username=db_user, # e.g. "my-database-user"
36+
password=db_pass, # e.g. "my-database-password"
37+
host=db_hostname, # e.g. "127.0.0.1"
38+
port=db_port, # e.g. 5432
39+
database=db_name, # e.g. "my-database-name"
40+
),
41+
)
42+
print("Created TCP connection pool")
43+
return pool
44+
45+
46+
def init_unix_connection_engine(
47+
db_user: str,
48+
db_pass: str,
49+
db_name: str,
50+
cloud_sql_connection_name: str,
51+
db_socket_dir: str,
52+
) -> sqlalchemy.engine.base.Engine:
53+
# Remember - storing secrets in plaintext is potentially unsafe. Consider using
54+
# something like https://cloud.google.com/secret-manager/docs/overview to help keep
55+
# secrets secret.
56+
57+
pool = sqlalchemy.create_engine(
58+
# Equivalent URL:
59+
# mpostgresql+pg8000://<db_user>:<db_pass>@/<db_name>?unix_socket=<socket_path>/<cloud_sql_instance_name>
60+
sqlalchemy.engine.url.URL(
61+
drivername="postgresql+pg8000",
62+
username=db_user, # e.g. "my-database-user"
63+
password=db_pass, # e.g. "my-database-password"
64+
database=db_name, # e.g. "my-database-name"
65+
query={
66+
"unix_sock": "{}/{}/.s.PGSQL.5432".format(
67+
db_socket_dir, # e.g. "/cloudsql"
68+
cloud_sql_connection_name) # i.e "<PROJECT-NAME>:<INSTANCE-REGION>:<INSTANCE-NAME>"
69+
}
70+
),
71+
)
72+
print("Created Unix socket connection pool")
73+
return pool
74+
75+
76+
def init_db(
77+
db_user: str,
78+
db_pass: str,
79+
db_name: str,
80+
table_name: str,
81+
cloud_sql_connection_name: str = None,
82+
db_socket_dir: str = None,
83+
db_host: str = None,
84+
) -> sqlalchemy.engine.base.Engine:
85+
86+
if db_host:
87+
db = init_tcp_connection_engine(db_user, db_pass, db_name, db_host)
88+
else:
89+
db = init_unix_connection_engine(
90+
db_user, db_pass, db_name, cloud_sql_connection_name, db_socket_dir
91+
)
92+
93+
# Create tables (if they don't already exist)
94+
with db.connect() as conn:
95+
conn.execute(
96+
f"CREATE TABLE IF NOT EXISTS {table_name} "
97+
"( vote_id SERIAL NOT NULL, time_cast timestamp NOT NULL, "
98+
"team VARCHAR(6) NOT NULL, voter_email BYTEA, "
99+
"PRIMARY KEY (vote_id) );"
100+
)
101+
102+
print(f"Created table {table_name} in db {db_name}")
103+
return db
104+
105+
106+
# [END cloud_sql_postgres_cse_db]

0 commit comments

Comments
 (0)