5

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?

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

2 Answers 2

2

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:
6
  • I would need to wrap that in a src_emacs-lisp{} block?
    – Peter
    Nov 16, 2016 at 18:20
  • No, this is independent of src_emacs-lisp{} block. Maybe you can do that.
    – Marco Wahl
    Nov 17, 2016 at 11:23
  • I added more details to the answer.
    – Marco Wahl
    Nov 17, 2016 at 11:43
  • Don't you need header-args+, instead? Jan 29, 2017 at 20:54
  • 1
    I think the second colon of the argument keyword is too much. E.g. :engine: should be :engine.
    – Marco Wahl
    Jan 31, 2017 at 20:32
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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