About client-side encryption

importcom.zaxxer.hikari.HikariConfig;
importcom.zaxxer.hikari.HikariDataSource;
importjava.security.GeneralSecurityException;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.SQLException;
importjavax.sql.DataSource;
publicclass CloudSqlConnectionPool{
publicstaticDataSourcecreateConnectionPool(StringdbUser,StringdbPass,StringdbName,
StringinstanceConnectionName)throwsGeneralSecurityException{
HikariConfigconfig=newHikariConfig();
config.setJdbcUrl(String.format("jdbc:postgresql:///%s",dbName));
config.setUsername(dbUser);// e.g. "root", "postgres"
config.setPassword(dbPass);// e.g. "my-password"
config.addDataSourceProperty("socketFactory","com.google.cloud.sql.postgres.SocketFactory");
config.addDataSourceProperty("cloudSqlInstance",instanceConnectionName);
DataSourcepool=newHikariDataSource(config);
returnpool;
}
publicstaticvoidcreateTable(DataSourcepool,StringtableName)throwsSQLException{
// Safely attempt to create the table schema.
try(Connectionconn=pool.getConnection()){
Stringstmt=String.format("CREATE TABLE IF NOT EXISTS %s ( "
+"vote_id SERIAL NOT NULL, time_cast timestamp NOT NULL, team CHAR(6) NOT NULL,"
+"voter_email BYTEA, PRIMARY KEY (vote_id) );",tableName);
try(PreparedStatementcreateTableStatement=conn.prepareStatement(stmt);){
createTableStatement.execute();
}
}
}
}

importsqlalchemy
definit_tcp_connection_engine(
 db_user: str, db_pass: str, db_name: str, db_host: str
) -> sqlalchemy.engine.base.Engine:
"""
 Creates a connection to the database using tcp socket.
 """
 # 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.
 # Extract host and port from db_host
 host_args = db_host.split(":")
 db_hostname, db_port = host_args[0], int(host_args[1])
 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"
 ),
 )
 print("Created TCP connection pool")
 return pool
definit_unix_connection_engine(
 db_user: str,
 db_pass: str,
 db_name: str,
 instance_connection_name: str,
 db_socket_dir: str,
) -> sqlalchemy.engine.base.Engine:
"""
 Creates a connection to the database using unix socket.
 """
 # 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.
 pool = sqlalchemy.create_engine(
 # Equivalent URL:
 # mpostgresql+pg8000://<db_user>:<db_pass>@/<db_name>?unix_socket=<socket_path>/<cloud_sql_instance_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"
 database=db_name, # e.g. "my-database-name"
 query={
 "unix_sock": "{}/{}/.s.PGSQL.5432".format(
 db_socket_dir, instance_connection_name # e.g. "/cloudsql"
 ) # i.e "<PROJECT-NAME>:<INSTANCE-REGION>:<INSTANCE-NAME>"
 },
 ),
 )
 print("Created Unix socket connection pool")
 return pool
definit_db(
 db_user: str,
 db_pass: str,
 db_name: str,
 table_name: str,
 instance_connection_name: str = None,
 db_socket_dir: str = None,
 db_host: str = None,
) -> sqlalchemy.engine.base.Engine:
"""Starts a connection to the database and creates voting table if it doesn't exist."""
 if db_host:
 db = init_tcp_connection_engine(db_user, db_pass, db_name, db_host)
 else:
 db = init_unix_connection_engine(
 db_user, db_pass, db_name, instance_connection_name, db_socket_dir
 )
 # Create tables (if they don't already exist)
 with db.connect() as conn:
 conn.execute(
 f"CREATE TABLE IF NOT EXISTS {table_name} "
 "( vote_id SERIAL NOT NULL, time_cast timestamp NOT NULL, "
 "team VARCHAR(6) NOT NULL, voter_email BYTEA, "
 "PRIMARY KEY (vote_id) );"
 )
 print(f"Created table {table_name} in db {db_name}")
 return db

importcom.google.crypto.tink.Aead;
importcom.google.crypto.tink.KmsClient;
importcom.google.crypto.tink.aead.AeadConfig;
importcom.google.crypto.tink.aead.AeadKeyTemplates;
importcom.google.crypto.tink.aead.KmsEnvelopeAead;
importcom.google.crypto.tink.integration.gcpkms.GcpKmsClient;
importjava.security.GeneralSecurityException;
publicclass CloudKmsEnvelopeAead{
publicstaticAeadget(StringkmsUri)throwsGeneralSecurityException{
AeadConfig.register();
// Create a new KMS Client
KmsClientclient=newGcpKmsClient().withDefaultCredentials();
// Create an AEAD primitive using the Cloud KMS key
AeadgcpAead=client.getAead(kmsUri);
// Create an envelope AEAD primitive.
// This key should only be used for client-side encryption to ensure authenticity and integrity
// of data.
returnnewKmsEnvelopeAead(AeadKeyTemplates.AES128_GCM,gcpAead);
}
}

importlogging
importtink
fromtinkimport aead
fromtink.integrationimport gcpkms
logger = logging.getLogger(__name__)
definit_tink_env_aead(key_uri: str, credentials: str) -> tink.aead.KmsEnvelopeAead:
"""
 Initiates the Envelope AEAD object using the KMS credentials.
 """
 aead.register()
 try:
 gcp_client = gcpkms.GcpKmsClient(key_uri, credentials)
 gcp_aead = gcp_client.get_aead(key_uri)
 except tink.TinkError as e:
 logger.error("Error initializing GCP client: %s", e)
 raise e
 # Create envelope AEAD primitive using AES256 GCM for encrypting the data
 # This key should only be used for client-side encryption to ensure authenticity and integrity
 # of data.
 key_template = aead.aead_key_templates.AES256_GCM
 env_aead = aead.KmsEnvelopeAead(key_template, gcp_aead)
 print(f"Created envelope AEAD Primitive using KMS URI: {key_uri}")
 return env_aead

importcom.google.crypto.tink.Aead;
importjava.security.GeneralSecurityException;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.SQLException;
importjava.sql.Timestamp;
importjava.util.Date;
importjavax.sql.DataSource;
publicclass EncryptAndInsertData{
publicstaticvoidmain(String[]args)throwsGeneralSecurityException,SQLException{
// Saving credentials in environment variables is convenient, but not secure - consider a more
// secure solution such as Cloud Secret Manager to help keep secrets safe.
StringdbUser=System.getenv("DB_USER");// e.g. "root", "postgres"
StringdbPass=System.getenv("DB_PASS");// e.g. "mysupersecretpassword"
StringdbName=System.getenv("DB_NAME");// e.g. "votes_db"
StringinstanceConnectionName=
System.getenv("INSTANCE_CONNECTION_NAME");// e.g. "project-name:region:instance-name"
StringkmsUri=System.getenv("CLOUD_KMS_URI");// e.g. "gcp-kms://projects/...path/to/key
// Tink uses the "gcp-kms://" prefix for paths to keys stored in Google Cloud KMS. For more
// info on creating a KMS key and getting its path, see
// https://cloud.google.com/kms/docs/quickstart
Stringteam="TABS";
StringtableName="votes";
Stringemail="hello@example.com";
// Initialize database connection pool and create table if it does not exist
// See CloudSqlConnectionPool.java for setup details
DataSourcepool=
CloudSqlConnectionPool.createConnectionPool(dbUser,dbPass,dbName,instanceConnectionName);
CloudSqlConnectionPool.createTable(pool,tableName);
// Initialize envelope AEAD
// See CloudKmsEnvelopeAead.java for setup details
AeadenvAead=CloudKmsEnvelopeAead.get(kmsUri);
encryptAndInsertData(pool,envAead,tableName,team,email);
}
publicstaticvoidencryptAndInsertData(
DataSourcepool,AeadenvAead,StringtableName,Stringteam,Stringemail)
throwsGeneralSecurityException,SQLException{
try(Connectionconn=pool.getConnection()){
Stringstmt=
String.format(
"INSERT INTO %s (team, time_cast, voter_email) VALUES (?, ?, ?);",tableName);
try(PreparedStatementvoteStmt=conn.prepareStatement(stmt);){
voteStmt.setString(1,team);
voteStmt.setTimestamp(2,newTimestamp(newDate().getTime()));
// Use the envelope AEAD primitive to encrypt the email, using the team name as
// associated data. This binds the encryption of the email to the team name, preventing
// associating an encrypted email in one row with a team name in another row.
byte[]encryptedEmail=envAead.encrypt(email.getBytes(),team.getBytes());
voteStmt.setBytes(3,encryptedEmail);
// Finally, execute the statement. If it fails, an error will be thrown.
voteStmt.execute();
System.out.println(String.format("Successfully inserted row into table %s",tableName));
}
}
}
}

importdatetime
importlogging
importos
importsqlalchemy
importtink
from.cloud_kms_env_aeadimport init_tink_env_aead
from.cloud_sql_connection_poolimport init_db
logger = logging.getLogger(__name__)
defmain() -> None:
"""
 Connects to the database, encrypts and inserts some data.
 """
 db_user = os.environ["DB_USER"] # e.g. "root", "postgres"
 db_pass = os.environ["DB_PASS"] # e.g. "mysupersecretpassword"
 db_name = os.environ["DB_NAME"] # e.g. "votes_db"
 # Set if connecting using TCP:
 db_host = os.environ["DB_HOST"] # e.g. "127.0.0.1"
 # Set if connecting using Unix sockets:
 db_socket_dir = os.environ.get("DB_SOCKET_DIR", "/cloudsql")
 instance_connection_name = os.environ["INSTANCE_CONNECTION_NAME"]
 # e.g. "project-name:region:instance-name"
 credentials = os.environ.get("GOOGLE_APPLICATION_CREDENTIALS", "")
 key_uri = "gcp-kms://" + os.environ["GCP_KMS_URI"]
 # e.g. "gcp-kms://projects/...path/to/key
 # Tink uses the "gcp-kms://" prefix for paths to keys stored in Google
 # Cloud KMS. For more info on creating a KMS key and getting its path, see
 # https://cloud.google.com/kms/docs/quickstart
 table_name = "votes"
 team = "TABS"
 email = "hello@example.com"
 env_aead = init_tink_env_aead(key_uri, credentials)
 db = init_db(
 db_user,
 db_pass,
 db_name,
 table_name,
 instance_connection_name,
 db_socket_dir,
 db_host,
 )
 encrypt_and_insert_data(db, env_aead, table_name, team, email)
defencrypt_and_insert_data(
 db: sqlalchemy.engine.base.Engine,
 env_aead: tink.aead.KmsEnvelopeAead,
 table_name: str,
 team: str,
 email: str,
) -> None:
"""
 Inserts a vote into the database with email address previously encrypted using
 a KmsEnvelopeAead object.
 """
 time_cast = datetime.datetime.now(tz=datetime.timezone.utc)
 # Use the envelope AEAD primitive to encrypt the email, using the team name as
 # associated data. Encryption with associated data ensures authenticity
 # (who the sender is) and integrity (the data has not been tampered with) of that
 # data, but not its secrecy. (see RFC 5116 for more info)
 encrypted_email = env_aead.encrypt(email.encode(), team.encode())
 # Verify that the team is one of the allowed options
 if team != "TABS" and team != "SPACES":
 logger.error(f"Invalid team specified: {team}")
 return
 # Preparing a statement before hand can help protect against injections.
 stmt = sqlalchemy.text(
 f"INSERT INTO {table_name} (time_cast, team, voter_email)"
 " VALUES (:time_cast, :team, :voter_email)"
 )
 # Using a with statement ensures that the connection is always released
 # back into the pool at the end of statement (even if an error occurs)
 with db.connect() as conn:
 conn.execute(stmt, time_cast=time_cast, team=team, voter_email=encrypted_email)
 print(f"Vote successfully cast for '{team}' at time {time_cast}!")

importcom.google.crypto.tink.Aead;
importjava.security.GeneralSecurityException;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.sql.Timestamp;
importjavax.sql.DataSource;
publicclass QueryAndDecryptData{
publicstaticvoidmain(String[]args)throwsGeneralSecurityException,SQLException{
// Saving credentials in environment variables is convenient, but not secure - consider a more
// secure solution such as Cloud Secret Manager to help keep secrets safe.
StringdbUser=System.getenv("DB_USER");// e.g. "root", "postgres"
StringdbPass=System.getenv("DB_PASS");// e.g. "mysupersecretpassword"
StringdbName=System.getenv("DB_NAME");// e.g. "votes_db"
StringinstanceConnectionName=
System.getenv("INSTANCE_CONNECTION_NAME");// e.g. "project-name:region:instance-name"
StringkmsUri=System.getenv("CLOUD_KMS_URI");// e.g. "gcp-kms://projects/...path/to/key
// Tink uses the "gcp-kms://" prefix for paths to keys stored in Google Cloud KMS. For more
// info on creating a KMS key and getting its path, see
// https://cloud.google.com/kms/docs/quickstart
StringtableName="votes123";
// Initialize database connection pool and create table if it does not exist
// See CloudSqlConnectionPool.java for setup details
DataSourcepool=
CloudSqlConnectionPool.createConnectionPool(dbUser,dbPass,dbName,instanceConnectionName);
CloudSqlConnectionPool.createTable(pool,tableName);
// Initialize envelope AEAD
// See CloudKmsEnvelopeAead.java for setup details
AeadenvAead=CloudKmsEnvelopeAead.get(kmsUri);
// Insert row into table to test
// See EncryptAndInsert.java for setup details
EncryptAndInsertData.encryptAndInsertData(
pool,envAead,tableName,"SPACES","hello@example.com");
queryAndDecryptData(pool,envAead,tableName);
}
publicstaticvoidqueryAndDecryptData(DataSourcepool,AeadenvAead,StringtableName)
throwsGeneralSecurityException,SQLException{
try(Connectionconn=pool.getConnection()){
Stringstmt=
String.format(
"SELECT team, time_cast, voter_email FROM %s ORDER BY time_cast DESC LIMIT 5",
tableName);
try(PreparedStatementvoteStmt=conn.prepareStatement(stmt);){
ResultSetvoteResults=voteStmt.executeQuery();
System.out.println("Team\tTime Cast\tEmail");
while(voteResults.next()){
Stringteam=voteResults.getString(1);
TimestamptimeCast=voteResults.getTimestamp(2);
// Postgres pads CHAR fields with spaces. These will need to be removed before
// decrypting.
Stringaad=voteResults.getString(1).trim();
// Use the envelope AEAD primitive to encrypt the email, using the team name as
// associated data. This binds the encryption of the email to the team name, preventing
// associating an encrypted email in one row with a team name in another row.
Stringemail=newString(envAead.decrypt(voteResults.getBytes(3),aad.getBytes()));
System.out.println(String.format("%s\t%s\t%s",team,timeCast,email));
}
}
}
}
}

importos
importsqlalchemy
importtink
from.cloud_kms_env_aeadimport init_tink_env_aead
from.cloud_sql_connection_poolimport init_db
from.encrypt_and_insert_dataimport encrypt_and_insert_data
defmain() -> None:
"""
 Connects to the database, inserts encrypted data and retrieves encrypted data.
 """
 db_user = os.environ["DB_USER"] # e.g. "root", "postgres"
 db_pass = os.environ["DB_PASS"] # e.g. "mysupersecretpassword"
 db_name = os.environ["DB_NAME"] # e.g. "votes_db"
 # Set if connecting using TCP:
 db_host = os.environ["DB_HOST"] # e.g. "127.0.0.1"
 # Set if connecting using Unix sockets:
 db_socket_dir = os.environ.get("DB_SOCKET_DIR", "/cloudsql")
 instance_connection_name = os.environ["INSTANCE_CONNECTION_NAME"]
 # e.g. "project-name:region:instance-name"
 credentials = os.environ.get("GOOGLE_APPLICATION_CREDENTIALS", "")
 key_uri = "gcp-kms://" + os.environ["GCP_KMS_URI"]
 # e.g. "gcp-kms://projects/...path/to/key
 # Tink uses the "gcp-kms://" prefix for paths to keys stored in Google
 # Cloud KMS. For more info on creating a KMS key and getting its path, see
 # https://cloud.google.com/kms/docs/quickstart
 table_name = "votes"
 team = "TABS"
 email = "hello@example.com"
 env_aead = init_tink_env_aead(key_uri, credentials)
 db = init_db(
 db_user,
 db_pass,
 db_name,
 table_name,
 instance_connection_name,
 db_socket_dir,
 db_host,
 )
 encrypt_and_insert_data(db, env_aead, table_name, team, email)
 query_and_decrypt_data(db, env_aead, table_name)
defquery_and_decrypt_data(
 db: sqlalchemy.engine.base.Engine,
 env_aead: tink.aead.KmsEnvelopeAead,
 table_name: str,
) -> list[tuple[str]]:
"""
 Retrieves data from the database and decrypts it using the KmsEnvelopeAead object.
 """
 with db.connect() as conn:
 # Execute the query and fetch all results
 recent_votes = conn.execute(
 f"SELECT team, time_cast, voter_email FROM {table_name} "
 "ORDER BY time_cast DESC LIMIT 5"
 ).fetchall()
 print("Team\tEmail\tTime Cast")
 output = []
 for row in recent_votes:
 team = row[0]
 # Postgres pads CHAR fields with spaces. These will need to be removed before
 # decrypting.
 aad = team.rstrip()
 # Use the envelope AEAD primitive to decrypt the email, using the team name as
 # associated data. Encryption with associated data ensures authenticity
 # (who the sender is) and integrity (the data has not been tampered with) of that
 # data, but not its secrecy. (see RFC 5116 for more info)
 email = env_aead.decrypt(row[2], aad.encode()).decode()
 time_cast = row[1]
 # Print recent votes
 print(f"{team}\t{email}\t{time_cast}")
 output.append((team, email, time_cast))
 return output