目前我有一个简单的模式,一个sites
表和一个items
表。
Sites
拥有许多items
,items
有一个siteId
列来存储拥有它的站点的引用。
我希望能够在查询时强制执行一个条件items
,这意味着如果在实践中没有使用 - 查询它,则返回错误siteId
:
select * from items
会失败select * from items where siteId = 1
会成功,尽管存在 ID 为 1 的站点记录
这可以在应用程序级别轻松实现。
我最近开始进一步研究行级策略、角色、功能等……我发现它们非常有趣,因为它们支持较低级别的设置。也就是说,我对此还很陌生,缺乏理解/知识。我想知道上面描述的用例是否可以简单地在 SQL 中解决(通过策略、函数等的组合……或任何其他 Postgres 内置工具)?
一种可能性(显然是您凭直觉感觉到的一种可能性)是将您的查询集中到一个函数中,否则禁用查询。
假设这些表归一个名为 的角色所有
owner
,并且有一个可以对这些表reader
运行SELECT
查询的角色。然后有一个application
可以连接到数据库并能够调用函数的角色。所有这一切都可以通过以下方式实现:此时,任何可以承担该
reader
角色(通过成为成员和/或使用SET ROLE
)的人都可以SELECT
对表运行任何操作。请注意,application
此处没有任何权限。然后为您的函数创建另一个模式:
该
SECURITY DEFINER
子句将使函数继承超级用户从表中读取的权限(无需授予)。现在,如果您以 身份登录application
,则可以调用该函数:但是,您不能直接从表中读取:
注意:为了
GRANT
在您创建表或函数时一直保留 s,您可以设置默认权限。请参阅我之前关于如何执行此操作的回答的底部部分。