The following bug has been logged on the website:
Bug reference: 17511
Logged by: Kirk Parker
Email address: khp@equatoria.us
PostgreSQL version: 13.7
Operating system: AWS Linux 2 -- 4.14.276-211.499.amzn2.x86_64
Description:
In attempting to write a schema-documenting template, I ran across what
might be a minor bug. Given this list of roles in the relevant database:
List of roles
Role name | Attributes |
Member of
-----------+------------------------------------------------------------+-----------
apache | |
{}
p3dev | Superuser |
{}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS |
{}
The following query produces the expected output (list of foreign tables
referenced by foreign-key-columns) for a role with the Superuser attribute,
but shows zero results for an non-Superuser role:
select kcu.column_name as my_column, ccu.table_name as foreign_table,
ccu.column_name as foreign_column
from information_schema.table_constraints AS tc
join information_schema.key_column_usage AS kcu ON tc.constraint_name =
kcu.constraint_name
join information_schema.constraint_column_usage AS ccu ON
ccu.constraint_name = tc.constraint_name
where constraint_type = 'FOREIGN KEY' AND tc.table_name='some_table';
The table at issue is constraint_column_usage--the ordinary role 'apache'
does not have SELECT rights to that table, though it does to the other two
catalog tables used by this query.
Yes, there's an easy workaround by just GRANTing SELECT on that table to
'apache', but it seems like an odd inconsistency. Interestingly, the same
limitation does not apply to pg_catalog.pg_get_constraintdef(), which is
used by psql's \dt command, but that query does not produce the local column
name as a separate result column (which is more useful for my immediate
purpose here.)