Hi all,

Recently solving a design problem for a friend, an idea crossed my mind.

Is it possible for an established connection to backend, to switch user on the 
fly, if proper credentials are supplied?

If this can be done, it would avoid initialization penalty of a new conenction 
and many applications which does their own user management, can deligate the 
task to backend. 

Many applications are written in such a way that application always connects 
and operates as one user and does necessary access control. There are situatons 
where such a design is best available choice.

If it can switch connection on the fly, it will allow to have much finer 
control over database access.

That would help immensely for any applications that use connection pooling. 
Right now, if an app uses connection pooling, it has to go via a single 
application user and do all the things on it's own.

Besides I think this idea would be a smart implementation of what oracle called 
thin/virtual users.

Any thoughts?

Bye
 Shridhar

--
The First Rule of Program Optimization:	Don't do it.The Second Rule of Program 
Optimization (for experts only!):	Don't do it yet.		-- Michael Jackson


Mon, 27 Jan 2003 14:07:59 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Switching connection on the fly
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:
> Is it possible for an established connection to backend, to switch user on the 
> fly, if proper credentials are supplied?

Are you looking for SET SESSION AUTHORIZATION?

			regards, tom lane

Mon, 27 Jan 2003 09:16:47 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Switching connection on the fly 
On 27 Jan 2003 at 9:16, Tom Lane wrote:

> "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:
> > Is it possible for an established connection to backend, to switch user on the 
> > fly, if proper credentials are supplied?
> 
> Are you looking for SET SESSION AUTHORIZATION?

I went thr http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-set-session-
authorization.html to get what it is. I didn't have an idea of such thing.

Back to the topic, yes, pretty much except for few differences. 

1) It says 'The session user identifier may be changed only if the initial 
session user (the authenticated user) had the superuser privilege. Otherwise, 
the command is accepted only if it specifies the authenticated user name.'

That mean an ordinary user can not set session to any other authorised user. It 
is like running setuid program with input accessible to any user.

2) Where do I specify password? I mean I take a password and start a connection 
to database. But when it comes to switching connection, there is no password. 
Probably because only superuser can switch connection?

If there is a password clause there and if any user can switch to any user, 
then it is the thing I am looking for. Probably even excluding switching to 
superuser as a security measure.

But thanks for it. That is very close.


Bye
 Shridhar

--
And 1.1.81 is officially BugFree(tm), so if you receive any bug-reportson it, 
you know they are just evil lies."(By Linus Torvalds, 
Linus.Torvalds@cs.helsinki.fi)


Mon, 27 Jan 2003 19:55:46 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Re: Switching connection on the fly 

On Mon, 27 Jan 2003, Shridhar Daithankar wrote:

> I went thr http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-set-session-
> authorization.html to get what it is. I didn't have an idea of such thing.
>
> Back to the topic, yes, pretty much except for few differences.
>
> 1) It says 'The session user identifier may be changed only if the initial
> session user (the authenticated user) had the superuser privilege. Otherwise,
> the command is accepted only if it specifies the authenticated user name.'
>
> That mean an ordinary user can not set session to any other authorised user. It
> is like running setuid program with input accessible to any user.
>
> 2) Where do I specify password? I mean I take a password and start a connection
> to database. But when it comes to switching connection, there is no password.
> Probably because only superuser can switch connection?
>
> If there is a password clause there and if any user can switch to any user,
> then it is the thing I am looking for. Probably even excluding switching to
> superuser as a security measure.

I need this feature also. The problem with set session authorization is
that you can always change back so it's not that secure. Actually I wanted
to have a function that could augment the privileges of user if supplied
the right password, which in turn had nothing to do with original
password. I believe it could be easy to implement such a function in C.
But it could be better and easier to have pl/pgsql function that could set
the session authorization.

So, could it be made possible that pl/pgsql functions created by superuser
could "set session authorization" even when not called by superuser (or
user logged in as superuser)?

-- 
Antti Haapala


Mon, 27 Jan 2003 17:20:08 +0200 (EET)
Antti Haapala <antti.haapala@iki.fi>
Re: Switching connection on the fly 

On Mon, 27 Jan 2003, Antti Haapala wrote:

> I need this feature also. The problem with set session authorization is
> that you can always change back so it's not that secure. Actually I wanted
> to have a function that could augment the privileges of user if supplied
> the right password, which in turn has nothing to do with original
> password.

s/original/pg_shadow/ :)

-- 
Antti Haapala
+358 50 369 3535
ICQ: #177673735


Mon, 27 Jan 2003 17:24:00 +0200 (EET)
Antti Haapala <antti.haapala@iki.fi>
Re: Switching connection on the fly 