Matt Sullivan <matt@sullivan.gen.nz> writes:
> This would infer though that the passwd data is cached within each
> instance of psql which could present it's own set of security risks.
Yeah. There's been discussion about that, and the consensus seems to
be that the advantages outweigh the very small risks. (One of the
disadvantages of forgetting the password is that PQreset can't work...)
> I would think that it should probably be *forgotton* after
> authentication is established and required on any new \connect. This
> might present some issues with pg_dump etc. I guess though.
pg_dump is actually pretty nearly useless in a password-auth
installation; to run the restore script, you'd have to manually enter
a password each time it hits a \connect command :-(.
The best idea I've heard for fixing this is to invent a quasi-suid
mechanism: the pg_dump script would be started as postgres (enter
password for same, once) and it would NOT do any \connect commands.
Instead it would issue some kind of "SET effective_user = 'name'"
command, which would determine the ownership assigned to subsequently-
created objects, but the backend would still remember that the user
was "really" postgres. Presumably this SET command would only be
allowed to superusers, so the backend must remember that the user is
really postgres, or it'll reject SET effective_user commands after
the first one.
The devil is in the details, of course, and the details here would be
to figure out which operations should pay attention to effective_user
and which to the true userid. But it seems doable.
(Hey Peter, wanna put this on your todo list for that privilege-system
work?)
regards, tom lane