Re: [HACKERS] TODO item: make pg_shadow updates more robust

> I learned the hard way last night that the postmaster's password
> authentication routines don't look at the pg_shadow table.  They
> look at a separate file named pg_pwd, which certain backend operations
> will update from pg_shadow.  (This is not documented in any user
> documentation that I could find; I had to burrow into
> src/backend/commands/user.c to discover it.)
>
> Unfortunately, if a clueless dbadmin (like me ;-)) tries to update
> password data with the obvious thing,
>     update pg_shadow set passwd = 'xxxxx' where usename = 'yyyy';
> pg_pwd doesn't get fixed.
>
> A more drastic problem is that pg_dump believes it can save and
> restore pg_shadow data using "copy".  Following an initdb and restore
> from a pg_dump -z script, pg_shadow will look just fine, but only
> the database admin will be listed in pg_pwd.  This is likely to provoke
> some confusion, IMHO.

Good point.  We did not want the backend to make database reads, so we
have the flat file created after every user operation.  It is a royal
hack, but we never came up with a better way.

>
> As a short-term thing, the fact that you *must* set passwords with
> ALTER USER ought to be documented, preferably someplace where a
> dbadmin who's never heard of ALTER USER is likely to find it.

Suggestions?

>
> As a longer-term thing, I think it would be far better if ordinary
> SQL operations on pg_shadow just did the right thing.  Wouldn't it
> be possible to implement copying to pg_pwd by means of a trigger on
> pg_shadow updates, or something like that?
>
> (I'm afraid that pg_dump -z is pretty well broken for operations on
> a password-protected database, btw.  Has anyone used it successfully
> in that situation?)

Good idea.

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)