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