Org-mode header properties from .authinfo.gpg?

Question

I was just looking at This literate SQL / org-mode example and see that org-mode supports username and password fields in a SQL source code block. As illustrated there:

** MySQL Analysis
  :PROPERTIES:
  :engine:   mysql
  :dbhost:   localhost
  :database: <redacted>
  :dbuser:   <redacted>
  :dbpassword: <redacted>
  :cmdline:  --protocol=tcp
  :exports:  both
  :END:

As fun as storing credentials in plain text is, I'm hoping someone can suggest a straightforward path to pulling those critical bits from a gpg-encrypted .authinfo.gpg file.

What's the best way of pulling those properties from .authinfo.gpg, emacs gurus?

Answer 1

You could use a suitable lisp-function which returns the suitable string.

E.g. if you already had function `my-secret-password' which picks the password for a user, you could write

** MySQL Analysis
:PROPERTIES:
:header-args:   :engine:   mysql
:header-args:   :dbhost:   localhost
:header-args:   :database: MY_DB
:header-args:   :dbuser:   DB_GUY
:header-args:   :dbpassword: (my-secret-password "DB_GUY@MY_DB" )
:header-args:   :cmdline:  --protocol=tcp
:header-args:   :exports:  both
:END:

Also note the syntax of the properties for the recent Org version.

It remains to write the suitable function (to replace the `my-secret-password' from above.)

I think this function could build on `auth-source-search' (and needs some energy to get into existence.)

Invested some energy (20161117):

After a little research I found a concrete example how to pull out a password from the return of `auth-source-search'.

With my personal settings I get my password for [email protected] with the lisp line

(funcall (plist-get (car (auth-source-search :user  "[email protected]")) :secret ))

You need to find such line for your 'dbpassword' and insert it at the right place at the properties. Possibly it is

:PROPERTIES:
...
:header-args:   :dbpassword: (funcall (plist-get (car (auth-source-search :user  "Peter@my-super-db")) :secret ))
...
:END:

Answer 2

Thanks to Marco Wahl and this emacs documentation I came up with the following emacs-lisp to put in the PROPERTIES drawer:

:PROPERTIES:
...
:header-args: :dbpassword: (funcall (plist-get (nth 0 (auth-source-search :user "Peter" :port 3306 :require '(:secret))) :secret))
...
:END:

The :port is not needed but I figured it would help narrow down the search for just MySQL DBs. The :require makes sure that the entry has a passphrase. I personally used this at the head of the file as #+PROPERTY: header-args :dbpassword elisp-func but the function should work just as well in the PROPERTIES drawer.

I did try Marco Wahl's answer and it didn't work for me, unfortunately. My solution is very close to his though so I'm sure we looked through similar documentation.