Bruce Momjian

Postgres Blog

This blog is about my work on the Postgres open source database, and is published on Planet PostgreSQL. PgLife allows monitoring of all Postgres community activity.

Online status:
Unread Postgres emails:
Email graphs: incoming, outgoing, unread, commits (details)

Security Removable Certificate Authentication

Wednesday, January 16, 2019

I mentioned previously that it is possible to implement certificate authentication on removable media, e.g., a usb memory stick. This blog post shows how it is done. First, root and server certificates and key files must be created:

$ cd $PGDATA
# create root certificate and key file
$ openssl req -new -nodes -text -out root.csr -keyout root.key -subj "/"
$ chmod og-rwx root.key
$ openssl x509 -req -in root.csr -text -days 3650 -extfile /etc/ssl/openssl.cnf -extensions v3_ca -signkey root.key -out root.crt
# create server certificate and key file
$ openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/"
$ chmod og-rwx server.key
$ openssl x509 -req -in server.csr -text -days 365 -CA root.crt -CAkey root.key -CAcreateserial -out server.crt

Then ssl must be enabled, cert authentication specified, and the server restarted:

# configure server for SSL and client certificate authentication
$ psql -c 'ALTER SYSTEM SET ssl = ON;' test
$ psql -c "ALTER SYSTEM SET ssl_ca_file = 'root.crt';" test
# configure pg_hba.conf for 'cert'
$ sed 's/host/#host/' pg_hba.conf > /tmp/$$ && cat /tmp/$$ > pg_hba.conf && rm /tmp/$$
$ echo 'hostssl    all             all               cert' >> pg_hba.conf
# restart server
$ pg_ctl stop
$ pg_ctl -l server.log start

Finally, the client must have a copy of the root certificate and a client certificate must be created:

# copy root certificate to the client
$ mkdir ~/.postgresql 2> /dev/null
$ cd ~/.postgresql
$ cp $PGDATA/root.crt .
# create client certificate and key file
# Use of -nodes would prevent a required password
$ openssl req -new -text -out postgresql.csr -keyout postgresql.key -subj "/CN=postgres"
$ chmod og-rwx postgresql.key
$ openssl x509 -req -in postgresql.csr -text -days 365 -CA $PGDATA/root.crt -CAkey $PGDATA/root.key -CAcreateserial -out postgresql.crt

And now the test:

$ psql -c 'SELECT version();' ' dbname=test sslmode=verify-full'
Enter PEM pass phrase:
 PostgreSQL 12devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10+deb8u1) 4.9.2, 64-bit

With this all in place, we can implement removable certificate authentication. Insert a usb memory stick and give it a name, which causes the usb file system to appear at a predictable file path each time it is inserted. (The method varies among operating systems.) Now, move the ~/.postgresql directory to the usb memory stick and create a symbolic link to it from the local file system:

$ mv ~/.postgresql '/media/bruce/Bruce M USB/.postgresql'
$ ln -s '/media/bruce/Bruce M USB/.postgresql' ~/.postgresql

With the usb memory stick inserted, psql runs normally, but when it is ejected, the symbolic link points to nothing and an error is generated:

$ psql -c 'SELECT version();' ' dbname=test sslmode=verify-full'
psql: root certificate file "/var/lib/postgresql/.postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.

In conclusion, cert authentication with a password-enabled private key is already two-factor authentication — the private key file ("Something you have"), and its password ("Something you know"). By storing the private key file on a usb memory stick, the "Something you have" becomes independent of the computer used to access the database. As I mentioned before, piv devices have even more security advantages.


Security Three Factors of Authentication

Monday, January 14, 2019

Traditionally, passwords were used to prove identity electronically. As computing power has increased and attack vectors expanded, passwords are proving insufficient. Multi-factor authentication uses more than one authentication factor to strengthen authentication checking. The three factors are:

  1. Something you know, e.g., password, pin
  2. Something you have, e.g., cell phone, cryptographic hardware
  3. Something you are, e.g., finger print, iris pattern, voice

Postgres supports the first option, "Something you know," natively using local and external passwords. It supports the second option, "Something you have," using cert authentication. If the private key is secured with a password, that adds a second required factor for authentication. Cert only supports private keys stored in the file system, like a local file system or a removable usb memory stick.

One enhanced authentication method allows access to private keys stored on piv devices, like the YubiKey. There are two advantages of using a piv device compared to cert:

  • Requires a pin, like a private-key password, but locks the device after three incorrect pin entries (File-system-stored private keys protected with passwords can be offline brute-force attacked.)
  • While the private key can be used to decrypt and sign data, it cannot be copied from the piv device, unlike one stored in a file system

Unfortunately, libpq does not support piv access directly, though it can be accessed using external authentication methods like pam. Google Authenticator and FreeOTP can also be used to add a second factor of authentication to pam and other external authentication methods.

The third type of authentication factor, "Something you are," also requires an external authentication method. It is unclear if Postgres should support more authentication methods directly or improve documentation about how to integrate them with existing external authentication methods.

Post a Comment

Security Fourteen Authentication Methods

Wednesday, January 2, 2019

Postgres supports fourteen authentication methods — that might seem like a lot, but Postgres is used in many environments, and it has to support whatever methods are being used in those environments. The fourteen methods can seem confusing, but they are easier to understand in categories:

  • absolute: trust, reject always allow or reject
  • local password: scram-sha-256, md5, password compare a user-supplied password with something stored in the database
  • external password: ldap, pam, radius, bsd compare to a password stored outside the database
  • trusted network: peer, ident rely on the network connection to authenticate
  • trusted tokens: gss, sspi use possession of a token generated by a trusted key distribution server
  • certificate authority: cert uses access to the private key of a certificate signed by a trusted certificate authority

So, there is one absolute and five conditional classes of authentication.

Post a Comment