On Mon, Feb 15, 2016 at 7:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Teodor Sigaev <teodor@sigaev.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.
yes, I remember AT&T and BSD :)
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.