I noticed today that pg_dumpall from current CVS does *NOT*
dump a password assiged to the postgres user.  

I consider this BAD, since if one has to restore from
a pg_dumpall, one may forget to reset the password. 

LER

-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Sun, 31 Dec 2000 17:59:17 -0600
Larry Rosenman <ler@lerctr.org>
pg_dumpall (7.1beta1, current CVS)
* Larry Rosenman <ler@lerctr.org> [001231 17:59]:
> I noticed today that pg_dumpall from current CVS does *NOT*
> dump a password assiged to the postgres user.  
> 
> I consider this BAD, since if one has to restore from
> a pg_dumpall, one may forget to reset the password. 
> 
> LER
And, it was easier than I thought to fix it....

Here is a patch:

Index: src/bin/pg_dump/pg_dumpall.sh
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.sh,v
retrieving revision 1.9
diff -c -r1.9 pg_dumpall.sh
*** src/bin/pg_dump/pg_dumpall.sh	2000/12/19 22:12:47	1.9
--- src/bin/pg_dump/pg_dumpall.sh	2001/01/01 01:33:58
***************
*** 164,169 ****
--- 164,174 ----
      || CAST(valuntil AS TIMESTAMP) || '''' ELSE '' END || ';'
  FROM pg_shadow
  WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0');
+ SELECT 'ALTER USER "' || usename || '"' 
+   || CASE WHEN passwd IS NOT NULL THEN ' PASSWORD ''' || passwd || '''' ELSE  ''
+ END || ';' 
+ FROM pg_shadow 
+ WHERE usesysid = (SELECT datdba FROM pg_database WHERE datname = 'template0');
  __END__
  echo
  
-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Sun, 31 Dec 2000 19:36:13 -0600
Larry Rosenman <ler@lerctr.org>
Re: pg_dumpall (7.1beta1, current CVS)
Larry Rosenman <ler@lerctr.org> writes:
> I noticed today that pg_dumpall from current CVS does *NOT*
> dump a password assiged to the postgres user.  

> I consider this BAD, since if one has to restore from
> a pg_dumpall, one may forget to reset the password. 

I'm unconvinced.  The pg_dumpall script is clearly intended to allow
restoration into a new database installation with a different superuser;
you will note that the script is careful not to assume that the old and
new superuser names are the same (an assumption your proposed patch
does make).

In any case the new installation certainly already *has* a superuser.
I'm not sure it's the job of the restore script to change his password
for him.

This does point up that there is some state that is not saved/restored
by pg_dumpall --- the pg_hba.conf file and other config files that
live in $PGDATA come to mind.  Perhaps there should be a separate
utility that saves/restores these.  (pg_dump can't do it because there's
no way to retrieve these files through a database connection.)

			regards, tom lane

Mon, 01 Jan 2001 14:16:05 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dumpall (7.1beta1, current CVS) 
* Tom Lane <tgl@sss.pgh.pa.us> [010101 13:16]:
> Larry Rosenman <ler@lerctr.org> writes:
> > I noticed today that pg_dumpall from current CVS does *NOT*
> > dump a password assiged to the postgres user.  
> 
> > I consider this BAD, since if one has to restore from
> > a pg_dumpall, one may forget to reset the password. 
> 
> I'm unconvinced.  The pg_dumpall script is clearly intended to allow
> restoration into a new database installation with a different superuser;
> you will note that the script is careful not to assume that the old and
> new superuser names are the same (an assumption your proposed patch
> does make).
> 
> In any case the new installation certainly already *has* a superuser.
> I'm not sure it's the job of the restore script to change his password
> for him.
> 
> This does point up that there is some state that is not saved/restored
> by pg_dumpall --- the pg_hba.conf file and other config files that
> live in $PGDATA come to mind.  Perhaps there should be a separate
> utility that saves/restores these.  (pg_dump can't do it because there's
> no way to retrieve these files through a database connection.)
How would you suggest doing this?  A shell script that makes a SHAR or
somesuch?  Or what?  Should it save the SuperUser password?  

I agree that this is a shortcoming.  

As to the SuperUser password thing, how do we remind the user that
they had one set?  Can we at least put out a comment in the pg_dumpall
output that mentions it? 


> 
> 			regards, tom lane
-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Mon, 1 Jan 2001 20:04:56 -0600
Larry Rosenman <ler@lerctr.org>
Re: pg_dumpall (7.1beta1, current CVS)