PostgreSQL Extensions: The Superuser Trap and How to Escape It

PostgreSQL Extensions: The Superuser Trap and How to Escape It

Why simple commands like CREATE EXTENSION turn into a security nightmare, and how to solve it using a dedicated Extension Admin architecture.

If you manage PostgreSQL databases or develop on them, you have likely starred at this error message in frustration:

ERROR: permission denied to create extension postgis HINT: Must be superuser to create this extension.

It usually happens on a Tuesday afternoon. A developer pings you: Hey, I just need to enable PostGIS for the new geolocation feature. Can you give me permissions?

You think,

Sure, it’s just an extension. I’ll grant CREATE on the database. They run the command. It fails. Then they ask the forbidden question: Can you just make me Superuser for 5 minutes?

As a DBA or a security-conscious developer, your answer must be NO. Giving a developer Superuser privileges is like giving a house guest the deed to your property just so they can open a window.

In this article, we will explore why PostgreSQL is so paranoid about extensions, examine the real-world PostGIS Case Study, and implement a Production-Grade Solution using a dedicated extension_admin role—avoiding the usage of the default postgres user entirely.

The Root Cause: Trusted vs. Untrusted Extensions

To understand the problem, you must understand how PostgreSQL views extensions. Not all extensions are created equal.

1. The Trusted Ones (Safe)

Starting with PostgreSQL 13, the concept of Trusted Extensions was introduced. These are extensions that the PostgreSQL community has deemed safe. They mostly consist of SQL-level functions that do not threaten the operating system.

• Examples: pgcrypto, uuid-ossp, tablefunc, citext.
• Privilege Required: If a user has CREATE privilege on the database, they can install these without being a Superuser.

2. The Untrusted Ones (Risky)

This is where the trouble begins. Extensions like PostGIS, file_fdw, or dblink are considered Untrusted.

Why? Because they are not just SQL scripts. They load C-language libraries (.so files) directly into the PostgreSQL server process. A malicious or buggy C library can crash the entire server, read sensitive system files, or bypass database security entirely.

Because of this risk, PostgreSQL mandates that only a Superuser can load these libraries.

Case Study: The PostGIS Dilemma

Let’s look at the most common real-world scenario: PostGIS.

Even though PostGIS is the industry standard for geospatial data, it is heavily dependent on external C++ libraries (GEOS, PROJ, GDAL). When a developer tries to run CREATE EXTENSION postgis;, the database blocks the action immediately to protect the server's memory integrity.

So, how do we unblock the developer without compromising security? We have two distinct approaches depending on your environment.

Method 1: The King’s Hand (Manual Intervention)

Best for: Production Environments (PROD)

In a live production environment, you want friction. You don’t want developers installing heavy extensions dynamically. In this scenario, the DBA should perform the action.

However, simply running the create command isn’t enough. PostGIS creates specific tables that standard users often cannot read by default.

The Protocol

Log in with your admin user and run:

-- 1. Create the extension yourself
CREATE EXTENSION IF NOT EXISTS postgis SCHEMA public;

-- 2. CRITICAL STEP: Grant permissions on the hidden system tables
-- PostGIS creates a table called 'spatial_ref_sys' that users need to read for coordinate transformation.
GRANT SELECT ON TABLE spatial_ref_sys TO developer_user;

-- 3. Ensure they can use the schema
GRANT USAGE ON SCHEMA public TO developer_user;

Method 2: The Extension Admin Pattern (Automated)

Best for: Development, Test, Docker, and CI/CD Environments

In a development environment, you don’t want to be the bottleneck every time a team resets a database. You need a way to let them install specific extensions via a whitelist.

To do this securely, we use a Security Definer Function.

Pro Tip: Don’t Use the postgres User!

Most tutorials tell you to own this function with the default postgres user. This is a security anti-pattern. Using the root user for operational tasks makes auditing difficult.

Instead, we will create a dedicated extension_admin role.

Step 1: Create the Dedicated Admin Role

First, create a user that has the Superuser privilege, but is distinct from the system root. This allows you to rotate passwords easily and audit logs effectively.

-- Create a dedicated user for managing extensions
CREATE USER extension_admin WITH PASSWORD 'VeryStrongPassword123!';

-- Grant Superuser ONLY to this specific admin user
ALTER USER extension_admin WITH SUPERUSER;

Step 2: Create the Wrapper Function

Now, we create a function owned by extension_admin. The SECURITY DEFINER clause ensures that when a normal developer runs this function, it executes with the privileges of extension_admin.

CREATE OR REPLACE FUNCTION admin_tools.safe_install_extension(ext_name text)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER -- <== The Magic: Runs with the OWNER'S (Superuser) privileges
AS $$
DECLARE
    -- The Whitelist: Only allow these specific untrusted extensions
    allowed_extensions text[] := ARRAY['postgis', 'postgis_topology', 'pg_trgm', 'uuid-ossp'];
BEGIN
    -- Check if the requested extension is in our whitelist
    IF ext_name = ANY(allowed_extensions) THEN
        EXECUTE format('CREATE EXTENSION IF NOT EXISTS %I CASCADE', ext_name);
        RAISE NOTICE 'Extension % installed successfully by Extension Admin.', ext_name;
    ELSE
        -- Block anything else (like file_fdw or adminpack)
        RAISE EXCEPTION 'Permission Denied: % is not on the allowed whitelist.', ext_name;
    END IF;
END;
$$;


-- CRITICAL: Set the owner to our dedicated admin user
ALTER FUNCTION admin_tools.safe_install_extension(text) OWNER TO extension_admin;

Step 3: Grant Access to the Developer

Finally, give the developer permission to run only this function.

-- Revoke public access first (Security First)
REVOKE EXECUTE ON FUNCTION admin_tools.safe_install_extension(text) FROM PUBLIC;

-- Allow your developer to run it
GRANT EXECUTE ON FUNCTION admin_tools.safe_install_extension(text) TO developer_user;

The Developer’s New Workflow

Instead of running the forbidden SQL command, the developer now runs:

SELECT admin_tools.safe_install_extension('postgis');

Conclusion

PostgreSQL’s security model regarding extensions is strict for a reason. By distinguishing between Trusted and Untrusted extensions, and implementing a Dedicated Extension Admin role, you achieve two things:

• Security: Dangerous extensions like file_fdw remain blocked.
• Agility: Developers can self-service necessary tools like PostGIS without pinging you at midnight.

Next time a developer asks for PostGIS, don’t hand over the keys to the kingdom build them a secure door.