This is part 3 of the blog “Understanding Security Features in PostgreSQL”, in which I will be discussing how to apply TLS in both PostgreSQL server and client using the principles we have learned in part 2 of the blog. In the end, I will also briefly talk about Transparent Data Encryption (TDE) and security vulnerability.
Here is the overview of the
security topics that will be covered in all parts of the blog:
- PostgreSQL Server Listen Address
- Host-Based Authentication
- Authentication with LDAP Server
- Authentication with PAM
- Role-Based Access Control
- Assign Table and Column Level Privileges to Users
- Assign User Level Privileges as Roles
- Assign and Column Level Privileges via Roles
- Role Inheritance
- Security Concepts around TLS
- Symmetrical Encryption
- Asymmetrical Encryption (a.k.a Public Key Cryptography)
- Block Cipher Mode of Operation (a.k.a Stream Cipher)
- Key Exchange Algorithm
- TLS Certificate and Chain of Trust
- Data Integrity Check / Data Authentication
- TLS Cipher Suite and TLS handshake
- TLS versions
- Preparing TLS Certificates
- Enabling Transport Layer Security (TLS) to PostgreSQL Server
- Enabling Transport Layer Security (TLS) to PostgreSQL Client
- TLS Connect Examples
- Transparent Data Encryption (TDE)
- Security Vulnerability
2. Preparing TLS Certificates
Before we can utilize TLS to secure both the server and the client, we must prepare a set of TLS certificates to ensure mutual trust. Normally the CA (Certificate Authority) certificates can be purchased from a trusted organization and used it to create more CA-Signed certificates for services and applications. In this section, I will show you how to create your own CA Certificate and CA-Signed certificates using OpenSSL command line tool for both PostgreSQL server and client.
You may also have heard the term
self-signed certificate. This type of certificate is not signed by a trusted CA and is normally considered insecured in many applications. We will not go over the self-signed certificate generation in this blog.
2.1 Generate a Private Key for CA Certificate
Remember in last blog we mention that each certificate contains organization information and public key, which is paired with a private key file. Let’s generate a private key file for our CA first.
$ openssl genrsa -des3 -out cacert.key 2048 Generating RSA private key, 2048 bit long modulus (2 primes) ...............+++++ ........................+++++ e is 65537 (0x010001) Enter pass phrase for cacert.key: Verifying - Enter pass phrase for cacert.key:
Your will be prompted with pass phrase, which is recommended to provide as it will prevent someone else from generating more root CA certificate from this key.
2.2 Generate CA Certificate Using the Private key
Now, let’s generate the CA Certificate with the private key
$ openssl req -x509 -new -nodes -key cacert.key -sha256 -days 3650 -out cacert.pem Enter pass phrase for cacert.key: You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [AU]:CA State or Province Name (full name) [Some-State]:BC Locality Name (eg, city) :Vancouver Organization Name (eg, company) [Internet Widgits Pty Ltd]:HighGo Organizational Unit Name (eg, section) :Software Common Name (e.g. server FQDN or YOUR name) :va.highgo.com Email Address :email@example.com
Please note that OpenSSL will prompt you to enter several pieces of organizational information that identifies the CA certificate. You should enter these information suited to your organization. The most important field is
Common Name, which is commonly checked against the hostname or domain name of the service. Depending on the security policy, some server will enforce the rule that common name must equal its host / domain name; some servers do not have this restriction.
2.3 Generate a private key for CA-Signed certificate
Like in the CA case, CA-signed certificate is also paired with a private key file
$ openssl genrsa -out server.key 2048 Generating RSA private key, 2048 bit long modulus (2 primes) ...................+++++ ............................................................................+++++ e is 65537 (0x010001)
2.4 Generate a Certificate Signing Request for CA-Signed certificate
Then we create a Certificate Signing Request (CSR), which contains a list of organizational information to be presented to the CA server for verification. The CA server then decide if the CSR should be granted a new certificate according to the security policy configured. Since we are using OpenSSL for certificate generation, the
CA server here refers to OpenSSL itself, and the security policy configuration is located in
openssl.cnf, which is commonly located in
/usr/local/ssl/openssl.cnf. In an enterprise environment where Public Key Infrastructure (PKI) is deployed, the CA Server could refer to an actual service whose sole purpose is to verify incoming CSRs and renew or issue new certificates to requesting clients.
$ openssl req -new -key server.key -out server.csr You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [AU]:CA State or Province Name (full name) [Some-State]:BC Locality Name (eg, city) :Vancouver Organization Name (eg, company) [Internet Widgits Pty Ltd]:HighGo Organizational Unit Name (eg, section) :Software Common Name (e.g. server FQDN or YOUR name) :va.highgo.ca Email Address :firstname.lastname@example.org Please enter the following 'extra' attributes to be sent with your certificate request A challenge password : An optional company name :HighGo Canada
2.5 Generate a CA-Signed certificate
Since we are generating CA-signed certificate with OpenSSL locally, we can configure how the certificate should be generated using
openssl.cnf file. We will just be using the default policy set in
openssl.cnf. Here’s a snapshot of the default settings:
[ usr_cert ] # These extensions are added when 'ca' signs a request. # This goes against PKIX guidelines but some CAs do it and some software # requires this to avoid interpreting an end user certificate as a CA. basicConstraints=CA:FALSE # Here are some examples of the usage of nsCertType. If it is omitted # the certificate can be used for anything *except* object signing. # This is OK for an SSL server. # nsCertType = server # For an object signing certificate this would be used. # nsCertType = objsign # For normal client use this is typical # nsCertType = client, email # and for everything including object signing: # nsCertType = client, email, objsign # This is typical in keyUsage for a client certificate. # keyUsage = nonRepudiation, digitalSignature, keyEncipherment
Let’s generate the CA-signed certificate. Note that the command will take
server.csr as inputs, in which we have already generated from previous steps.
server.pem will be the output.
$ openssl x509 -req -in server.csr -CA cacert.pem -CAkey cacert.key -CAcreateserial -out server.pem -days 3650 -sha256 Signature ok subject=C = CA, ST = BC, L = Vancouver, O = HighGo, OU = Software, CN = va.highgo.ca, emailAddress = email@example.com Getting CA Private Key Enter pass phrase for cacert.key:
We can repeat from step 2.3 to 2.5 to generate a new pair for the client application.
To conclude, we have the following files generated:
- cacert.pem – Root CA certificate that is at the top of the chain of trust. We use it to sign and create other certificates
- cacert.key – key for the Root CA Certificate – must keep it secured.
- server.pem – CA-signed certificate for server application
- server.key – key for the server certificate
- client.pem – CA-signed certificate for client application
- client.key – key for the client certificate
3. Enabling Transport Layer Security (TLS) to PostgreSQL Server
PostgreSQL has native support for TLS to secure connection between client and server. The TLS support has to be enabled during build time and requires OpenSSL libraries. Depending on the versions of OpenSSL that the client or server is built with, TLS versions and ciphersuites may differ as well. This does not mean that both client and server must be linked with the same version of OpenSSL. It is possible that a client with older OpenSSL can connect to a server with newer OpenSSL if the server is configured to accept it. The TLS handshake process is initiated when a client first connects to the server in which they will evaluate TLS version used and negotiate ciphersuite that both ends are able to support. In this case, the server may use less secured ciphersuite and TLS version to communicate with the client, which may not be ideal.
The TLS support for a PostgreSQL server can be enabled in postgresql.conf.
ssl = on ssl_ca_file = '~/cert/cacert.pem' ssl_cert_file = '~/cert/server.pem' ssl_crl_file = '' ssl_key_file = '~/cert/server.key' ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers ssl_prefer_server_ciphers = on ssl_ecdh_curve = 'prime256v1' ssl_min_protocol_version = 'TLSv1.2' ssl_max_protocol_version = '' ssl_dh_params_file = '' ssl_passphrase_command = '' ssl_passphrase_command_supports_reload = off
Let’s examine the configuration parameters.
ssl = on
This line turns on the TLS support. Please note that even if TLS is turned on, the server will still be able to accept connections that do not use TLS. Normally, the client is the entity that decides if TLS should be used or not. The server can also enforce the incoming connections to use TLS by modifying the
pg_hba.conf file like this, where the connections from 172.16.30.0/24 must be TLS, otherwise the server will deny.
hostssl sales_team all 172.16.30.0/24 trust
ssl_ca_file = '~/cert/cacert.pem' ssl_cert_file = '~/cert/server.pem' ssl_crl_file = '' ssl_key_file = '~/cert/server.key'
These 4 lines tell PostgreSQL where to load the X509 certificate, the CA certificate, server private key and the certificate revocation list. These certificates must be pre-generated by OpenSSL command or purchased from a trusted organization. For TLS to work,
ssl_key_file must be provided. We will use the certificates we have generated for server in the previous section.
The file pointed by
ssl_ca_file will be used to determined if the certificate can be trusted by deriving the chain of trust.
The file pointed by
ssl_cert_file will be sent to the connecting client during TLS handshake for authentication purposes.
The file pointed by
ssl_key_file will be used for asymmetrical encryption during authentication
The file pointed by
ssl_crl_file is optional and it contains a list of certificates that cannot be trusted (or revoked). Distributing revoked certificates using this file is not the most ideal but still being practice today. It may have performance impact if the list is very large and it introduces a problem of when the list should be renewed and how often. Online Certificate Status Protocol (OCSP. ref:https://en.wikipedia.org/wiki/Online_Certificate_Status_Protocol) is a newer protocol designed for Public Key Infrastructure (PKI) for querying certificate revocation status that addresses some of the issues with revocation file. Feel free to give a read on OCSP in the link above.
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers ssl_prefer_server_ciphers = on ssl_ecdh_curve = 'prime256v1'
During TLS handshake, both client and server will present to each other a list of desired ciphersuites ordered by preference. Handshake process will go through both lists and find a common ciphersuite supported by both sides or abort if there is nothing in common. The
ssl_ciphers configuration is used to configure the size of the ciphersuite lists to be presented to the client during handshake.
ssl_ciphers is a string list consisting of one or more
cipher strings separated by colons ( ref: https://www.openssl.org/docs/man1.1.1/man1/ciphers.html) and defaults to
HIGH:MEDIUM:+3DES:!aNULL which translates to:
- allows high strength ciphersuites (HIGH)
- allows medium strength ciphersuites (MEDIUM)
- move any ciphersuite using 3DES algorithm to the end of the list (+3DES)
- remove any ciphersuite that does not have authentication algorithm (!aNULL)
For example, “HIGH:!ADH:!MD5:!RC4:!SRP:!PSK:!DSS:!ECDHE:!ECDSA:!EDH:!DH:!ECDH:!CAMELLIA256” will use high strength ciphersuites while removing any ciphersuites containing ADH, MD5, RC4…etc.
Before applying the cipher string to PostgreSQL, it is recommended to check the output cipher list after tuning the cipher string using Openssl client tool.
$ openssl ciphers -v 'HIGH:!ADH:!MD5:!RC4:!SRP:!PSK:!DSS:!ECDHE:!ECDSA:!EDH:!DH:!ECDH:!CAMELLIA256' TLS_AES_256_GCM_SHA384 TLSv1.3 Kx=any Au=any Enc=AESGCM(256) Mac=AEAD TLS_CHACHA20_POLY1305_SHA256 TLSv1.3 Kx=any Au=any Enc=CHACHA20/POLY1305(256) Mac=AEAD TLS_AES_128_GCM_SHA256 TLSv1.3 Kx=any Au=any Enc=AESGCM(128) Mac=AEAD AES256-GCM-SHA384 TLSv1.2 Kx=RSA Au=RSA Enc=AESGCM(256) Mac=AEAD AES256-CCM8 TLSv1.2 Kx=RSA Au=RSA Enc=AESCCM8(256) Mac=AEAD AES256-CCM TLSv1.2 Kx=RSA Au=RSA Enc=AESCCM(256) Mac=AEAD ARIA256-GCM-SHA384 TLSv1.2 Kx=RSA Au=RSA Enc=ARIAGCM(256) Mac=AEAD AES128-GCM-SHA256 TLSv1.2 Kx=RSA Au=RSA Enc=AESGCM(128) Mac=AEAD AES128-CCM8 TLSv1.2 Kx=RSA Au=RSA Enc=AESCCM8(128) Mac=AEAD AES128-CCM TLSv1.2 Kx=RSA Au=RSA Enc=AESCCM(128) Mac=AEAD ARIA128-GCM-SHA256 TLSv1.2 Kx=RSA Au=RSA Enc=ARIAGCM(128) Mac=AEAD AES256-SHA256 TLSv1.2 Kx=RSA Au=RSA Enc=AES(256) Mac=SHA256 AES128-SHA256 TLSv1.2 Kx=RSA Au=RSA Enc=AES(128) Mac=SHA256 CAMELLIA128-SHA256 TLSv1.2 Kx=RSA Au=RSA Enc=Camellia(128) Mac=SHA256 AES256-SHA SSLv3 Kx=RSA Au=RSA Enc=AES(256) Mac=SHA1 AES128-SHA SSLv3 Kx=RSA Au=RSA Enc=AES(128) Mac=SHA1 CAMELLIA128-SHA SSLv3 Kx=RSA Au=RSA Enc=Camellia(128) Mac=SHA1
ssl_prefer_server_ciphers specifies whether to use the server’s SSL cipher preferences, rather than the client’s. It should always be
on for more control in terms of ciphersuite selection.
ssl_ecdh_curve specifies the name of the curve to use in ECDH key exchange algorithms and is useful only if the ciphersuite uses ECDHE key exchange algorithm. The most common curves are : prime256v1, secp384r1 and secp521r1 and normally leaving it default should suffice.
ssl_min_protocol_version = 'TLSv1.2' ssl_max_protocol_version = ''
These 2 lines configure the minimum and maximum TLS versions to accept. By default the server will only serve the TLS client using TLSv1.2 and above. TLSv1.2 is a very secured TLS version and it is widely used in the world. Normally, we only change the minimum TLS version with assumption that all future versions will be more secured and for this reason, we normally don’t put restriction on the max version.
TLSv1.3 is recently introduced that has new ciphersuite support and has more improvement in the handshake process. To enforce TLSv1.3 to be used, set the
ssl_min_protocol_version to ‘TLSv1.3’ will suffice.
#ssl_dh_params_file = '' #ssl_passphrase_command = '' #ssl_passphrase_command_supports_reload = off
ssl_dh_params_file points to a file that contains custom diffie-hellman key exchange algorithm parameter. This is an optional parameter and is only useful if the ciphersuite uses DHE key exchange algorithm. If left empty, compiled-in defaults will be used. Custom DH parameters can be generated using command
openssl dhparam -out dhparams.pem 2048 and will normally reduce the attack exposure as attacker will have hard time cracking the key exchange process using custom parameter instead of the well-known default.
ssl_passphrase_command is the command to obtain the password for the private key file specified by
ssl_key_file. There is an option to add a password to a private key file during its generation and if password is used,
ssl_passphrase_command must be set with the system command that will retrieve such password. Otherwise, TLS handshake will abort as PostgreSQL will not be able to access private key without password.
ssl_passphrase_command_supports_reload configures if the
ssl_passphrase_command should be re-run at every reload (ie. SIGHUP). It is default to
off, so the
ssl_passphrase_command will not be run at every reload.
4. Enabling Transport Layer Security (TLS) to PostgreSQL Client
Now that we have a PostgreSQL server with TLS setup, we can use psql client to connect to the server also using TLS. Depending on the client connect parameters given, we can utilize TLS in different security levels. I will show the most common usages here:
# Case 1: connect to server in TLS mode $ psql -U user -h localhost -d "sslmode=require dbname=postgres" # Case 2: connect to server in TLS mode if server supports it $ psql -U user -h localhost -d "sslmode=prefer dbname=postgres" # Case 3: connect to server in TLS mode and verify server CA against client CA $ psql -U user -h localhost -d "sslmode=verify-ca dbname=postgres sslrootcert=~/cert/cacert.pem" # Case 4: connect to server in TLS mode and present client certificate. Verify all certificate details and trust chain. Check certificate revocation list does not contain server cert. $ psql -U user -h localhost -d "sslmode=verify-full dbname=postgres sslrootcert=~/cert/cacert.pem sslcert=~/cert/client.pem sslkey=~/cert/client.key"
The usage in Case 4 is the most secured because both server and client will verify each other’s certificate and decide if both can be mutually trusted. The common name field in the certificate is checked against the server hostname; certificate validity period is checked, organization details are checked; certificate trust chain is checked; revocation list is checked.
Please note that PostgreSQL server with TLS enabled by default does not force the client to present a TLS certificate for verification. If client presents one like in Case 4 above, the server will verify and deny connection is certificate is bad. If client does not provide a certificate like in Case 1 ~ 3, the server will skip the client certificate verification as there is nothing to verify, which is less secure.
To enforce the connecting client to present a TLS certificate for verification, we will need to add a special
clientcert=1 argument in existing authentication rules defined in
# TYPE DATABASE USER ADDRESS METHOD hostssl production_team production_user 0.0.0.0/0 pam clientcert=1
The example above will enforce connecting client to present TLS certificate to access
production_team database as
production_user. If a TLS certificate is not provided by client, the connection will abort.
5. TLS Connect Examples
$ psql -U user -h localhost -d "sslmode=require dbname=postgres" psql (13devel) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. postgres=#
Please note that psql prints the TLS version used (TLSv1.2) and the cipher suite negotiated during handshake (ECDHE-RSA-AES256-GCM-SHA384). Below is the wireshark capture of the above TLS connection:
$ psql -U cary -h localhost -d "sslmode=verify-full dbname=postgres sslrootcert=~/cert/cacert.pem sslcert=~/cert/client.pem sslkey=~/cert/client.key" psql: error: could not connect to server: server certificate for "va.highgo.ca" does not match host name "localhost"
Here, we have an error when we set sslmode to
verify-full, where both server and client will verify each other with the most strict criteria. This error happens because the
Common Name field in the certificate does not match the host name. Did I mention that
Common Name is the most important field of a certificate? To resolve this error, we can either re-generate certificate with matching Common name, or change the host name.
I simply add an entry to
/etc/hosts to resolve the error
127.0.0.1 localhost 127.0.0.1 va.highgo.ca
and the error will disappear when both Common Name and Hostname match
$ psql -U cary -h va.highgo.ca -d "sslmode=verify-full dbname=postgres sslrootcert=~/cert/cacert.pem sslcert=~/cert/client.pem sslkey=~/cert/client.key" psql (13devel) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help.
Please note that this command also forces the client to submit a certificate to server as well as seen from the wireshark capture. We can tell by looking at the
length field of the packet capture. There are 2 exchanges having lengths = 2675 and 2446. Those are the actual certificate contents being transmitted. Previous capture only has 1 exchanges having packet length = 2675; it means only server is providing certificate to client for verification.
6. Transparent Data Encryption (TDE)
Transparent Data Encryption refers to the process of protecting data at rest by encrypting database files on the hard disk level and decrypting them while reading from hard disk. This is to prevent physical storage media theft. This is called
transparent because the encryption and decryption happen between PostgreSQL server and the physical hard disk and it is not visible to the client applications. TDE uses symmetrical encryption for securing blocks of database files such as shared buffer and WAL files, and it is designed to accompany with a internal Key Management System (KMS) to manage the lifecycle of the encryption keys.
TDE and KMS are still under development by the PostgreSQL community. The KMS feature is expected to be released in PG13 while the TDE feature to be in PG14. With its completion, it will add another layer of security feature on top of already security-rich PostgreSQL database.
7. Security Vulnerability
Security Vulnerability is a weakness which can be exploited by an attacker to perform unauthorized actions, sabotage a service, or inject malicious software or virus. These weaknesses are generally implementation mistakes, undiscovered bugs or a legacy problem that require an update to the server to resolve.
PostgreSQL also has a list of known security vulnerability that has been discovered and fixed by the community. The list can be found here: https://www.postgresql.org/support/security/. These vulnerability ranges from different severity levels, from simple memory leak to crash the server.
This is why doing regular PostgreSQL server upgrade is important because each minor release fixes some of the discovered security vulnerabilities and therefore reducing the attack surface on your server.
In part 3 of the blog, we have learned and understood what each TLS related configuration means in
postgresql.conf and how to initiate TLS connection with psql client. We learned that keeping PostgreSQL server up-to-date can reduce the attack surface on some of the discovered vulnerabilities. We can ensure a fairly secured database network environment with TLS having adequate understanding of its fundamentals and practices. With the TDE feature coming in near future, we can further secure the database environment in the disk level and prevent possible data loss due to disk theft.
Cary is a Senior Software Developer in HighGo Software Canada with 8 years of industrial experience developing innovative software solutions in C/C++ in the field of smart grid & metering prior to joining HighGo. He holds a bachelor degree in Electrical Engineering from University of British Columnbia (UBC) in Vancouver in 2012 and has extensive hands-on experience in technologies such as: Advanced Networking, Network & Data security, Smart Metering Innovations, deployment management with Docker, Software Engineering Lifecycle, scalability, authentication, cryptography, PostgreSQL & non-relational database, web services, firewalls, embedded systems, RTOS, ARM, PKI, Cisco equipment, functional and Architecture Design.