From: Sergey Holod <sss@radiocom.net.ua>
Organization: RadioCom ISP
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Subject: Re: Making "SECURITY DEFINER" procedures..
Date: Wed, 30 Apr 2003 02:18:08 +0300
User-Agent: KMail/1.5
References: <20030429150829.G54888-100000@megazone23.bigpanda.com>
In-Reply-To: <20030429150829.G54888-100000@megazone23.bigpanda.com>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200304300218.08129.sss@radiocom.net.ua>

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