Thread

1. TODO item: make pg_shadow updates more robust

   Tom Lane <tgl@sss.pgh.pa.us> — 1998-08-02T23:28:13Z

   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
   
   

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

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-08-03T05:25:20Z

   > 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")
   
   

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

   Bruce Momjian <maillist@candle.pha.pa.us> — 1998-08-03T05:58:02Z

   > 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)
   
   

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

   Maarten Boekhold <maartenb@dutepp2.et.tudelft.nl> — 1998-08-03T07:13:31Z

   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                         |
   -----------------------------------------------------------------------------