11 Aug 2022

Aiven Security Agent for PostgreSQL®

PostgreSQL extensions provide great functionality for users, but they are difficult to provide securely. Aiven has an open source solution to this problem!

james-arlen

James Arlen

|

RSS Feed

Chief Information Security Officer at Aiven

Aiven Security Agent for PostgreSQL® illustration

Aiven provides PostgreSQL® as a Database as a Service (DBaaS). As part of the service offering, customers are given a privileged user (avnadmin). The superuser privilege, however, is not given to this user. Superuser access allows bypassing all permission checks and provides the ability to reconfigure the database, something we don't want from a manageability stand-point. The superuser privilege also provides access to functions that allow interaction, reading files and executing programs, on the underlying host.

A database is useful, but can be made even more useful through extensions. PostgreSQL supports a plethora of useful extensions, and we in turn want to ensure our customers can use those extensions. Unfortunately, due to the design of PostgreSQL, most extensions require elevated (superuser) permissions for installation. This creates a security conundrum: how to allow customers to install extensions without superuser access?

The struggle with many (or even most) security issues is that they are only contextually security issues. They’re actually features that you want in most contexts. The security industry struggles with this, often making hot-headed declarations about how inadequate the architectural design work or even the competence of the developers represents the real problem. As stated, this is exacerbated in the case of a Database Platform-as-a-Service such as Aiven. We sell peace of mind and confidence that our customers can trust us to take care of their data under all circumstances.

How to provide this balance between the three competing forces of application designers, the security (and compliance) industry, and customer trust? We need to make sure that customers have the full benefit of the service without unnecessary risk, and we need to ensure that we share the burden with the customer as we have this common interest.

We continue to experience this hamster wheel of pain as new security vulnerabilities are found and patched on a near-constant basis. When you’re working as intimately with customer data as we do at Aiven, every maintenance activity is a potential risk to our customer’s most important asset and even their ability to function.

We work hard with our post-sales Solution Architect group to make sure that customers have architected their Aiven services to be as resilient, available, and fault-tolerant as possible - but maintenance events aren’t free. Looking back to December 2021, we experienced 0december and had a series of maintenance events piling one on top of the other as the Log4j project released new patches in rapid succession.

This pattern of vulnerability → patch → vulnerability-with-slight-variation → patch-of-slightly-different-trigger-conditions → vulnerability-permutation-number-3 → patch-which-catches-a-few-additional-permutations is extremely costly to all parties involved. And yet, too often, the capacity or capabilities of an open source development team is simply not sufficient to provide a durable fix for a whole class of vulnerabilities.

In my opinion, this is one of the greatest failures of the big companies that profit through the use of open source software - they seem unwilling to pay for the proper (often expensive) architectural changes necessary to truly remediate the problem. This symptomatic patching cycle is what the open source world has to accept as their best effort. Developers find this just as frustrating as users of their projects do, if not more so!

I’d encourage anyone in the security industry to put yourself in the developers’ place before you point fingers and say “not good enough” - it must be completely mentally exhausting to do your best for the greater good and find yourself getting kicked while you’re already down.

One of the many reasons I wake up happy to go to work every day is that Aiven’s investors, Board of Directors, and executive team have empowered me to take a different approach to this endless cycle of patching for symptoms.

About six months ago, we started seeing an increase in bug bounty and internal product security findings related to issues in PostgreSQL extensions. We were experiencing this cyclical pain on behalf of the developers who work so hard on these extensions. We had to be the bearer of bad news and sometimes have multiple discussions about the same package with the same exhausted developers. Thankfully, we have a fantastic team here at Aiven and our Security Architect Etienne Stalmans hit upon a viable mitigation to the entire class of extension-related problems we had seen.

It’s not a complete solution. A complete solution would be to re-engineer PostgreSQL fundamentals that have existed for decades. But it’s a whole lot better than nothing at all!

In order to understand the solution, let’s examine how Aiven implements extension management for our customers. This gets a little technical, so I’ll quote Etienne directly:

Aiven allows customers to enable a predefined list of extensions. To get around the requirement of superuser access to install extensions, the pgextwlist extension is used to allow creation of extensions from a lower privileged user. pgextwlist functions by first elevating privileges to the default superuser, postgres, and then running the extension install scripts. This now exposes Aiven to the risk of privilege escalation attacks. This is clearly documented in PostgreSQL: https://www.postgresql.org/docs/current/sql-createextension.html.

Should an extension installation script be written in an insecure manner, unfortunately a very common occurrence despite the warnings in PostgreSQL documentation, it would be possible for users to gain superuser privileges.

Another common misconfiguration in extensions, which allows privilege escalation post extension creation, is the use of SECURITY DEFINER without schema qualifying functions within the SECURITY DEFINER function. Because the extension is created by the superuser, the SECURITY DEFINER functions will execute with superuser privileges. This can easily be abused to execute attacker controlled functions with elevated privileges.

This balance between enabling customers and having Bad Things™ occur is a constant battle to ensure positive outcomes for our customers.

A nearly unanswerable question posed as we saw the increased number of these inbound issues with PostgreSQL extensions was how to do a better job of finding them ourselves? Enter pghostile - an internal tool developed by the Security Engineering team, that we’re excited to open source today!

From the project readme.md:

Pghostile can make PostgreSQL an hostile environment for superusers and a nice playground for attackers. Pghostile is an automated tool for overriding "system" functions (the ones from the 'pg_catalog' schema) allowing an attacker to elevate privileges if/when these functions are called by a superuser.

It can be also used to test the security of the PostgreSQL extension. You can run pghostile to create the "exploit functions" and then run the extension's unit tests and see if you get superuser power after that.

As it turns out, there are a lot of these sorts of issues in PostgreSQL - pghostile checks for about 900 of them at this time (pull requests welcome!). We are joining some excellent company with this release. The fantastic tool pgspot by Sven Klemm from Timescale allows for easily identifying common misconfigurations in PostgreSQL extensions.

Open-sourcing a tool that helps look for even more of these vulnerable extensions sounds like a “typical security move” - making things worse instead of better. That’s not the Aiven way at all - our values of Ownership, Openness, and Courage push us to be better than that. We needed to protect our customers from this entire class of extension-based privilege escalation attacks, but we could also go ahead and help protect the other millions of PostgreSQL databases out there if we went ahead and open sourced the solution we built for our customers.

The Aiven Security Agent for PostgreSQL (aiven-gatekeeper) allows controlling which privileged functions are exposed and prevents their abuse in common privilege escalation attacks controlling which functions are available in elevated contexts, such as extension creation and security definer functions.

The agent is loaded as a shared library at PostgreSQL server startup and uses the UtilityProcess hook to intercept the utility function execution before the PostgreSQL engine executes them. A security decision can be made by examining the current execution state and determining if a privileged action should be allowed or not.

The agent uses the following three criteria for making a risk assessment before allowing or disallowing a function:

  • creating_extension
    • The function executes during the CREATE EXTENSION function.
  • is_elevated
    • The execution context is deemed to be "elevated" when the current_user is a superuser but the session_user does not have the superuser privilege. This occurs during CREATE EXTENSION or SECURITY DEFINER function execution.
  • is_security_restricted
    • PostgreSQL can set the current execution context to SECURITY RESTRICTED and already limits some of the actions that can be performed during this context. The agent complements these existing restrictions.

The security agent examines three primary utility functions:

  • ALTER/CREATE/GRANT ROLE
    • When altering, creating or granting a role with the superuser privilege.
    • Prevent granting the privileged permissions: pg_read_server_files, pg_write_server_files, pg_execute_server_program
  • Prevent adding a role to an existing role with the above privileges.
  • COPY TO/FROM PROGRAM
    • This is normally reserved for the superuser or roles with the pg_execute_server_program permission. This is always blocked, regardless of the context. There is no reason, on the Aiven platform, for execution of underlying host commands from within PostgreSQL
  • COPY TO/FROM FILE
    • This functionality is normally reserved for the superuser or roles with the pg_read_server_files or pg_write_server_files permission. This is blocked during an elevated context.

The security agent also blocks access to certain built-in functions and their derivatives:

  • pg_read_file
  • pg_read_binary_file
  • pg_reload_conf
  • lo_import
  • lo_export

These functions all allow read or write to the underlying filesystem, and can be chained together to achieve command execution.

And finally, there are some restrictions on what can and can't be written to the internal tables pg_proc and pg_authid.

The agent is designed to sit quietly, not interfering with legitimate PostgreSQL functionality, and thus not interfering with customer workloads or data. All efforts have been made to ensure that there is no performance impact. Like most good security guardrails, you shouldn’t know it is there until it is actually needed.

It’s one thing to write some software that claims to solve a significant class of security vulnerability. Trusting the authors of that software to do a good job and install it on the production systems that are responsible for the durable storage of your data is another thing entirely. This is something that we understand viscerally as we experience this burden not only ourselves but shared with our customers.

To validate that our work was as good as we thought it was, we reached out to friends, ex-colleagues, and anyone we could think of to review our work and provide a second (third, fourth, n-th!) opinion on the architecture and implementation of the solution. Over the last few months as we worked through development, several of these reviewers have actually implemented the Aiven Security Agent for PostgreSQL in their production environments and we know it now helps to protect millions of databases in as many different implementations as we could find.

Despite this earned trust from our professional peers, that’s still not the best we can do. We reached out to one of our application assessment and penetration testing partners Leviathan Security Group and asked them to provide us a Statement of Work to evaluate the Aiven Security Agent for PostgreSQL that would allow us to publish the results.

I am personally very pleased to have been able to sign off on that SoW and have the work completed - third-party validation of open source projects is still a rarity. You can read their full report here as well as a letter suitable to share with your audit, risk, or compliance staff. We are also including these documents as part of the source repository and will renew them as significant code changes are made to the project.

The approach taken is to maintain original service functionality as far as possible. Aiven does not aim to run a custom fork of PostgreSQL, and customers who migrate from their own self-hosted PostgreSQL instances should know what to expect. By using the PostgreSQL hooks mechanism, it is possible to extend existing PostgreSQL functionality and add additional security in a safe and predictable manner. This also makes the solution portable between PostgreSQL versions. Best of all, it makes it dead simple to open-source the solution and anyone can incorporate it into any existing PostgreSQL instance without a redeploy or fundamental changes to the system.

The agent cannot be a “100%” solution as it operates within the design limitations of the PostgreSQL extension mechanism. Although this does not prevent all possible privileged actions that could be done, such as bypassing row level security (RLS), it does take the sting out of possible post exploitation actions. This also allows for building solid monitoring and alerting around extension installation and privileged context, enabling the detection of malicious actions.

All Aiven for PostgreSQL customers have had the protection of the Aiven Security Agent for PostgreSQL for all new instances launched since 2022-07-08, or if they completed the maintenance updates available since then (99.91% of all PostgreSQL instances at Aiven). If you haven’t had a chance to implement this maintenance, please do so as soon as practical to take advantage of this added protection.

In the words of people far wiser than I, something is infinitely more than nothing, and I’m very privileged to lead this fantastic team of security engineers, developers, and architects at Aiven with complete support from above.

I believe we’ve provided something genuinely useful to the community and although my personal contribution to the code is nothing at all, my team has performed awesome work. Special thanks to investors, Board members, Exec team members, Open Source Program Office staff, friends and ex-colleagues who tested our work, unnamed third party organizations (you know who you are!), the Leviathan Security Group team, the development teams for both PGhostile the Aiven Security Agent for PostgreSQL, and especially our customers for giving us another opportunity to earn your Trust.

--

To get the latest news about Aiven and our services, plus a bit of extra around all things open source, subscribe to our monthly newsletter! Daily news about Aiven are available on our LinkedIn and Twitter feeds.

If you just want to stay find out about our service updates, follow our changelog.

Related blogs

All things open source, plus our product updates and news in a monthly newsletter.

Subscribe to the Aiven newsletter

Loading...