Skip to content

Instantly share code, notes, and snippets.

@daurnimator
Created June 25, 2019 23:26
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save daurnimator/ee6ffe5f1afe1be28da9046eda2d3342 to your computer and use it in GitHub Desktop.
Save daurnimator/ee6ffe5f1afe1be28da9046eda2d3342 to your computer and use it in GitHub Desktop.
PostgreSQL issue where constraint function is not being called as view owner
begin;
-- Load in uuid-ossp extension for uuid_generate_v1mc
create schema "uuid-ossp";
create extension "uuid-ossp" with schema "uuid-ossp";
-- Create roles
create role alice;
create role bob;
-- Create data tables
create table foo(
id uuid primary key default "uuid-ossp".uuid_generate_v1mc(),
things jsonb not null
);
grant select,insert,update,delete on table foo to alice;
create table bar(
id uuid primary key default "uuid-ossp".uuid_generate_v1mc(),
foo uuid not null references foo(id) on delete cascade,
name text not null
);
grant select,insert,update,delete on table bar to alice;
create function relevant_thing(bar) returns jsonb
language sql
as $$
select things->$1.name from foo where foo.id=$1.foo;
$$;
create function thing_exists_in_foo() returns trigger
language plpgsql
as $$
begin
if relevant_thing(new) is null then
raise exception $fmt$thing '%' does not exist in foo$fmt$, new.name;
end if;
return new;
end
$$;
create constraint trigger exists_in_foo
after insert or update of foo,name on bar
for each row
execute procedure thing_exists_in_foo();
-- Create documented views for public users
create view fooview as select * from foo;
grant select, insert(things) on table fooview to bob;
alter view fooview owner to alice;
create view barview as select * from bar;
grant select, insert(foo, name) on table barview to bob;
alter view barview owner to alice;
-- Demonstrate issue
set role bob;
with new_foo as (
insert into fooview(things)
values ('{"mybar":{}}'::jsonb)
returning id
)
insert into barview(foo, name)
select new_foo.id, 'mybar' from new_foo;
-- Fails with:
-- ERROR: permission denied for table foo
-- CONTEXT: SQL function "relevant_thing" statement 1
-- PL/pgSQL function thing_exists_in_foo() line 3 at IF
rollback;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment