Informazioni sulla crittografia lato client

MySQL   |  PostgreSQL   |  SQL Server

Questa pagina descrive come implementare la crittografia lato client su Cloud SQL.

Panoramica

La crittografia lato client consiste nel criptare i dati prima di scriverli in Cloud SQL. Puoi criptare i dati di Cloud SQL in modo che solo la tua applicazione possa decriptarli.

Per attivare la crittografia lato client, hai a disposizione le seguenti opzioni:

1. Utilizzo di una chiave di crittografia archiviata in Cloud Key Management Service (Cloud KMS).
2. Utilizzo di una chiave di crittografia archiviata localmente nell'applicazione.

In questo argomento viene descritto come utilizzare la prima opzione, che offre la gestione delle chiavi più semplice. Creiamo una chiave di crittografia in Cloud KMS e implementiamo la crittografia dell'involucro utilizzando Tink, la libreria crittografica open source di Google.

Perché hai bisogno della crittografia lato client?

La crittografia lato client è necessaria se vuoi proteggere i dati di Cloud SQL a livello di colonna ¹. Immagina di avere una tabella di nomi e numeri di carte di credito. Vuoi concedere a un utente l'accesso a questa tabella, ma non vuoi che possa visualizzare i numeri di carta di credito. Puoi criptare i numeri utilizzando la crittografia lato client. Finché all'utente non viene concesso l'accesso alla chiave di crittografia in Cloud KMS, non può leggere i dati della carta di credito.

Creare chiavi utilizzando Cloud KMS

Cloud KMS ti consente di creare e gestire le chiavi sulla Google Cloud Platform.

Cloud KMS supporta molti tipi di chiavi diversi. Per la crittografia lato client, devi creare una chiave simmetrica.

Per concedere all'applicazione l'accesso alla chiave in Cloud KMS, devi assegnare all'account di servizio utilizzato dall'applicazione il ruolo cloudkms.cryptoKeyEncrypterDecrypter. In gcloud, utilizza il seguente comando:

gcloud kms keys add-iam-policy-binding key \
--keyring=key-ring \
--location=location \
--member=serviceAccount:[e-mail] \
--role=roles/cloudkms.cryptoKeyEncrypterDecrypter

Sebbene tu possa utilizzare la chiave KMS per criptare direttamente i dati, qui utilizziamo una soluzione più flessibile chiamata crittografia dell'involucro. In questo modo, possiamo criptare i messaggi più lunghi di 64 KB, che è la dimensione massima supportata dall'API Cloud Key Management Service.

Crittografia envelope Cloud KMS

Nella crittografia envelope, la chiave KMS funge da chiave di crittografia della chiave (KEK). In altre parole, viene utilizzata per criptare le chiavi di crittografia dei dati (DEK), che a loro volta vengono utilizzate per criptare i dati effettivi.

Dopo aver creato una KEK in Cloud KMS, per criptare ogni messaggio devi:

• Genera una chiave di crittografia dei dati (DEK) localmente.
• Utilizza questa DEK localmente per criptare il messaggio.
• Chiama Cloud KMS per criptare (avvolgere) la DEK con la KEK.
• Memorizza i dati criptati e la chiave DEK con wrapping.

Anziché implementare la crittografia dell'involucro da zero, in questo argomento utilizziamo Tink.

Tink

Tink è una libreria multipiattaforma e multilingue che fornisce API di crittografia di alto livello. Per criptare i dati con la crittografia dell'envelope di Tink, fornisci a Tink un URI chiave che rimandi alla tua KEK in Cloud KMS e le credenziali che consentono a Tink di utilizzare la KEK. Tink genera la DEK, cripta i dati, cripta la DEK e restituisce un singolo testo cifrato con i dati criptati e la DEK criptata.

Tink supporta la crittografia dell'involucro in C++, Java, Go e Python utilizzando l'API AEAD:

public interface Aead{
  byte[] encrypt(final byte[] plaintext, final byte[] associatedData)
  throws…
  byte[] decrypt(final byte[] ciphertext, final byte[] associatedData)
  throws…
}

Oltre al normale messaggio/crittogramma, i metodi di crittografia e decrittografia supportano i dati associati facoltativi. Questo argomento può essere utilizzato per associare il testo cifrato a un dato. Ad esempio, supponiamo di avere un database con un campo user-id e un campo encrypted-medical-history. In questo caso, il campo user-id dovrebbe probabilmente essere utilizzato come dati associati durante la crittografia della storia clinica. In questo modo, un malintenzionato non può spostare la storia clinica da un utente all'altro. Viene utilizzato anche per verificare di avere la riga di dati corretta quando esegui una query.

Esempi

In questa sezione esamineremo il codice campione di un database di informazioni sugli elettori che utilizza la crittografia lato client. Il codice campione mostra come:

• Crea una tabella di database e un pool di connessioni
• Configurare Tink per la crittografia envelope
• Crittografa e decripta i dati utilizzando la crittografia dell'envelope di Tink con una KEK in Cloud KMS

Prima di iniziare

1. Crea un'istanza Cloud SQL seguendo queste istruzioni. Prendi nota della stringa di connessione, dell'utente e della password del database che hai creato.

2. Crea un database per la tua applicazione seguendo queste istruzioni. Prendi nota del nome del database.

3. Crea una chiave KMS per la tua applicazione seguendo queste istruzioni. Copia il nome della risorsa della chiave creata.

4. Crea un account di servizio con le autorizzazioni "Client Cloud SQL" seguendo queste istruzioni.

5. Aggiungi l'autorizzazione "Autore crittografia/decrittografia CryptoKey Cloud KMS" per la chiave al tuo account di servizio seguendo queste istruzioni.

Crea un pool di connessioni e una nuova tabella nel database.

Java

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.security.GeneralSecurityException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.sql.DataSource;

public class CloudSqlConnectionPool {

  public static DataSource createConnectionPool(String dbUser, String dbPass, String dbName,
      String instanceConnectionName) throws GeneralSecurityException {
    HikariConfig config = new HikariConfig();
    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);
    DataSource pool = new HikariDataSource(config);
    return pool;
  }

  public static void createTable(DataSource pool, String tableName) throws SQLException {
    // Safely attempt to create the table schema.
    try (Connection conn = pool.getConnection()) {
      String stmt = 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 (PreparedStatement createTableStatement = conn.prepareStatement(stmt);) {
        createTableStatement.execute();
      }
    }
  }
}

Python

import sqlalchemy


def init_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


def init_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


def init_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

Inizializza una primitiva AEAD con intestazione con Tink.

Java

import com.google.crypto.tink.Aead;
import com.google.crypto.tink.KmsClient;
import com.google.crypto.tink.aead.AeadConfig;
import com.google.crypto.tink.aead.AeadKeyTemplates;
import com.google.crypto.tink.aead.KmsEnvelopeAead;
import com.google.crypto.tink.integration.gcpkms.GcpKmsClient;
import java.security.GeneralSecurityException;

public class CloudKmsEnvelopeAead {

  public static Aead get(String kmsUri) throws GeneralSecurityException {
    AeadConfig.register();

    // Create a new KMS Client
    KmsClient client = new GcpKmsClient().withDefaultCredentials();

    // Create an AEAD primitive using the Cloud KMS key
    Aead gcpAead = 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.
    return new KmsEnvelopeAead(AeadKeyTemplates.AES128_GCM, gcpAead);
  }
}

Python

import logging

import tink
from tink import aead
from tink.integration import gcpkms

logger = logging.getLogger(__name__)


def init_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

Cripta i dati e inseriscili nel database.

Java

import com.google.crypto.tink.Aead;
import java.security.GeneralSecurityException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import javax.sql.DataSource;

public class EncryptAndInsertData {

  public static void main(String[] args) throws GeneralSecurityException, 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.
    String dbUser = System.getenv("DB_USER"); // e.g. "root", "postgres"
    String dbPass = System.getenv("DB_PASS"); // e.g. "mysupersecretpassword"
    String dbName = System.getenv("DB_NAME"); // e.g. "votes_db"
    String instanceConnectionName =
        System.getenv("INSTANCE_CONNECTION_NAME"); // e.g. "project-name:region:instance-name"
    String kmsUri = 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

    String team = "TABS";
    String tableName = "votes";
    String email = "[e-mail]";

    // Initialize database connection pool and create table if it does not exist
    // See CloudSqlConnectionPool.java for setup details
    DataSource pool =
        CloudSqlConnectionPool.createConnectionPool(dbUser, dbPass, dbName, instanceConnectionName);
    CloudSqlConnectionPool.createTable(pool, tableName);

    // Initialize envelope AEAD
    // See CloudKmsEnvelopeAead.java for setup details
    Aead envAead = CloudKmsEnvelopeAead.get(kmsUri);

    encryptAndInsertData(pool, envAead, tableName, team, email);
  }

  public static void encryptAndInsertData(
      DataSource pool, Aead envAead, String tableName, String team, String email)
      throws GeneralSecurityException, SQLException {

    try (Connection conn = pool.getConnection()) {
      String stmt =
          String.format(
              "INSERT INTO %s (team, time_cast, voter_email) VALUES (?, ?, ?);", tableName);
      try (PreparedStatement voteStmt = conn.prepareStatement(stmt); ) {
        voteStmt.setString(1, team);
        voteStmt.setTimestamp(2, new Timestamp(new Date().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));
      }
    }
  }
}

Python

import datetime
import logging
import os

import sqlalchemy
import tink

from .cloud_kms_env_aead import init_tink_env_aead
from .cloud_sql_connection_pool import init_db


logger = logging.getLogger(__name__)


def main() -> 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 = "[e-mail]"

    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)


def encrypt_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}!")

Esegui query sul database e decripta i dati archiviati.

Java

import com.google.crypto.tink.Aead;
import java.security.GeneralSecurityException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import javax.sql.DataSource;

public class QueryAndDecryptData {

  public static void main(String[] args) throws GeneralSecurityException, 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.
    String dbUser = System.getenv("DB_USER"); // e.g. "root", "postgres"
    String dbPass = System.getenv("DB_PASS"); // e.g. "mysupersecretpassword"
    String dbName = System.getenv("DB_NAME"); // e.g. "votes_db"
    String instanceConnectionName =
        System.getenv("INSTANCE_CONNECTION_NAME"); // e.g. "project-name:region:instance-name"
    String kmsUri = 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

    String tableName = "votes123";

    // Initialize database connection pool and create table if it does not exist
    // See CloudSqlConnectionPool.java for setup details
    DataSource pool =
        CloudSqlConnectionPool.createConnectionPool(dbUser, dbPass, dbName, instanceConnectionName);
    CloudSqlConnectionPool.createTable(pool, tableName);

    // Initialize envelope AEAD
    // See CloudKmsEnvelopeAead.java for setup details
    Aead envAead = CloudKmsEnvelopeAead.get(kmsUri);

    // Insert row into table to test
    // See EncryptAndInsert.java for setup details
    EncryptAndInsertData.encryptAndInsertData(
        pool, envAead, tableName, "SPACES", "[e-mail]");

    queryAndDecryptData(pool, envAead, tableName);
  }

  public static void queryAndDecryptData(DataSource pool, Aead envAead, String tableName)
      throws GeneralSecurityException, SQLException {

    try (Connection conn = pool.getConnection()) {
      String stmt =
          String.format(
              "SELECT team, time_cast, voter_email FROM %s ORDER BY time_cast DESC LIMIT 5",
              tableName);
      try (PreparedStatement voteStmt = conn.prepareStatement(stmt); ) {
        ResultSet voteResults = voteStmt.executeQuery();

        System.out.println("Team\tTime Cast\tEmail");
        while (voteResults.next()) {
          String team = voteResults.getString(1);
          Timestamp timeCast = voteResults.getTimestamp(2);

          // Postgres pads CHAR fields with spaces. These will need to be removed before
          // decrypting.
          String aad = 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.
          String email = new String(envAead.decrypt(voteResults.getBytes(3), aad.getBytes()));

          System.out.println(String.format("%s\t%s\t%s", team, timeCast, email));
        }
      }
    }
  }
}

Python

import os

import sqlalchemy
import tink

from .cloud_kms_env_aead import init_tink_env_aead
from .cloud_sql_connection_pool import init_db
from .encrypt_and_insert_data import encrypt_and_insert_data


def main() -> 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 = "[e-mail]"

    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)


def query_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

---

1. Puoi anche limitare l'accesso a livello di istanza o database. ↩