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(at)sss(dot)pgh(dot)pa(dot)us>
To: "Chris Ochs" <chris(at)paymentonline(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Monday, June 21, 2004 9:25 AM
Subject: Re: [GENERAL] Possible SET SESSION AUTHORIZATION bug

> "Chris Ochs" <chris(at)paymentonline(dot)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
>