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 without the 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 should be 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