Re: Making "SECURITY DEFINER" procedures..

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