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
  :engine:   mysql
  :dbhost:   localhost
  :database: <redacted>
  :dbuser:   <redacted>
  :dbpassword: <redacted>
  :cmdline:  --protocol=tcp
  :exports:  both

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?

  • Maybe you can save the entire file with gpg. something like mysqlstuff.org.gpg – Jules Nov 15 '16 at 23:36
  • That would technically work, though it would thwart being able to share the org file as documentation. – Peter Nov 16 '16 at 18:02

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
: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

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 marco.wahl@gmail.com with the lisp line

(car (aref (aref  (plist-get (car (auth-source-search :user  "marco.wahl@gmail.com")) :secret ) 2) 0))

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

:header-args:   :dbpassword: (car (aref (aref  (plist-get (car (auth-source-search :user  "Peter@my-super-db")) :secret ) 2) 0))
  • I would need to wrap that in a src_emacs-lisp{} block? – Peter Nov 16 '16 at 18:20
  • No, this is independent of src_emacs-lisp{} block. Maybe you can do that. – Marco Wahl Nov 17 '16 at 11:23
  • I added more details to the answer. – Marco Wahl Nov 17 '16 at 11:43
  • Don't you need header-args+, instead? – user3341592 Jan 29 '17 at 20:54
  • If I understand correctly the "+" applies when using the same property on multiple lines. See orgmode.org/Changes.html, search for 'header-args:'. – Marco Wahl Jan 31 '17 at 8:53

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

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.