[GENERAL] PLPGSQL 'SET SESSION ROLE' problems ..
Hi, Is it possible to change the current role to another arbitrary role using a PLPGSQL function? In essence I've a function "authorise" created by the db superuser with 'SECURITY DEFINER' specificied. However from within plpgsql the 'superuser' attribute isn't honored when trying to change roles (ie: the non 'superuser' rules for role change must be honoured). Postgresql version 8.2.3 Is this a bug? .. If not, how do I achieve an 'adhoc' change of role from within the "rules system"? tnx, -greg -- Dr. Greg Wickham Program Manager, e-Research Phone: +61 2 6222 3552 AARNet Mob: +61 4 0785 4566 CSIRO, Building 9, Banks St Email: [EMAIL PROTECTED] Yarralumla ACT 2600 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PLPGSQL 'SET SESSION ROLE' problems ..
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> To: Greg Wickham
> > However from within plpgsql the 'superuser' attribute isn't honored
> > when trying to change roles
>
> IIRC we deliberately restrict inheritance of superuser
> status. If you want to argue there's a bug here you need to
> provide specific details of what you did, as well as an
> argument why superuser status should be more laxly controlled.
Hi Tom,
In the example there is a connection to a DB using a user with virtually
no privledges ('guest'). As part of the rules system changes to the
current 'role' of this connection should only succeed when the rules
system is happy.
so for example (Hopefully this is easy to follow):
1/ DB connection as role 'guest'
2/ sql: select authorise( 'user1', 'password' )
function authorise changes current role to 'user1' and returns
session key
3/ all SQL commands can be checked (grant / allow) against user 'user1'
example 2. Using the 'session key'
1/ DB connection as role 'guest'
2/ sql: select authorise( 'session key' )
function authorise looks up the session key and assigns the
current role to 'user1'.
example 3. Failure
1/ DB connection as role 'guest'
2/ sql: select authorise ('user1', 'wrong password' )
function authorise would determine that the password was wrong
and would leave
(or assign) the current role to 'guest'.
I've read the 'set role' and 'set session' a few times but it's not what
I'm after. I really want the role change mechanism to be wrapped in a
function to do the appropriate sanity checks etc before it succeeds.
I'm open to suggestions as to how else to achive this thou.
tnx,
-greg
> regards, tom lane
>
--
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] PLPGSQL 'SET SESSION ROLE' problems ..
Hi, Is it possible to change the current role to another arbitrary role using a PLPGSQL function? In essence I've a function "authorise" created by the db superuser with 'SECURITY DEFINER' specificied. However from within plpgsql the 'superuser' attribute isn't honored when trying to change roles (ie: the non 'superuser' rules for role change must be honoured). Is this a bug? tnx, -greg -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Changing session ownership in a web app (or how to peel an onion)
Hi All, Earlier this year there was a discussion between Tom and Ezra regarding extending 'set session authorization' to facilitate changing the identity of a connection. A synopsis of the discussion is that Tom felt this was bad and the web application should have more responsibility for handling session security. I need to implement some session based authentication / authorization and would like to learn from others experience before embarking too far down this path. Some constraints: 1/ I'm not keen on embedding secret passwords in a web config file but if I have to I will (*sigh*). 2/ The user names used in the authentication credentials (from the perspective of the user) are _NOT_ the same as those internally used in postgres. (Postgres has strict limitations on usernames which make using them for users impractical.) 3/ I want to use cookies and session based authentication (rather than continually use a username password tuple for each request). (But then you could rationalize that the username / password could be reversed out of the session key so this may be a mute point - it will be over a secure connection). To meet these constraints it would appear necessary to: 1/ Run an external mapping of human usernames to postgres user names (or burn a connect / disconnect cycle to the db). 2/ Connect using the credentials (mapped username) and provided password 3/ Work as necessary (using connected uid) 4/ Disconnect Is this the best (or only) technique? If any one has any suggestions or experience in this then I'd appreciate hearing them. Thanks in advance, -Greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Changing session ownership in a web app (or how to peel an onion)
Hi Tom, I didn't know that double quotes around user names permitted much more variety (of user names). As always - many many thanks. -Greg | -Original Message- | From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom | Lane | Sent: Saturday, 16 October 2004 3:14 AM | To: Greg Wickham | Cc: [EMAIL PROTECTED] | Subject: Re: [GENERAL] Changing session ownership in a web app (or how to peel an onion) | | "Greg Wickham" <[EMAIL PROTECTED]> writes: | > ... (Postgres has strict limitations on usernames which make using | > them for users impractical.) | | Er, which "strict limitations" would those be? You can put almost | anything into a double-quoted identifier. | | regards, tom lane | | ---(end of broadcast)--- | TIP 3: if posting/reading through Usenet, please send an appropriate | subscribe-nomail command to [EMAIL PROTECTED] so that your | message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
