Thread: Making "SECURITY DEFINER" procedures..
Hello!!!
Just trying to make subj
I made user "data", schema "data", several tables and small procedures on them
in that schema and then a greater function (as "SECURITY DEFINER") in
"public" schema which uses previous functions.
I graned "EXECUTE" access to that function to "PUBLIC".
When I try execute that function I get "ERROR: data: permission denied", but
when I add some notices between parts of function I see following:
tst=> select new_session('sergey','mypassword'); NOTICE: current user is data NOTICE: after delete NOTICE: after
select NOTICE: after insert NOTICE: before return ERROR: data: permission denied
so function executed with "data" privilegies, It deletes some data,
inserts another and so on, It even runs till "return", but then I get error...
It seems last error takes place during "auto commit" of transaction in which
function executes..
Just don't undestand what is happens..:(
--
With Best Regards,
Sergey Holod
On Tue, 29 Apr 2003, Sergey Holod wrote:
> Just trying to make subj
>
> I made user "data", schema "data", several tables and small procedures on them
> in that schema and then a greater function (as "SECURITY DEFINER") in
> "public" schema which uses previous functions.
> I graned "EXECUTE" access to that function to "PUBLIC".
>
> When I try execute that function I get "ERROR: data: permission denied", but
> when I add some notices between parts of function I see following:
>
> tst=> select new_session('sergey','mypassword');
> NOTICE: current user is data
> NOTICE: after delete
> NOTICE: after select
> NOTICE: after insert
> NOTICE: before return
> ERROR: data: permission denied
>
> so function executed with "data" privilegies, It deletes some data,
> inserts another and so on, It even runs till "return", but then I get error...
>
> It seems last error takes place during "auto commit" of transaction in which
> function executes..
>
> Just don't undestand what is happens..:(
What is the function and the schema of the tables involved? I'd wonder
about triggers or foreign key constraints or something of that sort.
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
On Wed, 30 Apr 2003, Sergey Holod wrote:
> 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:
I made bogus gen_random_string and password functions and a bogus
ManageUser table and couldn't reproduce an error like the above with a
different user (one who can't read/write to the tables used), so I'd guess
it's from something that isn't being shown. Are there any foreign keys
to ManageSession?
SS> I made bogus gen_random_string and password functions and a bogus
SS> ManageUser table and couldn't reproduce an error like the above with a
SS> different user (one who can't read/write to the tables used), so I'd guess
SS> it's from something that isn't being shown. Are there any foreign keys
SS> to ManageSession?
GRANT ALL ON SCHEMA public TO "data";
CREATE SCHEMA data AUTHORIZATION "data";
\connect - "data"
SET search_path = data;
..
Create table ManageUser
( id Serial NOT NULL, Name Varchar(20) NOT NULL UNIQUE , Passwd bytea, Birthday timestamp(0)
NOTNULL Default now(), ManageGroupId integer NOT NULL, -- FOREIGN KEY
TO ManageGroup OnlyCustomerId integer NULL , --
FOREIGN KEY TO Customer memo Varchar(500) NULL ,primary key (id)
);
..
Create table ManageSession
( id Serial NOT NULL, UserId integer NOT NULL, -- FOREIGN KEY TO
ManageUsertable Key bytea NOT NULL, Birthday timestamp(0) NOT NULL Default now(),primary key (id)
);
Alter table ManageSession add foreign key (UserId) references ManageUser (id) on update cascade on delete cascade ;
..
set search_path=public;
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 data.ManageSession WHERE (now() - Birthday) >
CAST(''10min'' AS INTERVAL);
raise notice ''after delete''; SELECT id INTO u_id FROM data.ManageUser WHERE Name = u_login AND
Passwd = password(u_passwd);
raise notice ''after select - %'',u_id; IF u_id IS NOT NULL THEN u_key := gen_random_string(20);
INSERT INTO data.ManageSession(UserId,Key) VALUES (u_id,
password(u_key));
raise notice ''after insert - %'',u_key; ELSE RAISE EXCEPTION ''Wrong login or password'';
ENDIF;
raise notice ''before return - %'',u_key; RETURN u_key;
END;
' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
...
So thera are several foreign constraints. But:
$psql -U rcbilling billing
billing=> select new_session('sergey','password');
NOTICE: current user is data
NOTICE: after delete
NOTICE: after select - 8
NOTICE: after insert - KMp7ciFzJAL10Xxqft9O
NOTICE: before return - KMp7ciFzJAL10Xxqft9O
ERROR: data: permission denied
$psql -U data billing
billing=> select new_session('sergey','password');
NOTICE: current user is data
NOTICE: after delete
NOTICE: after select - 8
NOTICE: after insert - n7c1gAqPB0WuFwCEapy4
NOTICE: before return - n7c1gAqPB0WuFwCEapy4 new_session
----------------------n7c1gAqPB0WuFwCEapy4
(1 row)
--
With Best Regards,
Sergey Holod
On Wednesday 30 April 2003 02:18, you wrote:
SH> $psql -U rcbilling billing
SH> billing=> select new_session('sergey','password');
SH> NOTICE: current user is data
SH> NOTICE: after delete
SH> NOTICE: after select - 8
SH> NOTICE: after insert - KMp7ciFzJAL10Xxqft9O
SH> NOTICE: before return - KMp7ciFzJAL10Xxqft9O
SH> ERROR: data: permission denied
GRANT USAGE ON schema data TO rcbilling;
solved problem:
$ psql -U rcbilling billing
billing=> select new_session('sergey','gfhjkm');
NOTICE: current user is data
NOTICE: after delete
NOTICE: after select - 8
NOTICE: after insert - H51udQO0sxt68fA3BLMY
NOTICE: before return - H51udQO0sxt68fA3BLMY new_session
----------------------H51udQO0sxt68fA3BLMY
(1 row)
Don't fully undestand why I need that, but It works..
MANY THANKS for help..
--
With Best Regards,
Sergey Holod
Sergey Holod <sss@radiocom.net.ua> writes:
> GRANT USAGE ON schema data TO rcbilling;
> Don't fully undestand why I need that, but It works..
Okay, that's the same bug we found just a couple days ago. Schema-usage
errors are checked at query parse time, not execution time, and the
foreign-key triggers weren't being careful to set the right context for
those checks. There's a fix in place for 7.3.3.
regards, tom lane
On Wed, 30 Apr 2003, Tom Lane wrote: > Sergey Holod <sss@radiocom.net.ua> writes: > > GRANT USAGE ON schema data TO rcbilling; > > Don't fully undestand why I need that, but It works.. > > Okay, that's the same bug we found just a couple days ago. Schema-usage > errors are checked at query parse time, not execution time, and the > foreign-key triggers weren't being careful to set the right context for > those checks. There's a fix in place for 7.3.3. As a more general question, as what user should triggers from an action inside a security definer function be run as? The fk triggers will work after changing the place the permissions are changed, but afaics user after triggers won't unless the trigger function is also security definer.