Thread: on connect/on disconnect

on connect/on disconnect

From
Robert Edwards
Date:
(this is my first post to this list...)

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)?

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).

Clearly, I can do this manually anyway as the first operation after
a connection is established, but I would like also to clear it out
when the session terminates (so, I guess I am really interested in
a "trigger" of some sort on end-of-session).

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.

Cheers,

Bob Edwards.


Re: on connect/on disconnect

From
"Aaron Bono"
Date:
On 9/10/06, Robert Edwards <bob@cs.anu.edu.au> wrote:
(this is my first post to this list...)

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)?

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).

Clearly, I can do this manually anyway as the first operation after
a connection is established, but I would like also to clear it out
when the session terminates (so, I guess I am really interested in
a "trigger" of some sort on end-of-session).

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 kind of operation are you wanting to do?  Would it work if an application like a web site used connection pooling - thus sharing the session across application users and rarely if ever connecting/disconnecting?

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

Re: on connect/on disconnect

From
Robert Edwards
Date:
Hi Aaron,

Thanks for your response. I guess I wasn't clear about "users". I am
referring to database users, not application users (although they
are the same in my application in any case - that is, each application
user is known to the database as a different database user).

As I understand it, connection pooling, as used by a web app, still
needs to connect "per-database-user" (and usually this is the same
"web-server" user). If the web app is connecting to the database
server as different database users, then different connections would
be set up. If the number of open connections exceeds the number
allowed to the database server, then older unused connections would
be terminated to allow new ones to be created. Is this correct?

Or is it possible, over the same connection, to change the database
user? My understanding of the frontend/backend protocol is that this
is not allowed.

Anyway, I still need to know if running functions during connection
setup and tear-down, or change of user, is possible or not.

Cheers,

Bob Edwards.

Aaron Bono wrote:
> On 9/10/06, *Robert Edwards* <bob@cs.anu.edu.au 
> <mailto:bob@cs.anu.edu.au>> wrote:
> 
>     (this is my first post to this list...)
> 
>     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)?
> 
>     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).
> 
>     Clearly, I can do this manually anyway as the first operation after
>     a connection is established, but I would like also to clear it out
>     when the session terminates (so, I guess I am really interested in
>     a "trigger" of some sort on end-of-session).
> 
>     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 kind of operation are you wanting to do?  Would it work if an 
> application like a web site used connection pooling - thus sharing the 
> session across application users and rarely if ever 
> connecting/disconnecting?
> 
> ==================================================================
>    Aaron Bono
>    Aranya Software Technologies, Inc.
>    http://www.aranya.com
>    http://codeelixir.com
> ==================================================================



Re: on connect/on disconnect

From
"Aaron Bono"
Date:
On 9/10/06, Robert Edwards <bob@cs.anu.edu.au> wrote:

As I understand it, connection pooling, as used by a web app, still
needs to connect "per-database-user" (and usually this is the same
"web-server" user). If the web app is connecting to the database
server as different database users, then different connections would
be set up. If the number of open connections exceeds the number
allowed to the database server, then older unused connections would
be terminated to allow new ones to be created. Is this correct?

Don't mean to get off topic but to answer your question...

Typically the web app has a single log in user.  You don't  have the user log into the database.  Connection pooling typically does not terminate connections to the database unless it decides to shrink the connection pool do to lack of use.  It doesn't disconnect when it exceeds the number of allowed users - it reuses the connections as much as possible to reduce the overhead of making connections.

Or is it possible, over the same connection, to change the database
user? My understanding of the frontend/backend protocol is that this
is not allowed.

I could not find this in the documentation - not sure.

Anyway, I still need to know if running functions during connection
setup and tear-down, or change of user, is possible or not.

 
If there is a table that keeps track of connection/disconnection you could put a trigger on that but I cannot find anything about this in the documentation.

Guess we will need feedback from someone more knowledgable than I...

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

Re: on connect/on disconnect

From
Markus Schaber
Date:
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.

Could you explain what exactly you want to achieve, may be we find a
better way to do the whole thing.

> 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?

Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org


Re: on connect/on disconnect

From
Markus Schaber
Date:
Hi, Robert,

Robert Edwards wrote:

> Or is it possible, over the same connection, to change the database
> user? My understanding of the frontend/backend protocol is that this
> is not allowed.

Not on protocol level, but one level higher.
 SET SESSION AUTHORIZATION <user>;

pg_dump uses this frequently. See the docs for more details.
http://www.postgresql.org/docs/8.1/interactive/sql-set-session-authorization.html

HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org


Re: on connect/on disconnect

From
Jorge Godoy
Date:
Robert Edwards <bob@cs.anu.edu.au> writes:

> Or is it possible, over the same connection, to change the database
> user? My understanding of the frontend/backend protocol is that this
> is not allowed.

It is possible to change users.  

SET SESSION AUTHORIZATION <username>;

http://www.postgresql.org/docs/8.1/interactive/sql-set-session-authorization.html


Also just for completeness: http://www.securityfocus.com/bid/16650/info


-- 
Jorge Godoy      <jgodoy@gmail.com>


Re: on connect/on disconnect

From
Robert Edwards
Date:
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



Re: on connect/on disconnect

From
"Aaron Bono"
Date:
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
==================================================================