Possible SET SESSION AUTHORIZATION bug

From: "Chris Ochs" <chris(at)paymentonline(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Possible SET SESSION AUTHORIZATION bug
Date: 2004-06-21 15:07:59
Message-ID: 001501c457a1$8a9f66b0$250a8b0a@chris
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Chris Ochs 2004-06-21 15:25:47 Re: Possible SET SESSION AUTHORIZATION bug
Previous Message Thomas F. O'Connell 2004-06-21 15:07:00 Re: pg_dump out of shared memory