Re: pg_dump dump catalog ACLs

On Wed, Mar 2, 2016 at 1:54 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> * Joe Conway (mail(at)joeconway(dot)com) wrote:
> > On 03/01/2016 08:00 AM, Tom Lane wrote:
> > > Joe Conway <mail(at)joeconway(dot)com> writes:
> > >> Would it be a terrible idea to add some attribute to ACLs which can be
> > >> used to indicate they should not be dumped (and supporting syntax)?
> > >
> > > Yes, we'd need some way to mark non-null ACLs as being "built-in
> > > defaults". I do not see the need to have SQL syntax supporting that
> > > though.
> >
> > I was thinking the supporting syntax might be used by extensions, for
> > example.
>
> I tend to agree with Tom that we don't really need SQL syntax for this.
>
> > > Actually, wouldn't you need to mark individual aclitems as built-in
> > > or not? Consider a situation where we have some function foo() that
> > > by default has EXECUTE permission granted to some built-in "pg_admin"
> > > role. If a given installation then also grants EXECUTE to "joe",
> > > what you really want to have happen is for pg_dump to dump only the
> > > grant to "joe". Mentioning pg_admin's grant would tie the dump to
> > > a particular major PG version's idea of what the built-in roles are,
> > > which is what I'm arguing we need to avoid.
> >
> > Yes, I guess it would need to be a per aclitem attribute.
>
> Agreed.
>
> > > I guess this could also be addressed by having two separate aclitem[]
> > > columns, one that is expected to be frozen after initdb and one for
> > > user-added grants.
> >
> > Yeah, that would work, but seems kind of ugly.
>
> Rather than have two aclitem[] columns in every catalog, since this
> information is only used by pg_dump and not during normal operation, we
> could use the approach that pg_description took and have an independent
> catalog table which just contains all non-NULL "system" ACLs. We could
> populate it at the bottom of system_views.sql, so that we don't have to
> explicitly think about updating that table whenever there's a change to
> what the default ACLs are.
>
> I don't see any reason it couldn't be used by extensions also, though
> we'd have to do a bit more work on pg_dump to make it actually dump
> out any non-default ACLs for extension-owned objects.
>

​It sounds like this train of thought would resolve this complaint?

​
http://www.postgresql.org/message-id/CADmxfmmz-ATwptaidTSAF0XE=cPeikMyc00sj6t9xF6KCV5jCQ@mail.gmail.com

​Namely allowing users to edit permissions on extension objects and have
those changes dumped and then restored after the dependent CREATE EXTENSION
command is executed during pg_restore.

Did I interpret that right?

David J.
​