Skip to content

Commit

Permalink
more tce docs
Browse files Browse the repository at this point in the history
  • Loading branch information
@michelp
michelp committed Sep 4, 2022
1 parent e60a13f commit a9d5a69
Show file tree
Hide file tree
Showing 2 changed files with 87 additions and 73 deletions.
87 changes: 47 additions & 40 deletions docs/Transparent_Column_Encryption.ipynb
View file
Original file line number Diff line number Diff line change
Expand Up @@ -68,7 +68,8 @@
}
],
"source": [
"%sql CREATE EXTENSION IF NOT EXISTS pgsodium;"
"%%sql \n",
"CREATE EXTENSION IF NOT EXISTS pgsodium;"
]
},
{
Expand Down Expand Up @@ -132,7 +133,7 @@
"name": "stdout",
"output_type": "stream",
"text": [
"The security label will be: ENCRYPT WITH KEY ID 00b27f02-be79-4083-aacd-29d60ad520ab\n"
"The security label will be: ENCRYPT WITH KEY ID 7dff7a77-1b3a-47e4-97d1-43d0c5c971d4\n"
]
}
],
Expand Down Expand Up @@ -209,25 +210,25 @@
" <tr>\n",
" <td>my_customer_secrets</td>\n",
" <td>pgsodium</td>\n",
" <td>ENCRYPT WITH KEY COLUMN key_id ASSOCIATED (id, associated_data, owner) NONCE nonce</td>\n",
" <td>DECRYPT WITH VIEW public.other_name_view</td>\n",
" </tr>\n",
" <tr>\n",
" <td>my_customer_secrets</td>\n",
" <td>pgsodium</td>\n",
" <td>DECRYPT WITH VIEW public.other_name_view</td>\n",
" <td>ENCRYPT WITH KEY COLUMN key_id ASSOCIATED (id, associated_data, owner) NONCE nonce</td>\n",
" </tr>\n",
" <tr>\n",
" <td>my_secrets</td>\n",
" <td>pgsodium</td>\n",
" <td>ENCRYPT WITH KEY ID 00b27f02-be79-4083-aacd-29d60ad520ab</td>\n",
" <td>ENCRYPT WITH KEY ID 7dff7a77-1b3a-47e4-97d1-43d0c5c971d4</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[('pgsodium.key', 'pgsodium', 'ENCRYPT WITH KEY COLUMN parent_key ASSOCIATED (id, associated_data) NONCE raw_key_nonce'),\n",
" ('my_customer_secrets', 'pgsodium', 'ENCRYPT WITH KEY COLUMN key_id ASSOCIATED (id, associated_data, owner) NONCE nonce'),\n",
" ('my_customer_secrets', 'pgsodium', 'DECRYPT WITH VIEW public.other_name_view'),\n",
" ('my_secrets', 'pgsodium', 'ENCRYPT WITH KEY ID 00b27f02-be79-4083-aacd-29d60ad520ab')]"
" ('my_customer_secrets', 'pgsodium', 'ENCRYPT WITH KEY COLUMN key_id ASSOCIATED (id, associated_data, owner) NONCE nonce'),\n",
" ('my_secrets', 'pgsodium', 'ENCRYPT WITH KEY ID 7dff7a77-1b3a-47e4-97d1-43d0c5c971d4')]"
]
},
"execution_count": 7,
Expand Down Expand Up @@ -268,7 +269,8 @@
}
],
"source": [
"%sql INSERT INTO my_secrets (secret) VALUES ('sekert1'), ('shhhhh'), ('0xABC_my_payment_processor_key');"
"%%sql \n",
"INSERT INTO my_secrets (secret) VALUES ('sekert1'), ('shhhhh'), ('0xABC_my_payment_processor_key');"
]
},
{
Expand Down Expand Up @@ -297,20 +299,20 @@
" <th>secret</th>\n",
" </tr>\n",
" <tr>\n",
" <td>kU3aImiltQk+UsVBvNFd7NRN3lQutqyQvhzyMmUNtkqC9apvAAKj</td>\n",
" <td>UPLO9S0j5JNynLxsXusXka5lIAGNYlRVqWWu4l8OcqbqrEitWXvp</td>\n",
" </tr>\n",
" <tr>\n",
" <td>fXDPJean7UQGYiYClQfgXdw0X4bxqgiuDaReddQaI1347oTtKIk=</td>\n",
" <td>XJi1+Q8V/BH1awvprPKu7JKC9ImCrk+E5/Ro/3DIIkjyFsUhBWc=</td>\n",
" </tr>\n",
" <tr>\n",
" <td>/cc70FtpmMz36SzsU7qeaOsGw4kLOE2g1dOIfEbRmanCst8HROWQq6EgssVVUQfi7XndpjQ7c4r5<br>4WrkkEs=</td>\n",
" <td>28fPDid2nuAYkCh49ZvrI8pXQHMF98FAKjscsYiq67hlWxsAGFiSjf9/PRAhEz1oxdhvQqOIN9qR<br>PUMetCs=</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[('kU3aImiltQk+UsVBvNFd7NRN3lQutqyQvhzyMmUNtkqC9apvAAKj',),\n",
" ('fXDPJean7UQGYiYClQfgXdw0X4bxqgiuDaReddQaI1347oTtKIk=',),\n",
" ('/cc70FtpmMz36SzsU7qeaOsGw4kLOE2g1dOIfEbRmanCst8HROWQq6EgssVVUQfi7XndpjQ7c4r5\\n4WrkkEs=',)]"
"[('UPLO9S0j5JNynLxsXusXka5lIAGNYlRVqWWu4l8OcqbqrEitWXvp',),\n",
" ('XJi1+Q8V/BH1awvprPKu7JKC9ImCrk+E5/Ro/3DIIkjyFsUhBWc=',),\n",
" ('28fPDid2nuAYkCh49ZvrI8pXQHMF98FAKjscsYiq67hlWxsAGFiSjf9/PRAhEz1oxdhvQqOIN9qR\\nPUMetCs=',)]"
]
},
"execution_count": 9,
Expand All @@ -319,7 +321,8 @@
}
],
"source": [
"%sql SELECT * FROM my_secrets;"
"%%sql \n",
"SELECT * FROM my_secrets;"
]
},
{
Expand Down Expand Up @@ -347,23 +350,23 @@
" <th>decrypted_secret</th>\n",
" </tr>\n",
" <tr>\n",
" <td>kU3aImiltQk+UsVBvNFd7NRN3lQutqyQvhzyMmUNtkqC9apvAAKj</td>\n",
" <td>UPLO9S0j5JNynLxsXusXka5lIAGNYlRVqWWu4l8OcqbqrEitWXvp</td>\n",
" <td>sekert1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>fXDPJean7UQGYiYClQfgXdw0X4bxqgiuDaReddQaI1347oTtKIk=</td>\n",
" <td>XJi1+Q8V/BH1awvprPKu7JKC9ImCrk+E5/Ro/3DIIkjyFsUhBWc=</td>\n",
" <td>shhhhh</td>\n",
" </tr>\n",
" <tr>\n",
" <td>/cc70FtpmMz36SzsU7qeaOsGw4kLOE2g1dOIfEbRmanCst8HROWQq6EgssVVUQfi7XndpjQ7c4r5<br>4WrkkEs=</td>\n",
" <td>28fPDid2nuAYkCh49ZvrI8pXQHMF98FAKjscsYiq67hlWxsAGFiSjf9/PRAhEz1oxdhvQqOIN9qR<br>PUMetCs=</td>\n",
" <td>0xABC_my_payment_processor_key</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[('kU3aImiltQk+UsVBvNFd7NRN3lQutqyQvhzyMmUNtkqC9apvAAKj', 'sekert1'),\n",
" ('fXDPJean7UQGYiYClQfgXdw0X4bxqgiuDaReddQaI1347oTtKIk=', 'shhhhh'),\n",
" ('/cc70FtpmMz36SzsU7qeaOsGw4kLOE2g1dOIfEbRmanCst8HROWQq6EgssVVUQfi7XndpjQ7c4r5\\n4WrkkEs=', '0xABC_my_payment_processor_key')]"
"[('UPLO9S0j5JNynLxsXusXka5lIAGNYlRVqWWu4l8OcqbqrEitWXvp', 'sekert1'),\n",
" ('XJi1+Q8V/BH1awvprPKu7JKC9ImCrk+E5/Ro/3DIIkjyFsUhBWc=', 'shhhhh'),\n",
" ('28fPDid2nuAYkCh49ZvrI8pXQHMF98FAKjscsYiq67hlWxsAGFiSjf9/PRAhEz1oxdhvQqOIN9qR\\nPUMetCs=', '0xABC_my_payment_processor_key')]"
]
},
"execution_count": 10,
Expand All @@ -372,7 +375,8 @@
}
],
"source": [
"%sql SELECT * FROM decrypted_my_secrets;"
"%%sql \n",
"SELECT * FROM decrypted_my_secrets;"
]
},
{
Expand Down Expand Up @@ -438,7 +442,8 @@
}
],
"source": [
"%sql SECURITY LABEL FOR pgsodium ON TABLE my_customer_secrets IS 'DECRYPT WITH VIEW public.other_name_view';"
"%%sql \n",
"SECURITY LABEL FOR pgsodium ON TABLE my_customer_secrets IS 'DECRYPT WITH VIEW public.other_name_view';"
]
},
{
Expand Down Expand Up @@ -532,29 +537,29 @@
" <th>key_id</th>\n",
" </tr>\n",
" <tr>\n",
" <td>wXmJsarHnYeDFS5bfkipBJny8/3CwM6YIxVbonkIoES/Oeqe</td>\n",
" <td>nUxRwiQhhxFZlBxTkldF8ff3N3XnPrTIZWMqyZ+aliwGygGp</td>\n",
" <td>{&#x27;type&#x27;: &#x27;color&#x27;}</td>\n",
" <td>bob</td>\n",
" <td>bb4c5512-2eed-4254-a262-b3a53b16e795</td>\n",
" <td>a42a4267-0fb1-4d83-8982-e33814010bae</td>\n",
" </tr>\n",
" <tr>\n",
" <td>d9hUv3oA2n0HtydyWLae1IAtcYP/nOH2i8tm1xTMlKLECMSk</td>\n",
" <td>0Agh3PJefpohSVYtMDxWdzCl765erewje0a40L50hw7SDm2Y</td>\n",
" <td>{&#x27;type&#x27;: &#x27;food&#x27;}</td>\n",
" <td>alice</td>\n",
" <td>1a7b37b1-f8a2-4a83-b410-da115c51a1db</td>\n",
" <td>6f4e4275-64e9-4de9-a3ae-f263a07645a8</td>\n",
" </tr>\n",
" <tr>\n",
" <td>/nJpnMbrh7me5KwwLWHC0+rvLV8J6SEDI3LNsM5qmZN6VNgF</td>\n",
" <td>4yNi9vag6yOttKIR7ZETJjrtin+WRRdKHKZGqH9xavBzV6Cp</td>\n",
" <td>{&#x27;type&#x27;: &#x27;car&#x27;}</td>\n",
" <td>mallory</td>\n",
" <td>e580cf08-3d15-4425-8520-91ea66142a22</td>\n",
" <td>2df9c696-3e31-4517-b12e-1c0b629db0f9</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[('wXmJsarHnYeDFS5bfkipBJny8/3CwM6YIxVbonkIoES/Oeqe', {'type': 'color'}, 'bob', UUID('bb4c5512-2eed-4254-a262-b3a53b16e795')),\n",
" ('d9hUv3oA2n0HtydyWLae1IAtcYP/nOH2i8tm1xTMlKLECMSk', {'type': 'food'}, 'alice', UUID('1a7b37b1-f8a2-4a83-b410-da115c51a1db')),\n",
" ('/nJpnMbrh7me5KwwLWHC0+rvLV8J6SEDI3LNsM5qmZN6VNgF', {'type': 'car'}, 'mallory', UUID('e580cf08-3d15-4425-8520-91ea66142a22'))]"
"[('nUxRwiQhhxFZlBxTkldF8ff3N3XnPrTIZWMqyZ+aliwGygGp', {'type': 'color'}, 'bob', UUID('a42a4267-0fb1-4d83-8982-e33814010bae')),\n",
" ('0Agh3PJefpohSVYtMDxWdzCl765erewje0a40L50hw7SDm2Y', {'type': 'food'}, 'alice', UUID('6f4e4275-64e9-4de9-a3ae-f263a07645a8')),\n",
" ('4yNi9vag6yOttKIR7ZETJjrtin+WRRdKHKZGqH9xavBzV6Cp', {'type': 'car'}, 'mallory', UUID('2df9c696-3e31-4517-b12e-1c0b629db0f9'))]"
]
},
"execution_count": 15,
Expand All @@ -563,7 +568,8 @@
}
],
"source": [
"%sql SELECT secret, associated_data, owner, key_id FROM my_customer_secrets;"
"%%sql \n",
"SELECT secret, associated_data, owner, key_id FROM my_customer_secrets;"
]
},
{
Expand Down Expand Up @@ -594,26 +600,26 @@
" <td>blue</td>\n",
" <td>{&#x27;type&#x27;: &#x27;color&#x27;}</td>\n",
" <td>bob</td>\n",
" <td>bb4c5512-2eed-4254-a262-b3a53b16e795</td>\n",
" <td>a42a4267-0fb1-4d83-8982-e33814010bae</td>\n",
" </tr>\n",
" <tr>\n",
" <td>nuts</td>\n",
" <td>{&#x27;type&#x27;: &#x27;food&#x27;}</td>\n",
" <td>alice</td>\n",
" <td>1a7b37b1-f8a2-4a83-b410-da115c51a1db</td>\n",
" <td>6f4e4275-64e9-4de9-a3ae-f263a07645a8</td>\n",
" </tr>\n",
" <tr>\n",
" <td>fast</td>\n",
" <td>{&#x27;type&#x27;: &#x27;car&#x27;}</td>\n",
" <td>mallory</td>\n",
" <td>e580cf08-3d15-4425-8520-91ea66142a22</td>\n",
" <td>2df9c696-3e31-4517-b12e-1c0b629db0f9</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[('blue', {'type': 'color'}, 'bob', UUID('bb4c5512-2eed-4254-a262-b3a53b16e795')),\n",
" ('nuts', {'type': 'food'}, 'alice', UUID('1a7b37b1-f8a2-4a83-b410-da115c51a1db')),\n",
" ('fast', {'type': 'car'}, 'mallory', UUID('e580cf08-3d15-4425-8520-91ea66142a22'))]"
"[('blue', {'type': 'color'}, 'bob', UUID('a42a4267-0fb1-4d83-8982-e33814010bae')),\n",
" ('nuts', {'type': 'food'}, 'alice', UUID('6f4e4275-64e9-4de9-a3ae-f263a07645a8')),\n",
" ('fast', {'type': 'car'}, 'mallory', UUID('2df9c696-3e31-4517-b12e-1c0b629db0f9'))]"
]
},
"execution_count": 16,
Expand All @@ -622,7 +628,8 @@
}
],
"source": [
"%sql SELECT decrypted_secret, associated_data, owner, key_id FROM other_name_view;"
"%%sql \n",
"SELECT decrypted_secret, associated_data, owner, key_id FROM other_name_view;"
]
}
],
Expand Down
Loading

0 comments on commit a9d5a69

Please sign in to comment.