From: Sergey Holod <sss@radiocom.net.ua>
Organization: RadioCom ISP
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Subject: Re: Making "SECURITY DEFINER" procedures..
Date: Wed, 30 Apr 2003 00:16:23 +0300
User-Agent: KMail/1.5
References: <20030429140220.I53158-100000@megazone23.bigpanda.com>
In-Reply-To: <20030429140220.I53158-100000@megazone23.bigpanda.com>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200304300016.23870.sss@radiocom.net.ua>

On Wednesday 30 April 2003 00:03, you wrote:
SS> On Tue, 29 Apr 2003, Sergey Holod wrote:
SS>
SS> > Just trying to make subj
SS> >
SS> > I made user "data", schema "data", several tables and small procedures 
SS> > on them  in that schema and then a greater function (as "SECURITY
SS> >  DEFINER") in "public" schema which uses previous functions.
SS> > I graned "EXECUTE" access to that function to "PUBLIC".
SS> >
SS> > When I try execute that function I get "ERROR:  data: permission
 denied", but SS> > when I add some notices between parts of function I see
 following: SS> >
SS> > tst=> select new_session('sergey','mypassword');
SS> >    NOTICE:  current user is data
SS> >    NOTICE:  after delete
SS> >    NOTICE:  after select
SS> >    NOTICE:  after insert
SS> >    NOTICE:  before return
SS> >    ERROR:  data: permission denied
SS> >
SS> > so function executed with "data" privilegies, It deletes some data,
SS> > inserts another and so on, It even runs till "return", but then I get
 error... SS> >
SS> > It seems last error takes place during "auto commit" of transaction in
 which SS> > function executes..
SS> >
SS> > Just don't undestand what is happens..:(
SS>
SS> What is the function and the schema of the tables involved?  I'd wonder
SS> about triggers or foreign key constraints or something of that sort.

When I run it under "data" user It works good..:(

Parts from schema + functions:

Create table  ManageSession
(
        id Serial NOT NULL,
        UserId integer NOT NULL,
        Key bytea,
        Birthday timestamp(0) NOT NULL Default now(),
 primary key (id)
);

CREATE OR REPLACE FUNCTION new_session (character varying, character varying) 
RETURNS character varying
    AS 'DECLARE
        u_login ALIAS FOR $1;
        u_passwd ALIAS FOR $2;
        u_id INTEGER;
        u_key VARCHAR;
        dbg VARCHAR;
BEGIN
select current_user into dbg;
raise notice ''current user is %'', dbg;

        DELETE FROM ManageSession WHERE
                (now()  - Birthday) > CAST(''10 min'' AS INTERVAL);

raise notice ''after delete'';

        SELECT id INTO u_id FROM ManageUser WHERE Name = u_login AND Passwd = 
password(u_passwd);

raise notice ''after select'';


        IF u_id IS NOT NULL THEN
                u_key := gen_random_string(20);
                INSERT INTO ManageSession(UserId,Key) VALUES (u_id, 
password(u_key));

raise notice ''after insert'';

        ELSE
                RAISE EXCEPTION ''Wrong login or password'';
        END IF;

raise notice ''before return'';

        RETURN u_key;
END;
' LANGUAGE plpgsql SECURITY DEFINER;


-- 
With Best Regards,
Sergey Holod