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

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
>