Обсуждение: Possible SET SESSION AUTHORIZATION bug
It doesn't currently seem possible to switch between different users using
SET SESSION AUTHORIZATION. If I log in as the superuser and switch to
another user that works, but if I then switch to a second user in succession
I get permission denied when I try to select from a table that the user does
have access to. Following is a cut and paste of two sessions showing what I
mean (with certain details masked out).
defender# psql db1-U pgsql
lcome to psql 7.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
paygw=# set search_path to poi,public;
SET
paygw=# set session authorization poi;
SET
paygw=> select * from get_accountsettings_by_username('poi');
settletype | fraudchecks | mer_id | username | cgipass | test_card |
testcard_status | network | dupchecks | gatewaypass | duptime |
itemtypes_allowed | debug_log
------------+-------------+--------+----------+---------+------------------+
-----------------+---------+-----------+-------------+-----------+----------
---------+-----------
AUTO | OFF | 9300 | poi | XXXX| XXXXXXXXXX |
vital | OFF | | 1 seconds | 1 | ON
(1 row)
paygw=> reset session authorization;
RESET
paygw=# set search_path to jdonline,public;
SET
paygw=# set session authorization jdonline;
SET
paygw=> select * from get_accountsettings_by_username('jdonline');
ERROR: permission denied for relation account_settings
CONTEXT: PL/pgSQL function "get_accountsettings_by_username" line 5 at
select into variables
paygw=> \q
-------------
psql db1 -U jdonline
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
paygw=> set search_path to jdonline,public;
SET
paygw=> select * from get_accountsettings_by_username('jdonline');
settletype | fraudchecks | mer_id | username | cgipass | test_card |
testcard_status | network | dupchecks | gatewaypass | duptime |
itemtypes_allowed | debug_log
------------+-------------+--------+----------+---------+------------------+
-----------------+---------+-----------+-------------+----------+-----------
--------+-----------
AUTO | OFF | 0502 | jdonline | XXXXXX|XXXX | fhms
| ON | | 3 months | 1 | ON
(1 row)
paygw=>
A followup on this. If I "select * from account_settings" directly it
works, but if I call the function "get_accountsettings_by_username" it
fails. So it seems like an issue with functions in particular. Following
is the function in question if that helps.
CREATE OR REPLACE FUNCTION get_accountsettings_by_username(varchar) RETURNS
acctsettingsrec AS
'
DECLARE
r acctsettingsrec%ROWTYPE;
in_username ALIAS FOR $1;
BEGIN
SELECT INTO r
settletype,fraudchecks,mer_id,username,cgipass,test_card,testcard_status,net
work,dupchecks,gatewaypass,duptime,item
types_allowed,debug_log from account_settings where username = in_username;
RETURN r;
END '
LANGUAGE 'plpgsql';
----- Original Message -----
From: "Chris Ochs" <chris@paymentonline.com>
To: <pgsql-general@postgresql.org>
Sent: Monday, June 21, 2004 8:07 AM
Subject: [GENERAL] Possible SET SESSION AUTHORIZATION bug
>
> It doesn't currently seem possible to switch between different users using
> SET SESSION AUTHORIZATION. If I log in as the superuser and switch to
> another user that works, but if I then switch to a second user in
succession
> I get permission denied when I try to select from a table that the user
does
> have access to. Following is a cut and paste of two sessions showing what
I
> mean (with certain details masked out).
>
> defender# psql db1-U pgsql
> lcome to psql 7.4, the PostgreSQL interactive terminal.
>
> Type: \copyright for distribution terms
> \h for help with SQL commands
> \? for help on internal slash commands
> \g or terminate with semicolon to execute query
> \q to quit
>
> paygw=# set search_path to poi,public;
> SET
> paygw=# set session authorization poi;
> SET
> paygw=> select * from get_accountsettings_by_username('poi');
> settletype | fraudchecks | mer_id | username | cgipass | test_card
|
> testcard_status | network | dupchecks | gatewaypass | duptime |
> itemtypes_allowed | debug_log
> ------------+-------------+--------+----------+---------+-----------------
-+
> -----------------+---------+-----------+-------------+-----------+--------
--
> ---------+-----------
> AUTO | OFF | 9300 | poi | XXXX| XXXXXXXXXX |
> vital | OFF | | 1 seconds | 1 | ON
> (1 row)
>
> paygw=> reset session authorization;
> RESET
> paygw=# set search_path to jdonline,public;
> SET
> paygw=# set session authorization jdonline;
> SET
> paygw=> select * from get_accountsettings_by_username('jdonline');
> ERROR: permission denied for relation account_settings
> CONTEXT: PL/pgSQL function "get_accountsettings_by_username" line 5 at
> select into variables
> paygw=> \q
>
> -------------
> psql db1 -U jdonline
>
> Type: \copyright for distribution terms
> \h for help with SQL commands
> \? for help on internal slash commands
> \g or terminate with semicolon to execute query
> \q to quit
>
> paygw=> set search_path to jdonline,public;
> SET
> paygw=> select * from get_accountsettings_by_username('jdonline');
> settletype | fraudchecks | mer_id | username | cgipass | test_card
|
> testcard_status | network | dupchecks | gatewaypass | duptime |
> itemtypes_allowed | debug_log
> ------------+-------------+--------+----------+---------+-----------------
-+
> -----------------+---------+-----------+-------------+----------+---------
--
> --------+-----------
> AUTO | OFF | 0502 | jdonline | XXXXXX|XXXX |
fhms
> | ON | | 3 months | 1 | ON
> (1 row)
>
> paygw=>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
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? I'll leave that to those that understand the
implications more than I do.
Chris
----- Original Message -----
From: "Chris Ochs" <chris@paymentonline.com>
To: <pgsql-general@postgresql.org>
Sent: Monday, June 21, 2004 8:25 AM
Subject: Re: [GENERAL] Possible SET SESSION AUTHORIZATION bug
> A followup on this. If I "select * from account_settings" directly it
> works, but if I call the function "get_accountsettings_by_username" it
> fails. So it seems like an issue with functions in particular. Following
> is the function in question if that helps.
>
> CREATE OR REPLACE FUNCTION get_accountsettings_by_username(varchar)
RETURNS
> acctsettingsrec AS
> '
> DECLARE
> r acctsettingsrec%ROWTYPE;
> in_username ALIAS FOR $1;
> BEGIN
> SELECT INTO r
>
settletype,fraudchecks,mer_id,username,cgipass,test_card,testcard_status,net
> work,dupchecks,gatewaypass,duptime,item
> types_allowed,debug_log from account_settings where username =
in_username;
> RETURN r;
> END '
> LANGUAGE 'plpgsql';
>
>
> ----- Original Message -----
> From: "Chris Ochs" <chris@paymentonline.com>
> To: <pgsql-general@postgresql.org>
> Sent: Monday, June 21, 2004 8:07 AM
> Subject: [GENERAL] Possible SET SESSION AUTHORIZATION bug
>
>
> >
> > It doesn't currently seem possible to switch between different users
using
> > SET SESSION AUTHORIZATION. If I log in as the superuser and switch to
> > another user that works, but if I then switch to a second user in
> succession
> > I get permission denied when I try to select from a table that the user
> does
> > have access to. Following is a cut and paste of two sessions showing
what
> I
> > mean (with certain details masked out).
> >
> > defender# psql db1-U pgsql
> > lcome to psql 7.4, the PostgreSQL interactive terminal.
> >
> > Type: \copyright for distribution terms
> > \h for help with SQL commands
> > \? for help on internal slash commands
> > \g or terminate with semicolon to execute query
> > \q to quit
> >
> > paygw=# set search_path to poi,public;
> > SET
> > paygw=# set session authorization poi;
> > SET
> > paygw=> select * from get_accountsettings_by_username('poi');
> > settletype | fraudchecks | mer_id | username | cgipass | test_card
> |
> > testcard_status | network | dupchecks | gatewaypass | duptime |
> > itemtypes_allowed | debug_log
>
> ------------+-------------+--------+----------+---------+-----------------
> -+
>
> -----------------+---------+-----------+-------------+-----------+--------
> --
> > ---------+-----------
> > AUTO | OFF | 9300 | poi | XXXX| XXXXXXXXXX
|
> > vital | OFF | | 1 seconds | 1 | ON
> > (1 row)
> >
> > paygw=> reset session authorization;
> > RESET
> > paygw=# set search_path to jdonline,public;
> > SET
> > paygw=# set session authorization jdonline;
> > SET
> > paygw=> select * from get_accountsettings_by_username('jdonline');
> > ERROR: permission denied for relation account_settings
> > CONTEXT: PL/pgSQL function "get_accountsettings_by_username" line 5 at
> > select into variables
> > paygw=> \q
> >
> > -------------
> > psql db1 -U jdonline
> >
> > Type: \copyright for distribution terms
> > \h for help with SQL commands
> > \? for help on internal slash commands
> > \g or terminate with semicolon to execute query
> > \q to quit
> >
> > paygw=> set search_path to jdonline,public;
> > SET
> > paygw=> select * from get_accountsettings_by_username('jdonline');
> > settletype | fraudchecks | mer_id | username | cgipass | test_card
> |
> > testcard_status | network | dupchecks | gatewaypass | duptime |
> > itemtypes_allowed | debug_log
>
> ------------+-------------+--------+----------+---------+-----------------
> -+
>
> -----------------+---------+-----------+-------------+----------+---------
> --
> > --------+-----------
> > AUTO | OFF | 0502 | jdonline | XXXXXX|XXXX |
> fhms
> > | ON | | 3 months | 1 | ON
> > (1 row)
> >
> > paygw=>
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to choose an index scan if
your
> > joining column's datatypes do not match
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
"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
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
>
"Chris Ochs" <chris@paymentonline.com> writes:
> On my system I get permission denied when I switch to pgtest2 and select *
> from pgtest_func.
What's being cached here is not the authorization, but the table
reference --- that is, the function's SELECT FROM pgtest_table
is resolved as pgtest1.pgtest_table the first time you run it,
and that remains true even though the schema search path is
different during the second call. The permissions failure occurs
because the correct user is trying to access the wrong table,
not wrong user and right table.
There's been some talk of trying to deal with this by associating
cached plans with particular schema search path strings, but no
one's done anything about it yet --- it looks expensive, and it
wouldn't prevent every failure of this sort anyway. Consider
for example that your search path is a,b,c, and on the first time
through "t1" is resolved as "b.t1". Now you create an "a.t1"
and call the function again. The search path is still the same,
so a cache based on checking that would not notice that it ought
to recompile the query.
regards, tom lane
Makes sense thanks for the explanation. Using FOR-IN-EXECUTE works just as well in our case. Every call to the function is likely to be a different schema, so caching wouldn't help us much anyways. The only negative is that the functions get uglier using EXECUTE:) Chris ----- 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 5:52 PM Subject: Re: [GENERAL] Possible SET SESSION AUTHORIZATION bug > "Chris Ochs" <chris@paymentonline.com> writes: > > On my system I get permission denied when I switch to pgtest2 and select * > > from pgtest_func. > > What's being cached here is not the authorization, but the table > reference --- that is, the function's SELECT FROM pgtest_table > is resolved as pgtest1.pgtest_table the first time you run it, > and that remains true even though the schema search path is > different during the second call. The permissions failure occurs > because the correct user is trying to access the wrong table, > not wrong user and right table. > > There's been some talk of trying to deal with this by associating > cached plans with particular schema search path strings, but no > one's done anything about it yet --- it looks expensive, and it > wouldn't prevent every failure of this sort anyway. Consider > for example that your search path is a,b,c, and on the first time > through "t1" is resolved as "b.t1". Now you create an "a.t1" > and call the function again. The search path is still the same, > so a cache based on checking that would not notice that it ought > to recompile the query. > > regards, tom lane >