Emacs, sql-mode, Postgresql and inputing password

Question

I'm trying to connect to a PostgreSQL DB thru Emacs using the sql-mode. I start Emacs, command M-x sql-postgres and it prompts for User, Database and Server but not for the password. An empty buffer opens and whatever I write, I get:

the Password for user james:
psql: FATAL:  password authentication failed for user "james"

I can login using psql, though. I'm running GNU Emacs 24.4.1 in Linux Antergos, PostgreSQL is 9.3.5.

Thanks..

Undershot

Answer 1

Kevin's answer provides an interactive solution.

Otherwise you must use a non-interactive method of authenticating (such as a .pgpass file).

---

My original (and incorrect) suggestion was to enable the "password" option at M-x customize-option RET sql-postgres-login-params RET.

The sql-*-login-params variables (and associated customize widgets) are generalised across all database types; but not all of those options are applicable to all of the databases.

Where applicable, passwords are read by Emacs and then used in the command line. psql doesn't allow the password to be supplied as part of the command, however, so there's no way for Emacs to use this approach here. This is why the password option is disabled for the postgres login params.

Agnail

I get [no match]. – Undershot

Sorry for being unclear. There is no match for sql-postgres-login-params. – Undershot

It's available as of Emacs 24.1. Do make sure the library is actually loaded first (e.g. call M-x sql-postgres), or Emacs won't know about it. – Agnail

I see. Now it prompts for the password and all the other credentials but I still get the psql: FATAL: password authentication failed for user "james". I triple checked and psql still works fine by itself. – Undershot

Ah. Ok, this was all a red herring :/ The customize interface is generalised across all databases; but not all (non-default) options apply to all databases. Where applicable, passwords are read by Emacs and then used in the command line. psql doesn't allow the password to be supplied as part of the command, however, so there's no way for Emacs to use this approach here. Hence passwords are disabled by default, and you possibly do need to use some other approach. – Agnail

No fish. I briefly tested this in two other systems and they worked fine. Apparently there is something wrong in that particular setup. Thanks for your troubles. – Undershot

It worked with postgres on a different server? Which versions of postgres and emacs? – Agnail

Answer 2

In case someone else comes looking I solved this by calling send-invisible and typing in my password, after the sql-postgres command completed. Emacs version 24.5.1.

1. Initiate postgres connection
2. At blank screen M-x send-invisible
3. Type password.
4. Profit.

Friedrich

Answer 3

Here is a simpler solution exploiting the fact that you can pass a password in the connection string. For example:

(setq sql-connection-alist
      '((my-db (sql-product 'postgres)
               (sql-database "postgresql://user:pass@host/database"))))

Then disable all postgres login params except for database.

---

As a bonus here is my config for using the unix password manager pass which I recommend:

(defun my-pass (key)
  (string-trim-right
   (shell-command-to-string (concat "pass " key))))

(setq sql-connection-alist
      '((db (sql-product 'postgres)
            (sql-database (concat "postgresql://user:"
                                  (my-pass "db/user")
                                  "@host/database")))))

Ruckus

If string-trim-right throws an error, call (require 'subr-x) before my-pass. See ergoemacs.org/emacs/elisp_trim_string.html. – Colubrine

Answer 4

I find it simplest to explicitly define the connections within the sql-connection-alist. Once the connections are defined, you can connect using sql-connect. A particular connection can be selected using up/down/previous/next. Avoid storing passwords by reading them in during connection with read-passwd.

;; Remove all default login parameters
(setq sql-postgres-login-params nil) 

;; define your connections
(setq sql-connection-alist
      '((primary-db (sql-product 'postgres)
                    (sql-database (concat "postgresql://"
                                          "username"  ;; replace with your username
                                          ":" (read-passwd "Enter password: ")
                                          "@host"      ;; replace with your host
                                          ":port"      ;; replace with your port
                                          "/database"  ;; replace with your database
                                          )))
        (secondary-db (sql-product 'postgres)
                      (sql-database (concat "postgresql://"
                                            "username:"
                                            (read-passwd "Enter password: ")
                                            "@host"
                                            ":port"
                                            "/database")))))

First, clear out the sql-postgres-login-params variable. There's no sense creating defaults since they'll be defined in the connections list. If sql-postgres-login-params is non-nil, you'll be prompted when connecting. This is annoying and defeats the purpose of explicitly defining connections.

Second, use "Connection URIs" to define a connection.

These have the form:

postgresql://[user[:password]@][netloc][:port][,...][/dbname][?param1=value1&...]

In the example above, we are creating connections called primary-db and secondary-db for postgres. The connections use only the "database" parameter. The database is a connection URI made by concatenating strings into the appropriate form. See how read-passwd prevents us from storing the password? Replace each parameter with your own. Pay attention to the various syntax elements for host (@), port (:), and database (/). If you don't want to store a particular parameter, I suppose you could use read-string just as with read-passwd, but that's basically reinventing the tooling which uses sql-postgres-login-params.

See C-h i d g (elisp) Association Lists for more about alists.

Furbish

Answer 5

I solved this temporarily by creating a .pgpass file where I stored my connection credentials. I am not comfortable with that and would like a solution that requires inputing the password at login.

Undershot

Answer 6

I have this problem also. If you go to the empty buffer & hit enter, do you get a password prompt there? I do, and when typing in the password the mode works just fine.

Root

Note that this puts the password in plain text in your SQL window. If somebody is looking over your shoulder, this won't do. – Cilla

@UdayReddy oh, I agree. It wasn't meant to be a solution, but I felt it might help people more "in the know" to work out a better solution, as it might help people figure out what the actual stumbling block is. – Root

Answer 7

works for me setting the PGPASSWORD environment variable, e.g. (setenv "PGPASSWORD" "whatever your password is")

Steerageway

Answer 8

One can monkey-patch sql-comint-postgres to use the PGPASSWORD environment variable with the standard connection list fields:

(defun sql-comint-postgres (product options &optional buf-name)
  "Create comint buffer and connect to Postgres."
  ;; username and password are ignored.  Mark Stosberg suggests to add
  ;; the database at the end.  Jason Beegan suggests using --pset and
  ;; pager=off instead of \\o|cat.  The later was the solution by
  ;; Gregor Zych.  Jason's suggestion is the default value for
  ;; sql-postgres-options.
  (let ((params
         (append
          (if (not (= 0 sql-port))
              (list "-p" (number-to-string sql-port)))
          (if (not (string= "" sql-user))
              (list "-U" sql-user))
          (if (not (string= "" sql-server))
              (list "-h" sql-server))
          options
          (if (not (string= "" sql-database))
              (list sql-database))))
        (process-environment
         (nconc
          (list (format "PGPASSWORD=%s" sql-password))
          process-environment)))
    (message (prin1-to-string process-environment))
    (sql-comint product params buf-name)))

Titanic