PostgreSQL “pg_hba.conf” Explained: part4
This article is the last part of pg_hba.conf explained. Note that pg_hba.conf is only for authentication. Most auth-methods make sure the client and the postmaster’s data exchange in this period secured, for example, ldap with tls, krb, pam_sss, scram-sha-256. In other words, the password is secure in transition.
What about the data in transit encryption? Can someone turn on the network sniffer and get all the query resultsets (network packets) I sent to the postmaster? Yes, it is possible. That the topic I want to explore; TLS/SSL. Let’s turn on TLS on pg-master. First, you need a server certificate from ipa-server. The steps are straightforward as ipa-client-install already setup most of the PKI for you.
[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 -rThis command creates server.key and server.crt and copies them directly to $PGDATA. You need to change the owner of server.[key,crt] to postgres.
[root@master1 data]# chown postgres. server.*You can use openssl command to check the content of the certificate. The basic idea is: the issuer of your server certificate should match the subject of the ca.crt.
[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 AuthorityData-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'Check the pg_hba.conf. I modify the line with “hostssl”, which ensures the clients need to use “sslmode=prefer/require” to connect to postmaster.
hostssl all all 0.0.0.0/0 pam pamservice=postgresqlDon’t forget to restart the server (ssl change in postgresql requires restart). Let’s try it out with the client.
[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=>
Do you see that you still need to “type” your password. pam authentication is different from data-in-transit encryption.
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 authority and the title. 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'Now we jump to the client vm to prepare the user certificate. First, use openssl command to generate a client key, and client.csr. Note that the subj should match the user role.
[pguser1@client tmp]$ openssl req -new -newkey rsa:2048 -days 365 -nodes -keyout pguser1.key -out pguser1.csr -subj '/CN=pguser1'Now kinit as admin (ipa-server administrator) and approve the csr.
[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): 0xCIf you check the user profile, you will find that “certificate” attached to this ipa user. Use the cert-find command to find pguser1’s cert number. It is 12 in my ipa-server.
[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
-----------------------------Next step, use cert-show command to print out the cert to x509 pem format. This pem is the client cert we will use.
[pguser1@client tmp]$ ipa cert-show 12 --out pguser1.crtTo be safe, I use openssl x509 command to check the content of certificate.
[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:B1Authority Information Access:
OCSP - URI:http://ipa-ca.ysung.vmware.lab/ca/ocspX509v3 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 AuthorityX509v3 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:
We have our client cert ready for pguser1. Go back to pg-client and check the connection.
[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=>
Again, we are still using pam authentication and password is reqiured. You can use pg_stat_ssl to ensure the connection has SSL turned on.
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=1Don’t forget to reload the pg_hba.conf. Let’s try it from pg-client. Note that somehow the connect string doesn’t work.
[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 offI switch to the following connecting string, and it passed.
[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=>
No password!? A client certificate with TPM is an excellent way to authenticate users. TPM can protect the client cert and key in your computer. There is no way to copy cert/key out of the TPM/vTPM. If you lost your laptop, your company would revoke the certificate, and the computer will not be able to connect to the server.
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.