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!