importcom.zaxxer.hikari.HikariConfig;importcom.zaxxer.hikari.HikariDataSource;importjava.security.GeneralSecurityException;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.SQLException;importjavax.sql.DataSource;publicclassCloudSqlConnectionPool{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();}}}}

importsqlalchemydefinit_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_hosthost_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. 5432database=db_name,# e.g. "my-database-name"),)print("Created TCP connection pool")returnpooldefinit_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")returnpooldefinit_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."""ifdb_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)withdb.connect()asconn: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}")returndb

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;publicclassCloudKmsEnvelopeAead{publicstaticAeadget(StringkmsUri)throwsGeneralSecurityException{AeadConfig.register();// Create a new KMS ClientKmsClientclient=newGcpKmsClient().withDefaultCredentials();// Create an AEAD primitive using the Cloud KMS keyAeadgcpAead=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);}}

importloggingimporttinkfromtinkimportaeadfromtink.integrationimportgcpkmslogger=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)excepttink.TinkErrorase:logger.error("Error initializing GCP client:%s",e)raisee# 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_GCMenv_aead=aead.KmsEnvelopeAead(key_template,gcp_aead)print(f"Created envelope AEAD Primitive using KMS URI:{key_uri}")returnenv_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;publicclassEncryptAndInsertData{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/quickstartStringteam="TABS";StringtableName="votes";Stringemail="hello@example.com";// Initialize database connection pool and create table if it does not exist// See CloudSqlConnectionPool.java for setup detailsDataSourcepool=CloudSqlConnectionPool.createConnectionPool(dbUser,dbPass,dbName,instanceConnectionName);CloudSqlConnectionPool.createTable(pool,tableName);// Initialize envelope AEAD// See CloudKmsEnvelopeAead.java for setup detailsAeadenvAead=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));}}}}

importdatetimeimportloggingimportosimportsqlalchemyimporttinkfrom.cloud_kms_env_aeadimportinit_tink_env_aeadfrom.cloud_sql_connection_poolimportinit_dblogger=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/quickstarttable_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 optionsifteam!="TABS"andteam!="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)withdb.connect()asconn: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;publicclassQueryAndDecryptData{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/quickstartStringtableName="votes123";// Initialize database connection pool and create table if it does not exist// See CloudSqlConnectionPool.java for setup detailsDataSourcepool=CloudSqlConnectionPool.createConnectionPool(dbUser,dbPass,dbName,instanceConnectionName);CloudSqlConnectionPool.createTable(pool,tableName);// Initialize envelope AEAD// See CloudKmsEnvelopeAead.java for setup detailsAeadenvAead=CloudKmsEnvelopeAead.get(kmsUri);// Insert row into table to test// See EncryptAndInsert.java for setup detailsEncryptAndInsertData.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));}}}}}

importosimportsqlalchemyimporttinkfrom.cloud_kms_env_aeadimportinit_tink_env_aeadfrom.cloud_sql_connection_poolimportinit_dbfrom.encrypt_and_insert_dataimportencrypt_and_insert_datadefmain()->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/quickstarttable_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.    """withdb.connect()asconn:# Execute the query and fetch all resultsrecent_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=[]forrowinrecent_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 votesprint(f"{team}\t{email}\t{time_cast}")output.append((team,email,time_cast))returnoutput