Re: ALTER ... OWNER TO ... vs. ALTER DEFAULT PRIVILEGES

On Thu, Oct 29, 2015 at 02:25:14PM -0400, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)org> writes:
> > Since it's not a green field project, I would like to propose the
> > following addition to the ALTER ... OWNER TO ... construct:
> > ALTER ... OWNER TO ... [{NEW | OLD} DEFAULT PRIVILEGES]
> > What say?
>
> I'd say "you haven't actually defined what either of those options mean".

Good point.

This morning, when this came up most recently for me, I wanted to
change the owner of a table, which was all well and good, and I wanted
the default privileges of the new owner applied to it, which was, to
coin a phrase, all unwell and bad.

Had this been part of the original ALTER DEFAULT PRIVILEGES patch,
those privileges would simply have been applied. Since it wasn't, I'm
ass-u-me'ing that changing the default behavior to that is going to
cause (possibly legitimate) anxiety.

On thinking this through, I'd like to propose a more extensible
syntax, as below.

If we're going with "preserve current behavior for current syntax," I
propose that:

- Leaving the clause out would apply no privileges (modulo event
triggers. Whee!), preserving current behavior.

- ALTER ... OWNER TO ... WITH (NEW DEFAULT PRIVILEGES) applies any
applicable default privileges for the new owner.

- ALTER ... OWNER TO ... WITH (OLD DEFAULT PRIVILEGES) ensures that
any any applicable default privileges for the previous owner are
applied, whether they already had been or not.

It would be legal to supply both WITH stanzas, as in WITH (NEW DEFAULT
PRIVILEGES, OLD DEFAULT PRIVILEGES).

If we're going with "current behavior is pretty crazy, and should
never have been this way," (my opinion) I propose that:

- Leaving the clause out would wipe the default privileges slate clean
and apply any default privileges for the new owner, i.e. behave as
though the object had just been created.

- ALTER ... OWNER TO ... WITH ( [OLD DEFAULT PRIVILEGES [, NO NEW
PRIVILEGES] ) would ensure the previous default privileges had been
applied and not apply any new default privileges, respectively.
Order shouldn't matter.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate