1

I came across a strange situation that gave me a big headache and after solving it I would also like to understand WHY.
Basically, a user had all the privileges he needed to execute a "trigger" (it was actually a foreign key constraint with an associated ON DELETE CASCADE) and all the privileges on all the objects that were touched by the trigger itself, BUT when he executed the trigger he actually used the object's owner privileges and not his own. The owner had fewer privileges than the user had and so it gave a really mysterious error.

I understand it is complicated to understand what I mean, so I have created a working example that I hope clarifies. The schema I created is a spotify-type toy example, where there are users and artists, and users have lists of artists they follow.

Perform all of the following operations with a superuser (just for simplicity's sake)

-- ALL DATA
create schema website;

create table public.artists (
    id_artist serial primary key,
    name text not null unique
);

insert into public.artists (name)
values ('Heilung'), ('Rammstain'), ('Iron Maiden');

create table website.users (
    id_user serial primary key,
    email text not null unique
);

insert into website.users (email)
values ('[email protected]'), ('[email protected]');

create table website.users_list (
    id_user int not null,
    id_artist int not null,
    CONSTRAINT user_fk FOREIGN KEY (id_user) REFERENCES website.users (id_user),
    CONSTRAINT artist_fk FOREIGN KEY (id_artist) REFERENCES public.artists (id_artist) ON DELETE CASCADE);

insert into website.users_list (id_user,id_artist)
values (1,1), (1,2), (1,3), (2,1), (2,3);

-- USERS AND PRIVILEGES

create user jenny with password '123';

create user tommy with password '123';

-- tommy is owner but has zero privileges
alter table website.users_list owner to tommy;

-- jenny is not owner but has all privileges
grant usage, create on schema website to jenny;

grant all on all tables in schema website,public to jenny;

grant usage on all sequences in schema website to jenny;

Then run these queries using the user Jenny, one by one.

--1)
select *
from website.users_list ul;

--2)
select *
from artists a;

--3)
delete from artists 
where id_artist = 3;

The first and second query work normally, because Jenny can do SELECT on both website.users_list and public.artists. She obviously has usage privilege on the schema website. But the third one she cannot do. It gives this seemingly meaningless error.

ERROR: permission denied for schema website

When she does a DELETE on public.artists she actually triggers the foreign key constraint of website.users_list so all rows related to the artist with id_artist = 3 must be dropped from website.users_list. BUT in that moment she is using the privileges of tommy (owner of website.users_list) who as we saw above does NOT have the privilege of USAGE on schema website, giving the strange error.

Can someone explain to me why this happens? What does the table owner have to do with running a trigger where the user had all the necessary privileges? I find this so confusing and frankly a nightmare to debug (I speak unfortunately from personal experience).

1 Answer 1

2

It is a security hazard if one user can create code (or maybe I should say "a situation" as FK are not explicitly code) which is executed in a non-obvious way by a different user. So for safety the user who creates the trigger is the user the trigger runs as.

Consider the alternative, tommy can create a trigger which would delete rows on a table he doesn't have delete privs to, and then just needs to entice jenny into doing something seemingly harmless which fires the trigger.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.