Preamble
A version of PostgreSQL called PostgreSQL TDE provides on-disk encryption for PostgreSQL. Currently, this software is maintained for use by the general public. We’ve recently been asked how to combine PostgreSQL TDE with key management solutions that are already out there. We have chosen to lay out the fundamental ideas and assist people in putting key management strategies for PostgreSQL to secure their infrastructures into practice in order to assist you manage encryption keys with PostgreSQL TDE.
PostgreSQL TDE: How encryption keys are handled
Running PostgreSQL TDE is easy. However, it necessitates a fundamental comprehension of how keys are typically handled.
Deploying a PostgreSQL instance
Behind the scenes, initdb runs when a new PostgreSQL instance is built. When the service is started for the first time, most systems take care of that for you. Initdb can do things like deploy a config file, create the initial content of the data directory, and more.
The most important thing to remember is that TDE (Transparent Data Encryption) needs the data files on a disk to be encrypted. The obvious issue that follows is: How do we locate the key? To handle keys effectively, we need to be a little inventive because it’s not a good idea to store the key directly in the data directory.
When creating PostgreSQL TDE, we chose a straightforward plugin design. Key management practices can vary widely. There are many different keystores, and each company has different needs based on use cases, philosophies, rules, and maybe even the law.
PostgreSQL TDE uses a plugin, which is an executable we must specify, to get the key:
[tde@cybertec]$ initdb --help
initdb
initializes a PostgreSQL database cluster.
Usage:
initdb [OPTION]... [DATADIR]
Options:
... -k, --data-checksums use data page checksums -K, --encryption-key-command command that returns encryption key
The -K
option requires you to point to a script that returns the key as a string. Note that this key is never visible to anybody but the database server. It does not show up in a process table. It does not show up anywhere. The beauty of this design is that any plugin can be created for any key manager. You can even run the command line and have the administrator enter the key to conduct tests. Although hardly anyone in the real world uses that strategy, it is still an option.
As stated: Provide a script or an executable that echoes the key to stdout. PostgreSQL will read it and use it to encrypt your data files. Having this script gives you complete freedom to work with any type of key management.
Here is an example:
[tde@cybertec]$ ./initdb -D /path/db -K /path/key_manager.sh
User “hs” will be the owner of the database system’s files.
The server process must also belong to this user.
The database cluster’s initialization will include locales
COLLATE: en_US. en_US. UTF-8 CTYPE: en_US. en_US. UTF-8 MESSAGES: en_US.UTF-8 MONETARY: de_DE UTF-8 NUMERIC: de_DE UTF-8 TIME: de_DE. UTF-8
Accordingly, the default database encoding has been set to “UTF-8.”
English will be selected as the default text search configuration.
There are no data page checksums.
Data encryption is enabled.
creating the directory /path/db, ok creating subdirectories, ok selecting the dynamic shared memory implementation, ok posix: selecting default max_connections: 100 selecting default shared_buffers ... 128MB selecting default time zone ... Europe/Vienna creating configuration files, ok running the bootstrap script, ok performing post-bootstrap initialization, ok syncing data to disk, ok initdb: warning: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
[tde@cybertec]$ ./pg_ctl -D /path/db -l logfile start
The-K
option is really the path to success here. In the simplest of all cases, the script simply returns a constant:
[tde@cybertec]$ cat /path/key_manager.sh #!/bin/sh echo 4e5358ab309bcdea23450934546298ab
However, as was already mentioned, this is merely a showcase. Do not keep the key on the same server; rather, integrate it with a reliable key repository and manage the key remotely. Otherwise, TDE would be pointless and simply a waste of resources.
It’s crucial to keep in mind that the key must be 32 characters long and written in hexadecimal. Otherwise, an errorinitdb
will occur.
initdb: fatal: encryption key is too short, should be a 32 character hex key
There is bundled PG Keytool software for using secrets that people can memorize that accepts a passphrase as input and uses a key derivation function to create a properly formatted output key. This adds another layer of protection against dictionary attacks by using a key derivation function that takes a lot of time to run.
We can start PostgreSQL normally once the instance has been deployed.
Starting PostgreSQL
What initdb
has done for us is to adjust the relevant values in postgresql.conf
:
# - Encryption - encryption_key_command = '/path/key_manager.sh'
The same program to retrieve the key is run every time the server boots up. Keep in mind that to decrypt those data files, we need this key. If the program does indeed return the correct key, the server has already taken precautions to ensure that nothing can go wrong. The server cannot be started if the key is not available. No back door, emergency key, or similar features exist. Your data is lost if you cannot provide the right key. The entire concept behind TDE is that. Backdoors are not what we want.
Ensuring encryption is enabled
How can I check to see if my PostgreSQL instance is encrypted, a common question? The way to do that is to control pg_controldata
:
[tde@cybertec]$ pg_controldata /path/db/ ... Database system identifier: 7036323720088499175 Database cluster state: shut down pg_control last modified: Di 30 Nov 2021 12:29:41 CET ... Mock authentication nonce: f12fd3308a81f946ffbb36a0a3bd4d41c87bbb9ba4d612b809a001a2202cdc6a Data encryption: on Data encryption fingerprint: D2D2CED8FE9F3980599289B1F468A9FB
You can find the encryption information at the bottom of the listing. Make sure that you’re using the right version of pg_controldata
. Otherwise, you won’t be able to find that information.
Popular keystores recommended for PostgreSQL TDE encryption keys
One of the main questions is: Which key store can one use for PostgreSQL? The answer is: any of them. Here are some that are quite popular and widely used by many companies. Be aware that this is not a comprehensive list.
- nitrokey.com
- YubiHSM
- HashiCorp Vault
In a typical production system, the database master key is made at random, encrypted with a key from a key management system, and then stored in the PostgreSQL data directory. This means that the whole database doesn’t have to be re-encrypted, and keys can be changed or even the key management system can be replaced.
About Enteros
Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.
The views expressed on this blog are those of the author and do not necessarily reflect the opinions of Enteros Inc. This blog may contain links to the content of third-party sites. By providing such links, Enteros Inc. does not adopt, guarantee, approve, or endorse the information, views, or products available on such sites.
Are you interested in writing for Enteros’ Blog? Please send us a pitch!
RELATED POSTS
Optimizing Database Performance with Enteros and AWS Resource Groups: A RevOps Approach to Streamlined Efficiency
- 13 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Enhancing Healthcare Data Integrity: How Enteros, Logical Models, and Database Security Transform Healthcare Operations
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Optimizing Budgeting and Cost Allocation in the Finance Sector with Enteros: A Smarter Approach to Financial Efficiency
- 12 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Enteros and Cloud FinOps: Unleashing Big Data Potential for eCommerce Profitability
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…