Date: Tue, 29 Apr 2003 14:03:14 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Sergey Holod <sss@radiocom.net.ua>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Making "SECURITY DEFINER" procedures..
In-Reply-To: <200304292358.35245.sss@radiocom.net.ua>
Message-ID: <20030429140220.I53158-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On Tue, 29 Apr 2003, Sergey Holod wrote:

> Just trying to make subj
>
> I made user "data", schema "data", several tables and small procedures on them
> in that schema and then a greater function (as "SECURITY DEFINER") in
> "public" schema which uses previous functions.
> I graned "EXECUTE" access to that function to "PUBLIC".
>
> When I try execute that function I get "ERROR:  data: permission denied", but
> when I add some notices between parts of function I see following:
>
> tst=> select new_session('sergey','mypassword');
>    NOTICE:  current user is data
>    NOTICE:  after delete
>    NOTICE:  after select
>    NOTICE:  after insert
>    NOTICE:  before return
>    ERROR:  data: permission denied
>
> so function executed with "data" privilegies, It deletes some data,
> inserts another and so on, It even runs till "return", but then I get error...
>
> It seems last error takes place during "auto commit" of transaction in which
> function executes..
>
> Just don't undestand what is happens..:(

What is the function and the schema of the tables involved?  I'd wonder
about triggers or foreign key constraints or something of that sort.