Re: Possible SET SESSION AUTHORIZATION bug

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(at)paymentonline(dot)com>
To: <pgsql-general(at)postgresql(dot)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(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
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>