Postgres RLS in Symfony: the login role owned no table, read both tenants' invoices, and my setup check said nothing

The setup check from my last row-level security article answered NOTHING TO REPORT on a database where the application's login role, which owns no table, read 2 invoices out of 2 with no tenant set. The check compares the table owner with current_user and session_user. Postgres does not decide who bypasses a policy that way: a role that inherits from the owner gets the owner's exemption, without being the owner and without any SET ROLE.

I found it by benching the setup @to21as described in the thread under that article, so this is mostly about that setup: what it closes, and the one grant option it depends on.

The setup from the thread

Three Postgres roles. Everything below is about Postgres roles, nothing here touches Symfony's security roles.

• sl_owner owns the tables and runs the migrations.
• sl_login is what the application connects with: LOGIN NOSUPERUSER NOBYPASSRLS, owns nothing, has no grant on the tables.
• sl_rw is NOLOGIN and holds the SELECT, INSERT, UPDATE, DELETE grants.

Each transaction starts with SET LOCAL ROLE sl_rw and sets the tenant with set_config('app.tenant_id', '1', true). Two tables, organization and invoice, two tenants, one invoice each. RLS is enabled with a tenant policy and FORCE is deliberately off, because that is the configuration the check exists to catch.

PostgreSQL 18.3, a throwaway database, and for the last part Doctrine DBAL 4.4.3 over pdo_pgsql on PHP 8.5.7.

What the setup closes

The route I could not close last time was a connection that steps down with SET ROLE and can step back up with RESET ROLE. Here the step back up lands on sl_login, which has no grant:

BEGIN;
SET LOCAL ROLE sl_rw;
SELECT set_config('app.tenant_id','1',true);
SELECT current_user, session_user, count(*) FROM invoice;
   sl_rw | sl_login | 1
RESET ROLE;
SELECT current_user, count(*) FROM invoice;
   ERROR:  permission denied for table invoice
ROLLBACK;
SELECT current_user;
   sl_login

A bare query outside a transaction fails the same way, permission denied for table invoice. That is the property @to21as described: since the step-down is transaction-local, a query that skipped it sees the login role, and the login role can do nothing. It fails loudly instead of reading.

It depends on one detail of the grant. I first wrote GRANT sl_rw TO sl_login, which is what most examples show. On PostgreSQL 16 and later that grant carries INHERIT TRUE unless the member role was created NOINHERIT, and pg_auth_members confirms it:

 role granted | member   | inherit_option | set_option
 sl_rw        | sl_login | t              | t

With inheritance, sl_login uses the grants of sl_rw directly. The bare query no longer fails, it returns 0 rows with no error, because the policy still applies and no tenant is set. Nothing leaks, but the loud failure is gone, and an empty list is a much harder symptom to trace than an error. The grant that keeps the loud failure is:

GRANT sl_rw TO sl_login WITH INHERIT FALSE, SET TRUE;

The hole in my check

Then I gave sl_login a membership in the owner role, GRANT sl_owner TO sl_login, with the defaults, and ran the check from the last article next to a version that asks Postgres about membership instead of comparing names:

                                     equality (last article)   pg_has_role(session_user, owner, 'MEMBER')
sl_owner granted, INHERIT TRUE       NOTHING TO REPORT         WARNING
sl_owner granted, INHERIT FALSE      NOTHING TO REPORT         WARNING

And what the login role reads, no tenant set, in each case:

INHERIT TRUE,  bare query:        current_user = sl_login, session_user = sl_login, invoices read: 2
INHERIT FALSE, bare query:        ERROR:  permission denied for table invoice
INHERIT FALSE, SET ROLE sl_owner: current_user = sl_owner, session_user = sl_login, invoices read: 2

The first line is the one from the opening paragraph. current_user is the login role, it owns nothing, the check is satisfied, and the policy is bypassed because the role inherits the owner's privileges, and for row security that is enough. The third line needs one statement, and no credential the application does not already have.

The fix is in the condition, not in the structure of the check. The join by name and the three outcomes from last time stay as they were, and this:

pg_get_userbyid(c.relowner) IN (current_user, session_user)

becomes this:

pg_has_role(session_user, c.relowner, 'MEMBER')

The Postgres documentation defines MEMBER as direct or indirect membership "without regard to what specific privileges may be conferred", so it is true for the owner itself and for any member, inheriting or not. Asking about session_user rather than current_user matters for the setup from the thread: the check run inside the step-down would be asking about sl_rw.

Where Doctrine comes in

The step-down only exists inside a transaction, and in Doctrine most reads are not in one. A repository find() or a query builder runs in autocommit mode. I issued the step-down the way a listener would, outside any transaction:

isTransactionActive()                                      false
executeStatement('SET LOCAL ROLE sl_rw')                   0
SELECT current_user                                        sl_login
SELECT count(*) FROM invoice   (grant INHERIT FALSE)       DriverException: permission denied for table invoice
SELECT count(*) FROM invoice   (grant INHERIT TRUE)        0

No exception on the SET LOCAL ROLE, and no effect. The server does send something, which I checked with Pdo\Pgsql::setNoticeCallback() from PHP 8.4 on a separate connection:

WARNING:  SET LOCAL can only be used in transaction blocks

DBAL does not listen for it, so the warning goes nowhere. Wrapped in $connection->transactional(), the same two statements give current_user = sl_rw and 1 invoice read, and current_user is back to sl_login after the commit.

The tenant setting behaves the same way, with a different symptom. set_config(..., true) outside a transaction only lasts for its own statement, and once a custom setting has been set in a session, current_setting('app.tenant_id', true) returns an empty string afterwards rather than NULL. The next query fails in the policy's cast:

ERROR:  invalid input syntax for type integer: ""

If you see that error after adding RLS, the tenant was set outside the transaction that runs the query.

So with this setup in a Symfony app, the step-down has to run at the start of every transaction, and reads have to happen inside one. With INHERIT FALSE, forgetting that gives permission denied on the first query. With the default grant, it gives empty lists.

@to21as's question

The question at the end of the thread was whether a boot check that only logs would still count as detecting rather than imposing, for the kit I sell. ShipAnvil is a multi-tenant Symfony kit that scopes tenants with a Doctrine filter, and it ships no RLS and no such check today, so this is what I would accept if it did. Logging is detecting. Refusing to start is right in your application, where RLS is the isolation. In a kit where RLS is an option on top of a Doctrine filter, refusing to start would impose it. The part of your setup I would keep as is: running the check on the pool that takes the traffic. It is what closes the different-DATABASE_URL problem from last time, and this bench did not change that.

What this does not cover

Nothing was measured behind PgBouncer or any other pooler; it is not installed on the machine I benched on. Nothing was measured on a managed Postgres service, where the administrative role is not a real superuser and memberships are set up by the provider. The check as written does not look at rolbypassrls or rolsuper on the login role, and I did not bench that case: a role with either attribute bypasses every policy whatever it owns. Everything ran on PostgreSQL 18.3; the WITH INHERIT option on GRANT requires 16 or later.

Reproduce it

One bash script and one PHP file. The script creates the database and the three Postgres roles, runs the check and the reads for each grant configuration, then runs the PHP file against Doctrine DBAL, and drops everything on exit. Its labels are in French: the outputs above are translated, the values are not. Every query that matters is in this article.

Thanks to @to21as, whose setup closes the route I left open, and whose question made me rerun my own check against it. That is how I learned that my check compared names where Postgres compares memberships.