Thread: Dumping roles improvements?

Dumping roles improvements?

From
Josh Berkus
Date:
It occurs to me that we could really use two things to make it easier to
move copies of database stuff around:

pg_dump -r, which would include a CREATE ROLE for all roles needed to
restore the database (probably without passwords), and

pg_dumpall -r --no-passwords which would dump the roles but without
CREATE PASSWORD statements.  This would be useful for cloning databases
for use in Dev, Test and Staging, where you don't what to copy the md5s
of passwords for possible cracking.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


Re: Dumping roles improvements?

From
Pavel Stehule
Date:
Hello

2011/10/11 Josh Berkus <josh@agliodbs.com>:
> It occurs to me that we could really use two things to make it easier to
> move copies of database stuff around:
>
> pg_dump -r, which would include a CREATE ROLE for all roles needed to
> restore the database (probably without passwords), and
>
> pg_dumpall -r --no-passwords which would dump the roles but without
> CREATE PASSWORD statements.  This would be useful for cloning databases
> for use in Dev, Test and Staging, where you don't what to copy the md5s
> of passwords for possible cracking.
>

maybe some switch for "complete" dump of one database with related roles.

Pavel

> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.com
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>


Re: Dumping roles improvements?

From
Bruce Momjian
Date:
Josh Berkus wrote:
> It occurs to me that we could really use two things to make it easier to
> move copies of database stuff around:
> 
> pg_dump -r, which would include a CREATE ROLE for all roles needed to
> restore the database (probably without passwords), and
> 
> pg_dumpall -r --no-passwords which would dump the roles but without
> CREATE PASSWORD statements.  This would be useful for cloning databases
> for use in Dev, Test and Staging, where you don't what to copy the md5s
> of passwords for possible cracking.

What would this do that pg_dumpall --globals-only doesn't?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +


Re: Dumping roles improvements?

From
Andrew Dunstan
Date:

On 10/11/2011 12:40 PM, Bruce Momjian wrote:
> Josh Berkus wrote:
>> It occurs to me that we could really use two things to make it easier to
>> move copies of database stuff around:
>>
>> pg_dump -r, which would include a CREATE ROLE for all roles needed to
>> restore the database (probably without passwords), and
>>
>> pg_dumpall -r --no-passwords which would dump the roles but without
>> CREATE PASSWORD statements.  This would be useful for cloning databases
>> for use in Dev, Test and Staging, where you don't what to copy the md5s
>> of passwords for possible cracking.
> What would this do that pg_dumpall --globals-only doesn't?
>

As stated, it would not export the passwords.

cheers

andrew


Re: Dumping roles improvements?

From
Bruce Momjian
Date:
Andrew Dunstan wrote:
> 
> 
> On 10/11/2011 12:40 PM, Bruce Momjian wrote:
> > Josh Berkus wrote:
> >> It occurs to me that we could really use two things to make it easier to
> >> move copies of database stuff around:
> >>
> >> pg_dump -r, which would include a CREATE ROLE for all roles needed to
> >> restore the database (probably without passwords), and
> >>
> >> pg_dumpall -r --no-passwords which would dump the roles but without
> >> CREATE PASSWORD statements.  This would be useful for cloning databases
> >> for use in Dev, Test and Staging, where you don't what to copy the md5s
> >> of passwords for possible cracking.
> > What would this do that pg_dumpall --globals-only doesn't?
> >
> 
> As stated, it would not export the passwords.

What is the logic for not dumping passwords but the CREATE ROLE
statement?  I don't see how anyone would recognize that behavior as
logical.  If you want to add a --no-passwords option to pg_dumpall, that
seems more logical to me.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +


Re: Dumping roles improvements?

From
Josh Berkus
Date:
> What is the logic for not dumping passwords but the CREATE ROLE
> statement?  I don't see how anyone would recognize that behavior as
> logical.  If you want to add a --no-passwords option to pg_dumpall, that
> seems more logical to me.

That's what I'm suggesting.

Incidentally, what's the difference between -g and -r in terms of actual
output, anyway?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


Re: Dumping roles improvements?

From
Bruce Momjian
Date:
Josh Berkus wrote:
> 
> > What is the logic for not dumping passwords but the CREATE ROLE
> > statement?  I don't see how anyone would recognize that behavior as
> > logical.  If you want to add a --no-passwords option to pg_dumpall, that
> > seems more logical to me.
> 
> That's what I'm suggesting.
> 
> Incidentally, what's the difference between -g and -r in terms of actual
> output, anyway?

Acording the docs, I assume -r is only roles, while -g includes
tablespace, so what you want is already available in pg_dumpall.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +


Re: Dumping roles improvements?

From
Tom Lane
Date:
Andrew Dunstan <andrew@dunslane.net> writes:
> On 10/11/2011 12:40 PM, Bruce Momjian wrote:
>> Josh Berkus wrote:
>>> pg_dumpall -r --no-passwords which would dump the roles but without
>>> CREATE PASSWORD statements.  This would be useful for cloning databases
>>> for use in Dev, Test and Staging, where you don't what to copy the md5s
>>> of passwords for possible cracking.

>> What would this do that pg_dumpall --globals-only doesn't?

> As stated, it would not export the passwords.

I can see some possible use in a --no-passwords option that's orthogonal
to all else.  The rest of this seems rather confused...
        regards, tom lane


Re: Dumping roles improvements?

From
Andrew Dunstan
Date:

On 10/11/2011 04:19 PM, Tom Lane wrote:
> Andrew Dunstan<andrew@dunslane.net>  writes:
>> On 10/11/2011 12:40 PM, Bruce Momjian wrote:
>>> Josh Berkus wrote:
>>>> pg_dumpall -r --no-passwords which would dump the roles but without
>>>> CREATE PASSWORD statements.  This would be useful for cloning databases
>>>> for use in Dev, Test and Staging, where you don't what to copy the md5s
>>>> of passwords for possible cracking.
>>> What would this do that pg_dumpall --globals-only doesn't?
>> As stated, it would not export the passwords.
> I can see some possible use in a --no-passwords option that's orthogonal
> to all else.  The rest of this seems rather confused...
>
>             

Yes, I also think that will meet the case.

cheers

andrew


Re: Dumping roles improvements?

From
Bruce Momjian
Date:
Josh Berkus wrote:
> 
> > Acording the docs, I assume -r is only roles, while -g includes
> > tablespace, so what you want is already available in pg_dumpall.
> 
> No, it's not.  You don't seem to be actually reading any of my proposals.
> 
> (1) I cannot produce a single file in custom dump format which includes
> both a single database and all of the roles I need to build that database.
> 
> (2) I cannot dump a set of roles without md5 passwords.
> 
> Both of these are things I need to support dev/stage/testing integration
> at multiple sites.

We are not writing this software for you.  Please submit a clear
proposal.  I am sure you have 10k customers who want this.  :-|

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +


Re: Dumping roles improvements?

From
Josh Berkus
Date:
> Acording the docs, I assume -r is only roles, while -g includes
> tablespace, so what you want is already available in pg_dumpall.

No, it's not.  You don't seem to be actually reading any of my proposals.

(1) I cannot produce a single file in custom dump format which includes
both a single database and all of the roles I need to build that database.

(2) I cannot dump a set of roles without md5 passwords.

Both of these are things I need to support dev/stage/testing integration
at multiple sites.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


Re: Dumping roles improvements?

From
Andrew Dunstan
Date:

On 10/11/2011 03:50 PM, Josh Berkus wrote:
>> Acording the docs, I assume -r is only roles, while -g includes
>> tablespace, so what you want is already available in pg_dumpall.
> No, it's not.  You don't seem to be actually reading any of my proposals.
>
> (1) I cannot produce a single file in custom dump format which includes
> both a single database and all of the roles I need to build that database.
>
> (2) I cannot dump a set of roles without md5 passwords.
>
> Both of these are things I need to support dev/stage/testing integration
> at multiple sites.
>

There seems to be agreement on something for (2), and it won't be hard. 
(1) would probably be much more complex. Essentially we'd need to add a 
new object type for roles, I think. But won't (2) give you most of what 
you need for (1) anyway? AIUI, your problem is that the roles might not 
exist, and so some or all of the dump will fail. But if you have (2) and 
dump the roles without passwords and restore them before running 
pg_restore that wouldn't happen. It won't be one command but it will be 
two or three pretty easy commands.

cheers

andrew


Re: Dumping roles improvements?

From
Josh Berkus
Date:
> There seems to be agreement on something for (2), and it won't be hard.
> (1) would probably be much more complex. Essentially we'd need to add a
> new object type for roles, I think. But won't (2) give you most of what
> you need for (1) anyway? AIUI, your problem is that the roles might not
> exist, and so some or all of the dump will fail. But if you have (2) and
> dump the roles without passwords and restore them before running
> pg_restore that wouldn't happen. It won't be one command but it will be
> two or three pretty easy commands.

These serve two different purposes.

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

Since the above is something I'm doing on around 11 different machines
between once a day and once a week, eliminating the 4 extra steps would
be really nice.

However, we'd also need CREATE OR REPLACE ROLE to really integrate
shipping database copies.  Without that, including roles in the database
dump doesn't help that much.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


Re: Dumping roles improvements?

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.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?
        regards, tom lane


Re: Dumping roles improvements?

From
Josh Berkus
Date:
On 10/11/11 9:43 PM, Tom Lane wrote:
> 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.

But what I'm asking for is (1).  The problem is that the roles don't
ship in the per-database pgdump file.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


Re: Dumping roles improvements?

From
Andrew Dunstan
Date:

On 10/12/2011 12:43 AM, Tom Lane wrote:
> Josh Berkus<josh@agliodbs.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


Re: Dumping roles improvements?

From
Andrew Dunstan
Date:

On 10/12/2011 03:16 PM, Josh Berkus wrote:
> On 10/11/11 9:43 PM, Tom Lane wrote:
>> 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.
> But what I'm asking for is (1).  The problem is that the roles don't
> ship in the per-database pgdump file.
>

I think Tom's (1) assumes you already have that environment, not that it 
will be created on the fly by pg_restore.

cheers

andrew


Re: Dumping roles improvements?

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.com> writes:
> On 10/11/11 9:43 PM, Tom Lane wrote:
>> 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.

> But what I'm asking for is (1).  The problem is that the roles don't
> ship in the per-database pgdump file.

In that case you do "pg_dumpall -r" first and then pg_dump your
individual database.  I thought you were looking for something that
would dump only roles referenced in the particular database, which
is why it sounded like an intermediate case.

I know that the division of labor between pg_dumpall and pg_dump could
use rethinking, but it needs to be actually rethought, in toto, not
hacked one minor feature at a time.
        regards, tom lane


Re: Dumping roles improvements?

From
Josh Berkus
Date:
> In that case you do "pg_dumpall -r" first and then pg_dump your
> individual database.  I thought you were looking for something that
> would dump only roles referenced in the particular database, which
> is why it sounded like an intermediate case.
> 
> I know that the division of labor between pg_dumpall and pg_dump could
> use rethinking, but it needs to be actually rethought, in toto, not
> hacked one minor feature at a time.

Sure.  Maybe I should start a wiki page for that?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


Re: Dumping roles improvements?

From
Dimitri Fontaine
Date:
Josh Berkus <josh@agliodbs.com> writes:
> (1) I cannot produce a single file in custom dump format which includes
> both a single database and all of the roles I need to build that database.

I would see addressing this with the proposal to have pg_dumpall able to
issue an archive of -Fc dumps, that pg_restore would know how to handle.
Then we could have some option to list or exclude databases you want in
this pg_dumpall -Fc format.

It's been said that extending custom format to handle several databases
would be complex, but I don't think it's necessary.  A simple archive,
tar formatted for example, containing the custom format file of all
selected databases, would be user friendly enough.  All the more if you
add a -j option to control how many databases you dump at the same time.

The main drawback is that you need to prepare a directory with the
globals.sql file and a custom format file per database, and only when
all of those are finished can you pack them into the tar format.

Again, good enough for me.

> (2) I cannot dump a set of roles without md5 passwords.
>
> Both of these are things I need to support dev/stage/testing integration
> at multiple sites.

I don't handle that in pg_staging, but the other features you need are
all implemented into that tool.  It knows how to fetch the per cluster
settings then init your staging cluster with that and only then restore
your database (with a pgbouncer layer in between so that you can have
more than one available in parallel and an easy switch from one to the
other, like db -> db_20111013 rather than db_20111012).
 https://github.com/dimitri/pg_staging

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


Re: Dumping roles improvements?

From
Dimitri Fontaine
Date:
Bruce Momjian <bruce@momjian.us> writes:
> We are not writing this software for you.  Please submit a clear
> proposal.  I am sure you have 10k customers who want this.  :-|

I think I did (write this software).
 https://github.com/dimitri/pg_staging http://tapoueh.org/blog/2011/03/29-towards-pg_staging-10.html

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support