Re: 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(at)paymentonline(dot)com>
To: <pgsql-general(at)postgresql(dot)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
>