Right, let’s talk about one of the most powerful, and therefore most dangerous, switches in PostgreSQL’s function arsenal: SECURITY DEFINER. It’s the equivalent of handing a function a master keycard to the entire building, and you’d better be damn sure you trust the person you gave it to—which, in this case, is past-you who wrote the function. We’re going to tear apart why this exists, when to use it, and how to use it without creating a gaping security hole that would keep a DBA awake at night.

At its core, the choice between SECURITY DEFINER (the default is SECURITY INVOKER) answers a simple question: “Whose permissions does this function use when it runs?”

A SECURITY INVOKER function, which is the sensible default, executes with the permissions of the user who calls it. If you don’t have permission to UPDATE the users table, and you call a function that tries to update the users table, it will fail. This is the principle of least privilege in action. It’s safe, predictable, and frankly, a bit boring. We like boring when it comes to security.

A SECURITY DEFINER function, on the other hand, executes with the permissions of the user who owns the function (i.e., the user who created or last altered it). It effectively temporarily elevates the privileges of the caller to those of the function’s owner for the duration of the function call. This is incredibly useful and incredibly scary.

Why You’d Ever Want This Madness

Why would you willingly create such a privilege-escalation trap? Because sometimes you need to give users the ability to do a specific, privileged thing without giving them blanket permissions to do whatever they want.

The classic example is an API for updating a user’s own email address. You might have a table with sensitive columns you don’t want users directly updating (password_hash, is_admin, billing_info). You can’t just GRANT UPDATE ON users TO public_user; because that would be a catastrophe.

The solution? A SECURITY DEFINER function owned by a superuser or a dedicated role with very specific privileges.

-- First, create a powerful role that owns the sensitive table and has update rights.
CREATE ROLE api_owner NOINHERIT;
GRANT UPDATE (email) ON TABLE users TO api_owner;

-- Now, create a function owned by that role.
CREATE OR REPLACE FUNCTION update_user_email(
    p_user_id INT,
    p_new_email TEXT
)
RETURNS VOID
SECURITY DEFINER -- This is the magic switch!
SET search_path = public -- This is the CRITICAL companion switch!
LANGUAGE plpgsql
AS $$
BEGIN
    -- This function runs with api_owner's permissions.
    -- It can update the 'email' column because api_owner has that grant.
    UPDATE users
    SET email = p_new_email
    WHERE id = p_user_id;
    -- Notice there's no auth check here? That's a pitfall. We'll fix it later.
END;
$$;

-- Make sure the function is owned by our privileged role.
ALTER FUNCTION update_user_email OWNER TO api_owner;

-- Now, grant a low-privilege user the right to *execute* this function.
GRANT EXECUTE ON FUNCTION update_user_email TO public_user;

Now, public_user can call update_user_email(...) and successfully update the email column, even though they have no direct UPDATE grant on the users table. They borrowed the api_owner’s keycard for a split second to do that one specific task.

The Search Path Landmine and How to Defuse It

Here’s where the designers made a choice that I will charitably call “a historical misstep.” When a SECURITY DEFINER function runs, it does not just inherit the owner’s table permissions; it also, by default, uses the owner’s search_path. The search_path determines which schema(s) are searched for unqualified object names (like users instead of public.users).

If the function owner has a search_path that includes a schema they own, like $user, public, and an attacker can create tables in that schema… well, you see where this is going. They can create a table named users in their own schema. When your function runs UPDATE users..., it will find the attacker’s users table first and update that instead. This is a textbook Trojan horse attack.

The fix is non-negotiable: Always explicitly set a hardcoded, trusted search_path inside every SECURITY DEFINER function you create. This is what the SET search_path = public clause does in the example above. It tells PostgreSQL, “Ignore whatever messy path the owner might have; during this function, only look in the public schema for objects.” This practice slams the door shut on this entire class of vulnerability.

The Principle of Least Privilege, Revisited

Just because your function can do anything the owner can do doesn’t mean it should. The function itself should be a narrow, purpose-built tunnel. The golden rule: The function’s owner should have the absolute minimum permissions required for the function to do its job—and nothing more.

In our example, we created a dedicated api_owner role and only granted it UPDATE (email) on the users table. We did not make api_owner a superuser or grant it ALL on the table. If the function code is somehow exploited, the blast radius is contained to just updating the email column on that one table. This is far superior to using your actual superuser account as the function owner.

The Inside-Out Vulnerability

Look back at our first example. Did you spot the gaping logic hole? The function updates users WHERE id = p_user_id. But it makes no check to see if the caller is allowed to update that specific user! A user could pass any user_id they wanted and change someone else’s email. Whoops.

A SECURITY DEFINER function handles authentication (who you are) but not authorization (what you’re allowed to do). You must implement authorization logic inside the function. The correct way is to have the caller pass their own identity (e.g., via a session_user parameter, or better yet, by using a context variable if this is behind a web application) and then validate that they have the right to affect the record they’re trying to affect.

CREATE OR REPLACE FUNCTION update_my_email(p_new_email TEXT)
RETURNS VOID
SECURITY DEFINER
SET search_path = public
LANGUAGE plpgsql
AS $$
BEGIN
    -- Get the current user's name from the session.
    -- You'd likely use a different method (like a JWT claim) in a real app,
    -- but this illustrates the point.
    UPDATE users
    SET email = p_new_email
    WHERE username = current_user; -- Authorize by matching the database user.
END;
$$;

In summary, SECURITY DEFINER is a scalpel, not a sledgehammer. Use it to carefully grant specific, elevated capabilities. Always pair it with SET search_path, own the function with a minimally privileged role, and rigorously validate authorization inside the function itself. Do that, and you’ll have a powerful tool. Forget any of it, and you’ve just built a very fancy security vulnerability.