Обсуждение: RLS creates inaccurate limit and offset results

Hello,

I have noticed that using row level security can cause use of `LIMIT` and `OFFSET` to return inconsistent results
withoutthe use of an explicit `ORDER BY`. 

Version info:
testdb=> select version();
                                                         version

--------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.6 (Debian 17.6-2.pgdg13+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0,
64-bit
(1 row)

For the example below, I’ll pretend we have a role “user” that is authenticated and has the GRANT for SELECT on the
table.

Sample data:

CREATE TABLE IF NOT EXISTS "organization"
(
"id" UUID DEFAULT gen_random_uuid(),
"name" TEXT NOT NULL,
"created_at" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY ("id")
);

COPY public.organization (id, name, created_at) FROM stdin;
db8d12e7-faac-4b6a-a4f1-127c1da8b297    Test    2025-11-11 15:57:29.323547+00
11111111-1111-1111-1111-111111111111    Acme Corporation    2025-11-11 15:57:29.324394+00
22222222-2222-2222-2222-222222222222    Beta Industries    2025-11-11 15:57:29.325026+00
33333333-3333-3333-3333-333333333333    Gamma Labs    2025-11-11 15:57:29.325643+00
\.

If I have a RLS policy like this:
CREATE POLICY organization_isolation_policy
    ON public.organization
    FOR SELECT USING (
    (id = ANY
     (
         ARRAY [
             '11111111-1111-1111-1111-111111111111'::uuid,
             '22222222-2222-2222-2222-222222222222'::uuid,
             '33333333-3333-3333-3333-333333333333'::uuid,
             'db8d12e7-faac-4b6a-a4f1-127c1da8b297'::uuid]
         )
        )
    );

Then using a `LIMIT 1 OFFSET 0` and `LIMIT 1 OFFSET 1` there is no difference in the data returned, though the latter
shouldbe shifted by 1 row in the result set: 
testdb=> SELECT * FROM organization LIMIT 1;
                  id                  |       name       |          created_at
--------------------------------------+------------------+-------------------------------
 11111111-1111-1111-1111-111111111111 | Acme Corporation | 2025-11-11 15:57:29.324394+00
(1 row)

testdb=> SELECT * FROM organization LIMIT 1 OFFSET 0;
                  id                  |       name       |          created_at
--------------------------------------+------------------+-------------------------------
 11111111-1111-1111-1111-111111111111 | Acme Corporation | 2025-11-11 15:57:29.324394+00
(1 row)

testdb=> SELECT * FROM organization LIMIT 1 OFFSET 1;
                  id                  |       name       |          created_at
--------------------------------------+------------------+-------------------------------
 11111111-1111-1111-1111-111111111111 | Acme Corporation | 2025-11-11 15:57:29.324394+00
(1 row)

Using `LIMIT 2 OFFSET 0` as the RLS user:
testdb=> SELECT * FROM organization LIMIT 2 OFFSET 0;
                  id                  |       name       |          created_at
--------------------------------------+------------------+-------------------------------
 db8d12e7-faac-4b6a-a4f1-127c1da8b297 | Test             | 2025-11-11 15:57:29.323547+00
 11111111-1111-1111-1111-111111111111 | Acme Corporation | 2025-11-11 15:57:29.324394+00
(2 rows)

If I remove the “Test” org ID from the RLS policy, then things seem to be correct (this is a very small dataset,
obviously).If I include any `ORDER BY` clause, then the results seem to be correct regardless of RLS policy. 


Without RLS (e.g. superuser):
testdb=# SELECT * FROM organization LIMIT 1 OFFSET 0;
                  id                  |       name       |          created_at
--------------------------------------+------------------+-------------------------------
 db8d12e7-faac-4b6a-a4f1-127c1da8b297 | Test             | 2025-11-11 15:57:29.323547+00
(1 row)

testdb=# SELECT * FROM organization LIMIT 2 OFFSET 0;
                  id                  |       name       |          created_at
--------------------------------------+------------------+-------------------------------
 db8d12e7-faac-4b6a-a4f1-127c1da8b297 | Test             | 2025-11-11 15:57:29.323547+00
 11111111-1111-1111-1111-111111111111 | Acme Corporation | 2025-11-11 15:57:29.324394+00
(2 rows)


—
Mike Brancato

> On Nov 11, 2025, at 08:29, mike@mikebrancato.com wrote:
> I have noticed that using row level security can cause use of `LIMIT` and `OFFSET` to return inconsistent results
withoutthe use of an explicit `ORDER BY`. 

LIMIT and OFFSET without ORDER BY are not guaranteed to return consistent results even without RLS.  RLS may cause it
bemore obvious, however. 

> On Nov 11, 2025, at 14:46, mike@mikebrancato.com wrote:
> If I understand the documentation (https://www.postgresql.org/docs/current/queries-limit.html), inconsistent results
fordifferent LIMIT / OFFSET values is only a known issue when selecting different subsets of data. 

The documentation could perhaps be better worded, but PostgreSQL makes no guarantee of the order of a result set in
absenceof an ORDER BY clause over that particular result set.  Since the ordering can't be guaranteed, which particular
subsetyou get from LIMIT and OFFSET is not guaranteed either. 

Again, this has nothing to do with RLS in particular.  The more complex the query and the more steps a result set has
togo through from disk ordering to result set ordering, the more likely it is to see inconsistent results, but it's
neverguaranteed in any case. 

mike@mikebrancato.com writes:
> If I understand the documentation (https://www.postgresql.org/docs/current/queries-limit.html), inconsistent results
fordifferent LIMIT / OFFSET values is only a known issue when selecting different subsets of data. 

You are willfully ignoring the lede on that page:

    When using LIMIT, it is important to use an ORDER BY clause that
    constrains the result rows into a unique order. Otherwise you will
    get an unpredictable subset of the query's rows.

This has nothing particularly to do with RLS; RLS is merely one way in
which a query might gain complications sufficient to cause the planner
to change plans.  Without ORDER BY, the planner is free to switch to
a plan that delivers rows in some other order.  Even just modifying
OFFSET can cause that to happen (since it's effectively a change in
the number of rows required to be fetched).

I suspect if you check with EXPLAIN, you'll find that you get
different plans with the different OFFSET values.  But whatever the
details, your code was making unjustified assumptions.

            regards, tom lane