PostgreSQL “pg_hba.conf” Explained: part4

Photo by Will Porada on Unsplash
[root@master1 ~]# kinit admin
Password for admin@YSUNG.VMWARE.LAB:
[root@master1 ~]# ipa-getcert request -f /var/lib/pgsql/13/data/server.crt -k /var/lib/pgsql/13/data/server.key -r
[root@master1 data]# chown postgres. server.*
[root@master1 data]# openssl x509 -in server.crt -noout -subject
subject=O = YSUNG.VMWARE.LAB, CN = master1.ysung.vmware.lab
[root@master1 data]# openssl x509 -in server.crt -noout -issuer
issuer=O = YSUNG.VMWARE.LAB, CN = Certificate Authority
[root@master1 data]# openssl x509 -in /etc/ipa/ca.crt -noout -subject
subject=O = YSUNG.VMWARE.LAB, CN = Certificate Authority

Data-in-transit encryption (sslmode=prefer/require)

With server.crt and server.key, we can enable data-in-transit encryption by modifying the ssl section in postgresql.conf. To turn on data-in-transit encryption, you only need the key pair. We can skip ssl_ca_file for now. Note we want data-in-transit for now. An analogy of this; you, the client, want to buy something on the street from someone you don’t care his/her identity. You only want to make sure the transaction is in private. Note that ssl_ciphers is a cipher filter. This line says I want to use TLSv1.2 cipher suite and ensure the authentication and encryption are not null algorithms.

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ciphers = 'TLSv1.2:!aNULL:!eNULL'
hostssl all             all             0.0.0.0/0               pam     pamservice=postgresql
[ysung@client ~]$ psql -h master1.ysung.vmware.lab -U pguser1 -d postgres --set=sslmode=require
Password for user pguser1:
psql (13.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=>

Data-in-transit encryption and verify ca trust (verify-ca)

I use another analogy to explain verify-ca. You go to a store to buy something. You (the client) want to make sure this store (the postmaster) has the right license to open a business in this location. You check the “state-issued license” hanging on the wall and ensure the owner/store name/address match. And you ensure the transaction is private. In this case, you trust the state government, aka CA.
In this mode, no change requires on the postmaster side. The client will need to specify the sslrootcert when connecting to the postmaster. If you use openssl command to check the file, you will find that the ca.crt subject should be the same as “issuer” of postmaster ca.crt.

[ysung@client ~]$ psql -h master1.ysung.vmware.lab -U pguser1 -d postgres --set=sslmode=verify-ca --set=sslrootcert=/etc/ipa/ca.crt
Password for user pguser1:
psql (13.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=>

Data-in-transit encryption and verify server identities (verify-full)

Let me use one more analogy to explain verify-full. You (the client) go to a liquor store (the postmaster) and want to buy some wines. The same as the verify-ca analogy, you verify the store has a valid license to sell. Moreover, when you (the client) check out the store, the store owner (postmaster) checks your ID (client cert). The transaction is private.
In this situation, you add “ssl_ca_file” in the postgresql.conf. This file should be in /etc/ipa as ipa-client-install prepared it. Now postmaster can use this ca.crt to validate client certificate. If you use openssl command to check the ca.crt file, the subject should be the same as the issuer of client.crt (we will prepare this client.crt in the next step). Don’t forget to restart the server.

ssl = on
ssl_ca_file = '/etc/ipa/ca.crt'
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ciphers = 'TLSv1.2:!aNULL:!eNULL'
[pguser1@client tmp]$ openssl req -new -newkey rsa:2048 -days 365 -nodes -keyout pguser1.key -out pguser1.csr -subj '/CN=pguser1'
[pguser1@client tmp]$ kinit admin
Password for admin@YSUNG.VMWARE.LAB:
[pguser1@client tmp]$ ipa cert-request pguser1.csr --principal=pguser1
Issuing CA: ipa
Certificate: MIIEmTCCAwGgAwIBAgIBDDANBgkqhkiG9w0BAQsFADA7MRkwFwYDVQQKDBBZU1VORy5WTVdBUkUuTEFCMR4wHAYDVQQDDBVDZXJ0aWZpY2F0ZSBBdXRob3JpdHkwHhcNMjAxMjIzMDQ0MTU1WhcNMjIxMjI0MDQ0.........
Subject: CN=pguser1,O=YSUNG.VMWARE.LAB
Issuer: CN=Certificate Authority,O=YSUNG.VMWARE.LAB
Not Before: Wed Dec 23 04:41:55 2020 UTC
Not After: Sat Dec 24 04:41:55 2022 UTC
Serial number: 12
Serial number (hex): 0xC
[pguser1@client tmp]$ ipa cert-find
-----------------------
12 certificates matched
-----------------------
...
Issuing CA: ipa
Subject: CN=pguser1,O=YSUNG.VMWARE.LAB
Issuer: CN=Certificate Authority,O=YSUNG.VMWARE.LAB
Not Before: Wed Dec 23 04:41:55 2020 UTC
Not After: Sat Dec 24 04:41:55 2022 UTC
Serial number: 12
Serial number (hex): 0xC
Status: VALID
Revoked: False
-----------------------------
Number of entries returned 12
-----------------------------
[pguser1@client tmp]$ ipa cert-show 12 --out pguser1.crt
[pguser1@client tmp]$ openssl x509 -in pguser1.crt -noout -text
Certificate:
Data:
Version: 3 (0x2)
Serial Number: 12 (0xc)
Signature Algorithm: sha256WithRSAEncryption
Issuer: O = YSUNG.VMWARE.LAB, CN = Certificate Authority
Validity
Not Before: Dec 23 04:41:55 2020 GMT
Not After : Dec 24 04:41:55 2022 GMT
Subject: O = YSUNG.VMWARE.LAB, CN = pguser1
Subject Public Key Info:
Public Key Algorithm: rsaEncryption
RSA Public-Key: (2048 bit)
Modulus:
00:c6:64:9d:d6:bb:01:d0:e1:e9:ee:43:b9:0e:f1:
dd:f3:c1:76:4e:a1:1e:f8:f3:ed:37:36:ea:e4:dc:
d2:aa:b6:84:de:9f:dd:d4:b8:42:10:10:69:3c:33:
fc:fe:e3:e8:eb:5c:01:d1:a6:f3:66:b8:d4:41:b0:

Exponent: 65537 (0x10001)
X509v3 extensions:
X509v3 Authority Key Identifier:
keyid:79:51:47:38:A7:03:C6:1A:47:DC:38:32:98:88:FE:75:A5:7F:69:B1
Authority Information Access:
OCSP - URI:http://ipa-ca.ysung.vmware.lab/ca/ocsp
X509v3 Key Usage: critical
Digital Signature, Non Repudiation, Key Encipherment, Data Encipherment
X509v3 Extended Key Usage:
TLS Web Server Authentication, TLS Web Client Authentication
X509v3 CRL Distribution Points:
Full Name:
URI:http://ipa-ca.ysung.vmware.lab/ipa/crl/MasterCRL.bin
CRL Issuer:
DirName:O = ipaca, CN = Certificate Authority
X509v3 Subject Key Identifier:
58:02:A5:DD:80:69:AA:B6:76:CA:8D:8E:B5:B7:D0:F7:24:DA:BF:AF
Signature Algorithm: sha256WithRSAEncryption
4a:f5:89:75:e8:81:34:7a:78:03:ed:0d:0e:91:b1:18:a1:49:
25:df:9e:78:62:16:07:0c:44:33:8a:ce:ea:49:f1:a5:08:06:
71:02:17:0b:91:50:0c:2d:a3:ff:f4:49:ec:51:d1:35:73:23:
[pguser1@client tmp]$ psql -h master1.ysung.vmware.lab -U pguser1 -d postgres --set=sslmode=verify-full --set=sslrootcert=/etc/ipa/ca.crt --set=sslcert=pguser1.crt --set=sslkey=pguser1.key
Password for user pguser1:
psql (13.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=>
postgres=# select * from pg_stat_ssl;
pid | ssl | version | cipher | bits | compression | client_dn | client_serial | issuer_dn
-------+-----+---------+------------------------+------+-------------+-----------+---------------+-----------
51588 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | | |
51640 | f | | | | | | |
(2 rows)

Back to pg_hba.conf cert authentication

We have client certificate ready. We can try the “cert” auth-method in pg_hba. The only thing you need to change is pg_hba.conf. The following is an example:

hostssl all  all  0.0.0.0/0  cert clientcert=1
[pguser1@client tmp]$ psql -h master1.ysung.vmware.lab -U pguser1 -d postgres --set=sslmode=prefer --set=sslrootcert=/etc/ipa/ca.crt --set=sslcert=/tmp/pguser1.crt --set=sslkey=/tmp/pguser1.key
psql: error: FATAL: connection requires a valid client certificate
FATAL: no pg_hba.conf entry for host "192.168.20.21", user "pguser1", database "postgres", SSL off
[pguser1@client tmp]$ psql 'host=master1.ysung.vmware.lab port=5432 dbname=postgres user=pguser1 sslmode=verify-full sslcert=/tmp/pguser1.crt sslkey=/tmp/pguser1.key sslrootcert=/etc/ipa/ca.crt'
psql (13.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=>

In conclusion

We went through many pg_hba.conf authentication methods and a few troubleshooting. Many external auth-methods assume system/platform engineers implemented those services and are a part of infrastructure automation (IoC). The best practice of pg_hba.conf is “Do not change it. If you need to change it, make it short”. This rule implies that you (DBA or DBRE) should always use external authentication and try automating those authentication relative tasks.

A data nerd started from data center field engineer to cloud database reliability engineer.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store