Acerca del cifrado del lado del cliente

En esta página se describe cómo implementar el cifrado del lado del cliente en Cloud SQL.

Información general

El cifrado del lado del cliente consiste en cifrar los datos antes de escribirlos en Cloud SQL. Puedes encriptar los datos de Cloud SQL de forma que solo tu aplicación pueda desencriptarlos.

Para habilitar el cifrado del lado del cliente, tienes las siguientes opciones:

  1. Usar una clave de cifrado almacenada en Cloud Key Management Service (Cloud KMS).
  2. Usar una clave de cifrado almacenada de forma local en tu aplicación.

En este documento, se describe cómo usar la primera opción, que es la que ofrece la gestión de claves más fluida. Creamos una clave de cifrado en Cloud KMS e implementamos el cifrado de envolvente mediante Tink,la biblioteca de cifrado de código abierto de Google.

¿Por qué necesitas el cifrado del lado del cliente?

Necesitas el cifrado del lado del cliente si quieres proteger los datos de Cloud SQL a nivel de columna. Imagina que tienes una tabla con nombres y números de tarjetas de crédito. Quieres conceder acceso a esta tabla a un usuario, pero no quieres que vea los números de las tarjetas de crédito. Puedes cifrar los números mediante el cifrado del lado del cliente. Mientras no se conceda acceso a la clave de cifrado en Cloud KMS, el usuario no podrá leer la información de la tarjeta de crédito.

También puede restringir el acceso a nivel de instancia o de base de datos.

Crear claves con Cloud KMS

Cloud KMS te permite crear y gestionar claves en Google Cloud.

Cloud KMS admite muchos tipos de claves diferentes. Para el cifrado del lado del cliente, debes crear una clave simétrica.

Para dar acceso a tu aplicación a la clave de Cloud KMS, debes conceder a la cuenta de servicio que usa tu aplicación el rol cloudkms.cryptoKeyEncrypterDecrypter. En gcloud CLI, usa el siguiente comando para hacerlo:

gcloud kms keys add-iam-policy-binding key \
--keyring=key-ring \
--location=location \
--member=serviceAccount:service-account-name@example.domain.com \
--role=roles/cloudkms.cryptoKeyEncrypterDecrypter

Aunque puedes usar la clave de KMS para cifrar datos directamente, aquí vamos a usar una solución más flexible llamada cifrado envolvente. Esto nos permite cifrar mensajes de más de 64 KB, que es el tamaño máximo de mensaje que puede admitir la API de Cloud Key Management Service.

Cifrado de envolvente de Cloud KMS

En el cifrado de envolvente, la clave de KMS actúa como clave de cifrado de claves (KEK). Es decir, se usa para cifrar las claves de cifrado de datos (DEK), que a su vez se usan para cifrar los datos reales.

Después de crear una KEK en Cloud KMS, para cifrar cada mensaje, debes hacer lo siguiente:

  • Genera una clave de cifrado de datos (DEK) de forma local.
  • Usa esta DEK de forma local para cifrar el mensaje.
  • Llama a Cloud KMS para encriptar (envolver) la DEK con la KEK.
  • Almacena los datos cifrados y la DEK envuelta.

En lugar de implementar el cifrado envolvente desde cero, en este documento usamos Tink.

Tink

Tink es una biblioteca multiplataforma y multilingüe que proporciona APIs criptográficas de alto nivel. Para cifrar datos con el cifrado envolvente de Tink, debes proporcionar a Tink un URI de clave que apunte a tu KEK en Cloud KMS y credenciales que permitan a Tink usar la KEK. Tink genera la DEK, encripta los datos, encapsula la DEK y devuelve un solo texto cifrado con los datos encriptados y la DEK encapsulada.

Tink admite el cifrado de envolvente en C++, Java, Go y Python mediante la API AEAD:

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

Además del argumento normal de mensaje o texto cifrado, los métodos de cifrado y descifrado admiten datos asociados opcionales. Este argumento se puede usar para vincular el texto cifrado a un fragmento de datos. Por ejemplo, supongamos que tiene una base de datos con un campo user-id y un campo encrypted-medical-history. En este caso, el campo user-id probablemente debería usarse como datos asociados al cifrar el historial médico. De esta forma, un atacante no puede transferir el historial médico de un usuario a otro. También se usa para verificar que tienes la fila de datos correcta cuando ejecutas una consulta.

Ejemplos

En esta sección, veremos un ejemplo de código de una base de datos de información para votantes que usa el cifrado del lado del cliente. En el código de ejemplo se muestra cómo hacer lo siguiente:

  • Crear una tabla de base de datos y un grupo de conexiones
  • Configurar Tink para el cifrado envolvente
  • Encriptar y desencriptar datos con el cifrado envolvente de Tink con una KEK en Cloud KMS

Antes de empezar

  1. Crea una instancia de Cloud SQL siguiendo estas instrucciones. Anota la cadena de conexión, el usuario de la base de datos y la contraseña de la base de datos que crees.

  2. Crea una base de datos para tu aplicación siguiendo estas instrucciones. Anota el nombre de la base de datos.

  3. Crea una clave de KMS para tu aplicación siguiendo estas instrucciones. Copia el nombre de recurso de la clave que has creado.

  4. Crea una cuenta de servicio con los permisos "Cliente de Cloud SQL" siguiendo estas instrucciones.

  5. Añade el permiso "Encargado de cifrar o descifrar claves de CryptoKey de Cloud KMS" a tu cuenta de servicio siguiendo estas instrucciones.

Crear un pool de conexiones y una tabla en la base de datos

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

Inicializar una primitiva AEAD de envolvente 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

Cifra los datos e insértalos en la base de datos

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 = "hello@example.com";

    // 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 = "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)


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

Consultar la base de datos y descifrar los datos almacenados

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", "hello@example.com");

    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 = "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)


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