One-time passwords with Google Authenticator PAM (and friends)

PostgreSQL allows for more than plain password authentication in pg_hba.conf. One of the most flexible is authenticating against a PAM.

Let's see how this works with one-time passwords from  Google Authenticator.

1.) Install Google Authenticator on your Android or iOS device.

2.) Install the Google Authenticator PAM on the machine where your PostgreSQL server lives, like in Step 1 - 4 of this guide.

3.) Connect your device with the account on that machine.

4.) Create a login role for the database but without a password. Since roles without password can never log in regularly (See the PASSWORD remarks here), this limits this role to PAM logins.

5.) Configure a PAM service for PostgreSQL. E.g. create a file named postgresql where your PAM configs live, on Ubuntu this is /etc/pam.d/. The file should look like this:

auth         sufficient     pam_google_authenticator.so

6.) Configure PostgreSQL to use the PAM. E.g. a line in pg_hba.conf could look like this:

hostssl    all    all    127.0.0.1/32   pam    pamservice=postgresql

And that's basically it. Now, next time you login, PostgreSQL will ask you for a password that is generated individually on your device.

Of course you can use all kinds of PAM with PostgreSQL like this.

Unfortunately, I also found a few caveats along the way. :-(

First, PostgreSQL clients will ask only for one password, regardless if you chain n PAM's for n-factor authentication.

So if you e.g. chain a PAM against LDAP with Google Authenticator as the second factor, this won't work. This seems to be a shortcoming of the PAM implementation in PostgreSQL, not expecting multiple password prompts. It is still possible to enable n-factor authentication though, but only one PAM can prompt for a password. If the other factors are hardware devices like a fingerprint scanner that does not prompt for a password, you are fine.

Alternatively, you can provide your own PAM that takes all passwords in one prompt and handles them internally.

Second, PAM requires PostgreSQL clients to send the password in plaintext. So now is the time to switch on TLS and make it mandatory (Noticed the hostssl switch above?).

Third, some clients like pgAdmin3 break with one-time passwords, because they apparently open new connections without prompting for a password again, but re-use the initial one instead until you disconnect. This obviously does not work with passwords which are valid only for one login attempt.