Secrets when connecting to DBs
I should have dealt with comments I got to my posts on how I deal with secrets in my work notes, here, and here. Better late than never though, I hope.
Comment from Stefano R
The first one is a link to post titled How I use :dbconnection in org files. It
describes a nice way of setting sql-connection-alist based on the contents of
a file, in his case ~/.pgppass.
Comment from Harald J
The other starts with a function for searching ~/.authinfo.gpg for entries of
the form
machine <host>/<dbname> login <username> password <password> port <port>
and then setting sql-password-search-wallet-function and sql-password-wallet
to tell sql-mode to use it
(defun my/sql-auth-source-search-wallet (wallet product user server database port)
"Read auth source WALLET to locate the USER secret.
Sets `auth-sources' to WALLET and uses `auth-source-search' to locate the entry.
The DATABASE and SERVER are concatenated with a slash between them as the
host key."
(when-let (results (auth-source-search :host (concat server "/" database)
:user user
:port (number-to-string port)))
(when (and (= (length results) 1)
(plist-member (car results) :secret))
(plist-get (car results) :secret))))
(setq sql-password-search-wallet-function #'my/sql-auth-source-search-wallet)
(setq sql-password-wallet "~/.authinfo.gpg")
The value for sql-connection-alist is then as normal
(setq sql-connection-alist
'((some-dbname (sql-product 'oracle)
(sql-port 1521)
(sql-server ...)
...))
and the blocks in orgmode looks like this
SRC sql-mode :product oracle :dbconnection i3v1e-ro :results raw
SELECT to_char(sysdate, 'YYYY-MM-DD HH24:ii:ss') AS today,
to_char(sysdate + 1, 'YYYY-MM-DD HH24:ii:ss') AS tomorrow
FROM dual;
SRC
Thoughts
Both of these feel closer to the intent of sql-mode in a way. I'll have to try
using sql-connection-alist at some point.