The modern web stack can get very complicated, especially with the trend towards microservices. I currently work on a team owning many services, each with their own databases, connection parameters, etc. At times it can get very complicated to deal with. For example, suppose a bug occurred in a service and I need to debug it’s database. This involves a number of steps:

1. Start the CloudSQL proxy to the instance that the service is running in.
2. Print the contents of the K8s secret containing the connection information.
3. Decode the base64 encoded information.
4. Connect using the connection string.

As a shell script, it usually looks something like the following:

cloud_sql_proxy -instances=my-instance:5432
# Open new shell
kubectl get secrets -n production
# Look for secret I want...
kubectl get secret MY_SECRETS_NAME -o json -n production | \
  jq -r .data.postgresAddress | \
  base64 -D; echo
psql MY_CONNECTION_STRING

This workflow while not the longest in the world is still tedious, especially since I don’t know really the secret that I need. Manual visual scans for data make writing a script hard. Was it my-service-secret, myservice-secret, or my-service-postgres-secret? I could make this a shell script by making the environment and secret name parameters but I think we can do better. How does using a “framework for incremental completions and narrowing selections” sound? I’ll go through implementing this as both a bash script and an Emacs lisp program.

Completion with FZF

We’ll try implementing this automation first on the shell. The script would look as follows:

#!/bin/bash

cloud_sql_proxy -instances=k8s-production-123456789:europe-west1:my-instance &

K8S_ENV=production
SECRET_NAME=$(kubectl get secrets -n "$K8S_ENV" | grep postgres | fzf | awk '{print $1;}')

PSQL_CONN_STR=$(kubectl get secret -n "$K8S_ENV" -o json "$SECRET_NAME" | jq '.data.postgresAddress' | sed -e 's/^"//' -e 's/"$//' | base64 -d | sed -e 's/k8s-production-123456789:europe-west1:my-instance/localhost/')

psql "$PSQL_CONN_STR"

What’s impressive is that there is only a few lines of code. Compared to the Emacs section below this was much easier to write. I had to use sed to strip off the beginning at ending quotes so that the base64 command could properly decode it.

Enter Emacs

Our automation will hinge around the use of either of two very powerful Emacs packages: helm or ivy. These packages let you easily select various options from a list using fuzzy matching. If you’ve used the fzf tool, it’s just like that (but perhaps with more bells and whistles). You could even utilize fzf to achieve a similar effect. Let’s try to implement this in Emacs lisp though. The following will be various sections explaining parts of the program.

Starting sql-proxy

(defvar my-k8s-instance "my-sql-prod")

(defun zac/start-sql-proxy ()
  (start-process "sql-proxy"
                 "*cloud-sql-proxy*"
                 "cloud_sql_proxy"
                 (concat "-instances=" my-k8s-instance)))

This function simply starts a sql-proxy process that runs in the background. We factored out the instance name into a variable. This would let us easily swap out instances in the future.

Prompting user for the secret

;; first we create a function to return a list of the secrets
(defun list-postgres-secrets ()
  (let ((secrets
         (mapcar #'(lambda (l) (car (split-string l)))
                 (cdr (split-string
                       (shell-command-to-string
                        (concat "kubectl get secrets -n "
                                my-k8s-instance))
                       "\n")))))
    (seq-filter #'(lambda (s) (and s (string-match-p "postgres" s))) secrets)))

;; We can now ask the user to select a secret with the following:
;; (let ((secret (completing-read "Select DB to connect to:" (list-postgres-secrets))))
;;   ;; we have the user-selected secret
;;   )

This function shows the various functional aspects of Elisp. First we get a list of the secrets by getting the output of the kubectl get secrets -n production. We split the output line-by-line. Then split each line and get the first element. Finally we filter the secrets that contain the string postgres in it.

Obtaining connection data from secret

(require 'json)
(defun connection-data-from-secret (secret-name)
  (let* ((json-string (shell-command-to-string (concat "kubectl get secret -n production -o json " secret-name)))
         (json-object-type 'hash-table)
         (json-array-type 'list)
         (json-key-type 'string)
         (json (json-read-from-string json-string))
         (data (gethash "data" json)))
    (dolist (key '("database" "hostname" "password" "postgresAddress" "private_ip" "private_ip_connection" "username") data)
      (puthash key (base64-decode-string (gethash key data)) data))))

This function returns the various components of our secret in a hash map given the secrets name. Once we get the JSON string of the kubectl output, we can call the function json-read-from-string to get back a hashtable. I am expecting the secret to contain the keys “database”, “hostname”, “password”, etc. There is a function to decode base64 called base64-decode-string and is as straightforward as you’d expect. Instead of having a long list of puthash for each key, I use the function dolist to iterate through the keys and reassign it in the hash.

Putting it all together

(defun ta/write-pgpass-entry (data)
  "Clear .pgpass file and write DATA as entry."
  (with-temp-file "~/.pgpass"
    (erase-buffer)
    ;; Template: "hostname:port:database:username:password"
    (insert (format "%s:%s:%s:%s:%s"
                    "*"
                    "*"
                    (gethash "database" data)
                    (gethash "username" data)
                    (gethash "password" data)))))

(defun connect-db ()
  (interactive)
  (unless (get-buffer "*cloud-sql-proxy*")
    (start-sql-proxy))
  (let* ((secret (completing-read "Select DB to connect to:"
                                  (list-postgres-secrets)))
         (data (connection-data-from-secret secret))
         (sql-user     (gethash "username" data))
         ;;(sql-password (gethash "password" data))
         (sql-server   "localhost")
         (sql-database (gethash "database" data)))
    (write-pgpass-entry data)
    (sql-postgres)))

These two functions wrap up the functionality. connect-db is our command the end-user will run. It first has the user select the secret they want to use. It then gets the data from the secret and sets some defaults for the sql-postgres command. In order for the sql-postgres command to connect to the database using the password, we’ll utilize the .pgpass file feature of PostgreSQL. You can read more about the Password file here. This function opens the .pgpass file, erases it, and writes the new entry in it. You could also have it just append to the end if you want (you can go to the end of the file with (goto-char (point-max))).

Like a language vocabulary lesson, the following is a list of Elisp functions you’ve encountered in this section.

Final thoughts

Given these two example, it’s hard to argue for the Elisp version. That isn’t to say the Elisp version doesn’t have it’s advantages. For one, Elisp feels like a more fleshed out language rather than a bunch of pipes modifying strings. Due to us wanting to integrate with the sql-postgres command, we had to jump through a number of extra hoops. Without those steps it would essentially be a tradeoff between the Elisp functions (split-string, seq-filter, completing-read, json-read-from-string) and shell commands (awk, grep, fzf, sed). Also, integrating this into Emacs environment has it’s own advantages from easier editing to integration with other Emacs packages such as org mode.