Обсуждение: Recovery using Online Backup+WAL and broken passwords

PostgreSQL 8.0.1
Linux FC2, 2.6.8

Hi,

I'm very new to PostgreSQL.  I've been the admin of several MySQL
servers for years, but for various reasons we're switching to PostgreSQL.

I love the online backup feature, but I'm encountering a problem with
users restored from WAL files.  It seems that after a restore, the
password for any user created since the base backup is broken.

This is what I've done:

created a base backup (using online backup)
created some users
stopped postgresql
restored postgresql from base backup (and added recovery.conf)
started postgresql

The logs indicate that everything went great.  The archived and
unarchived WAL files are restored/used and the database backup is
brought up-to-date.

The users exist in the system, but their passwords are broken.  If I
used "ALTER USER" to reset the password then I can access the database ok.

I'm using md5 as the default encryption. I've tried crypt and that
doesn't work either. It doesn't seem to matter whether I use ENCRYPTED
or UNENCRYPTED when created the users.

So....is there something in the way users are created from WAL files
that doesn't properly restore passwords?  Has anyone else ecountered
this problem?

Thanks!

Michael

Michael Klatt <lists.tcimk@olympus.net> writes:
> I love the online backup feature, but I'm encountering a problem with
> users restored from WAL files.  It seems that after a restore, the
> password for any user created since the base backup is broken.

[ thinks about that... ]  Hmm, you're right, there is no mechanism to
cause the flat password file to be rebuilt after a restore.  So newly
created users won't be in it (and for that matter it won't reflect
post-backup password changes for existing users).  Just (re)assign any
one user's password and things should be ok.

Now that I think about it, this problem exists for ordinary crash
recovery from WAL as well.  We probably ought to just forcibly rebuild
the flat file at conclusion of any recovery.  Odd that it hadn't been
noticed already.

            regards, tom lane

Tom Lane wrote:
> [ thinks about that... ]  Hmm, you're right, there is no mechanism to
> cause the flat password file to be rebuilt after a restore.  So newly
> created users won't be in it (and for that matter it won't reflect
> post-backup password changes for existing users).  Just (re)assign any
> one user's password and things should be ok.

Great, this gives me a great short-term work-around solution to the
problem. I've verified that setting any user password automatically
fixes all of them.

Do you think a fix will show up in 8.0.2?

Thanks!

Michael

Michael Klatt <lists.tcimk@olympus.net> writes:
> Tom Lane wrote:
>> [ thinks about that... ]  Hmm, you're right, there is no mechanism to
>> cause the flat password file to be rebuilt after a restore.

> Do you think a fix will show up in 8.0.2?

Not sure; a proper fix may have to wait for 8.1.  The ideas I've had so
far about it require nontrivial code additions, and we generally don't
backpatch changes like that when there's an easy workaround.

            regards, tom lane

Tom Lane wrote:
  > [ thinks about that... ]  Hmm, you're right, there is no mechanism to
> cause the flat password file to be rebuilt after a restore.  So newly
> created users won't be in it (and for that matter it won't reflect
> post-backup password changes for existing users).  Just (re)assign any
> one user's password and things should be ok.

Great, this gives me a great short-term work-around solution to the
problem. I've verified that setting any user password automatically
fixes all of them.

Do you think a fix will show up in 8.0.2?

Thanks!

Michael