时间戳

首页 > 解决方案 > 为什么 PostgreSQL 权限在触发器和检查约束之间表现不同？

postgresql - 为什么 PostgreSQL 权限在触发器和检查约束之间表现不同？

问题描述

我目前有一个具有两个模式app_private和app_public的数据库（除了默认的公共模式）。我还有一个角色，它已被授予app_public架构的使用权限，但不是app_private架构。我还在表上使用了两个函数（一个触发函数和一个检查约束函数）。

请参阅下面的代码：

(1) 创建模式（和赠款）

CREATE SCHEMA app_public;
CREATE SCHEMA app_private;

grant usage on schema public, app_public to "grant_test_role";

(2) 撤销 PUBLIC 用户的授权

然后我有这个特殊的 DDL 语句。它应该从公共用户角色（所有其他角色继承自）中撤销任何新添加的功能的权限。

alter default privileges revoke all on functions from public;

(3) 函数定义（触发器和约束）

-- Trigger Function
create OR replace function app_private.tg__timestamps() returns trigger as $$
begin
  NEW.created_at = (case when TG_OP = 'INSERT' then NOW() else OLD.created_at end);
  NEW.updated_at = (case when TG_OP = 'UPDATE' and OLD.updated_at >= NOW() then OLD.updated_at + interval '1 millisecond' else NOW() end);
  return NEW;
end;
$$ language plpgsql volatile set search_path to pg_catalog, app_private, public, pg_temp;

-- Constraint Function
CREATE OR REPLACE FUNCTION app_private.constraint_max_length(
  value text,
  maxLength integer,
  error_message text default 'The value "$1" is too long. It must be maximum $2 characters long.',
  error_code text default 'MXLEN'
) RETURNS boolean
  AS $$
begin
  if length(value) > maxLength then
    error_text = replace(replace(error_message, '$1', value), '$2', maxLength);
    raise exception '%', error_text using errcode = error_code;
  end if;
  return true;
end;
$$ LANGUAGE plpgsql set search_path to pg_catalog, app_private, public, pg_temp;

(4) 表定义（使用上面的Trigger & Constraint函数）

create table app_public.test_tab (
   id INT not null primary key,
   name text not null,
   created_at timestamptz not null default now(),
   updated_at timestamptz not null default now(),

   constraint name_length_check check (app_private.constraint_max_length(name, 5));
);

create trigger _100_timestamps
before insert or update on app_public.test_tab
for each row
execute procedure app_private.tg__timestamps();

-- Setting some restrictions on the test_tab for the "grant_test_role"
REVOKE ALL ON TABLE app_public.test_tab FROM "grant_test_role";
GRANT SELECT, DELETE ON app_public.test_tab TO "grant_test_role";
GRANT
INSERT(id, name),
UPDATE(id, name) ON app_public.test_tab TO "grant_test_role";

(5) 代码（作为grant_test_role运行）

begin;
  set local role to grant_test_role;
  insert into app_public.test_tab (id, name) values (1, 'Very Long Name');
commit;

我每次都尝试在一个新的数据库中执行此操作，以了解 PostgreSQL 权限如何在不同的调用上下文中工作（即触发函数、自动调用函数的约束检查等）

当我没有从 PUBLIC 用户撤消函数权限的代码块 (2) 时，代码块 (5) 执行时不会出现任何错误。尽管用户角色没有授予触发函数和约束函数存在的app_private模式的事件。但是在存在代码块 (2) 的情况下，代码可以很好地执行触发器，但给了我一个"permission denied for function constraint_max_length"检查约束。

所以我试图理解，

  1. 存在于用户角色没有使用授权的模式中的触发器函数如何始终成功执行？
  2. 如果触发函数执行，为什么CHECK约束函数给我上面的权限被拒绝错误？
  3. 代码块 (2) 的真正作用是什么？

我正在努力寻找有关权限如何应用于这种“自动执行”场景（触发器/约束）的文档，因为用户不是“显式”调用这些函数，而是由数据库自动调用它们。所以我不确定哪个角色正在执行它们。

标签： postgresqltriggerspermissionsconstraintsroles

解决方案

我将这个问题发布到 PostgreSQL 邮件列表，终于得到了答案。

所以到目前为止，这就是 PostgreSQL 的工作方式（不管它是否符合 SQL 规范：）

原始邮件线程 - https://www.postgresql.org/message-id/CANYEAx8vZnN9eeFQfsiLGMi9NdCP0wUdriHTCGU-7jP0VmNKPA%40mail.gmail.com

触发函数

  • 触发函数权限在“创建”时根据创建它们的角色进行检查。
  • 在运行时，触发器函数的权限根本不检查执行角色，而只是分析能力。
  • 触发函数内部的语句将针对执行角色进行常规权限检查

检查约束函数

  • 检查约束功能权限在“创建”时针对创建它们的角色进行检查。
  • 在运行时，仅检查约束函数的模式的“解析能力”。就像这样的模式是否存在，但如果它是可访问的则不是。
  • 但是在运行时，会检查函数本身（无论它存在于何种模式）针对执行角色的权限。

所以这解释了我在 PostgreSQL 中遇到的行为

推荐阅读