ORCE ROW LEVEL SECURITY makes ADD/VALIDATE FOREIGN KEY skip rows hidden from the owner (RI_Initial_Check)

Hi,

Thanks for the great work!

I think I found a bug in foreign key validation under FORCE ROW LEVEL
SECURITY. An AI assistant (Opus 5,5, by Anthropic) found it while working on
our multi-tenant schema. A second, independent AI agent reproduced it with
the generic script below, which uses no application code or data. I have
reviewed the report and rerun the script on 18.6 and 19rc1 myself. The rest
of this mail was written by the assistant.

Ludwig

---------------------------------------------------------------------------
The following was written by Opus 5.5:

PostgreSQL version: reproduced on 18.6 and 19rc1
18.6: Ubuntu 24.04.5 LTS, x86_64 (18.6-1.pgdg24.04+2)
Debian 13, aarch64 (18.6-1.pgdg13+2)
19rc1: Debian 13, aarch64 (19~rc1-1.pgdg13+1)

Summary

When a table owner adds a foreign key, or validates one created NOT VALID,
and the referencing or referenced table has ENABLE + FORCE ROW LEVEL
SECURITY, the initial validation query (RI_Initial_Check) sees only the rows
the owner's own policies expose:

- an orphan referencing row hidden by a policy is not checked, and the
constraint is marked convalidated = t although an existing row violates it;
- a referenced row hidden by a policy makes a valid referencing row fail with
"Key (...) is not present in table".

The role is neither superuser nor BYPASSRLS. Without FORCE, or as a superuser
or BYPASSRLS role, validation is correct. The RI triggers on later inserts
are correct, and so is the per-row fallback path (forced, for example, by the
owner revoking its own SELECT on the referencing table). SET ROLE, SET
SESSION AUTHORIZATION and a direct login behave the same.

This contradicts the documentation:

- 5.9 Row Security Policies: "Referential integrity checks, such as unique or
primary key constraints and foreign key references, always bypass row
security to ensure that data integrity is maintained."
- CREATE POLICY: policies "are not applied when the system is performing
internal referential integrity checks or validating constraints".

Repro (run as a superuser in a scratch database)

CREATE ROLE repro_owner NOSUPERUSER NOBYPASSRLS;
GRANT CREATE ON SCHEMA public TO repro_owner;
SET ROLE repro_owner;

CREATE TABLE parent (id int PRIMARY KEY, visible boolean NOT NULL);
CREATE TABLE child (id int PRIMARY KEY, parent_id int, visible boolean NOT NULL);
INSERT INTO parent VALUES (1, true), (2, false);
INSERT INTO child VALUES (10, 1, true), (30, 99, false); -- 30 is an orphan

ALTER TABLE child ENABLE ROW LEVEL SECURITY;
ALTER TABLE child FORCE ROW LEVEL SECURITY;
CREATE POLICY child_sel ON child FOR SELECT USING (visible);
CREATE POLICY child_ins ON child FOR INSERT WITH CHECK (true);

ALTER TABLE child ADD CONSTRAINT child_parent_fk
FOREIGN KEY (parent_id) REFERENCES parent (id);
-- expected: ERROR: ... violates foreign key constraint "child_parent_fk"
-- actual: ALTER TABLE

SELECT convalidated FROM pg_constraint WHERE conname = 'child_parent_fk';
-- actual: t

RESET ROLE;
SELECT c.* FROM child c
WHERE NOT EXISTS (SELECT 1 FROM parent p WHERE p.id = c.parent_id);
-- actual: 30 | 99 | f

The same happens with ADD ... NOT VALID followed by VALIDATE CONSTRAINT.

Reverse case: with FORCE and a policy on parent hiding parent 2, a valid
child row (20, 2, true) fails with

ERROR: insert or update on table "child" violates foreign key constraint "child_parent_fk"
DETAIL: Key (parent_id)=(2) is not present in table "parent".

With row_security = off, the owner instead gets

ERROR: query would be affected by row-level security policy for table "parent"

with the RI_Initial_Check query in the error context, which shows that the
check query is subject to the policy.

Probable cause (from reading ri_triggers.c on 18.x and master; not confirmed)

RI_Initial_Check falls back to the per-row check only when RLS is enabled
and the current user is neither the owner nor BYPASSRLS; it does not look at
relforcerowsecurity. Its query then runs through SPI_execute_snapshot as the
current user without SECURITY_NOFORCE_RLS, whereas ri_PerformCheck sets
SECURITY_LOCAL_USERID_CHANGE | SECURITY_NOFORCE_RLS.

In 2015, while FORCE ROW LEVEL SECURITY was being added, Noah Misch asked why
RI_Initial_Check hard-codes the RLS rules instead of calling
check_enable_rls(), and Stephen Frost replied that he saw no reason for it
now (message-id 20150921133015(dot)GK3685(at)tamriel(dot)snowman(dot)net, thread
"Re: row_security GUC, BYPASSRLS").

Two possible fixes: fall back to the per-row path whenever check_enable_rls()
returns RLS_ENABLED, or run the initial check query with
SECURITY_NOFORCE_RLS.

Not tested: partitioned tables, multi-column keys, MATCH FULL, roles that
are members of the owner role. Temporal (PERIOD) foreign keys skip
RI_Initial_Check and should be unaffected.

Prior reports: none found by searching the pgsql-bugs and pgsql-hackers
archives for RI_Initial_Check and FORCE ROW LEVEL SECURITY; only the 2015
exchange above touches this code.