Cascade view drop permission checks

[m7o...@gmail.com]

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?

Re: Cascade view drop permission checks

[m7o...@gmail.com]

David, thank you for the clarification.
Should we consider raising log level for cascade drops from NOTICE to
WARNING? By now cascade drops appears in log files only when log level >=
NOTICE.

--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -1105,7 +1105,7 @@ reportDependentObjects(const ObjectAddresses
*targetObjects,
   int flags,
   const ObjectAddress *origObject)
 {
- int msglevel = (flags & PERFORM_DELETION_QUIETLY) ? DEBUG2 : NOTICE;
+ int msglevel = (flags & PERFORM_DELETION_QUIETLY) ? DEBUG2 : WARNING;
bool ok = true;
StringInfoData clientdetail;
StringInfoData logdetail;

On Wed, Apr 6, 2022, 10:13 David G. Johnston 
wrote:

> On Tuesday, April 5, 2022, m7o...@gmail.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.
>
>

View invoker privileges

[m7o...@gmail.com]

Hello guys!
In Postgres we can create view with view owner privileges only. What’s
the reason that there is no option to create view with invoker
privileges? Is there any technical or security subtleties related to
absence of this feature?