X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 1BE099FA2D5 for ; Tue, 12 Sep 2006 15:21:56 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 75023-04 for ; Tue, 12 Sep 2006 15:21:37 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.182]) by postgresql.org (Postfix) with ESMTP id 288C89FA1A7 for ; Tue, 12 Sep 2006 15:21:36 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id d80so2744891pyd for ; Tue, 12 Sep 2006 11:21:36 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=G9YFUj6ycTQrxKrjRtEboi6C+Rdw1OB1WMsUMmqE/FZUzqwbyuLzFSn5NcJDbG3h2tFK7OD663Wsfna9fCNcTha7jurSsNvIwrq5NxEluYUr8+jycXuBAvzQrKo6YQauG5jMK1App5n//hGpe/XO2A9YYX8c2Tmx4aCo4gr1wG4= Received: by 10.35.93.1 with SMTP id v1mr11360231pyl; Tue, 12 Sep 2006 11:21:35 -0700 (PDT) Received: by 10.35.80.9 with HTTP; Tue, 12 Sep 2006 11:21:35 -0700 (PDT) Message-ID: Date: Tue, 12 Sep 2006 13:21:35 -0500 From: "Aaron Bono" To: "Robert Edwards" Subject: Re: on connect/on disconnect Cc: "Markus Schaber" , pgsql-sql@postgresql.org In-Reply-To: <450602C0.7000002@cs.anu.edu.au> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4266_22943126.1158085295700" References: <4504B474.9010801@cs.anu.edu.au> <45053B0F.5020109@logix-tt.com> <450602C0.7000002@cs.anu.edu.au> X-Google-Sender-Auth: e4ec22a3a2ce3f0f X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.437 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/82 X-Sequence-Number: 26293 ------=_Part_4266_22943126.1158085295700 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 9/11/06, Robert Edwards wrote: > > Markus Schaber wrote: > > Hi, Robert, > > > > Robert Edwards wrote: > > > >>(this is my first post to this list...) > > > > > > Welcome here. :-) > > > > > >>I am wondering if Postgres, and/or SQL in general, has a facility to > >>run a function at connection set-up time (after a successful connection > >>attempt) and/or at session completion (or disconnect)? > > > > > > Most JDBC connection pooling implementations will do that, at least for > > connection and session setup. (JBoss is one of them.) > > > > > >>I want to pre-populate a table (actually an INSERT rule on a view) > >>with some user-specific data that is unlikely to change during the > >>session and which is "difficult" to process (ie. affects performance > >>to do it too often). > > > > > > The problem here is that the INSERT rule might be globally visible to > > other, concurrent users on the database. > > > > Indeed it is, but the sole reason to use a rule (instead of a straight > INSERT) is that it qualifies the INSERT against the current user. > > > Could you explain what exactly you want to achieve, may be we find a > > better way to do the whole thing. > > > Basically, I have a heirarchical arrangement of users in "roles" (almost > the same as the 8.1 user/group/role mechanism, but in "PUBLIC" schema > space, and with various triggers etc. in play). The access controls > apply conditions based on which "roles" (groups) the current user is > a member of (and these users never have "super-user" privilege, so the > SET SESSION AUTHORIZATION mechanism does not apply here). The heirarchy > is not a "tree" structure - any role can belong to any number of other > roles (have many parents), so it is a Digraph (directed graph). > > I have some plpgsql functions, one of which is used to determine which > roles a user is in, but it is necessarily recursively called, which > means it runs in non-deterministic time. > > (Just for completeness, I'll include that function here: > create or replace function get_anc () returns setof member as ' > declare > rMem member; > begin > for rMem in select * from member where child = $1 loop > return next rMem; > for rMem in select * from get_anc (rMem.parent) loop > return next rMem; > end loop; > end loop; > return; > end; > ' language plpgsql; If your membership (member table) does not change very often, I would recommend adding an insert/update trigger to that table which updates a flattened version of these results (basically a materialized view). Then you don't need to run this function unless the membership changes and only once per membership change. Then you authorize the person against that flattened table rather than look at the member table. Then you wouldn't need to run this upon every log in. I do something like this (though simpler) and have the application get the permissions and cache them. It sounds like you are probably trying to put your permission checking in the database. You might want to consider using a session based temp table and run a different function that first checks the temp table, if the table is empty it runs the function above and populates the temp table, if the table is not empty it uses the temp table. This is a form of lazy instantiation and would not require you to have connect/disconnect triggers. ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_4266_22943126.1158085295700 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 9/11/06, Robert Edwards <bob@cs.anu.edu.au> wrote:
Markus Schaber wrote:
> Hi, Robert,
>
> Robert Edwards wrote:
>
>>(this is my first post to this list...)
>
>
> Welcome here. :-)
>
>
>>I am wondering if Postgres, and/or SQL in general, has a facility to
>>run a function at connection set-up time (after a successful connection
>>attempt) and/or at session completion (or disconnect)?
>
>
> Most JDBC connection pooling implementations will do that, at least for
> connection and session setup. (JBoss is one of them.)
>
>
>>I want to pre-populate a table (actually an INSERT rule on a view)
>>with some user-specific data that is unlikely to change during the
>>session and which is "difficult" to process (ie. affects performance
>>to do it too often).
>
>
> The problem here is that the INSERT rule might be globally visible to
> other, concurrent users on the database.
>

Indeed it is, but the sole reason to use a rule (instead of a straight
INSERT) is that it qualifies the INSERT against the current user.

> Could you explain what exactly you want to achieve, may be we find a
> better way to do the whole thing.
>
Basically, I have a heirarchical arrangement of users in "roles" (almost
the same as the 8.1 user/group/role mechanism, but in "PUBLIC" schema
space, and with various triggers etc. in play). The access controls
apply conditions based on which "roles" (groups) the current user is
a member of (and these users never have "super-user" privilege, so the
SET SESSION AUTHORIZATION mechanism does not apply here). The heirarchy
is not a "tree" structure - any role can belong to any number of other
roles (have many parents), so it is a Digraph (directed graph).

I have some plpgsql functions, one of which is used to determine which
roles a user is in, but it is necessarily recursively called, which
means it runs in non-deterministic time.

(Just for completeness, I'll include that function here:
create or replace function get_anc () returns setof member as '
declare
   rMem member;
begin
   for rMem in select * from member where child = $1 loop
     return next rMem;
     for rMem in select * from get_anc ( rMem.parent) loop
       return next rMem;
     end loop;
   end loop;
   return;
end;
' language plpgsql;

 
If your membership (member table) does not change very often, I would recommend adding an insert/update trigger to that table which updates a flattened version of these results (basically a materialized view).  Then you don't need to run this function unless the membership changes and only once per membership change.  Then you authorize the person against that flattened table rather than look at the member table.  Then you wouldn't need to run this upon every log in.

I do something like this (though simpler) and have the application get the permissions and cache them.  It sounds like you are probably trying to put your permission checking in the database.  You might want to consider using a session based temp table and run a different function that first checks the temp table, if the table is empty it runs the function above and populates the temp table, if the table is not empty it uses the temp table.  This is a form of lazy instantiation and would not require you to have connect/disconnect triggers.

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
================================================================== ------=_Part_4266_22943126.1158085295700--