Liste des contraintes pour tous les tableaux avec les différents propriétaires de PostgreSQL

Dois-je être propriétaire de ce qui concerne l'accès contrainte en matière de données dans les informations de schéma? J'ai testé la suivante, et il semble que je dois être le propriétaire.

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

Est là une autre manière d'obtenir la même information, si je ne suis pas propriétaire de la relation?

OriginalL'auteur Tomas Greif | 2013-05-30

  1. 9

    Pas toutes les contraintes liées de données est "protégée". Vous utilisez trois relations dans votre requête:

    • table_constraints
    • key_column_usage
    • constraint_column_usage

    Les deux premiers ne sont pas limités, mais la documentation pour constraint_column_usage vous dit:

    La vue constraint_column_usage identifie toutes les colonnes dans la base de données qui sont utilisés par certaines contraintes. Uniquement les colonnes sont affichées qui sont contenus dans un tableau détenue par un rôle actuellement activé.

    Depuis information_schema.constraint_column_usage est une vue, vous pouvez voir sa définition à l'aide de 

    \d+ information_schema.constraint_column_usage

    dans le psql shell. Le résultat est effrayant à première vue mais c'est vraiment pas si mal. La chose la plus intéressante - pour un premier test - est de la partie dans la dernière ligne: 

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

    Si vous collez la définition dans le psql shell qui est ouverte par le non-propriétaire rights_test_role et de supprimer la dernière ligne, vous obtiendrez le résultat souhaité. C'est bon, parce que cela signifie que la base de métadonnées n'est pas protégé par le système. De sorte que vous pouvez démonter la définition de la vue pour inclure uniquement les pièces que vous avez vraiment besoin.

    \d+ est votre ami.

    OriginalL'auteur A.H.

  2. 18

    Essayez d'utiliser cette.. donne tous les noms des contraintes et de la contrainte, description.

    • Clé étrangère
    • Vérifier
    • Clé primaire
    • Unique

    Comme: 

    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

    OriginalL'auteur Anupama Boorlagadda

  3. 0

    À la liste des contraintes relationnelles vous pouvez utiliser la requête suivante:

    SELECT
    tc.constraint_name, tc.table_name, kcu.column_name, 
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
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'

    OriginalL'auteur Eugen Konkov