Re: Possible SET SESSION AUTHORIZATION bug - Mailing list pgsql-general

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
>