pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

SECURITY DEFINER changes CURRENT_USER?
2+ messages / 2 participants
[nested] [flat]

* SECURITY DEFINER changes CURRENT_USER?
@ 2003-07-20 05:07  Chris Travers <chris@travelamericas.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Chris Travers @ 2003-07-20 05:07 UTC (permalink / raw)
  To: pgsql-sql

Hi all;

I found an unexpected behavior while trying to write a function to allow 
users to change their own passwords.  The function is as follows:

CREATE OR REPLACE FUNCTION change_password(VARCHAR)
RETURNS BOOL AS '
DECLARE
    username VARCHAR;
    CMD VARCHAR;
    password ALIAS FOR $1;
BEGIN
    SELECT INTO username CURRENT_USER;
    CMD := ''ALTER USER '' || username || '' WITH PASSWORD '';
    CMD := CMD || '''''''' || password || '''''''';   
    EXECUTE CMD;
    RETURN TRUE;
end;
' LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER

I would expect this to change the password of the user currently logged 
in but instead it changes MY password.  Evidently when a function is 
called which is set to SECURITY DEFINER, it changes the context of the 
current user.  The CURRENT_USER then returns the name of the definer 
rather than the invoker of the function.

So this being said-- are there any workarounds that don't allow anyone 
to change anyone else's password?

Best Wishes,
Chris Travers




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: SECURITY DEFINER changes CURRENT_USER?
@ 2003-07-20 06:19  Matthew Horoschun <mhoroschun@canprint.com.au>
  parent: Chris Travers <chris@travelamericas.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Matthew Horoschun @ 2003-07-20 06:19 UTC (permalink / raw)
  To: Chris Travers <chris@travelamericas.com>; +Cc: pgsql-sql

Hi Chris,

You want to use "session_user".

> I would expect this to change the password of the user currently 
> logged in but instead it changes MY password.  Evidently when a 
> function is called which is set to SECURITY DEFINER, it changes the 
> context of the current user.  The CURRENT_USER then returns the name 
> of the definer rather than the invoker of the function.
>
> So this being said-- are there any workarounds that don't allow anyone 
> to change anyone else's password?

Cheers




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2003-07-20 06:19 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-20 05:07 SECURITY DEFINER changes CURRENT_USER? Chris Travers <chris@travelamericas.com>
2003-07-20 06:19 ` Matthew Horoschun <mhoroschun@canprint.com.au>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox