Some time ago I wrote about the ways you can provide psql with a password to the database. I didn’t mention one of the simplest ideas I had, partly because it didn’t work, and partly because a variant of it did work, but was quite involved. So, here is my initial idea and its working version.
At first, I figured that I could make psql ask for password using the --password option, and just provide the password on the standard input, using redirection. This was a bad idea for two reasons. First and foremost, psql just doesn’t work like that – when you give it something on stdin, it just treats it as an SQL script to run and reads the password directly from your terminal. And even if that worked, what I really wanted then was to start an interactive psql session (just without having to type the password), so piping anything but the terminal to stdin would make it impossible anyway.
When I shared my initial idea with a colleague, he told me about the expect utility (warning: the latter link points to expect’s homepage on SourceForge). I have to admit that despite using GNU/Linux command line for over two decades, I have never heard about it earlier! It is a wonderful gem, probably easiest to describe as “AutoHotkey for the command line”.
The main drawback of expect is that it is written in Tcl, and instead of defining its own DSL, it just extends Tcl. This is of course a good idea – why write something from scratch when you can extend an existing language – but Tcl is, well, Tcl, an old, rather non-Lispy programming langauge I know basically nothing about. Still, there are both examples on the web and ChatGPT, which seems to be able to write at least very basic expect scripts.
For example, assume that I have a password to the database stored in pass. I want to get the password from the store (which, depending on my gpg-agent configuration, may or may not require typing some passkey interactively) and run psql, providing the read password to psql’s prompt. Here is what ChatGPT produced, which seems to work well enough for me.
# Retrieve the password from pass set password [exec pass show database/password] # Launch psql and provide the password at the password prompt spawn psql -h localhost -U mbork -d db expect "Password: " send "$password\r" interact
I read portions of Tcl’s manpage (and also Wikipedia page) to actually understand what is happening here. set and exec are commands from Tcl, not expect, and the first line sets the password variable to the output of pass. spawn, as you may have guessed, spawns a new process, which becomes the “current process”. The expect command waits until the output matches the given pattern. As you may guess from the fact that its name is the name of the tool, it is one of the central features of expect, and can be pretty complicated – here it just waits until psql prints the string Password:_ (where the underscore denotes a space). In a sense, send is its opposite – while expect pretends to be the eyes of the user who looks at the output of an interactive command, send pretends to be their fingers typing on the keyboard. The sequence \r means the “enter” key. Finally, interact sort of “gives back” the control to the user, although it is far from that simple in general.
And that’s it! I just wrote my first expect script (well, I had it written for me…), and here it is. Funnily enough, the manpage for expect (which is pretty long at over 1700 lines) gives an alphabetical list of expect commands, but says first
Commands are listed alphabetically so that they can be quickly located. However, new users may find it easier to start by reading the descriptions of spawn, send, expect, and interact, in that order.
and these are exactly the commands we needed, and almost in that order (not that it is a surprise).
I can only suggest that you read the expect manpage for more details, especially the hints near the end. I had some doubts whether using an obscure (well, at least one I didn’t know earlier) utility like this for something as delicate as passwords, but it seems that (a) expect was explicitly created with typing passwords like in our example as one of the uses, and (b) it was written by hackers infinitely better than me over the course of more than 30 years, so I am pretty confident that it is stable, mature and safe enough to be handling my passwords.