Re: local security

"David M. Kaplan" <dkaplan(at)bio(dot)puc(dot)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