Declared FKs are invisible to a SELECT-only role on Postgres: introspection falls back to name matching and infers wrong joins

[sandeep-agami]

Summary

The Postgres dialect discovers foreign keys through information_schema.table_constraints / key_column_usage / constraint_column_usage. Postgres restricts those views to the table owner (or a role holding a privilege other than SELECT). A least-privilege SELECT-only role therefore sees zero declared foreign keys, introspection silently falls back to name+type matching, and the inferred joins can point at the wrong parent table.

This matters now because ACE-036 (merged in 41cfd7d) makes the SELECT-only role the required DATASOURCE_URL posture for a deploy. The documented-correct deployment is exactly the one that loses every FK.

Severity: sev-2. No data loss or exposure, and the degraded joins land as confidence: proposed / review_state: unreviewed, so the review queue is a human gate in front of them. But they are usable while unreviewed (Rule 2), they are wrong rather than absent, and the resulting numbers reconcile at the top line while the breakdown is badly off — the failure mode a trust layer exists to prevent.

Affects PostgreSQL and Redshift (which subclasses it, dialects.py:246). Other information_schema-based dialects (MySQL, SQL Server, BigQuery, Oracle) were not tested and should be checked — MySQL's key_column_usage has different, more permissive visibility rules.

Root cause

packages/agami-core/src/semantic_model/dialects.py:194-209 builds the FK query from information_schema:

SELECT kcu.table_name AS from_table, kcu.column_name AS from_column, ...
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ...
JOIN information_schema.constraint_column_usage ccu ...
WHERE tc.constraint_type = 'FOREIGN KEY' ...

Per the Postgres docs, table_constraints shows only constraints on tables the current user owns or has a privilege other than SELECT on, and constraint_column_usage is owner-only. introspect.py:678 gates the declared-FK pass on catalog_ok and catalog_fks, so an empty result is indistinguishable from "this engine has no FKs" and the name-matching path takes over (introspect.py:657-711).

pg_catalog.pg_constraint carries no such restriction and is readable by any role.

Repro

Uses the shipped synthetic sample (plugins/agami/samples/store) — no customer data.

1. Load the sample into Postgres as an owner role (the SQLite DDL is ANSI-shaped; REFERENCES constraints survive verbatim). 12 FKs result.
2. Create the read-only role using the shipped recipe, plugins/agami/shared/readonly-grants.md:

   CREATE USER agami_ro WITH PASSWORD 'ro_pw';
   GRANT CONNECT ON DATABASE agami_ace_test TO agami_ro;
   GRANT USAGE ON SCHEMA public TO agami_ro;
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO agami_ro;
   ALTER DEFAULT PRIVILEGES FOR ROLE store_owner IN SCHEMA public GRANT SELECT ON TABLES TO agami_ro;

3. DATASOURCE_URL=postgresql://agami_ro:ro_pw@localhost:5432/agami_ace_test python -m semantic_model.cli introspect --profile store --db-type postgres --artifacts ./artifacts
4. Repeat with DATASOURCE_URL pointed at the owner role.

Expected vs actual

• Expected: the same 12 confirmed FK-derived relationships under either role — a SELECT-only login should not change what the model believes about the schema.
• Actual: as owner, 12 relationships, all confirmed, all correct. As agami_ro, 9 relationships, all proposed, two wrong and three missing:

	as agami_ro	as owner
relationships built	9 (proposed)	12 (confirmed)
payments.order_id →	order_items ✗	orders ✓
invoices.subscription_id →	subscription_events ✗	subscriptions ✓
products → categories	missing	present
subscription_events → plans (from_plan_id, to_plan_id)	missing	present

The value-overlap probe cannot discriminate here: order_items.id spans 1..10000 and payments.order_id spans 1..4000, so the wrong parent overlaps perfectly.

Evidence

Same database, same 12 constraints in pg_constraint:

catalog query	as agami_ro	as owner
information_schema.table_constraints where constraint_type='FOREIGN KEY'	0	12
information_schema.constraint_column_usage	0	—
pg_catalog.pg_constraint where contype='f'	12	12

Impact of the payments → order_items mis-join on a routine question ("payment revenue by order status"). The total is identical either way — 5,950,666 across 3,800 payments — so nothing looks wrong until you break it down:

status	correct	via the inferred join	error
pending	80,794	317,829	+293%
refunded	1,049,264	607,646	−42%
paid	685,799	577,059	−16%
shipped	1,087,295	1,190,897	+10%
delivered	3,047,516	2,955,422	−3%
cancelled	—	301,814	bucket that shouldn't exist

Fix

Read declared FKs from pg_catalog.pg_constraint (joining pg_attribute for the column names), which every role can see, instead of the owner-restricted information_schema views. Worth also making an empty FK result distinguishable from "engine has no FK support" at introspect.py:678, so a privilege-shaped zero can be reported rather than silently downgrading to name matching.

• Regression test / golden case added so it can't silently return

Related: ACE-036 (41cfd7d) — the spec that makes this posture required.