From: Sergey Holod <sss@radiocom.net.ua>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Making "SECURITY DEFINER" procedures..
Date: Wed, 30 Apr 2003 00:16:23 +0300
Message-ID: <200304300016.23870.sss@radiocom.net.ua> (raw)
In-Reply-To: <20030429140220.I53158-100000@megazone23.bigpanda.com>
References: <20030429140220.I53158-100000@megazone23.bigpanda.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sss@radiocom.net.ua, sszabo@megazone23.bigpanda.com
Subject: Re: Making "SECURITY DEFINER" procedures..
In-Reply-To: <200304300016.23870.sss@radiocom.net.ua>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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