CODEWITHSUNDEEP
postgresqltriggerspermissionsconstraintsroles
Neo
Neo

Reputation: 131

Why does PostgreSQL permissions behave differently between Triggers and Check constraints?

I'm currently having a Database with two schemas app_private & app_public (in addition to default public schema). I also have a role which has been granted usage on app_public schema, but not the app_private schema. I'm also using two functions (one trigger function and one check constraint function) on the table.

See below for code:

(1) Creation of the Schemas (and grants)

CREATE SCHEMA app_public;
CREATE SCHEMA app_private;

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

(2) Revoke grants from PUBLIC user

Then I'm having this special DDL statement. It is supposed to REVOKE permissions for any newly added function from the public user role (which all other roles inherit from).

alter default privileges revoke all on functions from public;

(3) Function Definitions (Trigger & Constraint)

-- 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) Table Definition (which uses above Trigger & Constraint functions)

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) Code (which runs as 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;

I'm trying to execute this in a fresh DB each time for me to understand how PostgreSQL permissions work in different call contexts (i.e. Trigger Function, constraint check which calls a function automatically, etc.)

When I don't have the code block (2) which revokes functions permissions from PUBLIC user, the code block (5) executes without any errors. Event though the user role doesn't have grants to app_private schema where the trigger function and the constraint function exists. But with the code block (2) present, the code executes the trigger just fine, yet gives me a "permission denied for function constraint_max_length" for the check constraint.

So I'm trying to understand,

  1. How does the Trigger function which exists in a schema where the user role does not have usage grants, execute successfully always?
  2. If the trigger function executes, why does the the CHECK constraint function give me the above permission denied error?
  3. What does the code block (2) really do?

I'm struggling a bit to find documentation about how permissions apply in this kind of "auto-executed" scenarios (triggers/constraints) since the User is not "explicitly" calling these functions, rather they are automatically called by the DB. So I'm not sure which ROLE is executing them.

Upvotes: 3

Views: 2112

Answers (1)

Neo
Neo

Reputation: 131

I posted this question to the PostgreSQL mailing list and finally got the answer.

So as of now, this is how PostgreSQL works (be it compliant with SQL spec or not :)

Original mail thread - https://www.postgresql.org/message-id/CANYEAx8vZnN9eeFQfsiLGMi9NdCP0wUdriHTCGU-7jP0VmNKPA%40mail.gmail.com

Trigger Functions

  • Trigger Function privileges are checked at "creation" time against the role that creates them.
  • At runtime, privileges for the trigger function are not checked against the executing role at all, but simply the ability to parse.
  • The statements inside of the trigger function will go through usual privilege checks against the executing role

Check Constraint Functions

  • Check Constraint function privileges are checked at "creation" time against the role that creates them.
  • At runtime, the schema of the constraint function is only checked for the "ability to parse". As in if such a schema exists, but not if it is accessible.
  • However at runtime, the function itself (regardless of the schema it exists in) is checked for privileges against the executing role.

So this explains the behavior I was encountering in PostgreSQL

Upvotes: 6

Related Questions