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 Insufficient Passwords

Monday, January 21, 2019

As I already mentioned, passwords were traditionally used to prove identity electronically, but are showing their weakness with increased computing power has increased and expanded attack vectors. Basically, user passwords have several restrictions:

  • must be simple enough to remember
  • must be short enough to type repeatedly
  • must be complex enough to not be easily guessed
  • must be long enough to not be easily cracked (discovered by repeated password attempts) or the number of password attempts must be limited

As you can see, the simple/short and complex/long requirements are at odds, so there is always a tension between them. Users often choose simple or short passwords, and administrators often add password length and complexity requirements to counteract that, though there is a limit to the length and complexity that users will accept. Administrators can also add delays or a lockout after unsuccessful authorization attempts to reduce the cracking risk. Logging of authorization failures can sometimes help too.

While Postgres records failed login attempts in the server logs, it doesn't provide any of the other administrative tools for password control. Administrators are expected to use an external authentication service like ldap or pam, which have password management features.

For a truly sobering view of users' motivation to improve security, read this 2010 paper mentioned on our email lists. These sentences sum it up:

Since victimization is rare, and imposes a one-time cost, while security advice applies to everyone and is an ongoing cost, the burden ends up being larger than that caused by the ill it addresses.

Security is not something users are offered and turn down. What they are offered and do turn down is crushingly complex security advice that promises little and delivers less.

In the model we set forward it is not users who need to be better educated on the risks of various attacks, but the security community. Security advice simply offers a bad cost-benefit tradeoff to users.

Combine this with the limited value of password policies, the difficulty in determining if a site is using a valid tls/ssl certificate, and the questionable value of certificate errors, and it makes you want rely as little as possible on users for security.


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.

Post a Comment

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