Hologres:Pgcrypto extension functions

encode(data, format)

• Description : Encode des données binaires (type bytea) en une chaîne de texte selon le format spécifié.

• Syntaxe

  encode(data bytea, format text)

• Paramètres

  • data : Données binaires à encoder.

  • format : Format d'encodage. Valeurs possibles : hex, base64 ou escape.

• Valeur de retour : Chaîne de texte encodée.

• Exemple

  -- Encode binary data as hex
  SELECT encode('\xDEADBEEF'::bytea, 'hex');
  -- Result: deadbeef
  
  -- Encode binary data as Base64
  SELECT encode('\x12345678'::bytea, 'base64');
  -- Result: EjRWeA==

decode(str, format)

• Description : Décode une chaîne de texte depuis un format spécifié vers des données binaires (bytea).

• Syntaxe

  decode(str text, format text)

• Paramètres

  • str : Chaîne à décoder.

  • format : Format de décodage. Formats pris en charge : hex, base64 et escape.

• Valeur de retour : Données binaires décodées (bytea).

• Exemple

  -- Decode a hex string into binary data
  SELECT decode('deadbeef', 'hex');
  -- Result: \xdeadbeef
  
  -- Decode a Base64 string into binary data
  SELECT decode('EjRWeA==', 'base64');
  -- Result: \x12345678

encrypt(data, key, algorithm)

• Description : Chiffre des données à l'aide d'une clé et d'un algorithme spécifiés.

• Syntaxe

  encrypt(data bytea, key bytea, algorithm text)

• Paramètres

  • data : Données brutes à chiffrer.

  • key : Clé de chiffrement.

  • algorithm : Algorithme de chiffrement, par exemple aes ou aes-ecb/pad:pkcs.

• Valeur de retour : Données binaires chiffrées (bytea).

• Exemple

  -- Encrypt data using the AES algorithm and output the result as a Base64 string
  SELECT encode(encrypt('hello world'::bytea, 'mysecretpassword'::bytea, 'aes-ecb/pad:pkcs'), 'base64');
  -- Result: The encrypted Base64 string. The output varies with each execution due to cryptographic randomness.

decrypt(data, key, algorithm)

• Description : Déchiffre des données avec une clé et un algorithme spécifiés. La clé et l'algorithme doivent correspondre à ceux utilisés lors du chiffrement.

• Syntaxe

  decrypt(data bytea, key bytea, algorithm text)

• Paramètres

  • data : Données à déchiffrer.

  • key : Clé de déchiffrement (identique à celle utilisée pour le chiffrement).

  • algorithm : Algorithme de déchiffrement (identique à celui utilisé pour le chiffrement).

• Valeur de retour : Données binaires déchiffrées (bytea), souvent utilisées avec convert_from pour obtenir le texte en clair.

• Exemple

  -- Decrypt the Base64 ciphertext and convert it to UTF-8 text. 
  -- Replace <encrypted_base64_string> with the actual ciphertext.
  SELECT convert_from(
      decrypt(
          decode('<encrypted_base64_string>', 'base64'),
          'mysecretpassword'::bytea,
          'aes-ecb/pad:pkcs'
      ),
      'utf8'
  );
  -- Result: The decrypted plaintext

convert_from(data, encoding)

• Description : Convertit des données binaires depuis un encodage de caractères spécifié vers la représentation interne de la base de données (par exemple UTF-8).

• Syntaxe

  convert_from(data bytea, encoding name)

• Paramètres

  • data : Données binaires avec un encodage spécifique.

  • encoding : Encodage de caractères des données source, par exemple utf8, latin1 ou gbk.

• Valeur de retour : Chaîne de texte convertie.

• Exemple

  -- Convert GBK-encoded binary data to text
  SELECT convert_from('\xb0\xc5\xba\xcd'::bytea, 'gbk');
  -- Result: Chinese characters

-- Enable the pgcrypto extension.
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Create a user information table that contains sensitive data.
CREATE TABLE mf_user_info (
    id bigint,
    name text,
    gender text,
    id_card_no text,
    tel text
);

INSERT INTO mf_user_info VALUES
(1, 'bob', 'male', '0001', '13900001234'),
(2, 'allen', 'male', '0011', '13900001111'),
(3, 'kate', 'female', '0111', '13900002222'),
(4, 'annie', 'female', '1111', '13900003333');

-- Create a table for encrypted storage.
CREATE TABLE mf_user_info_encrypt (
    id bigint,
    name text,
    gender text,
    id_card_no_encrypt text,
    tel text
);

-- Encrypt the sensitive data and write it to the new table.
INSERT INTO mf_user_info_encrypt
SELECT
    id, name, gender,
    encode(encrypt(id_card_no::text::bytea, 'myencryptionkey'::bytea, 'aes-ecb/pad:pkcs'), 'base64') AS id_card_no_encrypt,
    tel
FROM mf_user_info;

-- Decrypt and query the ID card numbers.
SELECT id, name, gender,
    convert_from(decrypt(decode(id_card_no_encrypt, 'base64'), 'myencryptionkey'::bytea, 'aes-ecb/pad:pkcs'), 'utf8') AS id_card_no,
    tel
FROM mf_user_info_encrypt;