Thread: ALTER ... OWNER TO ... vs. ALTER DEFAULT PRIVILEGES
Folks,
I've run into a problem recently, and I can't be the first to have
done so, and it's this.
We have a pretty sophisticated capability via ALTER DEFAULT
PRIVILEGES. When the creating role creates something in a schema so
altered, all kinds of nice recursive granting happens. That's well
and good.
BUT
When we change an object's owner, we have no practical access to those
default privileges, even when we want them applied.
If this were a green field project, I would advocate that the things
ALTER DEFAULT PRIVILEGES does should be applied by default on change
of ownership. I would still argue that this behavior should become
the default, but I would expect to lose that argument.
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?
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.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".
regards, tom lane
On Thu, Oct 29, 2015 at 02:25:14PM -0400, Tom Lane wrote:
> David Fetter <david@fetter.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
previousowner 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
privilegeshad been applied and not apply any new default privileges, respectively. Order shouldn't matter.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On Thu, Oct 29, 2015 at 10:31 PM, David Fetter <david@fetter.org> wrote: > 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. The word "applied" is not very clear here. You want to revoke all existing privileges and then regrant whatever the default privileges would have been given the new owner? That might be a reasonable thing to have a command for, but doing it automatically on an owner change does not sound like a good idea. That could be very surprising behavior. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Sat, Oct 31, 2015 at 12:16:31AM +0100, Robert Haas wrote: > On Thu, Oct 29, 2015 at 10:31 PM, David Fetter <david@fetter.org> wrote: > > 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. > > The word "applied" is not very clear here. You want to revoke all > existing privileges and then regrant whatever the default privileges > would have been given the new owner? That might be a reasonable thing > to have a command for, but doing it automatically on an owner change > does not sound like a good idea. That could be very surprising > behavior. OK, so I think there are operationally useful use cases for mix'n'match of the following: - Clear all existing DEFAULT PRIVILEGES - Preserve DEFAULT PRIVILEGES from the previous owner - Apply DEFAULT PRIVILEGES for the new owner Are there others? I suspect we could get some lift out of CREATE ... OWNER ... which would then Do The Right Thing™ with respect at least to initial creation without having to be connected as that role. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Sat, Oct 31, 2015 at 10:42 AM, David Fetter <david@fetter.org> wrote: > On Sat, Oct 31, 2015 at 12:16:31AM +0100, Robert Haas wrote: >> On Thu, Oct 29, 2015 at 10:31 PM, David Fetter <david@fetter.org> wrote: >> > 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. >> >> The word "applied" is not very clear here. You want to revoke all >> existing privileges and then regrant whatever the default privileges >> would have been given the new owner? That might be a reasonable thing >> to have a command for, but doing it automatically on an owner change >> does not sound like a good idea. That could be very surprising >> behavior. > > OK, so I think there are operationally useful use cases for > mix'n'match of the following: > > - Clear all existing DEFAULT PRIVILEGES > - Preserve DEFAULT PRIVILEGES from the previous owner > - Apply DEFAULT PRIVILEGES for the new owner I don't believe the privilege grant records in any way whether it came about because of DEFAULT PRIVILEGES or for some other reason. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company