- Db-engines.com called PostgreSQL the DBMS of the year. The DBMS of the year is calculated by subtracting the previous year’s score from the current year’s.
- Stack Overflow Annual Developer Survey in 2023 named PostgreSQL as the most popular choice for professional developers. The survey spans over 90k professionals who participated in this massive research on their own accord.
- The State of Open Source Report 2024 ranks PostgreSQL as one of the two top open source databases, competing with MySQL for the top spot and even winning it, but only in Europe.
As if we needed confirmation that PostgreSQL is good and important! I guess, unconsciously, we actually may have needed that.
While PostgreSQL takes the top spot for developers, when you compare the popularity of databases, PostgreSQL is not at the top. According to Db-engines.com, the top five most popular databases are:
So why this long intro that has nothing to do with the title? When comparing the most popular databases’ features, we noticed that something was missing from PostgreSQL… Yes, you guessed it right: all the other top five entries provide transparent data encryption (TDE) as a means of delivering data at rest encryption.
TDE even has a dedicated PostgreSQL wiki page. It’s not available in community PostgreSQL, but the need for it is well documented. Make no mistake; TDE is a complex topic, as evidenced by the discussion in the PostgreSQL Community that has been going on for over a decade.
Percona has been working to address the TDE predicament. We wrote a blog post that outlines “why we need TDE in PostgreSQL,” but for now, I will assume that since you’re here, you get the gist of it. For the sake of argument and just keeping it casual, lightweight, and with a “wink wink,” let’s say it’s just for the OCD-driven feature parity among the top five. 🙂
Open source: Why only “source”? Open product development!
Percona has a history of creating open source software solutions and contributions to projects that ‘make databases run better.’ What started with MySQL soon embraced the needs of other databases like MariaDB, MongoDB, PostgreSQL, and Valkey. Not stopping at database servers and tools, Percona has pushed the boundaries of database deployment, providing top-of-the-market Kubernetes operators and monitoring tools for databases.
This time around, we want to push ourselves to open up even more by giving you insider access to our process. We will show you what is coming and why. We’re sharing our struggles and questions with you. We are doing this to build trust, in hopes that you try and use software coming from us with more confidence because you know how it is made and what values drive us.
That being said, Percona is not a charity. It is true that we charge for premium services that allow us to drive innovation across the databases we support. By using our solutions, you not only get the best on-the-market services and products, but you also help us build better software for everyone. As our CEO emphasizes so often, “We believe an open world is a better world.”
During the development of the encryption tool, we faced many challenges. One of them was: “Should we change the way core PostgreSQL works or go with an extension?”
We reviewed the PostgreSQL wiki page that focuses on TDE, which emphasizes three criteria:
- “Must be secure”
- “Must be done in a way that has minimal impact on the rest of the Postgres code”
- “Should meet regulatory requirements”
We decided to create the extension to appreciate the community’s values and make the product approachable to any interested users.
Mind you, there are non-open source TDE solutions on the market, and there are also open source contributions to community PostgreSQL that implement TDE (see the history documented on the community Wiki). However, none have implemented true Transparent Data Encryption as an extension so far.
pg_tde_basic is now in public beta!
With this lovely AI-generated illustration of an elephant that seems to be super secure, I am happy to share that, as of now, the pg_tde extension, compatible with community PostgreSQL and loaded via the tde_heap_basic access method, is in the beta phase. We have concluded the initial implementation and are working on ensuring the stability of the solution.
tde_heap_basic has limitations that could pose challenges for production use
It’s important to note that the extension is still in beta and that the pure extension approach has its drawbacks.
The pg_tde extension implements the tde_heap_basic access method that encrypts data at the point where the data is formatted to be stored. This protects the many places where table content gets written in its on-disk format. Other database functions that use the values from the table and write them to disk separately are not protected. This includes indexes on values in the encrypted table and statistics on the most common values in fields.
Using the extension, indexes can be created on the encrypted data. Since the indexes are not encrypted, if they were based on encrypted data, they would be a potential leak vector. As such, depending on your use case, be aware that it could be considered a vulnerability.
Does that mean that indexes won’t be encrypted?
Do not fret; the solution is coming! We aim to have a solution that expands on this basic usage of TDE for PostgreSQL.
While the extension with the tde_heap_basic access method gives you the basic look and feel of Transparent Data Encryption, we are working on providing an open source solution to index encryption based on extending the API that PostgreSQL offers to extensions. The changes required to extend the API, and thereby the coverage of pg_tde, will be proposed to the PostgreSQL community for inclusion in core PostgreSQL. The changes will also be included in Percona Distribution for PostgreSQL. To get the full benefits of pg_tde, you will need to use a build that provides the extended API; we refer to this as our patched version.
The user experience between the extension alone and the patched version is not going to change much; we aim to make a painless transition:
- For now, table encryption is possible simply by creating a table with the access method “USING pg_tde_basic” on databases with the pg_tde extension available.
- With the patched version of PostgreSQL, the same extension is going to be able to use a more comprehensive encryption, based on a different access method and creating a table with an added clause “USING pg_tde”.
The aforementioned patch is in the works. Some of the required work has already been submitted to PostgreSQL, while other parts are still being developed. When all the work is done and submitted, the plan is to present this patch to the core PostgreSQL and collaborate with the community on how best to make it available to all users, not only Percona Distribution for PostgreSQL users.
Benefits of pg_tde
We can all celebrate when the full solution is available, but in the meantime, we want to share with you the assumptions we had when deciding upon particular features. Here are the differentiating factors we took into account:
- Make encryption more granular. Database-level encryption was the minimum requirement, but to make sure we could adapt to future requirements, we decided that table-level encryption would provide the best flexibility and scalability of capabilities.
- Be cloud-ready with multi-tenancy in mind. The assumption was that each table should have a separate encryption key, but each database has one principal key (known as the master key in some other implementations of TDE).
- Having security in mind, be ready for key management systems integrations, including Hashicorp Vault and ones based on KMIP implementation. While Vault integration is available, we aim to have KMIP available as well.
- Allow key rotation.
- Make the solution available for the community PostgreSQL users and Percona Distribution for PostgreSQL users alike.
- Be ready for requirements that may come from deployments at scale, such as multi-user requirements where more than one principal key would have to be used on one database. While we chose not to implement this scenario so that it does not complicate the more probable usage scenarios where one principal key is used for one database, the solution can be adapted to include such capabilities in the future.
What’s in store short term for open source PostgreSQL TDE?
There should not be many surprises when we share that the current focus has three main priorities:
- Deliver a patched version that allows index encryption
- Ensure the GA level stability of the access method “pg_tde_basic” table encryption
- Close the feature gap to cover topics like replication or KMIP integration
Other than this, a lot of testing! Performance testing, integration testing with other extensions, security testing, etc.
Support your local open source vendor, stay active, and provide feedback!
While the plans above may seem simple, they will take time. In the meantime, we are desperate for your feedback. In the open source world we often forget to share whether something is to our liking, of course when it does not work there are way more chances of getting feedback, but not necessarily when it’s good or simply ok. We hope that after reading this post, you will join us, test our extension, and supply feedback. We want to hear from you, so please stay active!
Please share the feedback:
- On our Community Forums
- In our GitHub project discussions
- By sharing our GitHub project
- By reaching out to any of us directly or at one of the conferences we attend!
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open source community in a single distribution, designed and tested to work together.
Learn Why Customers Choose Percona for PostgreSQL
About the Author
Jan WieremjewiczJan is a Senior Product Manager at Percona, leading the products for PostgreSQL. He has vast experience in the development, deployment and maintenance of enterprise systems. Professionally, he is passionate about simple solutions that solve complicated problems and user experience that maximizes the product potential. Privately he is a foodie by day, a tech geek into graphic novels, video and board games by night and parent/spouse in between. Having spent already almost two years in Percona, most of us learned that he has enough energy and topics to fill in any space and time.
I am very glad you are working on this. I assume the encryption of WAL will also be done at some point.
Hi Bruce!
Thanks for the comment and kind words!
Happy to share that for the encrypted tables the WAL data is already encrypted even for the tde_heap_basic access method.
For the tde_heap index data for the encrypted tables is also encrypted in WAL. So from the perspective of WAL encryption the upcoming build that will have the tde_heap access method available should be enough.
I am very glad you are working on this. I assume the encryption of WAL will also be done at some point.
Great news for PostgreSQL lovers 🐘. Thank you!