How do I use sql-password-wallet with sql-connect/sql-postgres in emacs?

Question

I'd like to make use of Emacs' sql-password-wallet variable (defined in the sql library) to decrypt my postgres connection credentials from my GPG encrypted auth-source.

The sql library seems to define the variable sql-password-wallet specifically for this but I just cannot figure out how to make use of it.

I'm a newcomer to elisp. I'm using Emacs 29.1.

I have the following in my init.el file:

(setq sql-postgres-options (quote ("--set" "sslmode=required" "-P" "pager=off")))
; Remove all default login parameters
(setq sql-postgres-login-params nil)
(setq sql-password-wallet '("/home/someone/.emacs.bedrock/sql-wallet.gpg"))

(setq sql-connection-alist
      '(
        (bigdb (sql-product 'postgres)
                   (sql-database (concat "postgresql://"
                                         "dbuser_ro"  ;; replace with your username
                                         "@db.example.com"
                                         ":6537"      ;; replace with your port
                                         "/bigdata"    ;; replace with your database
                                         )))))

When I invoke sql-connect, it still prompts me for the database password for the user dbuser_ro and, if I provide it, the connection works.

However, I want sql-connect to prompt me (when needed) for my GPG passphrase instead and not the database password. It should then find the appropriate auth-source in my sql-password-wallet file and use that as my postgres connection credentials.

By the way, I can visit my GPG encrypted sql-password-wallet file and emacs, through the auth-source library, decrypts it for me and asks for the GPG passphrase as required.

Any idea on how I can get sql-connect to make use of sql-password-wallet mechanism when connecting to a postgres database?

Update: I managed to get org-mode's babel ob-sql to work by using the following elisp to look up the auth-source.

(defun dug/lookup-password (&rest keys)
   (when-let ((result (apply #'auth-source-search keys)))
     (funcall (plist-get (car result) :secret))))

I added the following to an org-mode document and was able to query the database with emacs caching my gpg passphrase.

#+BEGIN_SRC sql :engine postgres :dbhost db.example.com :dbport 6537 :dbuser dbuser_ro :dbpassword (dug/lookup-password :max 1 :login "dbuser" :port 6537 :require '(:login :secret) :create t) :database bigdata

Flushed with success I tried to add this to my sql-connection-alist like so:

(setq sql-postgres-login-params nil)
(setq sql-connection-alist
      '(
        (bigdb (sql-product 'postgres)
               (sql-password (dug/lookup-password :max 1 :login "dbuser_ro" :port 6537 :require '(:login :secret) :create t))
               (sql-database (concat "postgresql://"
                                     "dbuser_ro"  ;; replace with your username
                                     "@db.example.com"
                                     ":6537"      ;; replace with your port
                                     "/bigdata"    ;; replace with your database
                                         )))))

I get prompted for my auth-sources passphrase but, unfortunately, I still get prompted by sql-interactive-mode for the database passphrase.

I realise this is moving away from my goal of getting the sql-password-wallet mechanism to work.

@phils thanks for the tip about auth-source-debug! I do see the new function decrypting my .authinfo.gpg but there's never been any mention of decrypting my sql-wallet.gpg.