Re: [WIP] ALTER ... OWNER TO ... CASCADE

Teodor Sigaev <teodor(at)sigaev(dot)ru> writes:
>> So basically, a generic CASCADE facility sounds like a lot of work to
>> produce something that would seldom be anything but a foot-gun.

> DELETE FROM or TRUNCATE could be a foot-gun too, but it's not a reason to
> remove tham. I faced with problem when I tried to change owner of datadase with
> all objects inside. Think, this feature could be useful although it should
> restricted to superuser obly.

That's a pretty weak argument, and I do not think you have thought through
all the consequences. It is not hard at all to imagine cases where using
this sort of thing could be a security vulnerability. Are you familiar
with the reasons why Unix systems don't typically allow users to "give
away" ownership of files? The same problem exists here.

To be concrete about it:

1. Alice does, say, "CREATE EXTENSION cube".

2. Bob creates a security-definer function owned by himself, using a
"cube"-type parameter so that it's dependent on the extension.
(It needn't actually do anything with that parameter.)

3. Alice does ALTER EXTENSION cube OWNER TO charlie CASCADE.

4. Bob now has a security-definer function owned by (and therefore
executing as) Charlie, whose contents were determined by Bob.
Game over for Charlie ... and for everyone else too, if Charlie is
a superuser, which is not unlikely for an extension owner.

The only way Alice can be sure that the ALTER EXTENSION is safe is if
she manually inspects every dependent object, in which case she might
as well not use CASCADE.

Moreover, the use case you've sketched (ie, change ownership of all
objects inside a database) doesn't actually have anything to do with
following dependencies. It's a lot closer to REASSIGN OWNED ... in
fact, it's not clear to me why REASSIGN OWNED doesn't solve that
use-case already.

I remain of the opinion that this is a terrible idea.

regards, tom lane