Thread: Real/effective user

I just read on -general that it is envisoned to have a SET command to
temporarily change the effective user id (for superusers only), so that
pg_dump generated scripts won't have to use \connect and be forced to run
under excessively loose permissons.

This isn't hard to do, in fact we probably only need a command to call an
already existing function.  I dug around in SQL for a name and the closest
thing was

SET SESSION AUTHORIZATION <value specification>  (clause 18.2)

Terminology note:  In SQL 'real user' == SESSION_USER, 'effective user' ==
CURRENT_USER.  So this command doesn't do it.  But the logical choice
would obviously be

SET CURRENT AUTHORIZATION <value specification>

This is nice, but the other end of the plan doesn't actually want to play
along.  In clause 11.1 SR 2b) it is described that the owner of a new
schema defaults to the *session* user.  (Note that at the end of the day
tables and other lowly objects won't have an owner anymore.  In any case
they should currently behave in that aspect as schemas would.)

I say we ignore this requirement, since it's not consistent with Unix
anyway (Files created by setuid programs are owned by the euid.) and it
would destroy our nice plan.  ;-)

Another restriction would be that a current user change cannot survive the
end of a transaction.  This is implied by the semantics of "suid"
functions and the way we handle exceptions (elog).  It could probably be
helped by saving the state of the "authorization stack" at the start of a
transaction.  But I'm not sure whether this would be a desirable feature
to have in the first place.  Most schema commands are rollbackable now, so
maybe this won't be a large restriction for pg_dump's purposes.

Comments?

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Peter Eisentraut <peter_e@gmx.net> writes:
> Terminology note:  In SQL 'real user' == SESSION_USER, 'effective user' ==
> CURRENT_USER.

Not sure about that.  I suspect that we actually need three values:

1. "real user" = what you originally authenticated to the postmaster.

2. "session user" = what you can SET if your real identity is a superuser.

3. "current user" = effective userid for permission checks.

current user is the value that would be pushed and popped during calls
to setuid functions.  The big reason for distinguishing current and
session user is that session user is what current user needs to revert to
after an elog.

Whether SQL's SESSION_USER corresponds to the first or second of these
concepts remains to be determined.

> This is nice, but the other end of the plan doesn't actually want to play
> along.  In clause 11.1 SR 2b) it is described that the owner of a new
> schema defaults to the *session* user.

I think we could still accept that, if we distinguish session and
current user as above.  (I have not yet read the spec to see if it
agrees though ;-))

Whether this is a good idea is another question; if a setuid function
does a CREATE, shouldn't the created object be owned by the setuid user?
I'm not sure that I *want* to accept the SQL spec on this point.
        regards, tom lane

Tom Lane writes:

> 1. "real user" = what you originally authenticated to the postmaster.
>
> 2. "session user" = what you can SET if your real identity is a superuser.
>
> 3. "current user" = effective userid for permission checks.

We could have a Boolean variable "authenticated user is superuser" which
would serve as the permission to execute SET SESSION AUTHENTICATION, while
we would not actually be making the identity of the real/authenticated
user available (so as to not confuse things unnecessarily).

> if a setuid function
> does a CREATE, shouldn't the created object be owned by the setuid user?
> I'm not sure that I *want* to accept the SQL spec on this point.

Me neither.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

I proclaimed:

> Tom Lane writes:
>
> > 1. "real user" = what you originally authenticated to the postmaster.
> >
> > 2. "session user" = what you can SET if your real identity is a superuser.
> >
> > 3. "current user" = effective userid for permission checks.
>
> We could have a Boolean variable "authenticated user is superuser" which
> would serve as the permission to execute SET SESSION AUTHENTICATION, while
> we would not actually be making the identity of the real/authenticated
> user available (so as to not confuse things unnecessarily).

I have implemented this; it seems to do what we need:

$ ~/pg-install/bin/psql -U peter

peter=# set session authorization 'joeblow';
SET VARIABLE
peter=# create table foo (a int);
CREATE
peter=# \dt   List of relationsName  | Type  |  Owner
-------+-------+---------foo   | table | joeblowtest  | table | petertest2 | table | peter
(3 rows)

Libpq's PQuser() can no longer be trusted for up to date information, so
psql's prompt, if set up that way, may be wrong, but I'm not sure whether
this is worth worrying about.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

On Sat, Apr 21, 2001 at 05:43:02PM +0200, Peter Eisentraut wrote:

> I have implemented this; it seems to do what we need:
> 
> $ ~/pg-install/bin/psql -U peter
> 
> peter=# set session authorization 'joeblow';
> SET VARIABLE
> peter=# create table foo (a int);
> CREATE
> peter=# \dt
>     List of relations
>  Name  | Type  |  Owner
> -------+-------+---------
>  foo   | table | joeblow
>  test  | table | peter
>  test2 | table | peter
> (3 rows)

Great! With this feature is possible use persisten connection and 
on-the-fly changing actual user, right? It's very useful for example
web application that checking user privilege via SQL layout.

I have I question, what happen with this code:

(connected as superuser)
set session authorization 'userA';set session authorization 'userB';

IMHO it must be disable, right must be something like:
set session authorization 'userA';unset session authorization;        <-- switch back to superuser   set session
authorization'userB';
 

..like as on Linux:

# su - zakkr
$ id -u
1000
$ su - jmarek
Password:
su: Authentication failure
Sorry.
        Karel

-- Karel Zak  <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz

Karel Zak writes:

>  Great! With this feature is possible use persisten connection and
> on-the-fly changing actual user, right? It's very useful for example
> web application that checking user privilege via SQL layout.

A real persistent connection solution would require real session
management, especially the ability to reset configuration options made
during the previous session.

> (connected as superuser)
>
>  set session authorization 'userA';
>  set session authorization 'userB';
>
> IMHO it must be disable, right must be something like:
>
>  set session authorization 'userA';
>  unset session authorization;        <-- switch back to superuser
>  set session authorization 'userB';

You can't "unset" the session user, there must always be one because there
is nothing below it.

> ..like as on Linux:
>
> # su - zakkr
> $ id -u
> 1000
> $ su - jmarek
> Password:
> su: Authentication failure
> Sorry.

The difference here is that 'su' also starts a new session but set session
authorization changes the state of the current session.  So 'su' is
similar to

START SESSION;  -- Don't know if this is the syntax.
SET SESSION AUTHORIZATION 'xxx';

all in one command.  When and if we get real session management we will
probably have the ability to revert user identity changes like you
probably imagine.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter