Created
June 25, 2019 23:26
-
-
Save daurnimator/ee6ffe5f1afe1be28da9046eda2d3342 to your computer and use it in GitHub Desktop.
PostgreSQL issue where constraint function is not being called as view owner
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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