9 min read
Latest Articles
- Accessing Large Language Models from PostgreSQL
- 8 Steps in Writing Analytical SQL Queries
- 4 Ways to Create Date Bins in Postgres: interval, date_trunc, extract, and to_char
- Crunchy Postgres for Kubernetes 5.7: Faster Backups, Automated Snapshots, Postgres 17 and More
- pg_parquet: An Extension to Connect Postgres and Parquet
Postgres and Crypto Supply Chain of Integrity
Question: How do I get PostgreSQL to use FIPS 140-2 crypto?
The answer, to some extent, depends on how rigorously you need to be able to prove your answer. If the proof required is more than a casual check, the process is not well documented as far as I can tell. Therefore I will attempt to address that deficiency here.
To be sure the crypto being used by PostgreSQL on a particular system is FIPS validated, you need to trace the chain of custody of the crypto software from the PostgreSQL backend process all the way back to the actual binary that was tested.
At a high level, that process involves the following steps:
- Which NIST Certificate Number applies to my OS?
- Which Vendor and Security Policy document belongs to that certificate?
- What version openssl RPM does that Security Policy require?
- Does my System Under Test (SUT) have the correct RPM installed?
- Is the RPM the correct version?
- Does the RPM originate with the correct vendor?
- Are the relevant openssl shared object files the ones that were installed by the verified RPM?
- Is the PostgreSQL process on the SUT using the openssl shared object files installed by the verified RPM?
- Is the PostgreSQL process on the SUT operating in FIPS mode?
We could go further down the rabbit hole, to include for instance verifying that the URLs we visit in this process take us to the correct web server (e.g. no poisoned DNS, etc.), but I leave that as an exercise for the reader.
So let’s take these steps one at a time. We will use RHEL 8 as our example while we walk through this. The process is partly knowing where to look and partly knowing how to extract the needed evidence. Let’s get started.
Certificate Number
First we need to determine what the NIST Certificate number is for RHEL 8. You can find the answer to that question here.
As it says on the page, we will use this search to help us find the validated cryptographic modules for Red Hat 8. The certificate of interest appears to be #3781.
Vendor and Security Policy
Drilling into Certificate #3781, among other things we can see that the vendor is “Red Hat®, Inc.”, and the Security Policy is conveniently linked under “Related Files” near the bottom.
RPM version
By opening the Security Policy, we next go to “1.3. Cryptographic Boundary”. There we find that the “logical cryptographic boundary is the shared library files and their integrity check HMAC files, which are delivered through” the openssl-libs-1.1.1c
RPM. This RPM includes the following files, that are considered “part of the module boundary”:
/usr/lib64/.libcrypto.so.1.1.1c.hmac
/usr/lib64/.libssl.so.1.1.1c.hmac
/usr/lib64/libcrypto.so.1.1.1c
/usr/lib64/libssl.so.1.1.1c
Installed RPM
At this point we know exactly which library files we should be using. Now we need to determine if we are actually using them.
Version
Getting the RPM version is the easiest part of this whole exercise:
$ sudo rpm -q openssl-libs
openssl-libs-1.1.1c-15.el8.x86_64
As you can see, on my SUT the openssl-libs
version is correct (1.1.1c).
Vendor
But how do we know for sure that this RPM came from Red Hat? There are undoubtedly multiple ways to determine that, but the following command is a good start:
$ sudo rpm -q --qf 'package:\t%{NAME}-%{VERSION}-%{RELEASE}\npgp sig:\t%{SIGPGP:pgpsig}\ngpg sig:\t%{SIGGPG:pgpsig}\nbuildhost:\t%{BUILDHOST}\n' openssl-libs
package: openssl-libs-1.1.1c-15.el8
pgp sig: RSA/SHA256, Thu 05 Mar 2020 01:14:37 PM UTC, Key ID 199e2f91fd431d51
gpg sig: (none)
buildhost: x86-vm-07.build.eng.bos.redhat.com
The build host confirms that the RPM was built by Red Hat, and the listed PGP Key ID will give us definitive proof.
$ sudo rpm -q gpg-pubkey --qf '%{NAME}-%{VERSION}-%{RELEASE}\t%{SUMMARY}\n'
...
gpg-pubkey-fd431d51-4ae0493b gpg(Red Hat, Inc. (release key 2) <security@redhat.com>)
...
gpg-pubkey-bc94c08d-5bacf016 gpg(Crunchy Data RPM Key (Crunchy Data Official RPM Signing Key) <packagers@crunchydata.com>)
...
Notice that the last eight characters in the PGP Key ID (fd431d51) match the first segment of the “Red Hat, Inc. (release key 2)”.
Installed library files
But what would happen if someone substituted their own files for the four that are listed above as being part of the module boundary? For example, I could download a openssl-libs-1.1.1c
source RPM, modify the source for my own (potentially nefarious) purposes, rebuild/reinstall the RPM elsewhere, harvest the files, and overlay them on my SUT. What then? No problem, the rpm command comes to the rescue again:
$ sudo rpm -Vav openssl-libs|grep -E "(libcrypto.so.1.1.1c|libssl.so.1.1.1c)"
......... /usr/lib64/.libcrypto.so.1.1.1c.hmac
......... /usr/lib64/.libssl.so.1.1.1c.hmac
......... /usr/lib64/libcrypto.so.1.1.1c
......... /usr/lib64/libssl.so.1.1.1c
The nine “.” characters in the output above mean that each of nine verification tests passed. A single “?” would have indicated that the test could not be performed, or another character (listed below) would denote failure of the corresponding verification test:
- S file Size differs
- M Mode differs (includes permissions and file type)
- 5 digest (formerly MD5 sum) differs
- D Device major/minor number mismatch
- L readLink(2) path mismatch
- U User ownership differs
- G Group ownership differs
- T mTime differs
- P caPabilities differ
Note that among other things, the digest of the file is compared to what was recorded when from the RPM was installed.
Library files in use
We are getting close to verifying our entire supply chain. The next item on our list is to ensure that the running PostgreSQL server is actually using the module boundary files and not something else. That might be done using LD_LIBRARY_PATH
, LD_PRELOAD
, or /etc/ld.so.preload
for instance. First use ps
to determine the PID of the running PostgreSQL server:
$ sudo ps -fu postgres
UID PID PPID C STIME TTY TIME CMD
postgres 6827 1 0 16:59 ? 00:00:00 /usr/pgsql-12/bin/postgres -D /var/lib/pgsql/12/data/
postgres 6829 6827 0 16:59 ? 00:00:00 postgres: logger
postgres 6831 6827 0 16:59 ? 00:00:00 postgres: checkpointer
postgres 6832 6827 0 16:59 ? 00:00:00 postgres: background writer
postgres 6833 6827 0 16:59 ? 00:00:00 postgres: walwriter
postgres 6834 6827 0 16:59 ? 00:00:00 postgres: autovacuum launcher
postgres 6835 6827 0 16:59 ? 00:00:00 postgres: stats collector
postgres 6836 6827 0 16:59 ? 00:00:00 postgres: logical replication launcher
Note that there are several processes shown by this command. We can use any of them, but lets pick the parent daemon PID, 6827. Now let’s see which openssl library files are being used by that process:
$ sudo lsof -p 6827 |grep -E "(libssl.so|libcrypto.so)"
postgres 6827 postgres mem REG 8,2 3058976 16999466 /usr/lib64/libcrypto.so.1.1.1c
postgres 6827 postgres mem REG 8,2 615504 16999468 /usr/lib64/libssl.so.1.1.1c
As you can see here, the correct openssl library files are in fact being loaded by the PostgreSQL server daemon.
Operating in FIPS mode
We are down to the short strokes. At this point we are sure that PostgreSQL has actually loaded the same crypto libraries that were FIPS validated by NIST. The final things we need to check are whether or not PostgreSQL is using that particular library’s FIPS validated crypto algorithms. This is know as being in “FIPS mode”.
If you have gotten this far, hopefully you have already switched your system into FIPS mode at the operating system level. This step generally means that every consumer of the openssl libraries will automatically be operating in FIPS mode without requiring modifications to the calling software. PostgreSQL itself does not have the logic to “turn on FIPS mode”, and so it depends on the system to provide system-wide FIPS crypto. We can check for this SUT condition like so:
$ sudo fips-mode-setup --check
FIPS mode is enabled.
However when PostgreSQL initializes openssl, it is still possible that the self-check would fail and the fallback mode (FIPS mode off) would be used. So ideally we would verify from within PostgreSQL that FIPS mode is in fact on. One way to do that is using gdb. The process is something like this:
- terminal #1: use psql to log into PostgreSQL on the SUT
- terminal #2: find the PID of the backend process associated with the psql connection
- terminal #2: use gdb to attach to the PID of interest and run a couple commands
In terminal #1:
psql -U postgres nmx
Here we log in as the postgres superuser to nmx, which is an existing database.
In terminal #2:
$ sudo ps -fu postgres
UID PID PPID C STIME TTY TIME CMD
postgres 6827 1 0 16:59 ? 00:00:00 /usr/pgsql-12/bin/postgres -D /var/lib/pgsql/12/data/
postgres 6829 6827 0 16:59 ? 00:00:00 postgres: logger
postgres 6831 6827 0 16:59 ? 00:00:00 postgres: checkpointer
postgres 6832 6827 0 16:59 ? 00:00:00 postgres: background writer
postgres 6833 6827 0 16:59 ? 00:00:00 postgres: walwriter
postgres 6834 6827 0 16:59 ? 00:00:00 postgres: autovacuum launcher
postgres 6835 6827 0 16:59 ? 00:00:00 postgres: stats collector
postgres 6836 6827 0 16:59 ? 00:00:00 postgres: logical replication launcher
postgres 8142 6827 0 23:17 ? 00:00:00 postgres: postgres nmx [local] idle
This looks like what we saw earlier, except now the last line shows our backend process PID of interest - in this case 8142.
Now in terminal #2, use gdb to inspect:
$ cd /
$ ls -l lib64
lrwxrwxrwx. 1 root root 9 Aug 12 2018 lib64 -> usr/lib64
$ ls -l /usr/lib64/libcrypto.so.1.1
lrwxrwxrwx. 1 root root 19 Mar 5 2020 /usr/lib64/libcrypto.so.1.1 -> libcrypto.so.1.1.1c
$ sudo gdb /usr/pgsql-12/bin/postgres 8142
...lots of output...
(gdb) info symbol FIPS_mode
FIPS_mode in section .text of /lib64/libcrypto.so.1.1
(gdb) print (int) FIPS_mode()
$1 = 1
Note that lib64
is just a symlink to usr/lib64
. Therefore /lib64/libcrypto.so.1.1
is actually /usr/lib64/libcrypto.so.1.1
. Also note that libcrypto.so.1.1
is a symlink to libcrypto.so.1.1.1c
, which of course is the library of requirement.
And there we go. We can see that the correct library is the source for the FIPS_mode
function, and the returned value of “1” means we are verified to be in FIPS mode. A “0” here would have meant that we were not.
Ideally we would prefer not to have to resort to gdb to verify that we are using the correct library and are in FIPS mode. PostgreSQL currently has no way to do those things (at least not that I am aware of, perhaps through contrib/pgcrypto?), but there is an open source PostgreSQL extension called pgnodemx which will soon have that ability. A little preview (drumroll please…):
nmx=# select symbol_filename('FIPS_mode');
symbol_filename
--------------------------------
/usr/lib64/libcrypto.so.1.1.1c
(1 row)
nmx=# select fips_mode();
fips_mode
-----------
t
(1 row)
Slightly easier.
Summary
When you are required to use FIPS 140-2 validated crypto with PostgreSQL, you really need to verify the entire supply chain. I was unable to find a recipe for that, but you need look no further. Enjoy!
Related Articles
- Accessing Large Language Models from PostgreSQL
5 min read
- 8 Steps in Writing Analytical SQL Queries
8 min read
- 4 Ways to Create Date Bins in Postgres: interval, date_trunc, extract, and to_char
8 min read
- Crunchy Postgres for Kubernetes 5.7: Faster Backups, Automated Snapshots, Postgres 17 and More
4 min read
- pg_parquet: An Extension to Connect Postgres and Parquet
4 min read