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 02:18:08 +0300
Message-ID: <200304300218.08129.sss@radiocom.net.ua> (raw)
In-Reply-To: <20030429150829.G54888-100000@megazone23.bigpanda.com>
References: <20030429150829.G54888-100000@megazone23.bigpanda.com>
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
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: <200304300218.08129.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