pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMaking "SECURITY DEFINER" procedures..
6+ messages / 3 participants
[nested] [flat]
* Making "SECURITY DEFINER" procedures..
@ 2003-04-29 20:53 Sergey Holod <sergey@radiocom.net.ua>
0 siblings, 0 replies; 6+ messages in thread
From: Sergey Holod @ 2003-04-29 20:53 UTC (permalink / raw)
To: pgsql-sql
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
SAH1-RIPE
^ permalink raw reply [nested|flat] 6+ messages in thread
* Making "SECURITY DEFINER" procedures..
@ 2003-04-29 20:58 Sergey Holod <sss@radiocom.net.ua>
2003-04-29 21:03 ` Re: Making "SECURITY DEFINER" procedures.. Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 6+ messages in thread
From: Sergey Holod @ 2003-04-29 20:58 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Making "SECURITY DEFINER" procedures..
2003-04-29 20:58 Making "SECURITY DEFINER" procedures.. Sergey Holod <sss@radiocom.net.ua>
@ 2003-04-29 21:03 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-04-29 21:16 ` Re: Making "SECURITY DEFINER" procedures.. Sergey Holod <sss@radiocom.net.ua>
0 siblings, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2003-04-29 21:03 UTC (permalink / raw)
To: Sergey Holod <sss@radiocom.net.ua>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Making "SECURITY DEFINER" procedures..
2003-04-29 20:58 Making "SECURITY DEFINER" procedures.. Sergey Holod <sss@radiocom.net.ua>
2003-04-29 21:03 ` Re: Making "SECURITY DEFINER" procedures.. Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2003-04-29 21:16 ` Sergey Holod <sss@radiocom.net.ua>
2003-04-29 22:10 ` Re: Making "SECURITY DEFINER" procedures.. Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 6+ messages in thread
From: Sergey Holod @ 2003-04-29 21:16 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Making "SECURITY DEFINER" procedures..
2003-04-29 20:58 Making "SECURITY DEFINER" procedures.. Sergey Holod <sss@radiocom.net.ua>
2003-04-29 21:03 ` Re: Making "SECURITY DEFINER" procedures.. Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-04-29 21:16 ` Re: Making "SECURITY DEFINER" procedures.. Sergey Holod <sss@radiocom.net.ua>
@ 2003-04-29 22:10 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-04-29 23:18 ` Re: Making "SECURITY DEFINER" procedures.. Sergey Holod <sss@radiocom.net.ua>
0 siblings, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2003-04-29 22:10 UTC (permalink / raw)
To: Sergey Holod <sss@radiocom.net.ua>; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Making "SECURITY DEFINER" procedures..
2003-04-29 20:58 Making "SECURITY DEFINER" procedures.. Sergey Holod <sss@radiocom.net.ua>
2003-04-29 21:03 ` Re: Making "SECURITY DEFINER" procedures.. Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-04-29 21:16 ` Re: Making "SECURITY DEFINER" procedures.. Sergey Holod <sss@radiocom.net.ua>
2003-04-29 22:10 ` Re: Making "SECURITY DEFINER" procedures.. Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2003-04-29 23:18 ` Sergey Holod <sss@radiocom.net.ua>
0 siblings, 0 replies; 6+ messages in thread
From: Sergey Holod @ 2003-04-29 23:18 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
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) NOT NULL 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 ManageUser table
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(''10 min'' 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'';
END IF;
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-04-29 23:18 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-29 20:53 Making "SECURITY DEFINER" procedures.. Sergey Holod <sergey@radiocom.net.ua>
2003-04-29 20:58 Making "SECURITY DEFINER" procedures.. Sergey Holod <sss@radiocom.net.ua>
2003-04-29 21:03 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-04-29 21:16 ` Sergey Holod <sss@radiocom.net.ua>
2003-04-29 22:10 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-04-29 23:18 ` Sergey Holod <sss@radiocom.net.ua>
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