Possible SET SESSION AUTHORIZATION bug

[Chris Ochs]

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

[Chris Ochs]

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,tes t_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" <ch***@paymentonline.com>
To: <pg***********@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]

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" <ch***@paymentonline.com>
To: <pg***********@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,tes t_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" <ch***@paymentonline.com>
To: <pg***********@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

"Chris Ochs" <ch***@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

"Chris Ochs" <ch***@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

---------------------------(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

[Chris Ochs]

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" <tg*@sss.pgh.pa.us>
To: "Chris Ochs" <ch***@paymentonline.com>
Cc: <pg***********@postgreSQL.org>
Sent: Monday, June 21, 2004 5:52 PM
Subject: Re: [GENERAL] Possible SET SESSION AUTHORIZATION bug

"Chris Ochs" <ch***@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings