postgresql - 计算所有表中所有模式中的行数(已授予权限)
问题描述
我正在尝试计算数据库中所有表中的行数。由于我想获得的答案必须区分不同的模式,因此我还考虑了特定表所在的模式。
这个答案最有帮助,但事实证明我没有访问数据库中所有模式的权限。
我知道我可以通过执行以下查询来检查我对特定表或模式的权限:
select count(*) from (
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='data' and privilege_type = 'SELECT') as foo
并检查输出是否等于或大于一。
我目前的代码如下:
CREATE or replace function rowcount_all(schema_name text default 'public')
RETURNS table(table_name_var text, cnt bigint) as
$$
declare
table_name_var text;
begin
for table_name_var in SELECT c.relname FROM pg_class c
JOIN pg_namespace s ON (c.relnamespace=s.oid)
WHERE c.relkind = 'r' AND s.nspname=schema_name
loop
if (
select count(*) from (
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name=table_name_var and privilege_type = 'SELECT') as foo
) >= 1 then
RETURN QUERY EXECUTE format('select cast(%L as text),count(*) from %I.%I',
table_name_var, schema_name, table_name_var);
end if;
END loop;
end
$$ language plpgsql;
在执行以下查询时
WITH rc(schema_name,tbl) AS (
select s.n,rowcount_all(s.n) from (values ('schema1'),
('schema2'), ('schema3'), ('schema4')) as s(n)
)
SELECT schema_name,(tbl).* FROM rc;
我收到一个错误,ERROR: permission denied for relation table1
即table1
我无权访问的架构中的位置。我认为我在 IF 语句中的逻辑不会过滤掉我无权访问的表。
解决方案
information_schema
,作为数据库元数据的 SQL 标准表示,如果您尝试构建可移植的东西,则很有用,但如果您只是尝试管理 Postgres 服务器,则通常有点笨拙。
检查权限的最简单方法是使用权限检查功能。我相信这只会返回您能够查询的表:
select oid::regclass::text
from pg_class
where relkind = 'r'
and relnamespace = schema_name::regnamespace
and has_schema_privilege(relnamespace, 'USAGE')
and has_any_column_privilege(oid, 'SELECT')
请注意,您的情况has_any_column_privilege()
是有用的罕见情况之一(与更明显的情况相比has_table_privilege()
),因为您不需要 a 的完整表权限select count(*)
,只需访问其中一列(但无关紧要)。
另请注意,oid::regclass::text
将返回一个已经被引用和模式限定的表名(如果需要),因此您的format()
调用可以使用简单%s
的而不是%I.%I
.
如果您可以使用近似且稍微过时的记录计数,则可以通过查询上次VACUUM
运行的统计信息完全绕过权限检查(并为自己节省大量表扫描):
select
oid::regclass::text,
reltuples
from pg_class
where relkind = 'r'
and relnamespace = schema_name::regnamespace
推荐阅读
- java - 如何使用cmd添加gson创建一个jar
- c# - C# 8 默认接口实现和继承
- flutter - 防止从外部触摸隐藏 showModalBottomSheet
- c# - 绑定到 UserControl 的属性不起作用
- asp.net-core-2.2 - 在 ASP.NET Core 2.2 中创建子类型实例
- python - OpenCV Python CalcHist:如何在 RGB 值范围内堆叠像素数?
- reactjs - 如何使用 Axios 将对象作为 QueryString 传递给 web api
- ruby-on-rails - 重新打开课程并添加方法不起作用
- image-processing - 检测图像中的一张纸,如 cam-scanner 应用程序
- python - 如何从文本文件中求解简单的数学方程