RLS creates inaccurate limit and offset results - Mailing list pgsql-bugs

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