Re: Making "SECURITY DEFINER" procedures..

On Wed, 30 Apr 2003, Sergey Holod wrote:

> On Wednesday 30 April 2003 00:03, you wrote:
> SS> On Tue, 29 Apr 2003, Sergey Holod wrote:
> SS>
> SS> > Just trying to make subj
> SS> >
> SS> > I made user "data", schema "data", several tables and small procedures
> SS> > on them  in that schema and then a greater function (as "SECURITY
> SS> >  DEFINER") in "public" schema which uses previous functions.
> SS> > I graned "EXECUTE" access to that function to "PUBLIC".
> SS> >
> SS> > When I try execute that function I get "ERROR:  data: permission
>  denied", but SS> > when I add some notices between parts of function I see
>  following: SS> >
> SS> > tst=> select new_session('sergey','mypassword');
> SS> >    NOTICE:  current user is data
> SS> >    NOTICE:  after delete
> SS> >    NOTICE:  after select
> SS> >    NOTICE:  after insert
> SS> >    NOTICE:  before return
> SS> >    ERROR:  data: permission denied
> SS> >
> SS> > so function executed with "data" privilegies, It deletes some data,
> SS> > inserts another and so on, It even runs till "return", but then I get
>  error... SS> >
> SS> > It seems last error takes place during "auto commit" of transaction in
>  which SS> > function executes..
> SS> >
> SS> > Just don't undestand what is happens..:(
> SS>
> SS> What is the function and the schema of the tables involved?  I'd wonder
> SS> about triggers or foreign key constraints or something of that sort.
>
> When I run it under "data" user It works good..:(
>
> Parts from schema + functions:

I made bogus gen_random_string and password functions and a bogus
ManageUser table and couldn't reproduce an error like the above with a
different user (one who can't read/write to the tables used), so I'd guess
it's from something that isn't being shown.  Are there any foreign keys
to ManageSession?