When an API Goes Rogue: Containing a Superuser Table-Creation Attack on PostgreSQL

A production call with a client is rarely a calm affair, and this one started the way these calls usually do: tables were appearing in the database at an alarming pace, nobody on the client’s side had created them, and the count kept climbing. This post walks through what we saw, how we contained it, and the checklist we handed over afterwards. If you run PostgreSQL (or EDB Postgres Advanced Server) behind an application API, there is a good chance something here applies to you.

The Situation

The client’s database was suddenly filling up with randomly named tables. The first instinct in such a case is to suspect the database itself: a compromised OS account, a rogue extension, a misconfigured pg_hba.conf. But as we dug in during the call, the picture pointed elsewhere:

• The activity didn’t originate from the database side. It was coming through the application’s API layer, a security breach at the API level.
• Only CREATE TABLE was happening. No rows were being inserted into, updated in, or deleted from the existing main business tables.
• When we checked which database role was executing the statements, the answer was the most uncomfortable one possible: the application was connecting as a superuser (the default enterprisedb login).

That last point is the real story. An attacker who can influence what your API sends to the database inherits whatever the application’s database role can do. With a superuser, that is everything: creating objects in any schema, reading any file the server process can read, and, depending on configuration, even running OS-level commands through mechanisms such as COPY ... PROGRAM. In this case the attacker (or a script) was only creating tables, but there was no technical barrier stopping something far worse.

Step 1: Isolate the Blast Radius

Before hunting for root cause, you shrink what the attacker can do. The fastest way to do that is to stop the application from using a superuser at all.

The plan we agreed on with the client was to create two separate roles, neither with superuser privileges:

1. Application user (read/write): LOGIN, DML (SELECT, INSERT, UPDATE, DELETE) on all tables, EXECUTE on stored procedures and functions, and USAGE plus SELECT on sequences. This is what the general application traffic uses.
2. Reporting user: everything the application user has, plus the ability to create (and, where truly needed, drop) tables, for reporting workloads that build temporary or summary tables.

The obvious question is: how do you know exactly which privileges the application needs, without guessing and breaking production?

Let pg_dump Tell You

The existing superuser-based login already works, but its permissions are tangled up with ownership and defaults. A schema-only pg_dump records the access control list (ACL) for every object, so you can lift the grants straight from it:

pg_dump -s -d your_database -f schema_only.sql

Then pull out every statement that references the login the application currently uses:

grep -iE "(GRANT|REVOKE|ALTER DEFAULT PRIVILEGES).*enterprisedb" schema_only.sql > acl_statements.sql

The resulting file contains the GRANT/REVOKE statements that define what that login can do on each table, sequence, function, and schema. Replace the role name with your new application role (and, for the reporting role, add the extra privileges), review the output, and apply it:

sed 's/enterprisedb/app_user/g' acl_statements.sql > app_user_acl.sql
psql -d your_database -f app_user_acl.sql

A minimal role setup looks like this (illustrative, adapt names and schemas to your environment):

CREATE ROLE app_user LOGIN PASSWORD '<strong-password>' NOSUPERUSER NOCREATEDB NOCREATEROLE;
CREATE ROLE report_user LOGIN PASSWORD '<strong-password>' NOSUPERUSER NOCREATEDB NOCREATEROLE;

GRANT USAGE ON SCHEMA app TO app_user, report_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_user, report_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_user, report_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA app TO app_user, report_user;

#Only the reporting role may create tables
GRANT CREATE ON SCHEMA app TO report_user;

Two details worth knowing:

• DROP TABLE cannot be granted directly. In PostgreSQL, only the owner of a table (or a member of the owning role, or a superuser) can drop it. If the reporting user needs to drop the tables it creates, that works naturally because it owns them. Avoid handing it ownership of the application’s core tables.
• Default privileges matter. If the application creates new objects over time, review ALTER DEFAULT PRIVILEGES so future tables and sequences get the right grants. Also make sure CREATE on the public schema is revoked from PUBLIC (this is the default from PostgreSQL 15 onward, but older clusters need it done explicitly).

With this in place, even if the API is still vulnerable, the application role can no longer create tables, which is exactly the behavior the attacker was exploiting. The attack surface drops from “the entire server” to “the data this application is meant to touch.”

Step 2: Investigate Beyond the Database

Because the attacker had superuser access, we cannot assume the damage stopped at the database. After containment, we asked the client to look at the wider environment:

1. Capture all logs: PostgreSQL logs, WAL, application logs, web server logs, and operating system logs (firewall logs, dmesg, and so on). Preserve them before rotation overwrites the evidence.
2. Check cron jobs for anything unfamiliar. Persistence via scheduled tasks is a classic.
3. Inspect the PostgreSQL directories, especially the bin folder and the data directory, for scripts or binaries that should not be there.
4. Review environment variables for unknown or foreign entries.

These checks exist because superuser privileges in PostgreSQL can reach the operating system. Checking for dropped scripts and modified environments is how you confirm whether the intrusion stayed inside the database or not.

Step 3: Remediation

Once the application is running on least-privilege roles and the investigation is underway, the cleanup begins:

1. Rotate the password of the compromised superuser login (enterprisedb in this case) if you use md5 or scram-sha-256 authentication. Treat any credential the application could reach as exposed.
2. Tighten pg_hba.conf. Allow logins only from known hosts such as the application servers and reporting servers. Reject everything else.
3. Take a schema-only pg_dump of production. This is the list of objects as they exist today, including the attacker’s.
4. Restore the last known good backup onto a separate system and take a schema-only pg_dump from that instance. This is the list of objects as they existed before the incident.
5. Diff the two dumps and drop every object that appears only in production. A plain diff over the two schema files is surprisingly effective here:

diff <(sort prod_schema.sql) <(sort known_good_schema.sql)

Review the diff carefully before dropping anything. Legitimate changes made since the backup (new features, hotfixes) will also show up, so cross-check with the client’s release history.

Lessons Learned

Never run an application as a superuser. Superuser bypasses every permission check in PostgreSQL. It is meant for administration, not for application connections. If your app’s connection string uses postgres or enterprisedb fix it this week.

Separate roles by purpose. A read/write application role and a more privileged reporting role keep each workload to its own needs and make anomalies easier to spot.

Keep a schema-only dump around. Regular schema snapshots turn “what changed?” from guesswork into a ten-second diff. They also double as a source of truth for ACLs.

Consider DDL auditing. Setting log_statement = 'ddl' (or using an event trigger or the pgaudit extension) would have surfaced the unexpected CREATE TABLE statements much earlier than a human noticing the table count.

Fix the real hole. Everything above limits damage and cleans up, but the root cause is a security flaw in the API. Until that is patched, the application remains an attacker’s entry point, just a much less powerful one.

Final Thoughts

In this incident, the attacker only created tables, and we were fortunate that nothing in the main business data was touched. Fortune is not a strategy, though. The combination of an exposed API and a superuser connection is how a nuisance becomes a data breach. Least privilege, host-restricted access, and regular schema snapshots are cheap to put in place and expensive to go without.

If you are facing something similar, or want a review of how your application connects to PostgreSQL, feel free to reach out.