Re: Cascade view drop permission checks

On Tuesday, April 5, 2022, m7onov(at)gmail(dot)com <m7onov(at)gmail(dot)com> wrote:

>
> -- 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?
>

The system dropped the now defunct view, not alice. Bob accepted that risk
by basing the view on an object owned by another role. I suppose other
behaviors are possible but not really worth exploring. Namely it would
nice to fix the problem with “create or replace view” and not have yet
other object types maybe have to be dropped. But if two users in the same
database own objects they should be expected to play nicely with each
other. Not sure why we picked this behavior instead of an error (avoid DoS
by bob is part of it though, but that seems like it should also be
addressed by playing nicely…) or maybe it is a bug (others will need to
chime in if that is the case).

I will say the lack of documentation here:

https://www.postgresql.org/docs/current/ddl-depend.html

which CASCADE links to as well, may be an omission worth fixing (or please
point me to where this is covered…)

David J.