Thread: Switching connection on the fly
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? ByeShridhar -- 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
"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
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. ByeShridhar -- 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)
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
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