Emacs, sql-mode, Postgresql and inputing password

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.

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.

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")))))