Re: Possible SET SESSION AUTHORIZATION bug

On my system I get permission denied when I switch to pgtest2 and select *
from pgtest_func.

Chris


-- create objects --
CREATE USER pgtest1 WITH ENCRYPTED PASSWORD 'pass' NOCREATEDB NOCREATEUSER;
CREATE USER pgtest2 WITH ENCRYPTED PASSWORD 'pass' NOCREATEDB NOCREATEUSER;
CREATE SCHEMA pgtest1 AUTHORIZATION pgtest1;
CREATE SCHEMA pgtest2 AUTHORIZATION pgtest2;

CREATE OR REPLACE FUNCTION pgtest_func() returns integer AS '
DECLARE
   in_test varchar;
BEGIN
   in_test := test FROM pgtest_table;
   RETURN 1;
END
' LANGUAGE 'plpgsql';


SET SESSION AUTHORIZATION pgtest1;
SET SEARCH_PATH TO pgtest1,public;
CREATE TABLE pgtest_table
(
        test varchar(24)
);
INSERT INTO pgtest_table(test) values('PGTEST1');


RESET SESSION AUTHORIZATION;
SET SESSION AUTHORIZATION pgtest2;
SET SEARCH_PATH TO pgtest2,public;
CREATE TABLE pgtest_table
(
        test varchar(24)
);
INSERT INTO pgtest_table(test) values('PGTEST2');

-- switch to pgtest1 and run pgtest_func --
RESET SESSION AUTHORIZATION;
SET SESSION AUTHORIZATION pgtest1;
SET SEARCH_PATH TO pgtest1,public;
SELECT * from pgtest_func();

-- switch to pgtest2 and run pgtest_func --
RESET SESSION AUTHORIZATION;
SET SESSION AUTHORIZATION pgtest2;
SET SEARCH_PATH TO pgtest2,public;
SELECT * from pgtest_func();


RESET SESSION AUTHORIZATION;
DROP USER pgtest1;
DROP USER pgtest2;
DROP SCHEMA pgtest1 CASCADE;
DROP SCHEMA pgtest2 CASCADE;
DROP FUNCTION pgtest_func();


----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Chris Ochs" <chris@paymentonline.com>
Cc: <pgsql-general@postgresql.org>
Sent: Monday, June 21, 2004 9:25 AM
Subject: Re: [GENERAL] Possible SET SESSION AUTHORIZATION bug


> "Chris Ochs" <chris@paymentonline.com> writes:
> > Ok this probably isn't a bug but a side affect of how functions are
cached.
> > Changing the function to use EXECUTE to perform the query works.  I
don't
> > know if this particular scenario was ever even though of before, or if
in
> > the future it would make sense to have the query planner not cache the
> > session user/current user?
>
> It doesn't cache that.  I'm not sure what's going on here ... could you
> provide a self-contained test script?
>
> regards, tom lane
>