2

(Note: I would normally use gpg versions of these files but for debugging I've scrapped gpg until I can get it working without encrypting!)

I'd like to store my username/password credentials securely for use with Emacs for database logins - using the sql-postgres interactive command.

According to the 27.1 release notes I should be able to store database connection details in ~/.emacs.d/sql-wallet, and I should be able to use the netrc format which I've successfully used in my ~/.authinfo file.

By default, the wallet file is expected to be in the 'user-emacs-directory', named "sql-wallet" or ".sql-wallet", with ".json" (JSON) or no (NETRC) suffix.

However, I have been unable to make this work using netrc, json, or by adding the database details directly to my authinfo file.

I have tried various different formats for the netrc file, including the traditional:

machine HOST login NAME password VALUE port NUMBER

And on inspection of the source in sql.el, I've also tried various variations on this, which revolved around various permutations of the above with the below:

product DATABASE-TYPE server DATABASE-ADDRESS database DATABASE-SCHEMA-NAME user USERNAME secret PASSWORD

None of them seem to work. To be 100% clear on the above the lowercase words are hardcoded strings in the file, and the upper-case words are supplied by me as the details of my specific connection.

Trying to debug this - I can see that sql-auth-source-search-wallet is being called with sensible values prompted from sql-postgres.

However the call to auth-source-search as far as I can tell is bombing out with:

auth-source-backend-parse: invalid backend spec: 126
auth-source-backend-parse: invalid backend spec: 47
auth-source-backend-parse: invalid backend spec: 46
auth-source-backend-parse: invalid backend spec: 101
...and so on

Which suggests that it can't parse my netrc file.

I've used .authinfo to great effect to connect to REST, SSH, e-mail, etc - and it's always worked flawlessly, so I don't think I'm doing anything obviously or fundamentally wrong.

Google is throwing up near-zero hits on using this to connect to a database tho - I'm wondering if anyone has got this working, and what was the exact format of the netrc or json file they used to achieve this?

Or any other ideas?

P.S. I'm aware I can just store the details in cleartext in my init file using the custom interface to give defaults for everything, including the password - but for obvious reasons I don't want to do this on a shared server!

2 Answers 2

2

This is a late answer but hopefully this will help someone.

The short answer is that for the entry in ~/.emacs.d/sql-wallet, the machine field needs to be a combination of server and database-name, concatenated with a slash.

For example

machine 192.168.1.80/MyDataBaseSchemaName login mydatebaseusername password theDbPassword

The key to figuring this out was the docstring for sql-auth-source-search-wallet:

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.

1

I was working on a similar problem - moving clear-text database passwords from init.el (in sql-connection-alist) to something more secure. I decided to use gnome Login keyring, and it's supported by emacs' auth-sources.

My first version was just calling elisp functions in sql-connection-alist, retrieving those secrets using well-known attributes. But, as auth sources is supposed to be somehow used auto-magically when I do sql-connect, I started exploring this direction.

I enabled debug logging in auth-sources and the first thing I discovered was that it's not trying to do any search at all. That's because you have sql-password-wallet pointing at your 'wallet'. In my case it's the Login keyring - "secrets:Login".

After that I saw messages like the ones in your question: "invalid backend spec", multiple times with different numbers. It was a bit puzzling, but in fact I was just setting that wallet variable in a wrong way, so that every single character in the string was treated as a separate backend spec. A proper way to specify the wallet looks like this:

(setq sql-password-wallet '("secrets:Login"))

Note that it's a string inside a list.

After that it was just a matter of having secrets stored in a proper format. I didn't have to combine the host and the database name in a single field. The following statement worked for me:

(secrets-create-item "Login" "my db login" "my-password" :user "my-user" :server "my-server" :database "my-database")

It creates secrets in the Login keyring using Secret Service API. I guess it will also work with a .netrc file using the same set of attributes.

Now sql-connection-alist declaration has the same set of attributes plus the 'product' attribute which I omitted when created the secret. And the password is looked up automatically.

Your Answer

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

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