Skip to main content
BLOG

Developer report: Identifying hidden dependencies in PostgreSQL

Marcel RöslerMarcel Rösler
2026-11-05
3 Min.

In his article, our colleague Marcel shows what's happening technically beneath the surface – and why successful modernization requires deep engineering.

A database has been running stably for years. Applications access it reliably, backups work, and monitoring shows no anomalies. The next major upgrade is planned, the maintenance window is coordinated, and there's a rollback in place for emergencies.

Actually, these are good conditions.

And yet, the upgrade can still fail.

I find precisely these kinds of cases fascinating. Because they show that database modernization doesn't end where the target architecture, migration plan, and cloud services are defined. Sometimes the real problem lies several levels deeper – in technical dependencies that have grown over years and go completely unnoticed during operation.

An example from Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL illustrates this very clearly: A perfectly functioning role and permissions model can suddenly become an upgrade blocker when switching to a new PostgreSQL major version.

When Permissions Become Dependencies

PostgreSQL's permissions model doesn't distinguish between users and groups in the traditional way. Both are roles. A role can be a member of another role and thereby inherit its permissions.

In theory, this is manageable. In real-world environments, however, it rarely remains a simple direct assignment.

An application role is granted write permissions. It is simultaneously a member of a business role. This, in turn, is dependent on an administrative role. Over the years, applications, users, and new requirements are added.

At some point, we no longer have just a list of permissions, but a directed graph.

Roles are the nodes, role memberships form the edges. Transitive permission paths are created across multiple edges. And these paths can become important later on.

Because every single role membership can be perfectly valid—and still become part of a dependency that only causes us problems years later.

When the upgrade suddenly closes a loop

The case described by AWS concerns upgrades from PostgreSQL 14 or earlier to PostgreSQL 15 or later on Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL.

A key role here is played by rds_superuser, which AWS uses for administrative tasks within the managed PostgreSQL environment.

When migrating to PostgreSQL 15 or later, the role model changes: As part of the upgrade, rds_superuser becomes a member of the built-in PostgreSQL roles pg_read_all_data and pg_write_all_data. This ensures that the administrative role retains the necessary data access even under the new engine version.

Problems arise if a path in the opposite direction already exists in the existing environment—either directly or via several intermediate roles.

In simplified terms, this could look like this:

Existing transitive role path in the role and permissions model

Role path in the role and authorization model - PROTOS Techologie.png

If the upgrade adds a new membership in the opposite direction, the cycle is complete.

PostgreSQL does not allow circular role memberships. Therefore, the necessary change cannot be made. The upgrade aborts and is rolled back.

The interesting thing is: nothing may have been broken before the upgrade.

The applications could function. The permissions could do exactly what they were configured for. The system could run stably for years. Only the target version changes the parameters and makes a previously hidden dependency a problem.

Stable doesn't automatically mean upgradeable

For me, this is precisely the more important insight behind the specific AWS case.

In daily operations, we primarily check whether the current state works.

With a major upgrade, we have to ask a different question:

Is today's state also compatible with the rules of the future platform?

This also changes the technical preparation.

Of course, CPU, memory, backup status, maintenance windows, and storage capacity remain relevant. But we also need to understand which roles depend on each other, which extensions are installed, which configurations change between versions, and what behavior applications rely on.

So we have to look beneath the surface.

PostgreSQL already knows much of this

That's the good news: We don't have to reconstruct much of this information from documentation or old architecture diagrams.

PostgreSQL has its own structure. In system catalogs, the database keeps track of all its objects and relationships:

pg_roles provides us with the list of all existing roles (the nodes in our graph).

pg_auth_members is the real heart of our problem: This system table stores precisely the relationships between roles – that is, which role is a member of another role (the edges of our graph). Only by reading this table do the connections become visible.

pg_extension shows installed extensions.

pg_settings provides the current configuration.

pg_depend makes dependencies between database objects traceable.

Why is `pg_auth_members` so crucial for this specific role problem?

Because PostgreSQL stores role memberships flatly, table by table (role A contains role B), at first glance only direct assignments are visible. To uncover the much more dangerous transitive authorization paths across multiple levels, we need to query this table recursively.

With a suitable query – PostgreSQL provides WITH RECURSIVE for this purpose – we traverse the entire graph along the entry rows of pg_auth_members. This allows us to trace complete paths across any number of intermediate roles.

A chain of relationships that is invisible in everyday use then becomes transparent: rds_superuser → administrative_role → application_role → pg_write_all_data

Only by looking at pg_auth_members do we transform individual permissions into a coherent picture. This way, we not only know that a risk exists, but we can also see, together with our colleagues, how it arose. This is precisely where troubleshooting becomes engineering.

From Individual Checks to Technical Preflight

For the specific AWS scenario, we could run a suitable query, review the result, and then start the upgrade. This might suffice for a single database. However, if we operate multiple databases, regularly perform major upgrades, or are responsible for a larger cloud platform, we want to make this a repeatable process.

We can automatically extract relevant metadata and check it against known requirements of the target version: role paths, extensions, configuration parameters, or version-dependent database objects.

Such checks can be integrated into an existing CI/CD pipeline or executed, for example, via AWS CodeBuild.

The specific tool is secondary for me.

What's more important is that the check runs reproducibly, its results are transparently documented, and critical findings are made visible before the actual upgrade begins. That's our technical preflight.

And yet, we still wouldn't upgrade to production first

Such a preflight can very effectively identify known conflicts. However, it cannot guarantee that we have pre-modeled every peculiarity of an existing database.

Therefore, for us, a robust major upgrade also includes realistic testing of the target version.

In AWS, there's an obvious way to do this: We create an isolated RDS instance or a separate Aurora cluster from a current snapshot and perform the major upgrade there.

This way, we're not testing against an artificial sample database, but against structures that actually correspond to the production environment: roles, extensions, database objects, configurations, and real data volumes.

And after that, a green upgrade status isn't enough for us.

We want to know if roles and permissions still work. If the necessary extensions are available. If applications can authenticate. If representative queries deliver the expected results. And also, how long the entire process actually takes under realistic conditions.

This fundamentally changes the role of the production maintenance window:

It's no longer the first real upgrade attempt, but the controlled repetition of a proven process.

Automation finds the problem. Engineering solves it.

Let's assume our preflight check actually finds a role path that would create a cycle during an upgrade.

Technically, the solution initially seems simple: We remove the membership that would later close the loop.

Problem solved?

Not necessarily.

Perhaps an application needs precisely the permission we just removed.

Therefore, we wouldn't stop at diagnosis. We want to understand why this membership exists, which users or applications depend on it, and whether the necessary rights can be assigned more precisely.

Perhaps an application doesn't even need `pg_write_all_data`, but merely write access to some defined tables and their associated sequences.

Then an upgrade problem suddenly becomes an architectural question.

And the well-known principle of minimum necessary permissions gains another dimension: A more precise role model not only increases security. It also reduces implicit dependencies—and thus potential surprises during later upgrades and migrations.

This is precisely what no automated check can decide on its own. Automation can show us where we need to look. Engineering means understanding what we are allowed to change.

The role cycle is just one example

The AWS case is particularly illustrative. However, the underlying principle extends far beyond this.

A PostgreSQL database consists of many such relationships.

Views depend on tables or other views. Functions use data types, extensions, or other functions. Triggers reference functions. Applications rely on drivers, authentication methods, and specific SQL behavior. Configurations also change between major versions.

Therefore, we don't see a technical preflight as a one-time checklist for a single role issue.

We can evolve it along with the platform.

Today, we check a known role cycle. With the next upgrade, we'll add a check for a critical extension. Later, perhaps a check for a changed parameter.

In this way, individual technical insights gradually create a safety net for future changes.

One level deeper in database modernization

In our first post in this series, we described why the move from a database monolith to the cloud is initially an architectural decision.

The second part focused on how to migrate legacy Oracle databases to modern cloud architectures in a controlled manner.

With the PostgreSQL example, we're now deliberately delving deeper. Even when the target architecture and migration path are clearly defined, the technical details of a legacy platform remain: roles, extensions, configurations, object dependencies, and application interfaces.

It's precisely here that the question often arises whether a modernization that looks clean on paper actually works in practice. For us at PROTOS, database modernization therefore means more than just replacing an engine or moving a workload to AWS. We want to understand what's happening technically beneath the surface. To achieve this, we analyze dependencies, identify risks early on, and test changes against realistic systems before they go live.

Because a database that runs stably today is far from being a database that can be easily evolved tomorrow.

Conclusion: The maintenance window shouldn't be the moment of truth

A major upgrade is more than just a version change.

The case described by AWS illustrates this with a surprisingly small technical detail: A role structure that has functioned flawlessly for years can suddenly create a circular path due to a change in the target version—thus preventing the entire upgrade.

This may initially sound like a special case.

But for me, it demonstrates something more fundamental: Many upgrade risks are already present in the system before the upgrade. We just need to know what to look for.

PostgreSQL provides us with the metadata for this. We can analyze it, develop automated preflight checks from it, and then test the target version against a realistic copy of the production environment.

This shifts the decisive moment forward. We don't discover the problem during the maintenance window, but rather beforehand.

And that's precisely where controlled cloud engineering begins for us.

Marcel Rösler

Marcel Rösler

Senior AWS Cloud Architect

Marcel Rösler is a Senior AWS Cloud Architect at PROTOS Technologie GmbH with extensive experience in developing and managing modern cloud and full-stack architectures. His expertise ranges from software and front-end development to full-stack projects and cloud engineering and architecture roles based on AWS, Terraform, and automated infrastructures. He also distinguishes himself through continuous professional development in the field of AI and agent systems.
LinkedIn Profil