Re: Dumping roles improvements?

On 10/12/2011 12:43 AM, Tom Lane wrote:
> Josh Berkus<josh(at)agliodbs(dot)com> writes:
>> The reason I want to have the dependant roles created as part of a
>> database dump is so that we can ship around dump files as a single file,
>> and restore them with a single command. This is considerably simpler
>> than the current requirements, which are:
>> 1. pg_dumpall the roles
>> 2. pg_dump the database
>> 3. tar both files
>> 4. ship file
>> 5. untar both files
>> 6. psql the role file
>> 7. pg_restore the database file
> I don't find this terribly convincing. I can see the rationales for two
> endpoint cases: (1) restore these objects into exactly the same
> ownership/permissions environment that existed before, and (2) restore
> these objects with the absolute minimum of ownership/permissions
> assumptions. The latter case seems to me to be covered already by
> --no-owner --no-privileges. Cases in between those endpoints seem
> pretty special-purpose, and I don't want to buy into the assumption that
> we should fix them by creating a plethora of --do-it-joshs-way switches.
> Can we invent something comparable to the --list/--use-list mechanism,
> that can handle a range of use cases with a bit more manual effort?
>
>

Not easily, that I can think of. The cleanest way I can imagine would be
to have explicit ROLE objects in the TOC. TWe can easily get a list of
object owners and turn that into a set of "create role" statements,
because owner names are in the metadata, but getting a list of roles
mentioned in ACL items can only be done by textually analysing them -
the information just isn't kept anywhere else currently.

I do think there's a case for doing "create if not exists role foo" (I
know we don't have that right now) for owners and roles mentioned in
ACLs. The hair in the ointment here comes when we consider how far to go
with that. In particular, would we follow role membership recursively?

OTOH, notwithstanding Josh's reasonable need, I'm not sure the ROI here
is high enough.

cheers

andrew