PostgreSQL's `information_schema.table_constraints` lists only constraints on tables the current user owns or holds a privilege other than SELECT on. A login with only SELECT therefore saw no primary key on any table: `Table.primary_key` was empty, `parents(primary=True)` returned nothing so `key_source` raised, and `len()` built `count(DISTINCT )`. MySQL was unaffected.
The keys are now read from `pg_constraint` and `pg_attribute`, which every login can read, the same way `load_foreign_keys_sql` already did:
- `get_primary_key_sql` (the heading, `Table.primary_key`) and `load_primary_keys_sql` (the dependency graph) select `contype = 'p'`, ordered by position within the constraint -- the same column order `information_schema` reported.
- `get_foreign_keys_sql` had the same problem, worse: `constraint_column_usage` shows only tables the user owns. It also joined the two column lists on constraint name alone, so a multi-column foreign key came back as a cross product of its columns. It now returns one row per foreign-key column, pairing each with the column it references, as the MySQL adapter does. Nothing in the library calls it today; output column names are unchanged.
The new integration test creates a SELECT-only login on each backend and checks that primary keys, headings, parents and children, the dependency graph, `key_source`, and `get_foreign_keys_sql` all match what the owner sees. Against the previous code all five PostgreSQL cases fail; MySQL passed before and after.
Closes #1554.
PostgreSQL's information_schema.table_constraints lists only constraints on tables the current user owns or holds a privilege other than SELECT on. A login with only SELECT therefore saw no primary key on any table: Table.primary_key was empty, parents(primary=True) returned nothing so key_source raised, and len() built count(DISTINCT ). MySQL was unaffected.
The keys are now read from pg_constraint and pg_attribute, which every login can read, the same way load_foreign_keys_sql already did:
The new integration test creates a SELECT-only login on each backend and checks that primary keys, headings, parents and children, the dependency graph, key_source, and get_foreign_keys_sql all match what the owner sees. Against the previous code all five PostgreSQL cases fail; MySQL passed before and after.
Closes #1554.