我在 Azure 上有一个 PostgreSQL 数据库,我创建了一个 readonly_user 角色,允许外部各方使用连接字符串以只读方式连接到数据库。
此 readonly_user 已被授予 SELECT 权限,并且它还具有对公共模式的 USAGE 访问权限。
但是,我数据库中的表具有复合主键。当我以管理员用户身份登录时,我可以使用以下命令访问这些主键:
SELECT
tc.table_schema,
tc.table_name,
kcu.column_name
FROM
information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
WHERE
tc.table_name = 'player_data' AND
tc.constraint_type = 'PRIMARY KEY';
出于某种原因,无论我如何尝试使用 readonly_user 的使用和访问角色,此命令(以及其他类似命令)访问数据库表的主键信息总是返回 0 行。每个表应该至少有 3 个,当我以管理员角色连接发出此命令时,我可以清楚地看到这一点。
只读用户是否存在某些限制其访问 information_schema 的内容?如何以只读用户身份访问表的主键?
这是设计使然。SQL 标准定义(ISO/IEC 9075-11:2003, 5.58):
也就是说,仅对
SELECT
某个对象拥有权限的用户看不到 中的条目information_schema.table_constraints
。有几种可能的解决方案:
授予对所有表的
readonly_user
特权REFERENCES
。这是无害的,只要它CREATE
在任何模式和TEMP
数据库上都没有。查询系统目录表
pg_class
并pg_constraint
在pg_catalog
.创建一个运行您的查询并由特权用户拥有的
SECURITY DEFINER
函数。然后可以调用这个函数来获取信息。SET search_path = information_schema
readonly_user