Postgres RLS in Symfony: a setup check for a missing FORCE, and two ways it says "nothing to report" without looking

Three articles ago I wrote that the Postgres role which runs your migrations bypasses every row-level security policy, unless the table has FORCE ROW LEVEL SECURITY. In the thread under the article before that one, @to21as made a proposal I had no argument against: a setup check does not have to impose FORCE or a separate role to be useful. It can just detect that the role serving the requests owns the tenant tables and that FORCE is off, and say so. Nothing imposed, and the buyer who deploys with CREATE DATABASE app_db OWNER app finds out at setup instead of never.

I agreed, and then I wrote the one thing I could not answer yet: what do those queries return for a serving role that is not the owner and cannot see what it does not own? A check that answers "nothing to report" because it cannot see the tables would be worse than no check at all.

I have the number now. The short answer is reassuring: the catalog does not have the hole I was afraid of. The long answer is that the check can still say "nothing to report" without looking, by two other routes, and one of them no query can fix.

The bench

PostgreSQL 18.3, a throwaway database, three roles. Three tenant tables, all created by the owner, which is what CREATE DATABASE app_db OWNER app followed by doctrine:migrations:migrate gives you. RLS enabled on all three, a tenant policy on each, and FORCE deliberately absent: that is the configuration the check is meant to catch. One of the tables, archive.audit_log, lives in a schema outside the default search_path, to test a second way a check can come back empty.

The three roles:

• det_owner serves the requests and owns the tables. The case to detect.
• det_app serves the requests, owns nothing, has normal read and write grants.
• det_bare serves the requests, owns nothing, and has no grant at all on the tables. A role that was never fully provisioned.

The two queries

The check needs one thing from the application: the list of tenant tables. In a Symfony app that list already exists, in the Doctrine metadata of the entities you scope by tenant. The queries join that list to pg_catalog by name, with a LEFT JOIN:

-- Does the serving role own the tenant tables?
SELECT w.s || '.' || w.t                          AS tenant_table,
       (c.oid IS NOT NULL)                        AS seen_in_catalog,
       pg_get_userbyid(c.relowner)                AS owner,
       pg_get_userbyid(c.relowner) = current_user AS owned_by_me
FROM   (VALUES ('public','organization'),
               ('public','invoice'),
               ('archive','audit_log')) AS w(s, t)
LEFT   JOIN pg_catalog.pg_namespace n ON n.nspname = w.s
LEFT   JOIN pg_catalog.pg_class     c ON c.relnamespace = n.oid AND c.relname = w.t;

-- Is RLS enabled, and is it forced?
-- same FROM and joins, selecting c.relrowsecurity and c.relforcerowsecurity

The LEFT JOIN is not cosmetic. It turns "I cannot see this table" into a row with seen_in_catalog = false, which is a result you can act on, instead of a missing row, which is silence.

What they return

                                        join by name        to_regclass()                 information_schema.tables
det_owner  owns the tables              3 rows, owned=t     3 rows                        3 rows
det_app    owns nothing, DML grants     3 rows, owned=f     3 rows                        3 rows
det_bare   owns nothing, no grants      3 rows, exact       ERROR: permission denied      0 rows, no error
                                                            for schema archive

The first column is the answer to the question from the thread. pg_class and pg_namespace are readable by PUBLIC by default, so a role with no privilege at all on the tenant tables still reads their owner, relrowsecurity and relforcerowsecurity, exactly. Visibility of the catalog is not the problem.

The verdict, written as one query over the same join, has three outcomes and never two:

det_owner   WARNING: the serving role owns 3 tenant table(s) without FORCE
det_app     NOTHING TO REPORT
det_bare    NOTHING TO REPORT      (correct: it owns nothing, and it can see that)
fresh db    UNDETERMINED: 3 tenant table(s) not found in the catalog

The third outcome is the one that answers the original fear. "I could not find the tables" is not "nothing to report", and a fresh database where the migrations have not run yet gets UNDETERMINED, which is the true answer.

Route 1: the first query you would naturally write

The obvious way to list tables is information_schema.tables. For det_bare it returns zero rows, with no error. Its views only show what the current role has some privilege on.

In this particular case the verdict happens to be right, since det_bare owns nothing. That is exactly why it survives review. A check built on it cannot tell "there are no tenant tables here" from "I am not allowed to see them" from "the migrations never ran", so it loses the third outcome, and the third outcome was the point.

to_regclass() is the other tempting shortcut, and it fails the other way. Resolving a qualified name needs USAGE on the schema, so for archive.audit_log the whole query dies with permission denied for schema archive, taking down the answers for the two tables it could have read. That one is at least loud. The join by name does no resolution, and has neither problem.

A detour: the check that warns every install

Before the second route, a mistake I nearly made in the verdict. The condition "the serving role owns the table and FORCE is off" is true on every database where RLS was never enabled. I dropped RLS from the three tables and ran it as the owner:

naive    (owned AND NOT forced)             WARNING: the serving role owns 3 tenant table(s) without FORCE
fixed    (owned AND enabled AND NOT forced) NOTHING TO REPORT: no RLS on these tables

A project that does not use RLS at all would get a warning about a feature nobody asked for, on every run. A check that warns everyone warns no one. It should only speak when RLS is enabled and not forced.

Route 2: the connection, not the query

Now the one I cannot close. Open the connection as the owner, then step down to the serving role with SET ROLE, which is one way to get a non-owner role without a second set of credentials:

SET ROLE det_app;
current_user = det_app, session_user = det_owner
verdict: NOTHING TO REPORT

RESET ROLE;
current_user = det_owner
rows read from invoice with no tenant context set: 2 of 2

The check asks about current_user, and current_user is the serving role, which owns nothing. The answer is accurate and meaningless: anything running on that connection can RESET ROLE and is back to being the owner, and the owner bypasses a policy that is not forced. Two rows out of two, no context set.

Asking about session_user as well does catch it, all three tables come back as owned by the login role. So the check should look at both. But that only reduces the problem, it does not remove it: the check measures the connection it is run on. If you run it locally with one DATABASE_URL and deploy with another, it measured the wrong role, and nothing in its output tells you.

Where the check can live

This bench also settled where such a check cannot go:

• Not before the migrations. On a fresh database it returns UNDETERMINED. Correct, and useless at that moment. It belongs after doctrine:migrations:migrate.
• Not in a pre-install check that runs before the environment is configured. It needs a real connection with the production role.
• Not without the application's list of tenant tables. Without it there is no LEFT JOIN, so no third outcome.

What this does not cover

Nothing was measured behind PgBouncer, or with a SET ROLE issued by a pooler rather than by the application. I also revoked SELECT on pg_class and pg_namespace from PUBLIC to test the edge: the check then fails with a permission error, which is the right way to fail. pg_tables still answers in that case, since a view runs with its owner's rights, but it does not expose FORCE, so it can answer the first question and not the second. I have not seen those grants revoked in production; the case is there to find the boundary, not to describe a real deployment.

Reproduce it

The bench is one bash script, and every query that matters is in this article. It creates the database and the three roles, runs every query above as each role, locks the catalog, computes the verdicts, does the step-down, disables RLS for the false positive, drops the tables for the fresh database case, and removes everything on the way out. Its labels are in French: the outputs above are translated, the values are not. I wrote it alongside ShipAnvil, a multi-tenant Symfony kit that scopes tenants with a Doctrine filter: here is what the source code licence contains and costs. To be clear about what that means here: the kit ships no RLS and no such check today. This is a measurement of the idea, not a feature announcement.

Thanks to @to21as for the distinction that started it: detecting is not imposing. It holds. The measurement just adds the condition: a check like this has to say what it could not see before it says anything.

Comments

[Tobias]

Glad I had a small part in it. I like that the check can come back "undetermined" instead of passing quietly. That's what makes a green result worth trusting.