从文件填充后，以编程方式测试 Postgres DB 的正确权限.sql

Question

我有一个项目，其中 postgres 数据库在 ci 测试服务器中用 .sql 文件播种。这将作为 docker 映像生成和发布。

现在，我不是唯一一个将这些.sql文件推送到服务器的人。所以我需要检查所有 postgres 用户(也许还有未来的用户(对所有数据库的权限，这是由这个文件引入的。

例：用户 A 和 B。用户 A 是超级用户，拥有所有数据库。用户 B 应该只能读取数据库 X 中的读取。等等

有没有办法以编程方式对此进行测试？除了"尝试从 Y -> 错误中读取为 B？

到目前为止，我发现的所有其他解决方案都是手动或"尝试/错误"的解决方案。

Answer 1

您可以通过访问权限查询功能检查各种类型的数据库对象的授权。这些都工作在单个对象的级别，因此对于任何类型的数据库范围的检查，都需要将它们与目录查询相结合。您还需要查阅目录以获取任何所有权信息。OID 类型在编写这些查询时非常有用。

要检查用户b是否对架构 p 中的所有表具有只读访问权限，请执行以下操作：

SELECT EVERY(
  has_table_privilege('b', oid, 'SELECT') AND
  NOT has_table_privilege('b', oid, 'INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER')
)
FROM pg_class
WHERE relkind = 'r'
  AND relnamespace = 'p'::regnamespace

(请注意，表权限被列级授权所取代，因此您可能还需要检查这些权限。

要检查a是否为超级用户，请执行以下操作：

SELECT rolsuper
FROM pg_roles
WHERE rolname = 'a'

要检查a是否拥有所有数据库(initdb创建的数据库除外(：

SELECT EVERY(datdba = 'a'::regrole)
FROM pg_database
WHERE datname NOT IN ('postgres', 'template0', 'template1')