列出PostgreSQL中具有不同所有者的所有表的约束 [英] List constraints for all tables with different owners in PostgreSQL

问题描述

我是否必须是信息架构中与访问限制相关的数据的关系的所有者？我已经测试了以下内容，而且看来我必须是所有者。

Do I have to be owner of relation to access constraint related data in information schema? I've tested the following and it seems that I have to be the owner.

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
*/

如果我不是关系的所有者，还有其他方法如何获取相同的信息吗？

Is there other way how to get the same information if I am not owner of the relation?

推荐答案

不是所有与约束相关的数据是受保护的。您在查询中使用三个关系：

Not all constraint-related data is "protected". You use three relations in your query:

• table_constraints


• key_column_usage


• constraint_column_usage

• table_constraints
• key_column_usage
• constraint_column_usage

前两个没有限制，但是 constraint_column_usage 告诉您：

The first two are not limited, but the documentation for constraint_column_usage tells you:

视图constraint_column_usage标识当前数据库中由某些约束使用的所有列。 仅显示当前启用角色拥有的表中包含的那些列。

自 information_schema.constraint_column_usage 是一个视图，您可以使用

Since information_schema.constraint_column_usage is a view, you can see its definition using

\d+ information_schema.constraint_column_usage

在psql shell中。乍一看，结果似乎令人恐惧，但实际上还不错。对于第一个测试，最有趣的是最后一行的内容：

in the psql shell. The result looks frightening at a first glance but it's really not so bad. The most interesting thing - for a first test - is the part in the very last line:

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

如果将定义粘贴到由非所有者打开的psql shell中，则 rights_test_role 并删除最后一行，您将获得所需的结果。这很好，因为这意味着基本元数据不受系统保护。因此，您可以精简视图定义以仅包括您真正需要的部分。

If you paste the definition into the psql shell which is open by the non-owner rights_test_role and delete that last line you will get the desired result. This is good, because that means that the basic metadata is not protected by the system. So you can strip down the view definition to include only the parts you really need.

这篇关于列出PostgreSQL中具有不同所有者的所有表的约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！