Re: on connect/on disconnect - Mailing list pgsql-sql

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;

my intention is to re-implement this in C once I get some other logic
sorted out - if anyone can see a "better" way, please let me know!)

So, to cut to the short of it, I want to call this function at
connection set up and "cache" the results into a "system" table that
the user can't insert (or update), using an insert rule on a view:

SELECT DISTINCT parent FROM get_anc (mypid);

Using a non-temporary table means I can use indexes etc. properly and
do O(1) lookups to quickly determine if the user has the access they
need for other SQL trigger functions and rules to use.

What I really need is to be able to automatically clear the users
entries back out of the table when they disconnect, just in case.

Looks like there is no "ON DISCONNECT" style trigger capability, so I
might have to look at implementing something there as well.

> 
>>Purely session/connection-based temporary tables would also do what
>>I need, but temporary tables don't seem to be able to work that way.
> 
> 
> What's the exact problem with them?

Sorry - I got the "sense" of that statement the wrong way around.
Temporary tables do work fine, but I need to control inserts and
deletes (using views and rules) to prevent someone from giving
themselves access to stuff they shouldn't. I don't think I can
create a temporary table as a different user, or maybe I can with
a "setuid" function?

Cheers,

Bob Edwards.
> 
> Markus