sql and auth-source

sql and auth-source

[Robert via Users list for the GNU Emacs text editor]

Hello,

how to configure the sql mode to work with a wallet file?
A code example would be very helpful.

I found function sql-auth-source-search-wallet, but i don't know how to use it.
https://git.savannah.gnu.org/cgit/emacs.git/tree/lisp/progmodes/sql.el#n736
Help please.

--
Robert

Re: sql and auth-source

[Filipp Gunbin]

On 26/11/2020 12:46 +0000, Robert via Users list for the GNU Emacs text editor wrote:

> Hello,
>
> how to configure the sql mode to work with a wallet file?
> A code example would be very helpful.
>
> I found function sql-auth-source-search-wallet, but i don't know how to use it.
> https://git.savannah.gnu.org/cgit/emacs.git/tree/lisp/progmodes/sql.el#n736
> Help please.
>
> --
> Robert

Generally, (setq sql-password-wallet '("~/my/sql-wallet.gpg")) should be
enough.

But, from what I recently found while investigating how it (not) works
for Postgres, I can tell the following:

The value of sql-password-search-wallet-function is used for searching,
it's usually the function sql-auth-source-search-wallet.

You can test whether it works (like, is able to decrypt gpg and parse) by
something like the following:

(sql-auth-source-search-wallet sql-password-wallet 'postgres "my_user" "localhost" "my_db" 5432)

BUT, sql-connect and friends will actually call the function in
sql-password-search-wallet-function only if sql-postgres-login-params
contains password login parameter, and it doesn't (this can be checked
with (sql-get-product-feature 'postgres :sqli-login)).  This is because
the login function, sql-comint-postgres, does not make use of it.

So, for Postgres, this looks like it's yet to be implemented.

Nevertheless, I was able to at least make use of sql-wallet file to
auto-set sql-connection-alist with this code:

(defun fg-dotemacs-get-sql-connections (file)
  (mapcar
   (lambda (alist)
     (let* ((machine (cdr (assoc "machine" alist)))
            (machine-list (split-string machine "/"))
            (host (nth 0 machine-list))
            (database (nth 1 machine-list))
            (name (cond ((string= host "localhost")
                         (concat "local-" database))
                        ((string-match-p "\\.prod$" host)
                         (concat "prod-" database))
                        ;; more rules...
                        (t
                         (concat host "-" database)))))
       `(,name
         (sql-product (quote ,(intern (cdr (assoc "product" alist)))))
         (sql-user ,(cdr (assoc "user" alist)))
         (sql-database ,database)
         (sql-server ,host)
         (sql-port ,(string-to-number (cdr (assoc "port" alist)))))))
   (auth-source-netrc-parse
    :file file :host t :port t :user t)))

(setq sql-connection-alist
      (fg-dotemacs-get-sql-connections (car sql-password-wallet)))

Filipp

Re: sql and auth-source

[Jean Louis]

* Robert via Users list for the GNU Emacs text editor <help-gnu-emacs@gnu.org> [2020-11-26 18:20]:
> Hello,
> 
> how to configure the sql mode to work with a wallet file?

> A code example would be very helpful.
> 
> I found function sql-auth-source-search-wallet, but i don't know how to use it.
> https://git.savannah.gnu.org/cgit/emacs.git/tree/lisp/progmodes/sql.el#n736

What you configure is .authinfo then how I understand, the sql-mode
would search for user/password and server datails in .authinfo by
using that function. Unless you are programming you need not use that
function. And function is prefixed sql- only because it belongs to sql
mode, not that it is doing any SQL itself.

I have not configured authinfo as I mostly work with local database.

Instead I have configured environment variables:

export PGDATABASE="databasename"
export PGUSER='username'
export PGCLIENTENCODING='UTF8'

You better say what you wish to achieve, do you wish to remember
credentials for remote databases?

File ~/.authinfo is in format:

host localhost port port-number user user-ID password password

Re: sql and auth-source

[Robert]

The ideal solution will include:
- no passwords in init.el
- I connect to the database using sql-connect or sql-postgres (usually PostgreSQL)
- when connecting, I choose an alias to the database
- I am only asked to enter a password in order to decrypt the authinfo wallet file

--
Robert

Sent from ProtonMail, encrypted email based in Switzerland.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Friday, November 27, 2020 2:58 AM, Jean Louis <bugs@gnu.support> wrote:

> * Robert via Users list for the GNU Emacs text editor help-gnu-emacs@gnu.org [2020-11-26 18:20]:
>
> > Hello,
> > how to configure the sql mode to work with a wallet file?
>
> > A code example would be very helpful.
> > I found function sql-auth-source-search-wallet, but i don't know how to use it.
> > https://git.savannah.gnu.org/cgit/emacs.git/tree/lisp/progmodes/sql.el#n736
>
> What you configure is .authinfo then how I understand, the sql-mode
> would search for user/password and server datails in .authinfo by
> using that function. Unless you are programming you need not use that
> function. And function is prefixed sql- only because it belongs to sql
> mode, not that it is doing any SQL itself.
>
> I have not configured authinfo as I mostly work with local database.
>
> Instead I have configured environment variables:
>
> export PGDATABASE="databasename"
> export PGUSER='username'
> export PGCLIENTENCODING='UTF8'
>
> You better say what you wish to achieve, do you wish to remember
> credentials for remote databases?
>
> File ~/.authinfo is in format:
>
> host localhost port port-number user user-ID password password

Re: sql and auth-source

[Jean Louis]

* Robert <rchar@protonmail.com> [2020-11-27 09:52]:
> The ideal solution will include:
> - no passwords in init.el

I do keep passwords in init.el as it is personal file.

I do not keep passwords in init.el on remote servers. Then I would
prefer entering them. If it is multi user server then what if
administrator or some other user with access rights or backdoor is
listening on tty to read what I am typing?

Change permissions:

  -rw-------    1  50K Nov 25 22:04 init.el

Use better umask limits and also change permission on /home/user
directory to be user readable only if user is "protected" then
/home/protected would be:

  drwx------ 244  92K Nov 27 09:22 protected

Database password is not the only thing that is private, there are
other more important or more private things in the user's directory.

Unless init.el is not published for demonstrations it can be used to
store passwords.

> - I connect to the database using sql-connect or sql-postgres
> - (usually PostgreSQL) when connecting, I choose an alias to the
> - database

> - I am only asked to enter a password in order to decrypt the
> - authinfo wallet file

Interesting, as I may use those methods for program I am developing
when it comes to be used by public.

For Unix domain sockets I use trust method in pg_hba.conf

# "local" is for Unix domain socket connections only
local   all             all                                     trust

For remote databases SSL security with usernames and passwords is
necessary.