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