postgresql - 如何在 Postgres 中以只读用户访问 information_schema 外键约束？

Question

简介

我一直在开发一个向导来为没有任何编程/SQL 背景的用户创建复杂的数据库 Postgres 查询。由于外键约束存储在 information_schema 的 View 中，用户可以选择任意数量的表，该工具将找到正确的连接设置(因此，用户不必添加 ON table_a.field_1 = table_b.field_2 )。

在开发过程中，我一直在使用管理数据库用户，现在想将其更改为只读用户以使其更加安全。但是，这个只读用户似乎不能访问外键约束。

现状

选择了多个表时，该工具会尝试获得各个表之间的连接，以了解如何加入它们。在此过程中，将执行以下查询:

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' 
  AND ccu.table_name = 'TableB' 
  AND tc.table_name IN ('TableA');

(注意:最后一个WHERE子句使用了IN，因为可以有多个可用的基表。TableA是基表，每个连接成功/连接表将可用于其他连接，例如第三个表可以使用 AND ccu.table_name = 'TableC' AND tc.table_name IN ('TableA', 'TableB'); 等等。)

当使用 admin db 用户(具有最常见的权限，如 GRANT、SELECT、INSERT、UPDATE、DELETE、TRUNCATE 等)执行查询时，结果如下所示:

constraint_name | table_name | column_name | foreign_table_name | foreign_column_name
----------------+------------+-------------+--------------------+---------------------
constraint1     | TableA     | field_1     | TableB             | field_2
(1 row) 

但是当只读数据库用户运行该查询时，它返回:

constraint_name | table_name | column_name | foreign_table_name | foreign_column_name
----------------+------------+-------------+--------------------+---------------------
(0 rows)

由于存在但未返回的外键约束条目，连接无法正确编写为 SQL，并且用户生成的查询(通过使用向导)失败。

我尝试过的

当然，首先，我认为只读用户 (ro_user) 可能没有权限访问数据库 information_schema 中的表和 View 。所以我跑了

GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO ro_user;

作为管理员，但无济于事。更深入地研究文档，我发现 information_schema 中的所有表和 View 在 postgres 中默认情况下对任何用户都是可用和可访问的。所以授予选择权限甚至不应该改变任何东西。

为了确定，我也跑了

GRANT REFERENCES ON ALL TABLES IN SCHEMA actual_database TO ro_user;

当然，这也没有改变任何东西，因为 REFERENCES 只需要创建 新外键，我只需要阅读它们。

接下来，我想，可能是由于某些信息不可用，工具中的 sql 失败了，所以我通过运行分别查询了三个 View :

SELECT * FROM information_schema.table_constraints AS tc WHERE constraint_type = 'FOREIGN KEY';
SELECT * FROM information_schema.key_column_usage AS kcu;
SELECT * FROM information_schema.constraint_column_usage AS ccu;

果然，最后一个不会为 ro_user 返回任何一行:

psql=> SELECT * FROM information_schema.constraint_column_usage AS ccu;
 table_catalog | table_schema | table_name | column_name | constraint_catalog | constraint_schema | constraint_name
---------------+--------------+------------+-------------+--------------------+-------------------+-----------------
(0 rows)

而管理员用户得到了很多结果。因此，归结为一个 View information_schema.constraint_column_usage。

Answer 1

当我在一个小时的时间里输入这个问题，记忆和总结我在过去几天尝试过的所有想法时，我终于找到了原因。

The view constraint_column_usage identifies all columns in the current database that are used by some constraint. Only those columns are shown that are contained in a table owned by a currently enabled role.

来自 documentation通过这个SO answer

通过它我找到了解决方案

SELECT 
  conrelid::regclass AS table_from,
  conname,
  pg_get_constraintdef(c.oid) AS cdef 
FROM pg_constraint c 
JOIN pg_namespace n 
  ON n.oid = c.connamespace 
WHERE contype IN ('f') 
AND n.nspname = 'public' 
AND pg_get_constraintdef(c.oid) LIKE '%"TableB"%' 
AND conrelid::regclass::text IN ('"TableA"') 
ORDER BY conrelid::regclass::text, contype DESC;

它不会输出与旧查询相同的格式，但它包含相同的信息并且 - 最重要的是 - 可供 ro_user 使用。