The scenario you mention here is basically what I was thinking. I was thinking
instead of using a SET that one would simply use the normal connect, but the
system would decide on whether or not to connect based first on your system id,
next on the current \connect id. If neither of these work (i.e. if neither of
these are superuser), the system would ask for your unix password to allow the
change of \connect id. This is basically the "su" mechanism.
I was never thinking of having unix superusers be postgres superusers, but rather
that if you are unix logged on as postgres, you would be able to connect to psql
as any user. Also, a user would only be allowed to connect if there was also a
postgresql user with the same username (or there could be a system of aliases as
used in the current ident security option).
I will look into postgres 7.1 and see what is there. Perhaps I will get a moment
to develop some of this.
Thanks,
David
Tom Lane wrote:
> "David M. Kaplan" <dkaplan@bio.puc.cl> writes:
> > ... If you do that, you
> > can no longer do things like restores from backups without editing the
> > configuration file because postgresql wont let you change users. This is of
> > course not a great problem, but it is a bit annoying. What I really want is
> > that normal users can only logon as themselves, but superusers can logon as
> > anyone.
>
> BTW, this has already been discussed (look in the pghackers archives),
> and I believe the consensus was that the most useful approach is to make
> Postgres distinguish between "real" userid (what you logged in as) and
> "effective" userid (what's used for object creation and permission
> checks). For unprivileged users these would be equal and unchanging
> throughout a session, but if your real ID is a Postgres superuser then
> you would be allowed to SET the effective-userid variable to different
> values. Then, for example, a pg_dump script would use "SET
> effective_userid" instead of "\connect" commands to change user
> identity, and a whole lot of the problems with executing pg_dump and
> pg_dumpall scripts under secure authentication models would go away.
>
> We need a notion of current effective userid anyway to allow rules and
> functions to execute as though they are "setuid" programs.
>
> I think Peter E. has laid some of the groundwork for this mechanism in
> 7.1, but work is still needed.
>
> regards, tom lane