{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Secret Box\n",
    "\n",
    "The `secretbox` API uses secret key authenticated encryption to encrypt and authenticate a message with a secret key that all parties must share.\n",
    "\n",
    "[Libsodium Documentation](https://doc.libsodium.org/secret-key_cryptography/secretbox)\n",
    "\n",
    "Functions:\n",
    "```\n",
    "    crypto_secretbox_keygen() -> bytea\n",
    "\n",
    "    crypto_secretbox_noncegen() -> bytea\n",
    "\n",
    "    crypto_secretbox(message bytea, nonce bytea, key bytea) -> bytea\n",
    "\n",
    "    crypto_secretbox(message bytea, nonce bytea, key_id bigint, context bytea = 'pgsodium') -> bytea\n",
    "\n",
    "    crypto_secretbox_open(ciphertext bytea, nonce bytea, key bytea) -> bytea\n",
    "\n",
    "    crypto_secretbox_open(ciphertext bytea, nonce bytea, key_id bigint, context bytea = 'pgsodium') -> bytea\n",
    "```\n",
    "\n",
    "`crypto_secretbox_keygen()` generates a random secret key which can be\n",
    "used to encrypt and decrypt messages. The role `pgsodium_keymaker` is required to call this function.\n",
    "\n",
    "`crypto_secretbox_noncegen()` generates a random nonce which will be\n",
    "used when encrypting messages.  For security, each nonce must be used\n",
    "only once, though it is not a secret.  The purpose of the nonce is to\n",
    "add randomness to the message so that the same message encrypted\n",
    "multiple times with the same key will produce different ciphertexts.  The role `pgsodium_keyiduser` or greater is required to call this function.\n",
    "\n",
    "`crypto_secretbox()` encrypts a message using a previously generated\n",
    "nonce and secret key.  The encrypted message can be decrypted using\n",
    "`crypto_secretbox_open()`  Note that in order to decrypt the message,\n",
    "the original nonce will be needed. The role `pgsodium_keyholder` is required to call the raw `key bytea` versions of these functions.  The key id versions of the functions can be called with the role `pgsodium_keyiduser`.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql\n",
    "%sql postgresql://postgres@/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Encryption requires a key and a nonce.  The nonce doesn't have to be confidential, but it should never ever be reused with the same key. The easiest way to generate a nonce is to use `crypto_secretbox_noncegen`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "key = %sql select pgsodium.crypto_secretbox_keygen()::text\n",
    "key = key[0][0]\n",
    "\n",
    "nonce = %sql select pgsodium.crypto_secretbox_noncegen()::text\n",
    "nonce = nonce[0][0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Encryption\n",
    "\n",
    "A new secretbox is created with the key and the nonce:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres@/\n",
      "1 rows affected.\n",
      " * postgresql://postgres@/\n",
      "1 rows affected.\n",
      " * postgresql://postgres@/\n",
      "1 rows affected.\n",
      "The encrypted secretbox is:  \\x7b11d8e3659f6fe2a7762f082019c607d5d64fd5f805f6ff6df68266664a6ec335\n"
     ]
    }
   ],
   "source": [
    "secretbox = %sql SELECT crypto_secretbox::text from pgsodium.crypto_secretbox('bob is your uncle', :nonce, (:key)::bytea)\n",
    "secretbox = secretbox[0][0]\n",
    "print('The encrypted secretbox is: ', secretbox)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Decryption\n",
    "\n",
    "Decryption requires the same key and nonce."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres@/\n",
      "1 rows affected.\n",
      "The decrypted message is : bob is your uncle\n"
     ]
    }
   ],
   "source": [
    "plaintext = %sql SELECT crypto_secretbox_open FROM pgsodium.crypto_secretbox_open(:secretbox, :nonce, (:key)::bytea)\n",
    "print('The decrypted message is :', plaintext[0][0].tobytes().decode('utf8'))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}