Thread: pg_dumpall (7.1beta1, current CVS)
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
* 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 datdbaFROM 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
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
* 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