PostgreSQL supports SSL, and SSL private keys can be protected by a passphrase. Many people choose not to use passphrases with their SSL keys, and that’s perhaps fine. This blog post is about what happens when you do have a passphrase.
If you have SSL enabled and a key with a passphrase and you start the server, the server will stop to ask for the passphrase. This happens automatically from within the OpenSSL library. Stopping to ask for a passphrase obviously prevents automatic starts, restarts, and reboots, but we’re assuming here that you have made that tradeoff consciously.
When you run PostgreSQL under systemd, which is very common nowadays, there is an additional problem. Under systemd, the server process does not have terminal access, and so it cannot ask for any passphrases. By default, the startup will fail in such setups.
As of PostgreSQL 11, it is possible to configure an external program to obtain the SSL passphrase, using the configuration setting ssl_passphrase_command. As a simple example, you can set
ssl_passphrase_command = 'echo "secret"'
and it will apply the passphrase that the program prints out. You can use this to fetch the passphrase from a file or other secret store, for example.
But what if you still want the security of having to manually enter the password? Systemd has a facility that lets services prompt for passwords. You can use that like this::
ssl_passphrase_command = '/bin/systemd-ask-password "%p"'
Except that that doesn’t actually work, because non-root processes are not permitted to use the systemd password system; see this bug. But there are workarounds.
One workaround is to use sudo. So you use
ssl_passphrase_command = 'sudo /bin/systemd-ask-password "%p"'
and then put something like this into /etc/sudoers:
postgres ALL=(root) NOPASSWD: /bin/systemd-ask-password
A more evil workaround (discussed in the above-mentioned bug report) is to override the permissions on the socket file underlying this mechanism. Add this to the postgresql service unit:
ExecStartPre=+/bin/setfacl -m u:${USER}:wx /run/systemd/ask-password
ExecStartPost=+/bin/setfacl -x u:${USER} /run/systemd/ask-password
This enables access to the socket before the service starts and then removes it again. Note that the + in the Exec lines, which runs those lines as root, requires at least system version 231. So for example CentOS 7 won’t support it. Maybe don’t do this.
Anyway, if you have this set up and run the usual sudo systemctl start postgresql, you should see
Enter PEM pass phrase:
Then you can enter the passphrase and the service should then start normally.