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: permissiondenied", but SS> > when I add some notices
betweenparts of function I seefollowing: 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 geterror... SS> >
SS> > It seems last error takes place during "auto commit" of transaction inwhich 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
Defaultnow(),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