Skip to content

Instantly share code, notes, and snippets.

@rietta
Last active January 12, 2016 04:31
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save rietta/79900d8df4d7a6adb85d to your computer and use it in GitHub Desktop.
Save rietta/79900d8df4d7a6adb85d to your computer and use it in GitHub Desktop.
Encrypts a hello world greeting to my personal public PGP key that's published at https://keybase.io/rietta.
SELECT
ARMOR(PGP_PUB_ENCRYPT(
'Hello, World',
DEARMOR('-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v2
mQINBFPRvdYBEACeM7pbpqxheVpIkfNSt1I//MLMmmvV/2XlJFj0z4zTOZPU5KTl
DNpfV0bHSUU5pOs9hSkM5WP+uWTAl/X5a5Ty7Vdr0r//sYNYt05ummAZQEwfaSzj
Ys57Ks1yC/31SOM/bOvWOIf/+D/GVAEuJTdfwic/Vv56ixuk8skLjWTmxBBCFpbF
dEXWyxuADvftrhaGq16xaOx0vLFxagL7mpIEjVN2yYadR5iMm1g48lAG6Tc/JEPY
tDXzctfzHoPmPY93tk86vbHS7osA3R323LWnE3DgDRZ5Ux75+xGFjYiJLeUfuiqY
FB4/MefjvnnxXryHQf7LiKLbW++TsUBRGtXa/ebjixX440IP4EHEZK76F0U+EvQw
WMIP6QrOXLaa8sX2w8wjo3R8qnSIg8Dl93qK5qJ2jVsamCzsuDONieyiiuThLN92
LEYpc0rrvzgx5Ims98Cob/k7X0eHO57TxrnhpOEymwnyJnGaBwwlJzup8uEWo6kN
I09Hj6GcBP6acY8knSasnTIjrIET+3PH1NshFO1A6bo6dkASOZNCLzdxvNQ2RcxP
Rczmj0AoY4UKRDRYRo2OSS6RgfBAfRuCMq2Sb1KUWwhwt1Td67rl7PWM3Fj/E8Hj
7q5UfaHL0NSWaHRP23QzYAws9VDGQYv6sGC/BEfxP9IDF3zHew1ZyK2dLwARAQAB
tCJGcmFuayBTLiBSaWV0dGEgPGZyYW5rQHJpZXR0YS5jb20+iQJABBMBCgAqAhsD
BQkLVwcABQsJCAcDBRUKCQgLBRYCAwEAAh4BAheABQJT1UtxAhkBAAoJEGWM1enA
BLrj3+gQAJNQ/FfJpwlLEwLy0HD80dhPXIOjlSdrzadWTsqy3iCHYfrot3yKYeDf
INyWaI7YWfV7TSvNRIPdQ2LBbiG/vruz8nX6U0nbD6evVw4Bj5Er/Slc1F7dBQ8d
FXWnraeD0tI8T+2OmAV1OAFOicVqJoHcOk4dvjn3VrWnac+oW200sTk8jYIOmiLl
1nRkf61jlNpgO+/MtmGhuCaLOzZwgx7fq61fvZ9WNlUVPgLWxZ3ssZ86BPDIJc+s
dbERlC9UY8NxrQdIpAMoKqbGx67HwbSNXaxAHHE2EMaqvUlECMiSMkOTPI89saSY
OHZKd3g/GVreTC54mQ1Pxpm4tYaUgXc0Mjor6jm4YIB8JJbXXRC2IWmmhPpXx3Fn
fze5lvw+T6tG0KbzMLOlD7uTayZ3eUFHd1FavohecK5n0ga1zL0ine15N+4cP/Vp
WVkqtbQT9PXk4XGWCEk5jyewAchV6CgkiNTdcdaUpEmoa+gWJjHsNbDxfTHlBjgL
/lD7aM/ou07ZA6zP9PgZcSAgiqjnGuxcAS1fAIKtVTgg7LNI997ZDJg2hN7wWc6U
e61onZy2KQ42Fr2yr2QTtftpcYjnL8CjCvstXRuaGrl+6p5J/BJ5jPAroVgCCwJM
ZIRX7HSgkq7bBUdqapUbxLOX6Gnk+VbkHTkdhn+wZPfoNKXdSI3jiEYEEBEKAAYF
AlPVXiAACgkQu9L44h+QFq/b+QCgn8bHp5UVMquZPLQsOUVj9wvjLgIAn3J5PKTF
/eGkTxXKJwvWAGZoomqptCNGcmFuayBTLiBSaWV0dGEgPHJpZXR0YUBnYXRlY2gu
ZWR1PokCPQQTAQoAJwUCU9VLZgIbAwUJC1cHAAULCQgHAwUVCgkICwUWAgMBAAIe
AQIXgAAKCRBljNXpwAS64xjaEACByy7Bb5Gcwy1VjjbCE5yRFnbnvEjonZuuTw30
II2iqsb10Ut2mcK5YjG6x1t32zNOsaZzpx3mPGJVWDic2ZEG/t1z22soYy4+FVRZ
BHaaMhtaHi0Q96Nk4TrHs4tAGaT+mPMPoNS3jqLlvQxNVLAGv2S/e/eRU1Lrco/5
3L4yf7ne9LyK9ITEbRgyf8k3apH63m112Dg09qf2bMWn3YZeYMsiU9x8noYx8Xb/
p9120NNLeGce09rJ7fYjKyOiKmWn42hlT6tzjrhwVAsng+5heqzb0nSZUiF0V7RK
O2tV09JOgkBQGiG2anBn10nOetyewheA2YAuSplqCE8y87jtM7AEiX50LUdGG4qi
Q3NFrDDHH5vYb1fL9GX0/M43wTdf9OyG33J9V4zutj03E1XxdQuh3USpaLyUpd3v
BVdWZ6oISoEvud1TaDq4rjDRxRKq3vwFJ17kLUQlX+wgCiNuWnNl5mEUx8y1vpAZ
4jhQKhTMSB5u8sSzMsqdTZFb/BFRUlyJpj69cbgSZNMbg8F3BrY/2k3tYKrcVcCg
EQhfTjgbjr2O1BU9eFC6rXDG2JvzFMfb3M3fy8htjZmg+7EleoTHy8VN37DTySaf
Ro/Xd/H1ez1dpzd4leEnNysLuEI2Y4VLSzdWwdiH+pF9dSqR2HlBxDb97IWl4M95
11ccnYhGBBARCgAGBQJT1V4hAAoJELvS+OIfkBavdbgAoM6LGmoxzQ5JATx5C4Rt
75Z/FbsBAJ0SgyQxtax/a9U+32mq4WLT06VNzLQoRnJhbmsgUy4gUmlldHRhIDxm
cmFuay5yaWV0dGFAZ21haWwuY29tPokCPQQTAQoAJwUCU9VLSAIbAwUJC1cHAAUL
CQgHAwUVCgkICwUWAgMBAAIeAQIXgAAKCRBljNXpwAS648uVD/9yP3hNMEedJsNQ
hJ2/Bv58dzrfoKv/Fdw/bKea5/KuYqEhSB5OeLym3NFiFIx0zeZUiKCfhd/yrP7o
T2NCflGYiKvd4X5mm6XeBUQ2NAAhTzDMAW1Ryg2SHmvaxaqNyL8x7AYkLKGZhv67
IHXHtCAXfMRHwg6iBxrNRNXizox9n2AX4Vavn2Uv6jCx7G94JAP5uNk1fICY0iWa
MylP0JeAtHWZLA2MjVk4gWnrIo40oNSdUiNNvMDaSYbORRefCXKrRzOxYUfAZdfC
RO2VEuVPz+o3WzuY/i4j9Z1mR6X/hpplcV1wyFtFuyXfKXZTwJojM0zrmyWj27SY
lBxvkhV/pb5Se5N+jw56mVvAlLsJx0KqsRl6/3z+CEDbMfejs8b2cT36aT2hclaA
fYi7lJ+9GZeFSedgRAc6q/0fRBNhTLyw2VAnPfvdPQmVFplVscowdKxNbGChgbJ7
0UvxA2ikcKl7eLHy1DU+nivblAyu4ZdXHN8bAyG5qq9/RDI9JB8JrTyEjns86fcp
C+siEpTIWrXwgOgZM9lRRI+Cjr1n+X7qYbkwVLvDvLrgMqNmeLSdbzYtX4a311oW
BHmVSvRihrNlFfEcEKvukm2w6sKc2gYpESPYhomw013vcp+6UK7XflXIIM+78hre
KOk7MPkNaS07B/ObnKZQmmXR/w8wV4hGBBARCgAGBQJT1V4hAAoJELvS+OIfkBav
r+UAoOKCK3nDEvdY2mJQeHCZe2cU+vcxAKCH7nlUZ8a7VdGlHStULDUAFxaI4bkC
DQRT0b3WARAAyMeFNBqXHnwfjqvacCFhXTWtFR4+jwjRcUObtgde+eW1OKgOGsob
TwkhBTe0cUjnSPgGeqJP4G3MjTVo6+aAmAcvEPoIfDm369vN2oSjP3RQFH5og8j3
HKA41S9d4yukwMJUZdHjTMKC6n7aosO8YKNwTsBgTR8v2lNxSkB/UWK2vzUf+OCm
AB6nm1RlMZgnLJhDYuhamYQqhuaygpuAJ+BDIWOpwW2Vx3Jjmymkb7lS1D5rpLlp
5aZunuFQPODMbo0hzLpmaGr0HEyk4B0K8wVb6Iwg05Dafjd0irLqLRoxSUSON/UL
58ISF80tqGiaqjP7RWG7rBO+/kUQY/8cnYP55n1kYm7JKfHN1LiiBjpJDB5UdehW
Ou+/7SG6RwpVB9O4IPWxdRKwuP2RpC2uTfKzqVvwBIakEYohcpswnk/PYVde9a5a
r3FfDjqrdXlh4Zmaujpwu0gA08NBDwz5/AJytUbjD1Neg9nXwOkxnu31mAYl8RTD
ms4XEpAi4hVqhwQCD3lHPXgL3uEt7rrzUEYYEm3k+Ukryme93jSUVakIXD5HBwsz
+tn9dgvM2tJsPW1Gjge+NeTl3YWhZNrbJNQhnM3ioswen3UgECGWfGPFJLwSG9QM
RwA16u+hc6d6a23FqjH+5gGOw0G0sQxVBuDs07U+6mgX5KaRP2BImk0AEQEAAYkC
JQQYAQgADwUCU9G91gIbDAUJC1cHAAAKCRBljNXpwAS644B4D/9sJZBzbfj8VmRJ
2OgGzP5JuvoyX4sqICQDm9PEQ0RA558vm1g46uYLVHtEMDa/2xQ+HqEXwMhLbbKw
Ok+fbSGFYPkj4TN/GJwsI9sxupbWnHfHLWuIxv3I7SNeIHzdyjdxWOPhbGc4CHIN
oNyJB2dHrH8UfkMW60hM9S04pmu2pVWTRwDgdYe0yceifQwYlJQUk6UWELoL+WpZ
iyMlzpVwqSem5+ld2t8ZN1N5xhpIarLPpI449Co6cEq1zDuDhPTVR8o3iDxdpBxo
p79LEGAiIow+moJSog/q/3G6513oRA8sTZJnivUmP9mwDORz6+9+tNbRvOQRLTHW
GAJMgQVppeKdsCIGrsEa1qc8ukTIej4ER5mxWKtKPJCrOTmV1uQL80P1Vx3uvgDa
3sFrPahjo+7ACkkn6Ddrv4u1uNiv3GJjCSqn/k3xKYIwqfcqrpC2a5Vs9M/Kw9iM
ufboa/JGUl4IOJeMvNTWEh35EDpw/pU/YOTfSuKzsjSYG7k+fXGIeJAdR1SFcG69
HzvU8Ex9lEmZbtps3Bxi5/lb+YerowCspVWP2AwAblWq/qqlb4vn2fC+wWwhyeoU
NaAsPHSAwOdM/oe2pOXlDwQ7wIHLva7lOYHVKMiUPHfbGfv61VDyE3yX6q8qpZ5y
iChky5yTrKJ3i5GikdTSeb1VSGOCjg==
=Q+PC
-----END PGP PUBLIC KEY BLOCK-----')
)) AS message;
@rietta
Copy link
Author

rietta commented Jan 5, 2016

One example invocation resulted in:

-----BEGIN PGP MESSAGE-----

wcFMAx79Htw/t0ZjARAAhOBYc8/7sQcIIBwKxP8P61LcA0LtYSnMeVCiwnzfzCSeT+UvhCKhCkBI
4NvsjrKlCTJv+qYu3suIRXhS5jhuVK5PDoqcX8oPBHh8bMHkn3hX5Ze16S4PHJlWeWCf6R4VW94M
pwopI93nFRTZYFHe9pRsFMXUtA+5YF0XX5kzBzi03/Uo1lt9lSDBo3xoBBEQuyvSkzjCQAmmN1RD
gdKEcMIBttzFj59WIWt+GP7N3yNBEIi5hdMfmfJ6nSZttvW6xybQk2ZdE/RJHrBSOWZWZ4/cImMu
T6wS/TVUYh1Lm6wp5SeqfAW3T+BGyyS7cy6cKlZCzVaG7/uV/+LJIEZfAkH1I+NqPP+/AvP2t7ml
2gsHs3+xcyWYAXhoQP/rd0iLWD5Icj7tl2zUt7cauSuufG+KgCZreTS4sh/knqF0nxb49CGYM6AY
S0o3GsRnsDW1DYZPFI6m5JpqJYTnZdA6csmTaAo696wKxfKPZF+NyqAdPnISKcCkeXUISZNpRU9o
iSa4qzrdyV/1TpfXptdw3eVDxjLtXOHjWn5DT1ayYl6+k3jcoTMCLSBiPerGY4qK/2XQTW2G+ZEM
R34OD3v9yvMi9X6/aW07fiKq07iRb8nh8IoMJTXhji+ZjZXINFt7RNqBdPfkpRf/A4ubXQW+zCuk
8q1XjGwX9dlbir2/7H7SPQHfq07Scf5g8+UWpuCSixpczy/kia06hZpn6Aguv1WA6/un0CPYaZy1
cloSuhBkBr2ifo95f3VlkkzyelY=
=8Ufw
-----END PGP MESSAGE-----

Which I can decrypt, but no one else can.

@wireddude
Copy link

Cool!

@rietta
Copy link
Author

rietta commented Jan 6, 2016

These are the setup instructions on a brand new Ubuntu Linux server instance (probably also applies to Debian).

Install both postgresql and postgresql-contrib (important).

sudo apt-get install postgresql postgresql-contrib

Enabling pgcrypto for a database requires superuser privileges so,

sudo su -l postgres
ENTER YOUR SUDO PASSWORD

psql project_database
CREATE EXTENSION pgcrypto;
\q
exit

Return to your project database needs using the regular non-root, non-admin user with access to the encryption database encryption functions documented in http://www.postgresql.org/docs/9.4/static/pgcrypto.html.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment