For the English part of the blog, see Content AND Presentation.
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.
I wrote a few times that I use and like Ledger a lot. As some of you might know, I even wrote a booklet about personal accounting, using Ledger in examples. One of the nice things about Ledger is that it comes with an Emacs mode to edit its files (which is not a surprise, given that it is written by John Wiegley himself). That is not to say, though, that it suits my needs perfectly.
Most of the transactions I enter in my Ledger file are purchases. They almost always have the same structure – one or more categories of expenses followed by the source, which is almost always some cash account (like Assets:Cash:Wallet:Me) or a payment card (like Assets:Bank:Wife). Ledger mode provides rudimentary support for inserting a transaction based on history (try C-c C-a and C-c
<tab>, that is, ledger-add-transaction and ledger-fully-complete-xact, respectively), but I wanted something even more automatic. Of course, this is Emacs, so coding something like this should be a breeze.
I started with skimming the Ledger mode manual and sources to make sure I don’t reinvent a wheel. A bit surprisingly, I didn’t find a ready-made command to insert a transaction, so I decided that I’ll just need a bunch of inserts and a call to ledger-post-align-xact.
I strive to enter my Ledger transactions on the day they were made, but I don’t always succeed, so my inserting command should first ask the user the date (defaulting to today). The next thing is the description, which should obviously have history and completions based on previously entered descriptions. I’m going to use persist-defvar from the persist package for that so that the history is remembered across Emacs sessions. (Note that I specifically do not want to use all the description from my Ledger file as autocompletion candidates – the file is over 8 years old, and I often don’t want to mimic transactions from many years ago.) Next comes the source – I could also use a persisted history for that, but since the possible source accounts are usually very limited (in my case, there are basically three of them possible – my wallet, my wife’s wallet, and my wife’s bank card, since I don’t use any), I decided to go with read-char-choice. This approach requires configuring the source accounts in the init file, but this is something done once, so it’s not a big problem. Finally, the user needs to provide the amount. Here I implemented a simple trick so that I won’t have to type the decimal point – if the amount is an integer greater than 100, it is treated as the number of cents (or grosze in my case) and divided by 100. This lets me save one keystroke.
The code is pretty simple. I thought about making this command a bit more versatile and allowing for non-interactive use (via the trick I wrote about almost a decade ago), but ultimately decided against it – the only purpose of my command is to allow entering transations quickly and interactively, so I figured that the added complexity is not worth it.
(require 'persist)
(persist-defvar mbork-ledger-descriptions ()
"Alist of transaction descriptions for `mbork-ledger-insert-transaction'.
Each element is a cons where the car is the transaction description and
the cdr is the default target account.")
(defcustom mbork-ledger-source-accounts
'((?c . "Assets:Cash")
(?b . "Assets:Bank")
(?d . "Liabilities:Card"))
"Alist of source accounts for `mbork-ledger-insert-transaction'.
Each element is a cons where the car is the character used to select the
account and the cdr is the account name.")
(defcustom mbork-ledger-default-commodity "PLN"
"The default commodity to use with `mbork-ledger-insert-transaction'.")
(defun mbork-ledger-insert-transaction ()
"Quickly insert a Ledger transation.
Ask about the date, description, source and amount. If the amount
entered is an integer greater than 100, divide it by 100, so that you
can enter e.g. 12.34 USD as `1234' for faster typing."
(interactive)
(let* ((date (ledger-read-date "Transation: "))
(date-encoded
(when (string-match ledger-iso-date-regexp date)
(encode-time 0 0 0 (string-to-number (match-string 4 date))
(string-to-number (match-string 3 date))
(string-to-number (match-string 2 date)))))
(description (completing-read "Description: "
mbork-ledger-descriptions
nil nil nil
#'mbork-ledger-descriptions))
(source (alist-get (read-char-choice
(concat (mapconcat (lambda (char)
(format "%c: %s" (car char) (cdr char)))
mbork-ledger-source-accounts
"\n")
"\n")
(mapcar #'car mbork-ledger-source-accounts))
mbork-ledger-source-accounts))
(amount (let ((input (read-number "Amount: ")))
(if (and (integerp input)
(> input 100))
(/ input 100.0)
input))))
(ledger-xact-find-slot date-encoded)
(insert (format "%s %s\n "
date description))
(save-excursion
(insert (format "\n * %s -%.2f %s\n\n"
source amount mbork-ledger-default-commodity)))))
That’s it for today, see you next time!
CategoryEnglish, CategoryBlog, CategoryEmacs, CategoryLedger
Some time ago I embarked on a quest to learn how to provide psql with a password in a non-interactive way (for example, in a script).
It turns out that there are quite a few ways to do that, and some ways in which you can’t do that.
For example, psql has the --password and --no-password command line options. Neither takes any argument – they only mean to never ask the user the password or always ask the user the password. The reason you might want to use the former is when you run psql in a script and you want it to fail immediately if no password is available. The latter can be used when you already know the user will need to type the password, so there is no point in trying to connect to the database, discovering that the password is needed, asking the user for it and connecting again – psql might as well ask the password first and only try to connect to the database once.
You might think that there is no option to give psql the password on the command line for security reasons – after all, if you give a password on the command line, then every other user on the same system can see the password with ps -ef. However, in fact there is a way to do exactly that – psql can accept an URI in place of the database name, and an URI may contain a password.
Another way to tell psql the password to use is to set the PGPASSWORD environment variable. As the manual says, this is “not recommended for security reasons, as some operating systems allow non-root users to see process environment variables via ps” – although apparently my Arch Linux does not allow that.
The recommended way is to use the “password file”, which is ~/.pgpass by default. This has one significant drawback – it means that you’ll have your password unencrypted in a plain text file. That is perfectly fine on an application server (where you need to store your database password unencrypted anyway), but it’s a big “no” on e.g. a laptop, which can be lost, stolen etc. (You could encrypt your disk, of course, either the whole partition or just your home directory, but that is not something that’s always feasible.) Interestingly, psql (in fact, it’s libpq, but psql uses it, of course) has some requirements with respect to the password file. For example, it must not be readable by anyone but the owner (in other words, it’s permissions should be at most 0600). This is perfectly understandable. However, I found the hard way that it must be a “regular file”. I didn’t understand this requirement at first, and here is how I found out about it. I wanted to create a .pgpass file so that I could use psql without typing the password. However, I already had the password in the .env file. Trying to be a good programmer following the DRY principle (which is not an absolute rule, but makes sense in this context), I didn’t want to put the password in two separate files. What if the password changes and someone updates only one of these files? So, I wanted to be fancy and use a named pipe for that. One process would be a simple Bash loop using sed to extract the hostname, port, database name, user name and password from the .env file and the other would be psql, reading the pipe ~/.pgpass. That way I would even be able to change the password mid-session, \c to another - or the same - database and it would still work (because psql reads .pgpass again when you open a connection to a database). To my big disappointment, when I set this up, psql told me this:
WARNING: password file "/home/mbork/.pgpass" is not a plain file
and my carefully crafted plan fell apart. That was a pity – while this solution would be a bit contrived, it would still be a nice way to create a “dynamic file” whose contents depend on the contents of another file. (Now that I think of it, I guess I know the reason this didn’t work. Here is my conjecture. libpq probably has to read the whole .pgpass before connecting – or at least read enough of it to find the password to use for the given connection data. The named pipe approach, however, would give it the same line over and over again, without any termination condition. If that line was the one libpq needed, everything would be fine – but if not, the process would hang indefinitely. On the other hand, creating a one-line regular file and removing it after connecting would not work, either, since psql may need .pgpass again if the user wants to establish another connection using \c, for example to connect to another database.)
There is probably another way to achieve a very similar result, namely create a custom fuse filesystem which would generate the .pgpass file dynamically from the .env file, much like the named pipe approach, but more sophisticated. While this could actually work – and in fact, I’m tempted to try and do this some day – this would be more of an interesting hack than a production-grade solution. It seems to me that fuse is much, much too complex to be a good fit here. (In fact, I finally went for the simplest solution of all – wrapping psql in a simple psql.sh script which first parses .env, then creates and populates .pgpass, and then runs psql. Simple but effective.)
The last way to provide passwords to psql I know about is complex enough (and interesting enough) to deserve its own article, which I am going to post quite soon. (And this time I mean really soon, not in a few years!)
That’s it for today, see you next time!