Transitive/nested permissions in PostgreSQL

Question

I have the following setup:

create role test_user noinherit nologin;

create table test_me ( attr text );
create view v_test_me as select * from test_me;

create function fn() returns trigger language plpgsql as $$
begin
    insert into test_me(attr) values (NEW.attr);
    return NEW;
end; $$;
create trigger tg instead of insert on v_test_me 
for each row execute procedure fn();

grant insert on v_test_me to test_user;

set role test_user;
insert into v_test_me(attr) values ('hello?');

Here I have a table and a view on top of it. The view has an instead-of-trigger. I am allowing test_user to insert into the view. But i get the following error:

ERROR:  permission denied for table test_me
CONTEXT:  SQL statement "insert into test_me(attr) values (NEW.attr)"

It looks like I do not have permission to execute an insert statement on the test_me table, which is expected. Is there any way I can allow user to insert into test_me table without directly granting them such permission?

Big Monday · Accepted Answer · 2019-09-25 00:04:29Z

3

In this case a SECURITY DEFINER modifier on the trigger function fn() will solve the issue. As per CREATE FUNCTION documentation:

SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that created it

This will allow inserting into test_me table, provided the owner of the function has such permission.

answered Sep 25, 2019 at 0:04

Big Monday

6381 gold badge6 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Laurenz Albe Over a year ago

Heed the warning in the documentation and set search_path on all SECURITY DEFINER functions.

Collectives™ on Stack Overflow

Transitive/nested permissions in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related