pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Recovery using Online Backup+WAL and broken passwords
5+ messages / 3 participants
[nested] [flat]

* Recovery using Online Backup+WAL and broken passwords
@ 2005-02-04 16:26 Michael Klatt <lists.tcimk@olympus.net>
  2005-02-04 17:04 ` Re: Recovery using Online Backup+WAL and broken passwords Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Michael Klatt @ 2005-02-04 16:26 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Recovery using Online Backup+WAL and broken passwords
  2005-02-04 16:26 Recovery using Online Backup+WAL and broken passwords Michael Klatt <lists.tcimk@olympus.net>
@ 2005-02-04 17:04 ` Tom Lane <tgl@sss.pgh.pa.us>
  2005-02-04 23:09   ` Re: Recovery using Online Backup+WAL and broken passwords Michael Klatt <tcimk@olympus.net>
  2005-02-04 23:18   ` Re: Recovery using Online Backup+WAL and broken passwords Michael Klatt <lists.tcimk@olympus.net>
  0 siblings, 2 replies; 5+ messages in thread

From: Tom Lane @ 2005-02-04 17:04 UTC (permalink / raw)
  To: Michael Klatt <lists.tcimk@olympus.net>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Recovery using Online Backup+WAL and broken passwords
  2005-02-04 16:26 Recovery using Online Backup+WAL and broken passwords Michael Klatt <lists.tcimk@olympus.net>
  2005-02-04 17:04 ` Re: Recovery using Online Backup+WAL and broken passwords Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-02-04 23:09   ` Michael Klatt <tcimk@olympus.net>
  1 sibling, 0 replies; 5+ messages in thread

From: Michael Klatt @ 2005-02-04 23:09 UTC (permalink / raw)
  To: ; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Recovery using Online Backup+WAL and broken passwords
  2005-02-04 16:26 Recovery using Online Backup+WAL and broken passwords Michael Klatt <lists.tcimk@olympus.net>
  2005-02-04 17:04 ` Re: Recovery using Online Backup+WAL and broken passwords Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-02-04 23:18   ` Michael Klatt <lists.tcimk@olympus.net>
  2005-02-04 23:50     ` Re: Recovery using Online Backup+WAL and broken passwords Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 5+ messages in thread

From: Michael Klatt @ 2005-02-04 23:18 UTC (permalink / raw)
  To: ; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Recovery using Online Backup+WAL and broken passwords
  2005-02-04 16:26 Recovery using Online Backup+WAL and broken passwords Michael Klatt <lists.tcimk@olympus.net>
  2005-02-04 17:04 ` Re: Recovery using Online Backup+WAL and broken passwords Tom Lane <tgl@sss.pgh.pa.us>
  2005-02-04 23:18   ` Re: Recovery using Online Backup+WAL and broken passwords Michael Klatt <lists.tcimk@olympus.net>
@ 2005-02-04 23:50     ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 2005-02-04 23:50 UTC (permalink / raw)
  To: Michael Klatt <lists.tcimk@olympus.net>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2005-02-04 23:50 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-04 16:26 Recovery using Online Backup+WAL and broken passwords Michael Klatt <lists.tcimk@olympus.net>
2005-02-04 17:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-04 23:09   ` Michael Klatt <tcimk@olympus.net>
2005-02-04 23:18   ` Michael Klatt <lists.tcimk@olympus.net>
2005-02-04 23:50     ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox