SECURITY DEFINER Fails in Production: the Executing Role

Conclusion

A SECURITY DEFINER function is owned by whichever role ran the migration.

Run migrations as a non-privileged role and the function is owned by that role. It can then no longer bypass RLS, and a function written to bypass it always returns zero rows.

The function definition is still correct. Nothing appears in a schema diff. The only thing that changed is the owner.

Symptom

• CI passes everything
• Staging works
• Only in production does every path using that function fail
• No exception. It just returns zero rows

Not being an error is what makes it awkward. Zero rows is correct as the result of RLS applying, so the database says nothing. From the caller’s side it is indistinguishable from “no matching records”.

Suspecting permissions and reviewing GRANT does not help. It is not a GRANT problem, it is an ownership problem.

Cause

Three facts combine.

① SECURITY DEFINER runs with the function owner’s privileges

Not the calling role’s — the owning role’s. That much is as specified, and most write-ups cover it.

② A function is owned by the role that ran its CREATE FUNCTION

Which means the role that ran the migration becomes the function’s owner. The owner is not written in the migration file, so it is not decided until the migration runs.

③ A table owner bypasses RLS (unless FORCE is set)

A table with only ENABLE ROW LEVEL SECURITY does not apply RLS to access from its owner. Adding FORCE ROW LEVEL SECURITY subjects the owner too.

Put together:

Run as table owner       → function owner = table owner       → bypasses RLS → as designed
Run as non-privileged    → function owner = non-privileged    → cannot bypass → always zero rows

Why CI does not reproduce it

CI normally connects as superuser to run migrations. The superuser is also the table owner, so CI always lands on the “as designed” side.

It only manifests through the role production chooses. Adding tests changes nothing while CI’s connection role stays superuser — it stays green forever.

How to check

First, the function’s owner.

SELECT p.proname, r.rolname AS owner, r.rolbypassrls
FROM pg_proc p
JOIN pg_roles r ON r.oid = p.proowner
WHERE p.proname = '<function name>';

Then the table’s owner, and whether they match.

SELECT c.relname, r.rolname AS owner
FROM pg_class c
JOIN pg_roles r ON r.oid = c.relowner
WHERE c.relname = '<table name>';

If they do not match, that is the cause.

To confirm in practice, switch to the application role and call the function.

SET ROLE <app role>;
SELECT * FROM <function name>('<valid argument>');  -- one row means OK
RESET ROLE;

The point is verifying inside the database, not through the calling code. Going through the app conflates “zero rows returned” with “rejected before it got there”.

The fix

Reassign the owner. No need to recreate.

ALTER FUNCTION <function name>(<arg types>) OWNER TO <table owner role>;

Then run the checks above again.

Making it not recur

Stopping at the reassignment means the next migration puts it back. The cause is “who ran it”, so what needs fixing is the procedure, not the function.

• Pin the migration’s executing role to the table owner. Separate connection details by role and hold a distinct connection string for migrations
• Reflect that pinning in the deployment pipeline too. Written only in a runbook, it comes off the moment someone applies by hand
• Put the ownership check into the post-apply verification. Make running the SQL above and confirming a match part of the definition of “applied”

Hardening CI to green will not catch this failure. The single fact that the connection role differs is the wall between CI and production reproducibility. If you cannot remove the wall, verification has to live on the production side.

For another “tests pass but only production stays broken”, see Cloud Run Job images freeze. There the code being run is stale; here the role running it is different — in both, what is running differs from what is expected and no error is raised.