Thread: 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.
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.
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?)
regards, tom lane
> 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.
There is a two sentence "chapter" in the Administrator's Guide in
start-ag.sgml which is titled "Adding and Deleting Users". This chapter
should be renamed "Users" and have sections covering this topic and
others.
It looks like there are several man pages (pg_hba, pg_passwd at least)
which could/should be folded into the Administrator's Guide. I'll try
doing that sometime soon, once we have the SQL language reference pages
done.
Would you like to add some information, or is what you have above
sufficient? If it is enough, I'll add it in. If it needs more, would you
be willing to flesh it out?
- Tom (the other "tgl")
> 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)
On Sun, 2 Aug 1998, Tom Lane wrote: > 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. This might explain why my test with the perl-interface failed.... > > 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. > > 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. An 'ALTER USER user RENAME TO new-user' would also come in handy then... Maarten _____________________________________________________________________________ | TU Delft, The Netherlands, Faculty of Information Technology and Systems | | Department of Electrical Engineering | | Computer Architecture and Digital Technique section | | M.Boekhold@et.tudelft.nl | -----------------------------------------------------------------------------