Ограничения списка для всех таблиц с разными владельцами в PostgreSQL

Должен ли я быть владельцем отношения к связанным с ограничением доступа данным в информационной схеме? Я тестировал следующее, и мне кажется, что я должен быть владельцем.

create schema rights_test;

create table rights_test.t1 (id int primary key);
create table rights_test.t2 (id int references rights_test.t1(id));

select  
        tc.constraint_name, 
        tc.constraint_schema || '.' || tc.table_name || '.' || kcu.column_name as physical_full_name,  
        tc.constraint_schema,
        tc.table_name, 
        kcu.column_name, 
        ccu.table_name as foreign_table_name, 
        ccu.column_name as foreign_column_name,
        tc.constraint_type
    from 
        information_schema.table_constraints as tc  
        join information_schema.key_column_usage as kcu on (tc.constraint_name = kcu.constraint_name and tc.table_name = kcu.table_name)
        join information_schema.constraint_column_usage as ccu on ccu.constraint_name = tc.constraint_name
    where 
        constraint_type in ('PRIMARY KEY','FOREIGN KEY')
        and tc.constraint_schema = 'rights_test'

/*
This will produce desired output:
t1_pkey;rights_test.t1.id;rights_test;t1;id;t1;id;PRIMARY KEY
t2_id_fkey;rights_test.t2.id;rights_test;t2;id;t1;id;FOREIGN KEY
*/

create user rights_test_role with password 'password';

grant all on rights_test.t1 to rights_test_role;
grant all on rights_test.t2 to rights_test_role;

/* Now login as rights_test_role and try the same constraint select.
   For rights_test_role it returns nothing although I've added ALL privileges
*/

Есть ли другой способ, как получить ту же информацию, если я не являюсь владельцем отношения?

Ответ 1

Не все связанные с ограничением данные являются "защищенными". В вашем запросе используются три отношения:

table_constraints
key_column_usage
constraint_column_usage

Первые два не ограничены, но документация для constraint_column_usage сообщает вам:

Ограничение представления_column_usage идентифицирует все столбцы в текущей базе данных, которые используются некоторым ограничением. Отображаются только те столбцы, которые содержатся в таблице, принадлежащей текущей роли.

Так как information_schema.constraint_column_usage - это представление, вы можете увидеть его определение, используя

\d+ information_schema.constraint_column_usage

в оболочке psql. Результат выглядит пугающим с первого взгляда, но это действительно не так уж плохо. Самое интересное - для первого теста - это часть в самой последней строке:

  WHERE pg_has_role(x.tblowner, 'USAGE'::text);

Если вы вставляете определение в оболочку psql, которое открыто невладельцем rights_test_role и удаляет эту последнюю строку, вы получите желаемый результат. Это хорошо, потому что это означает, что основные метаданные не защищены системой. Таким образом, вы можете разбить определение представления, чтобы включить только те части, которые вам действительно нужны.

Ответ 2

Попробуйте использовать это.. дает все имена ограничений и описание ограничений.

Внешний ключ
Проверить
Основной ключ
Уникальный

Вроде:

select conrelid::regclass AS table_from, conname, pg_get_constraintdef(c.oid)
from   pg_constraint c
join   pg_namespace n ON n.oid = c.connamespace
where  contype in ('f', 'p','c','u') order by contype