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(at)candle(dot)pha(dot)pa(dot)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)