Contraintes de liste pour toutes les tables avec différents propriétaires dans PostgreSQL
dois-je être le propriétaire de la relation aux données liées aux contraintes d'accès dans le schéma d'information? J'ai testé ce qui suit et il semble que je doive ê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-il d'autres moyens d'obtenir les mêmes informations si je ne suis pas propriétaire de la relation?
3 réponses
toutes les données liées aux contraintes ne sont pas "protégées". Vous utilisez trois relations dans votre requête:
table_constraintskey_column_usageconstraint_column_usage
Les deux premiers ne sont pas limités, mais la documentation de constraint_column_usage vous dit:
la vue constraint_column_usage identifie toutes les colonnes de la base de données courante qui sont utilisées par une contrainte. seules les colonnes qui sont contenue dans une table détenue par un rôle actuellement activé.
Depuis information_schema.constraint_column_usage est une vue, vous pouvez voir sa définition en utilisant
\d+ information_schema.constraint_column_usage
dans le shell psql. 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 shell psql qui est ouvert par le non-propriétaire rights_test_role et supprimez cette dernière ligne vous obtiendrez le résultat souhaité. C'est une bonne chose, car cela signifie que les métadonnées de base ne sont pas protégées par le système. Vous pouvez donc démonter la définition de la vue pour n'inclure que les pièces dont vous avez vraiment besoin.
essayez d'utiliser ceci.. donne tous les noms de contraintes et leur description.
- clé Étrangère
- Case
- touche 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
Pour 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'