Cascade view drop permission checks

From: "m7onov(at)gmail(dot)com" <m7onov(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Cascade view drop permission checks
Date: 2022-04-06 06:46:55
Message-ID: CAP8_6Xb2uvNcCzgm92hZS6b_DXTWmm6q96LE2mxDMOs3a_zbpQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello guys!
I've faced an interesting case with cascade drops. If we drop some view
that is dependency for another view then drop cascade will not check
permissions for cascade-droppping views.
Short example is:

create user alice with password 'apassword';
create user bob with password 'bpassword';

create schema sandbox_a;
create schema sandbox_b;

grant all on schema sandbox_a to alice;
grant all on schema sandbox_b to bob;
grant usage on schema sandbox_a to bob;

-- alice
create or replace view sandbox_a.alice_view as
select category, name, setting
from pg_catalog.pg_settings;

grant select on sandbox_a.alice_view to bob;

-- bob
create or replace view sandbox_b.bob_view as
select distinct category
from sandbox_a.alice_view;

-- alice
drop view sandbox_a.alice_view cascade;

-- !!! will drop sandbox_b.bob_view although alice is not an owner of
sandbox_b.bob_view

It seems strange to me that somebody who is not a member of owner role can
drop an object bypassing permission checks.
Is this behaviour OK?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2022-04-06 07:13:36 Re: Cascade view drop permission checks
Previous Message Peter J. Holzer 2022-04-06 06:19:57 Re: Select .... where id not in (....) returns 0 incorrectly