X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id F0315475F0D for ; Tue, 29 Apr 2003 18:10:49 -0400 (EDT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 9FE3A475ADE for ; Tue, 29 Apr 2003 18:10:44 -0400 (EDT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 905C0D61C; Tue, 29 Apr 2003 15:10:45 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 7BCFB5C0A; Tue, 29 Apr 2003 15:10:45 -0700 (PDT) Date: Tue, 29 Apr 2003 15:10:45 -0700 (PDT) From: Stephan Szabo To: Sergey Holod Cc: Subject: Re: Making "SECURITY DEFINER" procedures.. In-Reply-To: <200304300016.23870.sss@radiocom.net.ua> Message-ID: <20030429150829.G54888-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Spam-Status: No, hits=-26.0 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REPLY_WITH_QUOTES autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/433 X-Sequence-Number: 13134 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?