Me deparei com uma situação estranha que me deu uma grande dor de cabeça e depois de resolver também gostaria de entender o PORQUÊ.
Basicamente, um usuário tinha todos os privilégios necessários para executar um "gatilho" (na verdade era uma restrição de chave estrangeira com um ON DELETE CASCADE associado) e todos os privilégios em todos os objetos que foram tocados pelo próprio gatilho, MAS quando ele executou o gatilho, ele realmente usou os privilégios de proprietário do objeto e não os seus. O proprietário tinha menos privilégios do que o usuário e por isso ocorreu um erro realmente misterioso.
Entendo que é complicado entender o que quero dizer, então criei um exemplo prático que espero esclarecer. O esquema que criei é um exemplo de brinquedo do tipo spotify, onde existem usuários e artistas, e os usuários têm listas de artistas que seguem.
Execute todas as operações a seguir com um superusuário (apenas para simplificar)
-- 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;
Em seguida, execute essas consultas usando o usuário Jenny, uma por uma.
--1)
select *
from website.users_list ul;
--2)
select *
from artists a;
--3)
delete from artists
where id_artist = 3;
A primeira e a segunda consulta funcionam normalmente, pois Jenny pode fazer SELECT em website.users_list e public.artists. Ela obviamente tem privilégio de uso no site do esquema. Mas o terceiro ela não pode fazer. Dá esse erro aparentemente sem sentido.
ERROR: permission denied for schema website
Quando ela faz um DELETE em public.artists, ela na verdade aciona a restrição de chave estrangeira de website.users_list, portanto, todas as linhas relacionadas ao artista com id_artist = 3 devem ser eliminadas de website.users_list. MAS naquele momento ela está usando os privilégios do tommy (dono do site.users_list) que como vimos acima NÃO tem o privilégio de USAGE no esquema do site, dando o erro estranho.
Alguém pode me explicar por que isso acontece? O que o proprietário da tabela tem a ver com a execução de um gatilho onde o usuário tinha todos os privilégios necessários? Acho isso tão confuso e francamente um pesadelo para depurar (infelizmente falo por experiência própria).
