Postgres: Howto display PDF from byta column?

We successfully use Grafana to visualize the result of some SQL queries on Postgres. In one table we have a binary column (bytea) containing a PDF. See here for details on byeta: https://www.postgresql.org/docs/9.6/datatype-binary.html

We’d like to give the dashboard user the ability to click on a link/ icon… which then downloads the PDF. Alternatively opening a new tab and displaying the PDF directly is also fine ;-).

I am new to Grafana but I generally see two ways to achieve this behaviour:

  1. have an external web service which generates the PDF on request and include a link to that web service in the Grafana dashboard
  2. let Grafana somehow magically grab the binary PDF data from the database cell and “stream” it to the browser with the correct mime type so that the browser will either download or display the binary data.

I’d prefer the second solution since it doesn’t require a separate web service. I’ve seen that there are also scriptable dashboards so maybe this could help… Any ideas, suggestions, examples on how to download/ display a PDF without the extra web service?

Many thanks,

Maik

Tip: data URI

So encode binary data (in PostgreSQL) and create proper data URI in Grafana, e.g.:

<a href="data:application/pdf;base64,JVBERi0xLjUKJcOkw7zDtsOfCjIgMCBvYmoKPDwvTGVuZ3RoIDMgMCBSL0ZpbHRlci9GbGF0ZURlY29kZT4+CnN0cmVhbQp4nCXKOwqAQAwFwD6neLVFTFbXzYJYCNoLAS/gBywEbby+gkw7woqHLgiEJRhijhxShNXK1ijuleYC5z8+9069U2zYkFLF2TJ8QTkqNMC3VlSCVJ0fNDhNNOEF3X8UfQplbmRzdHJlYW0KZW5kb2JqCgozIDAgb2JqCjk1CmVuZG9iagoKNSAwIG9iago8PC9MZW5ndGggNiAwIFIvRmlsdGVyL0ZsYXRlRGVjb2RlL0xlbmd0aDEgODA2OD4+CnN0cmVhbQp4nOU4fXAbVX6/tyt/xrFkx19BUfSUjZ34bEn+SCAJcbyxLdmOnVj+AskhidbS2lJiS0KSHQJlUDsHl1EuJUcLHJApNwxlGErLmtCrOSjxtdD2hlJoy7S9K7nzzXHDDEcmKQe0kwO7v/d25Ti5ANNO/+uT3u7v+/uttU4nZ1QogQyIIIemlUQFMQkA8PcApDw0m6ZtA5W3IrwIIPzTRGJy+vG/uOMTANNLAAUvTU6dmPjT6qeeBCiJAIh3RFQl/ErNoUYA8+to4+YIEvYtnShA/HPEN0em03c9B7+wAFgo4papeEj5kNQRBJsQL55W7krcZNqJ/i27EacxZVql7aYexO8AWDObiKfSYdi8DGB9l/ETSTXR//j4G4hjTOIZpBH8sFWCYD7DBRH+X6+801AJPXltYIYEv16zxOdhPTwGsPwRw65el/qXr/xfRlGo374Lz8BLcBp+DIcMhhd8EIUZpKxeP4R/RCpbPhiD5yD7JWafh3nk63JBeJBlcsPlg0fhHPztNV58MA33YCx/Dj8mzfAjHJU4fEwK4XfhDbT6MdL238iUUIqXCQ5OrKL+OzwhnIJ9wvuIPMY4gluwwOtwlhxGy2nM8/RKxrt/y+i34F68DkMEZhHmK6/t859A0fKvMat7YR/8HuyFqVUar5InxWLs3wg8iTX9Iae5c8yCHvGo8H1B+OIPEPkOTOJWCOYunBb3fkmF/sdLHIW1pF6shaIbcYVtYF66IrQsfyJuhmIYXb6coy33Lf9aVJZipiOmDXltpje/ykf+d0zTqA3Lv1y6ZymcdyDvGezWswBy98GxgH90ZHho0DdwYH9/377enm6vp6uzY6/cvqdt9627du645ebtzU1ul7Nx65a62s3SJoe9pqLMYi5du6a4qLAgP88kCgQaqUaCHk2spWVeRfJISo+zkXpqIl3ORo/kDWpUoRreTHVSTw8nSYpGg1Srw5uyihzUZJScuE5S1iXlFUliobthN3MhUe2tLonOk7FBP8Knu6QA1S5yeD+HTXUcWYuIw4EaPCoWLfVo3tlI1hPEGMncmuJOqVMtdjbCXPEaBNcgpG2VEnNk6x7CAWGrZ9ecAIVrmVvM1KOENd+g39NldTgCzsZerVTq4izo5Ca1/E6tgJukURY6nKJzjQvZb89bYDzYUBKWwsodfk1UUDcrerLZb2llDVq91KXV3/1+DWauao1Sl0drYFb7hlb89F11SbS8WotEs58CpiNd/OhaimJQ8mstnwIDNaFTI0N+B1tWL9Y6m/VK1JsNZpX55cy4RC1Sdq6kJJvwYLnB50cT88s/OGXVvN8OaJZghOwKGKl7h/q0dYMH/ZpQ66URBSn4bZccO6yOshUZ35exAcuCxcEKOxysDKfmZRhHRMsM+nWcwrj1RZDdDQFNCDLOQo5TOco4mRxnRT0oYW/7hv1ZzVTbG5Y8WPFTipYZx+k6yhojWbTSz6wOKVteRne6A1yWYlS94SjV8uqwSKi1WgHnhqlkLRwp/Uy/XbSig7qycrpTQjPMjkfyBI3vbKQGDVAsdE+DPggjfk3uQkBWjI555prcqKEEsWHRLt5MzS0ltAqpY6W7LCxPdNjPVQw1raJTg2DI0NLcHn6uqCcb7NJDYLakQf/L0Lq8OLeNWs+1wjYIdDHhqk6csjpP1h+e0OxBaxjP3QT1Wx2aHMAOByS/GmBjhxWqX7Ty4QjwWRnx9w1LfYNj/h1GIDqDmTPVeq4zI/mtuhkcQK2wtpD6BasYQEELEqgXAaljN161gtpC3BYsOKeywe3YTf3ECjlpDEOrpx61y5Bj+DVG89g4dfbkrOUzFO109lgdAYe+nI0CsqnhGDUKWVF7cix8TCGjEOezs4eTWC1r2NBTv6RKASlCNdnnZ7mx8vAqG8XgNTd6NXINtqpYWCZwIDuHsGJq3gbr6uJq3RxfQXuuY/fm2DRbKPUNZ5lxyTAIGHmvBmyE5R1lVv4sYAdawmcvteCR5gc6OyfL7DBHdjEjUm84Kw37d3NpfJ7ca72b+SqHPtI30uFsxEdbx5xETg7OyeTk8Jj/ZQv+Ljw54n9RIEJnsCMwtxl5/pcp/tHgVIFRGZEhlCHM0hAihVze+rIMkOFcEydwPDRPgNMKczQCoXlBp1l0R3XckQwCckw6R85Jm5BWqNMynMbXHLCSycV5cqFcJJcIawXrHGGkF5HyA/wdW0TgXAlZS6xzqDXEyfMkM1ckW3WJDErIeoQnR6+6Hh3znyvBv85WfkVHHWzhuNREsNn4Z8VDw2xQficQyQYD7LBBFbYGv0Qj0h5sk7QHA8kv0YoltUNbI3Uwejujt+v0fEYvwBElVQTVM9h7n0bYBBz0O/BI0pt+ZM1aLrJOBfChkrX80onBOZb6RU18A6rBAffLgxvNpvLy6pri6uJNUnV5RbkvUGFdS32BtVU2a4F1MGAqsIjgC4hmWSIZiYBEdjZJZFEiCxwPSkReBbdL5BCuO9lKHj50KAntDVDT3tpQBq01HGRQWTnZubOslX1aW5ub1m3Z7qjeQ1pbqiorBGnTliobqa50bL+FbKuTNhWUVbW2fPcYKRS+cbr3pTf+9c07J/KfXpKPC+F775s5EDj6uTix3nnL5sYrH15aulLVU79U43bXiAcWXnF8UVaGP3dg3/JH4gf4+3sd2CAjD1SY1sD69RaTZaN9ncUXWFdpLvEFzFCwwRcosKxHBaF6MCBUgZ10++xEtpMmO6F2gviCnWQ4RQeCnG5km2Tr8CHQ08ulytPN5Yrf5qbafImWbStvbamuayOYdhkmSioww1vK6iQq/POdjy7d95N3p+L5f0S60kv/tWTPfPPOsUBy6XPvGPn5fxJS7bj/kxrnlZfXO8lbr72yRfgAU8RT0I5xP5f3FFjJzfK/lVdViVZr9bpik21DlXW91RdYXwkV6yqwievMBaW+wJoCYrURk418YiOv2Mg3bSRtI2EbabAZ9GPv28i7NvK6jbxkI3/IJZDdt0rnTzj9INep4PQ3c3S0NWIjXTn6rl9xQ0/byJlVrrbZyGYuATYiXLaRRRt5x0a+ZyMZG0nYiGwj1EYsNqJx1MLlVgaLrSPJq+vwIWPlmDfiAA7hqknUp7B6p9GXddL2W7Zvuxn7UiltwanLt5HWSqmMj+HPn3rqjx/e39Hs3NTUvu3KlTeXTKdEf/OWjncW1711T2Xi8bMjn3/mcDod/J0X35qfenhf9RHz7k8Fu/6+9Xdd7/zD1V/TePo+wE6tvIzx/kGBY8kDt6+mXLNM+TvxAuDAvU94jvUb113wIfkzQ9oEm5hvHoEF30HwTV34a/FvQOTcjSS2YvO2FfsEJW8zYAEK8H1Jh0Ww4luZDptQ5qQB50EpvjvqcD6+wz5jwAVwN74R6HAhVBC3ARdBKek04GISI4MGvAY2COdX/kPgwrc2HV4L28UiAy6Fm8Q9LHoTe7N5XvQbMAFqMhmwAKWmzQYsws2mFgM2oUzEgPNgg+mkAefDRtPTBlwAn5j+yoALYWve9w24CDbk/dSAi4X38q4Y8BrYUfgvBlwCdxSVGvBaOFp01IBLYVvRu13RyWg6ercapmElrdBQPHEiGZ2MpOnWUD1taWpuot3x+OSUSjvjyUQ8qaSj8ZiruPN6sRY6hCZ6lHQj7Y2FXP3RcVWXpcNqMjoxpE7OTCnJvamQGgurSeqk10tcj9+mJlMMaXE1Nblar3KvF46m8HUrnVTC6rSSPEbjE9cGQpPqZDSVVpNIjMboqGvYRX1KWo2lqRIL05EVxYGJiWhI5cSQmkwrKBxPRzDUozPJaCocDTFvKddKBqvKMZxWZ1W6X0mn1VQ81qGk0BdGNhKNxVON9HgkGorQ40qKhtVUdDKGzPET9FodilwFc4nF4rNoclZtxLgnkmoqEo1N0hRL2dCm6YiSZklPq+lkNKRMTZ3Ank0nUGscm3Q8mo6g42k1RQ+ox+lQfFqJPefSQ8HaTGBRaXQ6kYzP8hidqVBSVWPoTAkr49GpaBqtRZSkEsKKYdmioRSvCBaCJpSY0zOTjCdUjPT27v6rghigXs1UfGoWPTPpmKqGmUcMe1adQiV0PBWPH2P5TMSTGGg4HXGuinwiHkujapwq4TAmjtWKh2amWZ+wzOlccEooGUdeYkpJo5XplCuSTid2ud3Hjx93KUZrQtgZF1p2fxUvfSKhGv1IMivTU/3Y/hhr3QzvL0tiuLefDiSwPl4MjhoCjTQ3ms2uZsMFljGaSKdcqeiUK56cdA94+6ELojCJO437blAhDBS3griCUAjikIATkORSEaRS2IrUery3QBM046bQjVJx5E+hPoVOhJOoxa4KtxuHGLigmHO+2loLQkNGFD1cuxGhXtQPoYV+1BtH7mq7FIY5JYrPWaY5CTMYh4KUvZBCLRVlwlyCghP319n4Ov5tHEqtcFowrib8uKD1hrpfZzmKtiivdZpzWKzTPP5jSIuj3ldVhKKcyvuXQo7KsTC3ymyPosQwl/JxTVaLNPcW41IjN/A4gB4nUD/Ee5mTDHHbbCZ0y3GEI0ZVj2LFkzyCMNfL5ZZCz7/dgxtPxzCPbpb73M/pDE9xXgfiKSMvvWYjPIo4UlktjmMkzG+EwwqvZ5hrsymLGZrjOHf0K/1QQ1cx+hLjPmaNKJlOo1HvCX5Ncb8x9EF5fHqXr/VNeZ0UXnW909PITXPZENKn8HPCOGfTWBXd17hxko7zcxkxMp7mdikcwPtxPhVx3reYYxPv8dWq6HMzYUwq5boJhOM8i1wdnbw3LBOVR8oghZ/9cdSY4r712CJ8OhTeW9XodZpnkKtX2MiURZ3gFCd4+FywE68aNb0dnxT9N7SoV3D1bLKeTPF4U6tsx3i04ZUc9WozqSnDk57xFH8iHVvpzwSfN72iYW7N+SU1n+C1SRte4zyiMH70juuzFUfdGd4P/Tzp05z+rcopvL5xQy/Bn0tpI5Zpfj4ifAITsAt/W7oxOvZx8TlcfWpCxplxGTG7/9d6LK4Er+Dq85FciWUaY+w3Tn9s5dTNrDq/uU4M4zOonz8vEsb8eI3K0esssFNz/VOzGf01X5eFPo1RxNM8nhSvpYvnMIn8AfTQz39H87XswJhusOaKfHvHiQqERMgkvp7iKyUcIEdglOyFNiLjXUZeB947EWd3F2mDDMq1IX0P4ruRfis+PO14bcc9gPtB3CbcukQTSrjx7jZwJ+KNqPE2XgnfjNqOVHbfh3gP3ruNuxfpHrx7DLwXcbxDkBSwl05+PU9M8jmy+AV5+wtCvyD3/Yb4fkMyH5/5WPiPy/X2Fy6fvywMXDpy6YVLYtMlYr5ECuGi5aLvYvBi4uL3LuYXmz8iJfArUvaLxR32n7VdGP1p23ujcAEzu9B0wXchc0G7kHeBiKPviVV2ywJdaFpILGQW3llYXLi8UJh57cxrwl++6rabX7W/KtjPDZy775wYfJaYn7U/K/ieCD4hnDlLzGftZ91nxccfc9kf695of/SRLfbFRy4/IswvL5x7ZG2Z91UyQPqhDWt44Jy4bH9hbyXZj2mZ8WrH7cY9gDuO+0Hc+N6D4nbcbtIv7xCPPEzWPGR9qOGhex469VBe4oHMA2ceEDP3n7lfeGH2/KyQ8tXb47EGe6z7G/b1rTWjBa3iaD66Qe9y73jtVm/wiGw/gkIHx5rsY9319nWt5aN5mLAJBc2iXWwXB8S4+KB4XiwoHPJttA/iXvRd9gmyr6jEax6wD7gHxPnlRVntc6C1fYl9mX1ir7fe3tO9w27utne7u9/u/ln3pe78I93kSfx6X/Ce94qyt97tlb0bHd4NPdbRqtbK0TJiHrW0mkcFgo1uhVG3edksmM1HzPeZRTO+cAqZKpJH5smZuZHhhoa++YLloT6t0HdQIye12mF2lQfHtPyTGoyOHfTPEfL7gftPn4YOW5/WMuzXgrZAnxZGQGZABgGLba4KOgKpVLqBL9LQgPAMXqFhpgGJh1M6FVb40JAiKXxGpbgSaWACOk7w2sB4SGB6BLUPp4BdGLNBV2LaKcMcV9YvHKg5/N/quAtMCmVuZHN0cmVhbQplbmRvYmoKCjYgMCBvYmoKNDUyNQplbmRvYmoKCjcgMCBvYmoKPDwvVHlwZS9Gb250RGVzY3JpcHRvci9Gb250TmFtZS9CQUFBQUErTGliZXJhdGlvblNlcmlmCi9GbGFncyA2Ci9Gb250QkJveFstNTQzIC0zMDMgMTI3OCA5ODJdL0l0YWxpY0FuZ2xlIDAKL0FzY2VudCA4OTEKL0Rlc2NlbnQgLTIxNgovQ2FwSGVpZ2h0IDk4MQovU3RlbVYgODAKL0ZvbnRGaWxlMiA1IDAgUgo+PgplbmRvYmoKCjggMCBvYmoKPDwvTGVuZ3RoIDIzNS9GaWx0ZXIvRmxhdGVEZWNvZGU+PgpzdHJlYW0KeJxdUMtqwzAQvOsr9pgcgmSnpBcjCCkBH/qgbj9AltauIJbEWj747yvJaQs9SMywM8Ps8kv71Dob+Rt53WGEwTpDOPuFNEKPo3WsqsFYHe+s/HpSgfHk7dY54tS6wTcN4+9pNkdaYXc2vsc9469kkKwbYfd56RLvlhBuOKGLIJiUYHBIOc8qvKgJeXEdWpPGNq6HZPkTfKwBoS682qpob3AOSiMpNyJrhJDQXK+SoTP/ZsfN0Q/6S1FSVkkpxKOQCdcFnx4yPm74VDLu6pyW1/1pCXohSg3LTUq1XMo6/D1b8CG7yvsG3aByEwplbmRzdHJlYW0KZW5kb2JqCgo5IDAgb2JqCjw8L1R5cGUvRm9udC9TdWJ0eXBlL1RydWVUeXBlL0Jhc2VGb250L0JBQUFBQStMaWJlcmF0aW9uU2VyaWYKL0ZpcnN0Q2hhciAwCi9MYXN0Q2hhciAzCi9XaWR0aHNbNzc3IDUwMCA1MDAgMzMzIF0KL0ZvbnREZXNjcmlwdG9yIDcgMCBSCi9Ub1VuaWNvZGUgOCAwIFIKPj4KZW5kb2JqCgoxMCAwIG9iago8PC9GMSA5IDAgUgo+PgplbmRvYmoKCjExIDAgb2JqCjw8L0ZvbnQgMTAgMCBSCi9Qcm9jU2V0Wy9QREYvVGV4dF0KPj4KZW5kb2JqCgoxIDAgb2JqCjw8L1R5cGUvUGFnZS9QYXJlbnQgNCAwIFIvUmVzb3VyY2VzIDExIDAgUi9NZWRpYUJveFswIDAgNTk1LjMwMzkzNzAwNzg3NCA4NDEuODg5NzYzNzc5NTI4XS9Hcm91cDw8L1MvVHJhbnNwYXJlbmN5L0NTL0RldmljZVJHQi9JIHRydWU+Pi9Db250ZW50cyAyIDAgUj4+CmVuZG9iagoKNCAwIG9iago8PC9UeXBlL1BhZ2VzCi9SZXNvdXJjZXMgMTEgMCBSCi9NZWRpYUJveFsgMCAwIDU5NSA4NDEgXQovS2lkc1sgMSAwIFIgXQovQ291bnQgMT4+CmVuZG9iagoKMTIgMCBvYmoKPDwvVHlwZS9DYXRhbG9nL1BhZ2VzIDQgMCBSCi9PcGVuQWN0aW9uWzEgMCBSIC9YWVogbnVsbCBudWxsIDBdCi9MYW5nKGVuLUdCKQo+PgplbmRvYmoKCjEzIDAgb2JqCjw8L0NyZWF0b3I8RkVGRjAwNTcwMDcyMDA2OTAwNzQwMDY1MDA3Mj4KL1Byb2R1Y2VyPEZFRkYwMDRDMDA2OTAwNjIwMDcyMDA2NTAwNEYwMDY2MDA2NjAwNjkwMDYzMDA2NTAwMjAwMDM2MDAyRTAwMzM+Ci9DcmVhdGlvbkRhdGUoRDoyMDE5MDkxMDExNDAzNyswMicwMCcpPj4KZW5kb2JqCgp4cmVmCjAgMTQKMDAwMDAwMDAwMCA2NTUzNSBmIAowMDAwMDA1NTkxIDAwMDAwIG4gCjAwMDAwMDAwMTkgMDAwMDAgbiAKMDAwMDAwMDE4NSAwMDAwMCBuIAowMDAwMDA1NzYwIDAwMDAwIG4gCjAwMDAwMDAyMDQgMDAwMDAgbiAKMDAwMDAwNDgxMyAwMDAwMCBuIAowMDAwMDA0ODM0IDAwMDAwIG4gCjAwMDAwMDUwMjkgMDAwMDAgbiAKMDAwMDAwNTMzMyAwMDAwMCBuIAowMDAwMDA1NTA0IDAwMDAwIG4gCjAwMDAwMDU1MzYgMDAwMDAgbiAKMDAwMDAwNTg1OSAwMDAwMCBuIAowMDAwMDA1OTU2IDAwMDAwIG4gCnRyYWlsZXIKPDwvU2l6ZSAxNC9Sb290IDEyIDAgUgovSW5mbyAxMyAwIFIKL0lEIFsgPDRCRTg0NjlERTJFRTdDNzM2QzNFN0RDRUYwNDUyMzQzPgo8NEJFODQ2OURFMkVFN0M3MzZDM0U3RENFRjA0NTIzNDM+IF0KL0RvY0NoZWNrc3VtIC9DRjQ2RjYxQ0NBMkE0QjAxNzcyMUI3NUEyMEJFMzNFRAo+PgpzdGFydHhyZWYKNjEzMQolJUVPRgo=" target="_blank">PDF</a>

Thanks for your support.

At the end we created a link in Grafana with the following content:

data:application/pdf;base64,$__cell_0

whereas $__cell_0 refers to the first column. In Postgres we select this column using:

SELECT encode(my_binary_pdf_column, 'base64') FROM sometable;

Many thanks!