Skip to content

Instantly share code, notes, and snippets.

@RyanSquared
Last active October 17, 2020 00:24
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save RyanSquared/1b14850611e46059432378d8e892669d to your computer and use it in GitHub Desktop.
Save RyanSquared/1b14850611e46059432378d8e892669d to your computer and use it in GitHub Desktop.
pgcrypto example case

this assumes you have Docker installed. if you don't, just run the .sql script against a clean pgsql instance.

i've included two PGP keys. one is called "working.asc", one is called "breaking.asc". the working one has a single UID, the breaking one has two UIDs. they are still one single key.

-----BEGIN PGP PUBLIC KEY BLOCK-----
mQENBF1lrTcBCACwH7BO2EJGgIHDwo3jUyHnqKg6r5qE6SSFlCLkCeVsekTQcAfw
IaCt/MBDNiPrEnKfTT0j+jEiEvoZMEpL8XCpGxSj3LWj4uJY+/P+7K73gUDguPWF
ERJlsxqecjxqZpr7buQI9FN3sYIWxjK4b12OI6TzjSf9LUwl5lSvBCx0jVLeSV+N
V0jZQJ+iNC4SPB5qSdlvykl5n9+1t/B3anMZdXFJpnuqTYLNTGCr82MkMR5iredh
IDnBatpRlMO47lWlVOXi0s5MOdErUUQKX64lyQG0j/fsyo+5BBROLf2fczrIVRRl
IaQ8XkyTCdOSZnFyX3/ngelcIPrSgMpYqljdABEBAAG0P1J5YW4gSGV5d29vZCAo
UnlhblNxdWFyZWQgLSBZdWJpa2V5IDVDIE5hbm8pIDxyeWFuQGhhc2hiYW5nLnNo
PokBVAQTAQoAPhYhBIMz8pKxu9M0ph5vVmeF968o3nCBBQJdZa03AhsDBQkDwmcA
BQsJCAcCBhUKCQgLAgQWAgMBAh4BAheAAAoJEGeF968o3nCB38wIAIwfteKojzfh
zPNaUHQ4Czh5BYtnHMQh4W30x3wBu1FHZubl5PlkSj1rnMxMzu75Y4pjKAJdFRr5
q6I6TUcZUOLPjQqzKBYYzaE5F2qUnPaAqTqw1bXaoX0s3af3zNY7B7VNBphMPIt5
7H9RugqhY7xc0QBGY9JdBSpW0h+1ggtie76nRrZA4zK1sltHLQZMJFh5K/BgXn1k
2iwiRC9Jt7p01AYvipB03Lh1lUxAP/alsiXCFHfJ3PL1exXEwukH0ROAVpRKD1so
+lN8BpApLEpCF7sdOruMDfzGMSVdwsPuawQHWxGgZAOeQttUm/LjEiZd6czRZmi5
pcnM9eAaXf20I1J5YW4gSGV5d29vZCA8dmFuZG9yMjAxMkBnbWFpbC5jb20+iQFU
BBMBCgA+FiEEgzPykrG70zSmHm9WZ4X3ryjecIEFAl3x3FkCGwMFCQPCZwAFCwkI
BwIGFQoJCAsCBBYCAwECHgECF4AACgkQZ4X3ryjecIFNeQf/YIeML5ptYdZE+tC1
zbkX30elzYL4fYLzIhGv4IFB9KgxaDQUPWmxY3833D8BkBXF/w9RkS+kzgrSvY2j
v7DA5uhq3TnioVarXCy5Dpnih9KsmamovQln/ZC97vAnzjw1m9R+z8kBnyDRuggp
TwO7WgKhWWtrFRWbNaf3NHvG8JOQlGBX3eNqmFX7LVjMSh0xubVMM2laSzIhYYR9
n+URpt39QTkYkfeGAgkYAOCnbzIvg6wFwFHhA7GHTuS+qW3DKkqhYHVt/u8ZSUWX
JtBBGo+cTEHafafBsWkYvzpet+h4EeuFCu1YtU49NUGggUTj+3PukZCnNWcjrAh2
trZBzrkBDQRdZa03AQgAxHn96AR42unTdV7VJrfnXG+wJQ3XZGmb6DnU5NqVrdt0
Dv0/QrN1HjmYw4K4IzrAC7ebNXHzUNml1tr77mjlHmEekbdkvZngiP+8ncfcxwo8
xQi3ircZVKsnek+lDx65sg6+nFgfwtftANQri+4wi+a6y4ocxFSFd2O2jPkrdjAE
QBjFlgzBcJGi96bJcYi95XY3vWHS1sonCYjSXw7CSqqCpD6TbnjqNSvH3yCfsj1c
kcDkk6TAui5H80eI7CEFd4VDGPiLqed/K65NGaViAv87m9cOja8o7eSrAM99+FG6
SbMjChmGcpFIYE2gCyFpVESU851gJI63ZTTfosirDwARAQABiQE8BBgBCgAmFiEE
gzPykrG70zSmHm9WZ4X3ryjecIEFAl1lrTcCGyAFCQPCZwAACgkQZ4X3ryjecIFR
HAf+K7TvFZM+Ny+sA0bgI/LmRoIrn3r6t49tNARIDEN4eBAZLR6dhE1RzQAagIrA
rTsmybBQODmXVj5RKgkOTglRjVy8EEO6eqKZ5sfxBSzw3NHOHn+thI+7yqaXf34b
74249zoNI4/ezB20UaJOtnjSHo6IJ20gkltB0BR0tyelfcEgxlO1jYUUNJcLFzfz
/FvGbx3TC18bddmLykuD6kIOK2nG0IGoi11LMMr3K4JLP5U+DxnAShGY2KNRF+Gx
RAIhKZ/VqwLAd90Nv15uPgtu7wcsEluM6Uq/foxOLMNnt5MYWI36YA/OpHhsXovx
hYyQTkJTayYjY5/cQDDliSH2iLkBDQRdZa03AQgAqNLJN4ARHT37/9B3iEsuasbu
jRtLVGboNAMMtw4lYjinKuFBlcvbKI4DZyqkA3oRm1jxEkGC3sBD4a15x7OktN9x
K1b43B9GXQ/ZIgXd4fWAafrY7oJcWTYfaXOfTj4aOZJfICVAV4x+UvCYyf4uXmrE
IRcIfmhsAgoJbNyt5q67sHdJe0gqwgqSq7cmKZqdBDQzK7lTf6hY6ygTEy6sr2Dr
S+QGG9uFWsD3Y9NHDLfptYyU5uPA5BOhREVIaVrrYmBphqiszvuxrMxMcAj/X2vB
RPN1yDYp6Pvn7rYC5Xi+9h0VAGKYjmJ/9JzZBwaCqX4KMr269WMZ+QV4w2sHuQAR
AQABiQE8BBgBCgAmFiEEgzPykrG70zSmHm9WZ4X3ryjecIEFAl1lrTcCGwwFCQPC
ZwAACgkQZ4X3ryjecIE+twf9EMrYtF1wk7Wx2vUVtXJRYHRE9/R3Qvr00FnOhg5N
v2OWFBOwD5O7xJ0YSPD7W7+pdEUzYIsCEFL4HO645Z1PXQ1Iu1R4Xjhh5ZQTHy0w
N389dXw21Er7WC6tvDf3/+sFni5LkkzeTK45X+VExvRfMSVgboHEtysju+6GhnC5
bT4volWULkxGuoKGM9qWIkgMzOh5PSdvdfgvvCWUzDpatjIj13caEDOKHtc2AurQ
QoyE6kCd1WqbmuJau1tmp4INWm0MZWrtF84A4I/AM4POMMErqV7N8aQYyezV2udO
BLNCVZqTB1eoWIx+kgvigetwlhHmIEpM9V0tLSPcfKtT+w==
=8MvD
-----END PGP PUBLIC KEY BLOCK-----
#!/bin/bash
password="$(pwgen 24 1)"
docker stop postgres-ryan-pgcrypto
docker rm postgres-ryan-pgcrypto
docker run --name postgres-ryan-pgcrypto -e POSTGRES_PASSWORD="$password" -d -p 5432:5432 postgres
export PGPASSWORD="$password"
sleep 5
psql --host localhost --user postgres -f test.sql
-- vim:set ft=pgsql:
CREATE SCHEMA pgcrypto;
CREATE EXTENSION pgcrypto SCHEMA pgcrypto;
SELECT pgcrypto.pgp_key_id(pgcrypto.dearmor($$-----BEGIN PGP PUBLIC KEY BLOCK-----
mQENBF1lrTcBCACwH7BO2EJGgIHDwo3jUyHnqKg6r5qE6SSFlCLkCeVsekTQcAfw
IaCt/MBDNiPrEnKfTT0j+jEiEvoZMEpL8XCpGxSj3LWj4uJY+/P+7K73gUDguPWF
ERJlsxqecjxqZpr7buQI9FN3sYIWxjK4b12OI6TzjSf9LUwl5lSvBCx0jVLeSV+N
V0jZQJ+iNC4SPB5qSdlvykl5n9+1t/B3anMZdXFJpnuqTYLNTGCr82MkMR5iredh
IDnBatpRlMO47lWlVOXi0s5MOdErUUQKX64lyQG0j/fsyo+5BBROLf2fczrIVRRl
IaQ8XkyTCdOSZnFyX3/ngelcIPrSgMpYqljdABEBAAG0P1J5YW4gSGV5d29vZCAo
UnlhblNxdWFyZWQgLSBZdWJpa2V5IDVDIE5hbm8pIDxyeWFuQGhhc2hiYW5nLnNo
PokBVAQTAQoAPhYhBIMz8pKxu9M0ph5vVmeF968o3nCBBQJdZa03AhsDBQkDwmcA
BQsJCAcCBhUKCQgLAgQWAgMBAh4BAheAAAoJEGeF968o3nCB38wIAIwfteKojzfh
zPNaUHQ4Czh5BYtnHMQh4W30x3wBu1FHZubl5PlkSj1rnMxMzu75Y4pjKAJdFRr5
q6I6TUcZUOLPjQqzKBYYzaE5F2qUnPaAqTqw1bXaoX0s3af3zNY7B7VNBphMPIt5
7H9RugqhY7xc0QBGY9JdBSpW0h+1ggtie76nRrZA4zK1sltHLQZMJFh5K/BgXn1k
2iwiRC9Jt7p01AYvipB03Lh1lUxAP/alsiXCFHfJ3PL1exXEwukH0ROAVpRKD1so
+lN8BpApLEpCF7sdOruMDfzGMSVdwsPuawQHWxGgZAOeQttUm/LjEiZd6czRZmi5
pcnM9eAaXf20I1J5YW4gSGV5d29vZCA8dmFuZG9yMjAxMkBnbWFpbC5jb20+iQFU
BBMBCgA+FiEEgzPykrG70zSmHm9WZ4X3ryjecIEFAl3x3FkCGwMFCQPCZwAFCwkI
BwIGFQoJCAsCBBYCAwECHgECF4AACgkQZ4X3ryjecIFNeQf/YIeML5ptYdZE+tC1
zbkX30elzYL4fYLzIhGv4IFB9KgxaDQUPWmxY3833D8BkBXF/w9RkS+kzgrSvY2j
v7DA5uhq3TnioVarXCy5Dpnih9KsmamovQln/ZC97vAnzjw1m9R+z8kBnyDRuggp
TwO7WgKhWWtrFRWbNaf3NHvG8JOQlGBX3eNqmFX7LVjMSh0xubVMM2laSzIhYYR9
n+URpt39QTkYkfeGAgkYAOCnbzIvg6wFwFHhA7GHTuS+qW3DKkqhYHVt/u8ZSUWX
JtBBGo+cTEHafafBsWkYvzpet+h4EeuFCu1YtU49NUGggUTj+3PukZCnNWcjrAh2
trZBzrkBDQRdZa03AQgAxHn96AR42unTdV7VJrfnXG+wJQ3XZGmb6DnU5NqVrdt0
Dv0/QrN1HjmYw4K4IzrAC7ebNXHzUNml1tr77mjlHmEekbdkvZngiP+8ncfcxwo8
xQi3ircZVKsnek+lDx65sg6+nFgfwtftANQri+4wi+a6y4ocxFSFd2O2jPkrdjAE
QBjFlgzBcJGi96bJcYi95XY3vWHS1sonCYjSXw7CSqqCpD6TbnjqNSvH3yCfsj1c
kcDkk6TAui5H80eI7CEFd4VDGPiLqed/K65NGaViAv87m9cOja8o7eSrAM99+FG6
SbMjChmGcpFIYE2gCyFpVESU851gJI63ZTTfosirDwARAQABiQE8BBgBCgAmFiEE
gzPykrG70zSmHm9WZ4X3ryjecIEFAl1lrTcCGyAFCQPCZwAACgkQZ4X3ryjecIFR
HAf+K7TvFZM+Ny+sA0bgI/LmRoIrn3r6t49tNARIDEN4eBAZLR6dhE1RzQAagIrA
rTsmybBQODmXVj5RKgkOTglRjVy8EEO6eqKZ5sfxBSzw3NHOHn+thI+7yqaXf34b
74249zoNI4/ezB20UaJOtnjSHo6IJ20gkltB0BR0tyelfcEgxlO1jYUUNJcLFzfz
/FvGbx3TC18bddmLykuD6kIOK2nG0IGoi11LMMr3K4JLP5U+DxnAShGY2KNRF+Gx
RAIhKZ/VqwLAd90Nv15uPgtu7wcsEluM6Uq/foxOLMNnt5MYWI36YA/OpHhsXovx
hYyQTkJTayYjY5/cQDDliSH2iLkBDQRdZa03AQgAqNLJN4ARHT37/9B3iEsuasbu
jRtLVGboNAMMtw4lYjinKuFBlcvbKI4DZyqkA3oRm1jxEkGC3sBD4a15x7OktN9x
K1b43B9GXQ/ZIgXd4fWAafrY7oJcWTYfaXOfTj4aOZJfICVAV4x+UvCYyf4uXmrE
IRcIfmhsAgoJbNyt5q67sHdJe0gqwgqSq7cmKZqdBDQzK7lTf6hY6ygTEy6sr2Dr
S+QGG9uFWsD3Y9NHDLfptYyU5uPA5BOhREVIaVrrYmBphqiszvuxrMxMcAj/X2vB
RPN1yDYp6Pvn7rYC5Xi+9h0VAGKYjmJ/9JzZBwaCqX4KMr269WMZ+QV4w2sHuQAR
AQABiQE8BBgBCgAmFiEEgzPykrG70zSmHm9WZ4X3ryjecIEFAl1lrTcCGwwFCQPC
ZwAACgkQZ4X3ryjecIE+twf9EMrYtF1wk7Wx2vUVtXJRYHRE9/R3Qvr00FnOhg5N
v2OWFBOwD5O7xJ0YSPD7W7+pdEUzYIsCEFL4HO645Z1PXQ1Iu1R4Xjhh5ZQTHy0w
N389dXw21Er7WC6tvDf3/+sFni5LkkzeTK45X+VExvRfMSVgboHEtysju+6GhnC5
bT4volWULkxGuoKGM9qWIkgMzOh5PSdvdfgvvCWUzDpatjIj13caEDOKHtc2AurQ
QoyE6kCd1WqbmuJau1tmp4INWm0MZWrtF84A4I/AM4POMMErqV7N8aQYyezV2udO
BLNCVZqTB1eoWIx+kgvigetwlhHmIEpM9V0tLSPcfKtT+w==
=8MvD
-----END PGP PUBLIC KEY BLOCK-----
$$));
-----BEGIN PGP PUBLIC KEY BLOCK-----
mQINBF7LrDwBEAC1QdiZkithEU5QKFG0/AcZP2wi39erICfdHnGYsn1KmlVTHNxq
s6fjmy7Ks6UBoII4LUYgUAgz8yMTtxbU43B/ZAO3NOOGXOYsHeR6pWFEI4XlYAxq
D1a75Tp/x2HrvmDfpN9mrYNg+ld854XFzqG5Qi2W8sOaXt5zZ0HwVSb8wkRbPu1W
g3nTodwQ15KG28EU8cz/JKRZwtPQUmTiskdUSL1rN+NWLGeaPnj3y70PVKHvkRXh
WxX6Qlxc8Wleu0Hh3KLsYtJivE/hHZpC3+IRIUgu4QlwhWc6QXwjJy/vwunlbxH7
77Xn3Y1yqAwvrwGqGjlDLMl/MsOA3t0hW3UxrrMcroZGZfh4GVwV2Jiwft4c6GgQ
tn1A6Gf9P4A2s5IKwQD42NoUYXIR0vCo7FxUpMyVkm8AixO2FeGPTdtyjb7GV7wd
m8o37V1rJXpe2Luq+sa3gDjwRh4du0rsc/My/QBhNKptgggt6wPTRrCC5Xzmv1mT
AIXt4tRBOPqj+H4fye/OYltL8MDgaKx1+WbCNbIm/ZQFUrt4jNY1EWwAevOmaL9X
3ZbJ30bKk+Lm/1DoYrGwj1cfPvwO2740bkj5I0Aatxh0XtWW4zoEqCOy5ve2RPsU
UEOElY4KVNXTHV+kfEa8rcqgqFWEuDqjooWZlU6LtujAP3Jb95Au6j31hwARAQAB
tA9IYXNoYmFuZyBBcmdvQ0SJAlMEEwEKAD0WIQQf1mZ6CAjU1Ivbh1emG0jYKI/P
igUCXsusPAIbAwUJA8JnAAULCQoIBwQVCgkIBRYCAwEAAh4BAheAAAoJEKYbSNgo
j8+KA5kQAJ6mpI8cBFWGLWurMB8/J45gkDmqVfRVSoelkd8GtFST90UGkpKXLFcE
Qcm29LtN0Rhi2KelfOJiKYkR7i7O0bG8h8PtzoQ/vqFJjvCpKe5tFKD/Go/tZp37
dG8thG0MiQwuCf7BVpq1+n7QMUgJ+jjYSsNJXqIShaB9ReYONVk0AWyQkr1eR+2J
Ke6OaQP61RRZCF+F7rwJV54thgznvr4OEp6FBhfePzlIgGGEDI0YjRxXb+YRMdlz
xFtTfE9blZ7+0RRM1iKrBKbydC7nS7f9iJLntSQzvLeL+0IUsoArdjwRd8bg6/P1
HezpHxvFpbNXGRaips0NsmqiAdO/L/tU8KWmqKFYjURHnjVjgu4eFcmDlc8+ZPjI
WkF6SwKmt/2TwQFtp/BCSaz5oopKq6YcxdhUN8lRI8MA+q96s5v/zFDzhNkOIv0e
+fczDGRb5JtkXFVqHNcQY76V+qu5mPhy2ayXObgnavTh7fBMSdz/vOzdGFTE6Osr
K0oTQ6PZpBYSWaw0HH/x/raFG+a4YYeemkfoKguO9YL9TSlGzcuGvxDXVrtjimv1
MEtGDksqyr1Gm1xt/DF6TRx0stvVnmM++pxHUX0psH1Noz/vcx9fPQmiqNWYSBkj
LohakviPoGEQOS5IiE0xxHF8Vrzd+OgFSItKdAj0mPUXn06yK4xLuQINBF7LrDwB
EADfX3InYFYljCvRrhTRe9o4y/eU3NKOPK8zk9Z108jbuMW76ze3oNLwP9Ljz+TG
SHEYTq/8c6DI4oMglngc5Y+bC36/51KM+Qi+xTH+X0oNvJi6cRdAzi1aVeRc9g8d
zZ2e5+FaogLX8PIDTRNJgbahR4eBOXVV5xFkPvRnRGoL78Ss1BKN2EjXX6R03juX
Pzf8PX7PALVvHpKryALyTSdDdlIk68wyBJ4P1q2KVI9VBR78ZSf8Jvc+RWIK0QuW
ajYyDNqZA6ARBRXoorg2HnfnO+CuvI2+l/NMP8Ootq/5fixlpnM6gcz250vK7jPd
wub4Hsmkst2bWDj/aezLvj6eGoRdZLmdUyZHiFRKGUnAckhOJFaZHOQaeniaWyWt
xY7w2duSygq2+mRjPLT4jrdbLlLD3CVWXpm72rxA5OVZRLwHWSWzFDaBZZF2eyz7
edTJsqK/RsQAQYnoheWe3aUvuM4+RXfb3+Jtaire5CH/CBzBZogRHJjD07VXtH13
nnXudNUKgdBourozGjPhDfm2esgW24FgY4LfpyWzC7BKSKO74wUBUjzarugCIUxU
9/1EQ/RBFKHZbuLLcsI7geekdOCW550aspl32NVpRowxSaxL2ZYqrZhOiq/W5c8r
KZBs4bIZdUVAaviZvyOylhNVnUyvWkc7C4JKwafrfHGvdQARAQABiQI8BBgBCgAm
FiEEH9ZmeggI1NSL24dXphtI2CiPz4oFAl7LrDwCGwwFCQPCZwAACgkQphtI2CiP
z4qUDA//RMxzOoV2pJRTYmiuCNcHlH3IZ2b7AjQ+x2/8urItisMTk/vRsFRSrFmI
Mc3KxwszK67lFTAhSVFv9+KzXSs3ZSOAwVaHTleP5RLtFfCfaxb1YEeT9cBUlDUq
zkm7DOGqcICu6q6Sl+Kd9Qpv3cYIEpmBe2O8mtY600bb5domgoO6l62QVC5vwF0Y
q9ey/lZaFRWypOwtrdFAcUQ/u36NfJ+hsf81+SkAFj/ioDB/muQQg7SwuNxmN0Mi
INarO9qKkBwm5ojoBJjEcg4WtsQf6EDwzj9yLNITalJChj3HQKpqSVvjXV+jX5my
6djOFzhRyj3QpT5BZr5tylHB2E+iV3xkz7Z81whFENkfV7ZLjEd0ciNBAZ3HGElH
HcU1ZWk/qJfH8i1a6LOkcf5E7Tle5huauqbKckeTqQgzJ5Bg0kE/fvZ/zK7bLiZP
SpR8SCGt78kd7jDrSha0NyuNgFCWZl4DhmvDMRavHhcEiQbomIDgBEIVXL+pxAQn
KECKQ2EiJq6jw8hUpqCcwPXtji0aECX+72P13ErJSo9VRzOHgwlq5eYchJOep4dN
1oJQBjaYjjOPcj6P1l1GPFK+gwHG/TEfiMq2f6YVrONSk9XNq9U6RWmPo69BDAHA
/+s1BIcIyGDlmxaqpMTTnBYhV7Xt1bLRZHFkoNRQbvHP3pBzaRk=
=5hAt
-----END PGP PUBLIC KEY BLOCK-----
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment