Skip to content

Commit 0753711

Browse files
shubha-rajanshubha-rajan
authored
feat: add Cloud SQL samples for Client-side Encryption (GoogleCloudPlatform#4821)
* samples and integration tests for client-side encryption with Tink * move tink initialization to separate file * add README.md * linting fixes * fix env var name in tests * use encryptAndInsertData method in QueryAndDecryptDataIT * linting fixes * address review comments * add comments pointing to source code files for setup steps * linting * rename region tags
1 parent b7b1121 commit 0753711

File tree

9 files changed

+651
-0
lines changed

9 files changed

+651
-0
lines changed

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

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,40 @@
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 Java Development Environment (including google-cloud-sdk and
6+
maven utilities) by following the [java setup guide](https://cloud.google.com/java/docs/setup) and
7+
[create a project](https://cloud.google.com/resource-manager/docs/creating-managing-projects#creating_a_project).
8+
9+
1. Create a 2nd Gen Cloud SQL Instance by following these
10+
[instructions](https://cloud.google.com/sql/docs/mysql/create-instance). Note the connection string,
11+
database user, and database password that you create.
12+
13+
1. Create a database for your application by following these
14+
[instructions](https://cloud.google.com/sql/docs/mysql/create-manage-databases). Note the database
15+
name.
16+
17+
1. Create a KMS key for your application by following these
18+
[instructions](https://cloud.google.com/kms/docs/creating-keys). Copy the resource name of your
19+
created key.
20+
21+
1. Create a service account with the 'Cloud SQL Client' permissions by following these
22+
[instructions](https://cloud.google.com/sql/docs/mysql/connect-external-app#4_if_required_by_your_authentication_method_create_a_service_account).
23+
Then, add the 'Cloud KMS CryptoKey Encrypter/Decrypter' permission for the key to your service account
24+
by following these [instructions](https://cloud.google.com/kms/docs/iam).
25+
26+
## Running Locally
27+
28+
Before running, copy the `example.envrc` file to `.envrc` and replace the values for
29+
`GOOGLE_APPLICATION_CREDENTIALS`, `DB_USER`, `DB_PASS`, `DB_NAME`, `CLOUD_SQL_CONNECTION_NAME`,
30+
and `CLOUD_KMS_URI` with the values from your project. Then run `source .envrc` or optionally use
31+
[direnv](https://direnv.net/).
32+
33+
Once the environment variables have been set, run:
34+
```
35+
mvn exec:java -Dexec.mainClass=cloudsql.tink.EncryptAndInsertData
36+
```
37+
and
38+
```
39+
mvn exec:java -Dexec.mainClass=cloudsql.tink.QueryAndDecryptData
40+
```

‎cloud-sql/mysql/client-side-encryption/example.envrc‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,6 @@
1+
GOOGLE_APPLICATION_CREDENTIALS='path/to/service-account-key.json'
2+
DB_USER='your-database-username'
3+
DB_PASS='your-database-password'
4+
DB_NAME='your_database_name'
5+
CLOUD_SQL_CONNECTION_NAME='project:region:instance-name'
6+
CLOUD_KMS_URI='gcp-kms://your-kms-uri`

‎cloud-sql/mysql/client-side-encryption/pom.xml‎

Lines changed: 101 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,101 @@
1+
<!--
2+
Copyright 2021 Google LLC
3+
4+
Licensed under the Apache License, Version 2.0 (the "License");
5+
you may not use this file except in compliance with the License.
6+
You may obtain a copy of the License at
7+
8+
http://www.apache.org/licenses/LICENSE-2.0
9+
10+
Unless required by applicable law or agreed to in writing, software
11+
distributed under the License is distributed on an "AS IS" BASIS,
12+
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13+
See the License for the specific language governing permissions and
14+
limitations under the License.
15+
-->
16+
<project>
17+
<modelVersion>4.0.0</modelVersion>
18+
<packaging>jar</packaging>
19+
<version>1.0-SNAPSHOT</version>
20+
<groupId>com.google.cloud</groupId>
21+
<artifactId>cloud-sql-tink-mysql</artifactId>
22+
<name>Cloud SQL Client Side Encryption Samples</name>
23+
24+
<!--
25+
The parent pom defines common style checks and testing strategies for our samples.
26+
Removing or replacing it should not affect the execution of the samples in anyway.
27+
-->
28+
<parent>
29+
<groupId>com.google.cloud.samples</groupId>
30+
<artifactId>shared-configuration</artifactId>
31+
<version>1.0.21</version>
32+
</parent>
33+
34+
<properties>
35+
<maven.compiler.target>1.8</maven.compiler.target>
36+
<maven.compiler.source>1.8</maven.compiler.source>
37+
</properties>
38+
39+
<dependencyManagement>
40+
<dependencies>
41+
<dependency>
42+
<groupId>com.google.cloud</groupId>
43+
<artifactId>libraries-bom</artifactId>
44+
<version>18.0.0</version>
45+
<type>pom</type>
46+
<scope>import</scope>
47+
</dependency>
48+
<dependency>
49+
<groupId>com.google.api-client</groupId>
50+
<artifactId>google-api-client</artifactId>
51+
<version>1.31.3</version>
52+
</dependency>
53+
</dependencies>
54+
</dependencyManagement>
55+
56+
<dependencies>
57+
<dependency>
58+
<groupId>com.google.http-client</groupId>
59+
<artifactId>google-http-client-jackson2</artifactId>
60+
<version>1.39.0</version>
61+
</dependency>
62+
<dependency>
63+
<groupId>com.google.cloud.sql</groupId>
64+
<artifactId>mysql-socket-factory-connector-j-8</artifactId>
65+
<version>1.2.1</version>
66+
</dependency>
67+
<dependency>
68+
<groupId>mysql</groupId>
69+
<artifactId>mysql-connector-java</artifactId>
70+
<version>8.0.23</version>
71+
</dependency>
72+
<dependency>
73+
<groupId>com.google.crypto.tink</groupId>
74+
<artifactId>tink</artifactId>
75+
<version>1.5.0</version>
76+
</dependency>
77+
<dependency>
78+
<groupId>com.google.crypto.tink</groupId>
79+
<artifactId>tink-gcpkms</artifactId>
80+
<version>1.5.0</version>
81+
</dependency>
82+
<dependency>
83+
<groupId>com.zaxxer</groupId>
84+
<artifactId>HikariCP</artifactId>
85+
<version>4.0.2</version>
86+
</dependency>
87+
<dependency>
88+
<groupId>junit</groupId>
89+
<artifactId>junit</artifactId>
90+
<version>4.13.2</version>
91+
<scope>test</scope>
92+
</dependency>
93+
<dependency>
94+
<groupId>com.google.truth</groupId>
95+
<artifactId>truth</artifactId>
96+
<version>1.1.2</version>
97+
<scope>test</scope>
98+
</dependency>
99+
</dependencies>
100+
101+
</project>

‎cloud-sql/mysql/client-side-encryption/src/main/java/cloudsql/tink/CloudKmsEnvelopeAead.java‎

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,47 @@
1+
/*
2+
* Copyright 2021 Google LLC
3+
*
4+
* Licensed under the Apache License, Version 2.0 (the "License");
5+
* you may not use this file except in compliance with the License.
6+
* You may obtain a copy of the License at
7+
*
8+
* http://www.apache.org/licenses/LICENSE-2.0
9+
*
10+
* Unless required by applicable law or agreed to in writing, software
11+
* distributed under the License is distributed on an "AS IS" BASIS,
12+
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13+
* See the License for the specific language governing permissions and
14+
* limitations under the License.
15+
*/
16+
17+
package cloudsql.tink;
18+
19+
// [START cloud_sql_mysql_cse_key]
20+
21+
import com.google.crypto.tink.Aead;
22+
import com.google.crypto.tink.KeysetHandle;
23+
import com.google.crypto.tink.KmsClients;
24+
import com.google.crypto.tink.aead.AeadConfig;
25+
import com.google.crypto.tink.aead.AeadKeyTemplates;
26+
import com.google.crypto.tink.integration.gcpkms.GcpKmsClient;
27+
import com.google.crypto.tink.proto.KeyTemplate;
28+
import java.security.GeneralSecurityException;
29+
30+
public class CloudKmsEnvelopeAead {
31+
32+
public static Aead get(String kmsUri) throws GeneralSecurityException {
33+
AeadConfig.register();
34+
// Generate a new envelope key template, then generate key material.
35+
KeyTemplate kmsEnvKeyTemplate = AeadKeyTemplates
36+
.createKmsEnvelopeAeadKeyTemplate(kmsUri, AeadKeyTemplates.AES128_GCM);
37+
KeysetHandle keysetHandle = KeysetHandle.generateNew(kmsEnvKeyTemplate);
38+
39+
// Register the KMS client.
40+
KmsClients.add(new GcpKmsClient()
41+
.withDefaultCredentials());
42+
43+
// Create envelope AEAD primitive from keysetHandle
44+
return keysetHandle.getPrimitive(Aead.class);
45+
}
46+
}
47+
// [END cloud_sql_mysql_cse_key]

‎cloud-sql/mysql/client-side-encryption/src/main/java/cloudsql/tink/CloudSqlConnectionPool.java‎

Lines changed: 55 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,55 @@
1+
/*
2+
* Copyright 2021 Google LLC
3+
*
4+
* Licensed under the Apache License, Version 2.0 (the "License");
5+
* you may not use this file except in compliance with the License.
6+
* You may obtain a copy of the License at
7+
*
8+
* http://www.apache.org/licenses/LICENSE-2.0
9+
*
10+
* Unless required by applicable law or agreed to in writing, software
11+
* distributed under the License is distributed on an "AS IS" BASIS,
12+
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13+
* See the License for the specific language governing permissions and
14+
* limitations under the License.
15+
*/
16+
17+
package cloudsql.tink;
18+
19+
// [START cloud_sql_mysql_cse_db]
20+
21+
import com.zaxxer.hikari.HikariConfig;
22+
import com.zaxxer.hikari.HikariDataSource;
23+
import java.security.GeneralSecurityException;
24+
import java.sql.Connection;
25+
import java.sql.PreparedStatement;
26+
import java.sql.SQLException;
27+
import javax.sql.DataSource;
28+
29+
public class CloudSqlConnectionPool {
30+
31+
public static DataSource createConnectionPool(String dbUser, String dbPass, String dbName,
32+
String cloudSqlConnectionName) throws GeneralSecurityException {
33+
HikariConfig config = new HikariConfig();
34+
config.setJdbcUrl(String.format("jdbc:mysql:///%s", dbName));
35+
config.setUsername(dbUser);
36+
config.setPassword(dbPass);
37+
config.addDataSourceProperty("socketFactory", "com.google.cloud.sql.mysql.SocketFactory");
38+
config.addDataSourceProperty("cloudSqlInstance", cloudSqlConnectionName);
39+
DataSource pool = new HikariDataSource(config);
40+
return pool;
41+
}
42+
43+
public static void createTable(DataSource pool, String tableName) throws SQLException {
44+
// Safely attempt to create the table schema.
45+
try (Connection conn = pool.getConnection()) {
46+
String stmt = String.format("CREATE TABLE IF NOT EXISTS %s ( "
47+
+ "vote_id SERIAL NOT NULL, time_cast timestamp NOT NULL, team CHAR(6) NOT NULL,"
48+
+ "voter_email VARBINARY(255), PRIMARY KEY (vote_id) );", tableName);
49+
try (PreparedStatement createTableStatement = conn.prepareStatement(stmt);) {
50+
createTableStatement.execute();
51+
}
52+
}
53+
}
54+
}
55+
// [END cloud_sql_mysql_cse_db]

‎cloud-sql/mysql/client-side-encryption/src/main/java/cloudsql/tink/EncryptAndInsertData.java‎

Lines changed: 82 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,82 @@
1+
/*
2+
* Copyright 2021 Google LLC
3+
*
4+
* Licensed under the Apache License, Version 2.0 (the "License");
5+
* you may not use this file except in compliance with the License.
6+
* You may obtain a copy of the License at
7+
*
8+
* http://www.apache.org/licenses/LICENSE-2.0
9+
*
10+
* Unless required by applicable law or agreed to in writing, software
11+
* distributed under the License is distributed on an "AS IS" BASIS,
12+
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13+
* See the License for the specific language governing permissions and
14+
* limitations under the License.
15+
*/
16+
17+
package cloudsql.tink;
18+
19+
// [START cloud_sql_mysql_cse_insert]
20+
21+
import com.google.crypto.tink.Aead;
22+
import java.security.GeneralSecurityException;
23+
import java.sql.Connection;
24+
import java.sql.PreparedStatement;
25+
import java.sql.SQLException;
26+
import java.sql.Timestamp;
27+
import java.util.Date;
28+
import javax.sql.DataSource;
29+
30+
public class EncryptAndInsertData {
31+
32+
public static void main(String[] args) throws GeneralSecurityException, SQLException {
33+
// Saving credentials in environment variables is convenient, but not secure - consider a more
34+
// secure solution such as Cloud Secret Manager to help keep secrets safe.
35+
String dbUser = System.getenv("DB_USER"); // e.g. "root", "mysql"
36+
String dbPass = System.getenv("DB_PASS"); // e.g. "mysupersecretpassword"
37+
String dbName = System.getenv("DB_NAME"); // e.g. "votes_db"
38+
String cloudSqlConnectionName =
39+
System.getenv("CLOUD_SQL_CONNECTION_NAME"); // e.g. "project-name:region:instance-name"
40+
String kmsUri = System.getenv("CLOUD_KMS_URI"); // e.g. "gcp-kms://projects/...path/to/key
41+
42+
String team = "TABS";
43+
String tableName = "votes";
44+
String email = "hello@example.com";
45+
46+
// Initialize database connection pool and create table if it does not exist
47+
// See CloudSqlConnectionPool.java for setup details
48+
DataSource pool = CloudSqlConnectionPool
49+
.createConnectionPool(dbUser, dbPass, dbName, cloudSqlConnectionName);
50+
CloudSqlConnectionPool.createTable(pool, tableName);
51+
52+
// Initialize envelope AEAD
53+
// See CloudKmsEnvelopeAead.java for setup details
54+
Aead envAead = CloudKmsEnvelopeAead.get(kmsUri);
55+
56+
encryptAndInsertData(pool, envAead, tableName, team, email);
57+
}
58+
59+
public static void encryptAndInsertData(DataSource pool, Aead envAead, String tableName,
60+
String team, String email)
61+
throws GeneralSecurityException, SQLException {
62+
63+
try (Connection conn = pool.getConnection()) {
64+
String stmt = String.format(
65+
"INSERT INTO %s (team, time_cast, voter_email) VALUES (?, ?, ?);", tableName);
66+
try (PreparedStatement voteStmt = conn.prepareStatement(stmt);) {
67+
voteStmt.setString(1, team);
68+
voteStmt.setTimestamp(2, new Timestamp(new Date().getTime()));
69+
70+
// Use the envelope AEAD primitive to encrypt the email, using the team name as
71+
// associated data
72+
byte[] encryptedEmail = envAead.encrypt(email.getBytes(), team.getBytes());
73+
voteStmt.setBytes(3, encryptedEmail);
74+
75+
// Finally, execute the statement. If it fails, an error will be thrown.
76+
voteStmt.execute();
77+
System.out.println(String.format("Successfully inserted row into table %s", tableName));
78+
}
79+
}
80+
}
81+
}
82+
// [END cloud_sql_mysql_cse_insert]

0 commit comments

Comments
 (0)